Re: Questions about update, delete, ctid... - Mailing list pgsql-general

Tom Lane wrote:
> "Florian G. Pflug" <fgp@phlo.org> writes:
>> Martijn van Oosterhout wrote:
>>> It's the t_ctid field of the tuple header. But I don't see what that
>>> has to do with "WHERE CURRENT OF". That expression should return the
>>> tuple visible to your transaction, not some updated version which you
>>> won't be able to fetch. If you end up updating the old version, the
>>> system will take care of finding the newer version if necessary.
>
>> The problem is that the ctid of the tuple that "fetch" returned
>> might not actually be the tuple that needs to be updated.
>
> Martijn's got a point though: it's not clear that that needs any special
> care on the part of WHERE CURRENT OF.  It may be that the existing
> EvalPlanQual machinery does everything that's needful.  Is the example
> you show really any different from updating a tuple that someone else
> updated while your UPDATE was in progress?  In both cases the EPQ
> machinery will take care of chaining forward to the right version to
> update.

I agree, at least for "for-update"-cursors. If the cursor was not
declared "for update", then it is not even cleaer to me what the
correct behaviour would be. Imagine that you declared a cursor, and fetched
a row. After fetching, but before you call "update table foo where current of
mycursor" someone updates the row (and commits), and the new version would have
never been returned by your select statement in the first place. Should that row be
updates, or not? What if the other transaction deleted the row - should your
update raise an error? fail silently?

Because of those problems, I'll always assume that the cursor was declared
"for update" from now on.

After reading backend code for the last hours, I've created the following plan for
implementing "where current of"

1) Allow "declare ... cursor .... for update of <field1>, <field2>, <table1>, ...".
    Since all locking is done on row, not on particular columns, specifying
    <field1> is really equivalent to specifying the table that this field came from.
2) For each table in "for update of ..." remember the ctid of the returned tuple when
    doing fetch.

Now, there are two possibilities
A) Implement a "<table-alias> current of <cursor>" predicate for usage in where clauses.
    It would get the last ctid from the cursor (for the table aliased by <table-alias),
    follow ctid-chain to get the newest version, and compare this ctid to the one found
    in <table-alias>.
    "update <table> where current of <cursor>" would then be an abbreviation for
    "update <table> where <table> current of <cursor>", and e.g.
    "update <table> where <table> current of <cursor> and data like '%whatever%'"
    would be allowed to.
B) Implement "update <table> where current of <cursor>" as a special case. The plan
    generated would be similar to the one generated by "update <table> where ctid = ...",
    but the ctid stored would only be used to find an initial tuple version, and ignored
    when rechecking if a newer version still matches.

A) sound like a little bit more work, but it would avoid special-casing "where current of"
somewhere in either the planner or the executor. I've googled around a bit, and it
seems as if at least oracle and db2 only support "where current of <cursor>", and
not a general "<table> current of <cursor>" predicate.

Since I'm new to postgres-backend-hacking, this has no chance to be ready until feature
freeze, so it's 8.3 material at best, I guess ;-)
Still, I'd be thankfull for any comments, especially the "This can't work because ..."
type of comments ;-)

greetings, Florian Pflug