Re: Two proposed modifications to the PostgreSQL FDW

From: Stephen Frost <sfrost(at)snowman(dot)net>
To: Andres Freund <andres(at)anarazel(dot)de>
Cc: Chris Travers <chris(dot)travers(at)adjust(dot)com>, pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: Re: Two proposed modifications to the PostgreSQL FDW
Date: 2018-08-20 14:56:39
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers


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



In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Alexander Korotkov 2018-08-20 15:00:09 Re: Truncation failure in autovacuum results in data corruption (duplicate keys)
Previous Message Tom Lane 2018-08-20 14:43:59 Re: Two proposed modifications to the PostgreSQL FDW