Re: UPDATE/DELETE XXX WHERE CURRENT OF cursor_name

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