On Wed, Mar 13, 2013 at 10:15 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Andres Freund <andres@2ndquadrant.com> writes:
>> Perhaps pgsql-fdw should make sure the update was performed *without*
>> following the ctid chain to a new valid tuple?
>
> I did think about these issues before committing the patch ;-)
>
> The basic theory in PG's existing design is to postpone locking rows as
> long as possible; which means that when we do finally lock a target row,
> we have to check if it's changed since we scanned it, and that leads
> into the whole EvalPlanQual mess. I considered trying to make FDWs
> duplicate that behavior, but gave up on it. In the first place, it's
> hard to see how you even define "did the row change" unless you have
> something exactly like ctids (including forward update chains). And
> in the second place, this would mandate yet another round trip to the
> remote server for each row to be updated.
>
> In the patch as committed, the expectation (which is satisfied by
> postgres_fdw) is that FDWs should lock rows that are candidates for
> update/delete during the initial scan. This avoids an extra round trip
> and justifies leaving EvalPlanQual out of the picture altogether.
> The cost is that we may lock rows that we end up not updating, because
> they fail locally-checked restriction or join conditions. I think on
> balance that's a good trade-off.
agreed.
As long as lock as held between ctid examination and row modification
you are ok. didn't read the patch, just pointing that out (history is
full of client side drivers that did not do this properly).
I also might have missed some of the finer contextual points of the
discussion here: I was thinking that you are identifying rows on the
client over fetch transaction A to write back in transaction B. If
that is the case, ctid based identification to me is full of issues
and probably best used to handle awkward cases like there is no
identified primary key. But sure, if you are holding locks you can
base it on physical position.
with pkey row identification, yes, rows can change from under you, but
now you can employ application tricks to guard for that just as you
would with any optimistic locking strategy in SQL. This is and
expected and well understood problem (use transactions etc).
merlin
