Hi, On 2018-08-20 16:28:01 +0200, Chris Travers wrote: > 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.
That has a *lot* of semantics issues, because you suddenly don't get synchronous error reports anymore. I don't think that's OK on a per-table basis. If we invented something like this, it IMO should be a per-statement explicit opt in that'd allow streaming. > 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 There's been *substantial* work on this. You should at least read the discussion & coordinate with the relevant developers. See https://commitfest.postgresql.org/19/1574/ and the referenced discussions. Greetings, Andres Freund