ara/implement-copy-method

[anthonyra]

The pull request implements the COPY method for sending data to postgres. It also has the needed changes to use this copy method with transaction_actors.

[anthonyra]

When adding a block to the database the ETL uses BEGIN to batch all of the transactions into one. The COPY method however appears (I haven't seen any examples or documentation proving otherwise) to not be compatible with BEGIN. Meaning the COPY is initiated before the COMMIT is called on BEGIN. This creates a race condition that causes the transaction to be ROLLBACK due to foreign_key constraints.

It however works if the copies are after the initial COMMIT of a block. The copies are atomic in nature so if one fails we don't have a partial insert of data. With this in mind we could monitor said copies and ensure they are committed to the DB. If they fail we could crash like what is done with BEGIN.

• [ ] It might make sense to trigger a delete of the initial block transaction (?S_INSERT_BLOCK and ?S_INSERT_BLOCK_SIG) on failed copy to normalize the data in the DB.
• [ ] When it comes to normalizing the database depending on the amount of data added at time of a failed COPY results in a very long deletion process if you simply delete the blocks row and let the foreign_keys do the work. This process could be sped up if the inserts had "on conflict do nothing" and set session_replication_role to replica; prior to blocks row deletion and set session_replication_role to default; after deletion will re-activate triggers based on the foreign_key constraints. This will force the ETL to think it needs to load the failed block completely. When it encounters existing data it skips it and adds in all the missing data.

These options have compromises though, so the real question is "does the performance improvements warrant such compromises"...

Some performance numbers with using COPY for the transactions and transaction_actors tables;

**Normal Block**
be_db_follower:maybe_log_duration:123 be_db_block took 6895 ms
be_db_block:q_copy_transactions:247 Txns copy list took 1507 ms. CopyLists (1596)
be_db_follower:maybe_log_duration:123 be_db_txn_actor took 17243 ms
q_copy_transaction_actors:127 Txn actors copy list took 5883 ms. CopyLists (7525)
...
be_db_follower:load_block:107 Stored block: 1199717 took: 33563 ms

**Reward Block**
be_db_follower:maybe_log_duration:123 be_db_block took 46886 ms
be_db_block:q_copy_transactions:247 Txns copy list took 11469 ms. CopyLists (2)
be_db_follower:maybe_log_duration:123 be_db_txn_actor took 330198 ms
be_db_txn_actor:q_copy_transaction_actors:127 Txn actors copy list took 164007 ms. CopyLists (168938)
...
be_db_follower:load_block:107 Stored block: 1199698 took: 792255 ms