Tom Lane wrote:
"Florian G. Pflug" <[EMAIL PROTECTED]> 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
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
