Thread: UPDATE/DELETE XXX WHERE CURRENT OF cursor_name
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? thanksGolden 7.24
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