Re: Writable foreign tables: how to identify rows

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Wed, Mar 6, 2013 at 12:06 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> On the other hand, I don't have a problem with decreeing that
>> non-Postgres FDWs need to use PK row identification in the first
>> release; which would be the consequence if we don't do anything about
>> allowing new system columns in 9.3. We will certainly need that style
>> of row identification to be written and tested anyway. It won't stop
>> us from extending things later.

> Oh, I didn't realize that was how it was going to work out. That
> seems very reasonable to me. There is a performance problem with
> forcing DELETE FROM ft WHERE nonkey = 5 to be pushed to the remote
> side as SELECT pk FROM ft WHERE nonkey = 5 followed by DELETE FROM ft
> WHERE pk = $1 for each pk value returned by the SELECT, which sounds
> like it's what will happen under this system. But I don't have any
> problem leaving that as future work.

That performance issue exists for *all* foreign updates/deletes at the
moment, with or without a ctid-ish column. As you say, fixing it is
material for 9.4 or later. (It's possible that an FDW could dodge this
by itself without any additional help from the core code, but I'm not
sure. Anyway postgres_fdw hasn't tried yet, and I think there are a
number of things that are higher priority to worry about than bulk
update performance.)

regards, tom lane