Received: from sss.pgh.pa.us (sss.pgh.pa.us [209.114.132.154]) by hub.org (8.10.1/8.10.1) with ESMTP id e8PKQHN08471 for ; Mon, 25 Sep 2000 16:26:17 -0400 (EDT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.0/8.11.0) with ESMTP id e8PKQGB06913 for ; Mon, 25 Sep 2000 16:26:16 -0400 (EDT) To: pgsql-sql@hub.org Subject: Re: SQL functions not locking properly? In-reply-to: References: Comments: In-reply-to Forest Wilkinson message dated "Mon, 25 Sep 2000 11:46:50 -0700" Date: Mon, 25 Sep 2000 16:26:16 -0400 Message-ID: <6910.969913576@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200009/208 Forest Wilkinson writes: > 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! Hmm. If you do the same commands without wrapping them in an SQL function, they operate as expected. I'll bet there's some nasty interaction between the FOR UPDATE support and the way that SQL functions twiddle the current-command counter. Sigh, another bug. You might try it in plpgsql to see if that language has the same bug (and please report back the results!). If so, the only workaround right now is not to use a function, or possibly to code it in C using the SPI interface. I'm up to my armpits in subselect-in-FROM right now, but will put this on my to-do list. Will look at it in a week or two if no one else has fixed it before then... regards, tom lane