Search Postgresql Archives

Re: Select for update

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux