Received: from mail.rdc1.sdca.home.com (imail@ha1.rdc1.sdca.home.com [24.0.3.66]) by hub.org (8.10.1/8.10.1) with ESMTP id e8PIjmN78873 for ; Mon, 25 Sep 2000 14:45:48 -0400 (EDT) Received: from tajar ([24.1.6.224]) by mail.rdc1.sdca.home.com (InterMail vM.4.01.03.00 201-229-121) with SMTP id <20000925184543.MVXY15602.mail.rdc1.sdca.home.com@tajar> for ; Mon, 25 Sep 2000 11:45:43 -0700 From: Forest Wilkinson To: pgsql-sql@hub.org Subject: SQL functions not locking properly? Date: Mon, 25 Sep 2000 11:46:50 -0700 Message-ID: X-Mailer: Forte Agent 1.8/32.548 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200009/206 I'm having a problem with functions written in SQL. Specifically, they don't seem to be adhering to Postgres locking rules. For the record, I'm using postgres 7.0.2, installed from RPMs, on Red Hat 6.2. Here's what I'm seeing: (psql input represented by '<<'; output represented by '>>'.) session1<< create table idseq session1<< ( session1<< name varchar(32) not null, session1<< id int8 not null default 0 session1<< ); session1>> CREATE session1<< insert into idseq values ('myid'); session1>> INSERT 20700 1 Each row in the table is supposed to represent a named numeric sequence, much like the sequences built into postgres. (Mine use an int8 though, so their values can be much higher.) session1<< create function nextid( varchar(32)) returns int8 as ' session1<< select * from idseq where name = $1::text for update; session1<< update idseq set id = id + 1 where name = $1::text; session1<< select id from idseq where name = $1::text; session1<< ' language 'sql'; session1>> CREATE The idea here is that the select...for update within the nextid() function will establish a row level lock, preventing two concurrent function calls from overlapping. Next, I test with two sessions as follows: session1<< begin; session1>> BEGIN session2<< begin; session2>> BEGIN session1<< select nextid('myid'); session1>> nextid session1>> -------- session1>> 1 session1>> (1 row) session2<< select nextid('myid'); (session2 blocks until session1 completes its transaction) session1<< commit; session1>> COMMIT (session2 resumes) session2>> nextid session2>> -------- session2>> 0 session2>> (1 row) What gives??? I expected the second call to nextid() to return 2! session1<< select * from idseq; session1>> name | id session1>> ------+---- session1>> myid | 1 session1>> (1 row) session2<< select * from idseq; session2>> name | id session2>> ------+---- session2>> myid | 1 session2>> (1 row) As you can see, my nextid() function is not synchronized the way I hoped. I don't know why though. Can someone help? Thanks, Forest Wilkinson