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(at)fuhr(dot)org>
To: "Havasvölgyi Ottó" <h(dot)otto(at)freemail(dot)hu>
Cc: <pgsql-general(at)postgresql(dot)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(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>
>