From: Forest Wilkinson <fspam@home.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@hub.org
Subject: Re: SQL functions not locking properly?
Date: Tue, 26 Sep 2000 16:56:38 -0700
Message-ID: <h5b2ts0v7g7e0e7r8491pf6os68n324iso@4ax.com> (raw)
In-Reply-To: <200009260244.e8Q2isN09438@hub.org>
References: <200009260244.e8Q2isN09438@hub.org>
Tom Lane wrote:
>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 just happen to have a C function that has a similar problem.
In this case, I am again trying to approximate the behavior of postgres
sequences. The main difference is that I'm using alphanumerics, instead
of 64 bit integers, to represent sequential values. The values are very
much like base 36 numbers.
The table used to store these alphanumeric sequences looks like this:
create table my_uid(
name varchar(32) NOT NULL PRIMARY KEY, -- sequence name
uid varchar(32) NOT NULL default(text '0'), -- current value
min_uid varchar(32) NOT NULL default(text '1'),
max_uid varchar(32) NOT NULL default(text
'zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz'),
cycle bool NOT NULL default('f'));
My next_uid() function is written in C, and has an interface like the
built-in nextval() function. It originally did the following:
(Get the desired sequence name from the function arguments;
e.g. "testuid".)
SELECT \"uid\", \"min_uid\", \"max_uid\", \"cycle\" FROM \"my_uid\" WHERE
\"name\" = 'testuid' FOR UPDATE LIMIT 1;
(Detect & report any errors.)
(Calculate a new sequence value according to the row selected;
e.g. "newval".)
UPDATE \"my_uid\" SET \"uid\" = 'newval' WHERE \"name\" = 'testuid';
(Detect & report any errors.)
(Return the new value.)
However, with just those two queries, next_uid() fails during concurrent
calls on the same uid sequence. Here's a log of what happens:
(<< indicates input; >> indicates output.)
session1 << begin;
session1 >> BEGIN
session2 << begin;
session2 >> BEGIN
session1 << select next_uid('foo');
session1 >> next_uid
session1 >> --------------
session1 >> 1
session1 >> (1 row)
session2 << select next_uid('foo');
(session2 blocks until session1 ends its session)
session1 << commit;
session1 >> COMMIT
(session2 resumes)
session2 >> ERROR: testuid: update query processed 0 rows
That error message is generated by my error trapping within next_uid().
It is reporting that SPI_processed is <= 0, right after executing the
update query I listed above. After committing both transactions,
examination of the rows in the database confirms that the session2 didn't
update any rows.
As you can see, the SELECT...FOR UPDATE followed by UPDATE does not work
properly during concurrent access. However, I can achieve the desired
behavior by executing this query before the other two, within the
next_uid() function:
UPDATE \"my_uid\" SET \"uid\" = \"uid\" WHERE \"name\" = 'testuid';
Once I modified the next_uid() code to execute this new query before doing
the rest of its work, concurrent calls worked as expected. So, that's
what I'm doing in my production code. But it's really quite ugly, and I
shouldn't have to do that! I can't explain why this approach works, short
of saying that row level locking is broken in postgres. Furthermore, I am
not confident that this workaround avoids the problem in all cases.
BTW, I'm using postgres 7.0.2 now, but I discovered this problem (and the
workaround) with 6.5.2 or 6.5.3.
>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
Thanks, Tom. Please let me know when there's a fix.
I can provide more detailed C source code if you need it, but I think the
relevant parts of the code are expressed in this message.
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: fspam@home.com, tgl@sss.pgh.pa.us, pgsql-sql@hub.org
Subject: Re: SQL functions not locking properly?
In-Reply-To: <h5b2ts0v7g7e0e7r8491pf6os68n324iso@4ax.com>
References: <200009260244.e8Q2isN09438@hub.org> <h5b2ts0v7g7e0e7r8491pf6os68n324iso@4ax.com>
* 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