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@xxxxxxxx>
To: "Havasvölgyi Ottó" <h.otto@xxxxxxxxxxx>
Cc: <pgsql-general@xxxxxxxxxxxxxx>
Sent: Thursday, July 28, 2005 2:02 PM
Subject: Re: 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@xxxxxxxxxxxxxx so that your
message can get through to the mailing list cleanly
---------------------------(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
