Re: Two proposed modifications to the PostgreSQL FDW

Chris Travers <chris.travers@adjust.com> writes:
> 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.

It seems unlikely to me that an FDW option would be at all convenient
for this.  What about selecting it dynamically based on the planner's
estimate of the number of rows to be inserted?

A different thing we could think about is enabling COPY TO/FROM a
foreign table.

> 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.

ENOINFRASTRUCTURE ... and the FDW pieces of that hardly seem like the
place to start.

                        regards, tom lane