Re: Two proposed modifications to the PostgreSQL FDW

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