Re: Select for update

Hi,

Oh, sorry.

CREATE TABLE pidtest (pid integer, szoveg text) WITHOUT OIDS;

INSERT INTO pidtest (pid, szoveg) VALUES (3, 'three');
INSERT INTO pidtest (pid, szoveg) VALUES (4, 'four');
INSERT INTO pidtest (pid, szoveg) VALUES (5, 'five');
INSERT INTO pidtest (pid, szoveg) VALUES (6, 'six');
INSERT INTO pidtest (pid, szoveg) VALUES (7, 'seven');

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;


This function deletes a row, and updates the pid field where pid is geater
than the deleted pid value, so that the gap caused by the deletion is not
present any more.
Sorry, now I cannot reproduce it, but yesterday I was suprised that the
szoveg field's contents in the locked records went away.

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: Thursday, July 28, 2005 2:02 PM
Subject: Re: [GENERAL] Select for update


> On Thu, Jul 28, 2005 at 01:22:31PM +0200, Havasvölgyi Ottó wrote:
>> Is it normal that when I select for update a record, but I don't select
>> all
>> the fields, that the contents of fields not selected will be deleted:
>>
>> create table pidtest(pid integer, szoveg text) without oids;
>>
>> select pid from pistest where pid>5 for update;
>>
>> After committing (autocommit), the contents of the szoveg field for the
>> locked rows will be erased.
>
> Could you provide a complete test case?  Works fine here:
>
> CREATE TABLE pidtest (pid integer, szoveg text) WITHOUT OIDS;
>
> INSERT INTO pidtest (pid, szoveg) VALUES (3, 'three');
> INSERT INTO pidtest (pid, szoveg) VALUES (4, 'four');
> INSERT INTO pidtest (pid, szoveg) VALUES (5, 'five');
> INSERT INTO pidtest (pid, szoveg) VALUES (6, 'six');
> INSERT INTO pidtest (pid, szoveg) VALUES (7, 'seven');
>
> SELECT pid FROM pidtest WHERE pid > 5 FOR UPDATE;
> pid
> -----
>   6
>   7
> (2 rows)
>
> SELECT * FROM pidtest;
> pid | szoveg
> -----+--------
>   3 | three
>   4 | four
>   5 | five
>   6 | six
>   7 | seven
> (5 rows)
>
> --
> Michael Fuhr
> http://www.fuhr.org/~mfuhr/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
>
>