Two proposed modifications to the PostgreSQL FDW

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

Tel: +49 162 9037 210 | Skype: einhverfr | www.adjust.com
Saarbrücker Straße 37a, 10405 Berlin