Re: Select for update - Mailing list pgsql-general

From Havasvölgyi Ottó
Subject Re: Select for update
Date
Msg-id 008801c5942b$4f8f1990$b800a8c0@OTTO
Whole thread Raw
In response to Rules vs Triggers  (Randall Perry <rgp@systame.com>)
List pgsql-general
Hi,

Yes, I misspelled in the mail, I don't any way to copy it from the console,
so I rewrote it here.
I will post if I can reproduce it again. I hope that I have missed
something.

Best Regards,
Otto



----- Original Message -----
From: "Michael Fuhr" <mike@fuhr.org>
To: "Havasvölgyi Ottó" <h.otto@freemail.hu>
Cc: <pgsql-general@postgresql.org>
Sent: Friday, July 29, 2005 3:12 AM
Subject: Re: [GENERAL] 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/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>       choose an index scan if your joining column's datatypes do not
>       match
>
>



pgsql-general by date:

Previous
From: Oliver Jowett
Date:
Subject: Re: [JDBC] Bad plan for queries with IN clause
Next
From: Havasvölgyi Ottó
Date:
Subject: Re: Select for update