Greetings, * Andres Freund (and...@anarazel.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
signature.asc
Description: PGP signature
