Re: Select for update

On Fri, Jul 29, 2005 at 12:05:46AM +0200, Havasvölgyi Ottó wrote:
> create function pidtest_del(_pid integer) returns void as $$
> declare
> row pidtest;
> begin
> perform pid from pidtest where pid>=_pid for update;
> delete from pidtest where pid=_pid;
> for row in select * from pidtest where pid>_pid order by pid loop
> update pidtest set pid=pid-1 where pid=row.pid;
> end loop;
> return;
> end;
> $$ language plpgslq;

I suspect this isn't exactly the code you're running, because creating
this function fails with the following error:

ERROR: language "plpgslq" does not exist

If I correct the spelling to "plpgsql" then I get the following results:

SELECT * FROM pidtest;
pid | szoveg
-----+--------
3 | three
4 | four
5 | five
6 | six
7 | seven
(5 rows)

SELECT pidtest_del(5);
pidtest_del
-------------

(1 row)

SELECT * FROM pidtest;
pid | szoveg
-----+--------
3 | three
4 | four
5 | six
6 | seven
(4 rows)

> Sorry, now I cannot reproduce it, but yesterday I was suprised that the
> szoveg field's contents in the locked records went away.

What you describe isn't supposed to happen, but we can't do much
to investigate the problem unless we can see how to reproduce it.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/