From: Forest Wilkinson <fspam@home.com>
To: pgsql-sql@postgresql.org
Subject: possible row locking bug in 7.0.3 & 7.1
Date: Tue, 27 Mar 2001 13:10:16 -0800
Message-ID: <01032713101600.07241@bartok> (raw)
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.3, installed from RPMs, on Red Hat 6.2. I got the same
results with postgres 7.1 beta 6, installed from sources.
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 18734 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!
session2<< commit;
session2>> COMMIT
session2<< select * from idseq;
session2>> name | id
session2>> ------+----
session2>> myid | 2
session2>> (1 row)
session1<< select * from idseq;
session1>> name | id
session1>> ------+----
session1>> myid | 2
session1>> (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? I'm going to try out some of my
SPI functions with 7.1 beta 6, to see if they exhibit a locking problem as
well.
Thanks,
Forest Wilkinson
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: fspam@home.com
Subject: Re: possible row locking bug in 7.0.3 & 7.1
In-Reply-To: <01032713101600.07241@bartok>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox