UPDATE/DELETE XXX WHERE CURRENT OF cursor_name

On Mon, 24 Jul 2006, Golden Liu wrote:

Updateable cursors are used as follows:

begin;
declare foo cursor for select * from bar for update;
fetch foo;
update bar set abc='def' where current of foo;
fetch foo;
delete from bar where current of foo;
commit;

PostgreSQL doesn't support this feature now ( 8.1.4). Will PGSQL
support it recently? Does anyone work on this?

No one has stepped up to do this for 8.2 so unfortunately you will most
likely not see this within the next year or so :-(.

Thanks,

Gavin

PS: sorry for not responding to your private email in time.

Gavin Sherry wrote:

On Mon, 24 Jul 2006, Golden Liu wrote:

begin;
declare foo cursor for select * from bar for update;
fetch foo;
update bar set abc='def' where current of foo;
fetch foo;
delete from bar where current of foo;
commit;

No one has stepped up to do this for 8.2 so unfortunately you will most
likely not see this within the next year or so :-(.

Couldn't this be emulated by doing
begin;
declare foo cursor for select * from bar for update;
fetch foo into v_foo ;
update bar set abc='def' where ctid = v_foo.ctid;
fetch foo into v_foo ;
delete from bar where ctid = v_foo.ctid;
commit;

Or could a concurrent vacuum run lead to the wrong
rows being updated/deleted?

greetings, Florian Pflug

"Florian G. Pflug" <fgp@phlo.org> writes:

Couldn't this be emulated by doing
begin;
declare foo cursor for select * from bar for update;
fetch foo into v_foo ;
update bar set abc='def' where ctid = v_foo.ctid;

That wouldn't follow the expected semantics if there's a concurrent
update, because the updated row would always fail the WHERE clause,
and thus the update would just silently not happen. (I'm thinking
about READ COMMITTED mode of course --- in SERIALIZABLE you'd just get
the expected error.) You'd have to find some way to pump the row's most
up-to-date version through the cursor's query plan, a la EvalPlanQual,
to see if it still met the cursor's WHERE condition.

regards, tom lane

Florian G. Pflug wrote:

Gavin Sherry wrote:

On Mon, 24 Jul 2006, Golden Liu wrote:

begin;
declare foo cursor for select * from bar for update;
fetch foo;
update bar set abc='def' where current of foo;
fetch foo;
delete from bar where current of foo;
commit;

No one has stepped up to do this for 8.2 so unfortunately you will most
likely not see this within the next year or so :-(.

Couldn't this be emulated by doing
begin;
declare foo cursor for select * from bar for update;
fetch foo into v_foo ;
update bar set abc='def' where ctid = v_foo.ctid;
fetch foo into v_foo ;
delete from bar where ctid = v_foo.ctid;
commit;

Or could a concurrent vacuum run lead to the wrong
rows being updated/deleted?

No, a concurrent vacuum can't change that because vacuum can't change
the page unless it can get a super-exclusive lock on it (which means
nobody else can have a scan stopped at that page, which is exactly
what this cursor has).

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Tom Lane wrote:

"Florian G. Pflug" <fgp@phlo.org> writes:

Couldn't this be emulated by doing
begin;
declare foo cursor for select * from bar for update;
fetch foo into v_foo ;
update bar set abc='def' where ctid = v_foo.ctid;

That wouldn't follow the expected semantics if there's a concurrent
update, because the updated row would always fail the WHERE clause,
and thus the update would just silently not happen. (I'm thinking
about READ COMMITTED mode of course --- in SERIALIZABLE you'd just get
the expected error.) You'd have to find some way to pump the row's most
up-to-date version through the cursor's query plan, a la EvalPlanQual,
to see if it still met the cursor's WHERE condition.

How could there be a concurrent update of the _same_ row, when
I do "select * from bar *for update*". Or are you talking about
concurrent updates to the same page that could somehow alter
the ctid of _another_ tuple?

greetings, Florian Pflug

Alvaro Herrera <alvherre@commandprompt.com> writes:

No, a concurrent vacuum can't change that because vacuum can't change
the page unless it can get a super-exclusive lock on it (which means
nobody else can have a scan stopped at that page, which is exactly
what this cursor has).

More to the point, vacuum certainly may not delete a row that's still
visible to any open transaction, which this row would be by definition.
And VACUUM FULL couldn't move it, because it couldn't get exclusive
lock on the table.

You'd probably have to forbid use of WHERE CURRENT for a cursor WITH HOLD
though, since that quite possibly would contain rows that don't exist
anymore.

regards, tom lane

"Florian G. Pflug" <fgp@phlo.org> writes:

How could there be a concurrent update of the _same_ row, when
I do "select * from bar *for update*".

AFAICT the spec doesn't require one to have written FOR UPDATE
in order to use WHERE CURRENT OF. (In effect, they expect FOR UPDATE
to be the default, which is certainly not a change we're going to
want to make to DECLARE CURSOR.) If we did make that restriction
then we could probably skip the EvalPlanQual mess.

regards, tom lane

Tom Lane wrote:

"Florian G. Pflug" <fgp@phlo.org> writes:

How could there be a concurrent update of the _same_ row, when
I do "select * from bar *for update*".

AFAICT the spec doesn't require one to have written FOR UPDATE
in order to use WHERE CURRENT OF. (In effect, they expect FOR UPDATE
to be the default, which is certainly not a change we're going to
want to make to DECLARE CURSOR.) If we did make that restriction
then we could probably skip the EvalPlanQual mess.

But if the expect "for update" to be default, then essentially they
do require that one to use a cursor with "for update" semantics when
using "where current of" - or do they allow "where current of" even
for "not for update" cursors?

If one would restrict in implementation of "where current of" to
"for update", "without hold" cursors, the only non-trivial problem that
I can see is how to support more than one update of the same row.

Because as far as I can see, if you'd do
begin;
declare foo cursor select * from bar for update;
fetch foo into v_foo ;
update bar set ... where ctid = v_foo.ctid ;
update bar set ... where ctid = v_foo.ctid ;
commit;

the second update would silently be ignored. But since only
updates happing in the same transaction would somehow need to be
tracked, this should be much easier to do than supporting
the non-for-update case.

greetings, Florian Pflug