Re: Two proposed modifications to the PostgreSQL FDW - Mailing list pgsql-hackers

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