Re: Two proposed modifications to the PostgreSQL FDW

Greetings,

* Andres Freund (andres(at)anarazel(dot)de) wrote:
> 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.

Doing some kind of decoration on a per-statement level to do something
different for FDWs doesn't really seem very clean..

On reading this, a thought I had was that maybe we should just perform a
COPY to the FDW when COPY is what's been specified by the user (eg:

COPY my_foreign_table FROM STDIN;

), but that wouldn't help when someone wants to bulk copy data from a
local table into a foreign table.

COPY is already non-standard though, so we can extend it, and one option
might be to extend it like so:

COPY my_local_table TO TABLE my_foreign_table;

Which could be made to work for both foreign tables and local ones,
where it'd basically be:

INSERT INTO my_foreign_table SELECT * FROM my_local_table;

The COPY TO case already supports queries, such that you could then do:

COPY (SELECT c1,c2,c3 FROM my_local_table) TO TABLE my_foreign_table;

I'd also think we'd want to support this kind of 'bulk COPY-like'
operation for multiple FDWs (I wonder if maybe file_fdw could be made to
support this new method, thus allowing users to write out to files with
it, which we don't support today at all).

Just some brain-storming and ideas about where this could possibly go.

Thanks!

Stephen