Re: Two proposed modifications to the PostgreSQL FDW

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Chris Travers <chris(dot)travers(at)adjust(dot)com>
Cc: pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: Re: Two proposed modifications to the PostgreSQL FDW
Date: 2018-08-20 14:43:59
Message-ID: 19588.1534776239@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers

Chris Travers <chris(dot)travers(at)adjust(dot)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

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Stephen Frost 2018-08-20 14:56:39 Re: Two proposed modifications to the PostgreSQL FDW
Previous Message Andres Freund 2018-08-20 14:41:53 Re: Two proposed modifications to the PostgreSQL FDW