Hi all;
I am looking at trying to make two modifications to the PostgreSQL FDW and would like feedback on this before I do.
1. INSERTMETHOD=[insert|copy] option on foreign table.
One significant limitation of the PostgreSQL FDW is that it does a prepared statement insert on each row written which imposes a per-row latency. This hits environments where there is significant latency or few latency guarantees particularly hard, for example, writing to a foreign table that might be physically located on another continent. The idea is that INSERTMETHOD would default to insert and therefore have no changes but where needed people could specify COPY which would stream the data out. Updates would still be unaffected.
2. TWOPHASECOMMIT=[off|on] option
The second major issue that I see with PostgreSQL's foreign database wrappers is the fact that there is no two phase commit which means that a single transaction writing to a group of tables has no expectation that all backends will commit or rollback together. With this patch an option would be applied to foreign tables such that they could be set to use two phase commit When this is done, the first write to each backend would register a connection with a global transaction handler and a pre-commit and commit hooks would be set up to properly process these.
On recommit a per-global-transaction file would be opened in the data directory and prepare statements logged to the file. On error, we simply roll back our local transaction.
On commit hook , we go through and start to commit the remote global transactions. At this point we make a best effort but track whether or not we were successfully on all. If successful on all, we delete the file. If unsuccessful we fire a background worker which re-reads the file and is responsible for cleanup. If global transactions persist, a SQL administration function will be made available to restart the cleanup process. On rollback, we do like commit but we roll back all transactions in the set. The file has enough information to determine whether we should be committing or rolling back on cleanup.
I would like to push these both for Pg 12. Is there any feedback on the concepts and the problems first
--
Best Regards,
Chris Travers
Head of Database
Saarbrücker Straße 37a, 10405 Berlin
