pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedAre PL/pgSQL calls atomic?
4+ messages / 3 participants
[nested] [flat]
* Are PL/pgSQL calls atomic?
@ 1999-06-02 22:18 Mark Wright <mwright@pro-ns.net>
0 siblings, 2 replies; 4+ messages in thread
From: Mark Wright @ 1999-06-02 22:18 UTC (permalink / raw)
To: pgsql-sql
If I call a PL/pgSQL function, and another client makes the same call, does
the second client have to wait for the first to complete execution before it
begins? If not, is there some sort of mechanism I can use to prevent more
than one call to a function from happening at the same time (something like
a mutex in Win32).
I need to select a row and then mark it as unavailable for other clients to
use. The table looks like:
create table xyz (id serial, status char default 'N', ...);
My function finds a row by doing:
select id into my_id_variable from xyz where id
= (select min(id) from xyz where status = 'N');
and then marks that row as unavailable by setting status:
update xyz set status = 'Y' where id = my_id_variable;
Obviously, if a second client calls this function before the UPDATE takes
place, it will pick up the same row as the first client, causing rending of
hair and gnashing of teeth.
How do I prevent this?
Mark.
---
Mark Wright
mwright@pro-ns.net
mark_wright@datacard.com
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [SQL] Are PL/pgSQL calls atomic?
@ 1999-06-03 01:24 Vadim Mikheev <vadim@krs.ru>
parent: Mark Wright <mwright@pro-ns.net>
1 sibling, 0 replies; 4+ messages in thread
From: Vadim Mikheev @ 1999-06-03 01:24 UTC (permalink / raw)
To: Mark Wright <mwright@pro-ns.net>; +Cc: pgsql-sql
Mark Wright wrote:
>
> If I call a PL/pgSQL function, and another client makes the same call, does
> the second client have to wait for the first to complete execution before it
> begins? If not, is there some sort of mechanism I can use to prevent more
> than one call to a function from happening at the same time (something like
> a mutex in Win32).
>
> I need to select a row and then mark it as unavailable for other clients to
> use. The table looks like:
> create table xyz (id serial, status char default 'N', ...);
>
> My function finds a row by doing:
> select id into my_id_variable from xyz where id
> = (select min(id) from xyz where status = 'N');
6.5 allows you SELECT FOR UPDATE...
>
> and then marks that row as unavailable by setting status:
> update xyz set status = 'Y' where id = my_id_variable;
>
> Obviously, if a second client calls this function before the UPDATE takes
> place, it will pick up the same row as the first client, causing rending of
> hair and gnashing of teeth.
Vadim
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [SQL] Are PL/pgSQL calls atomic?
@ 1999-06-03 10:24 Jan Wieck <wieck@debis.com>
parent: Mark Wright <mwright@pro-ns.net>
1 sibling, 0 replies; 4+ messages in thread
From: wieck@debis.com @ 1999-06-03 10:24 UTC (permalink / raw)
To: mwright@pro-ns.net; +Cc: pgsql-sql
Mark Wright asked:
>
> If I call a PL/pgSQL function, and another client makes the same call, does
> the second client have to wait for the first to complete execution before it
> begins? If not, is there some sort of mechanism I can use to prevent more
> than one call to a function from happening at the same time (something like
> a mutex in Win32).
>
> I need to select a row and then mark it as unavailable for other clients to
> use. The table looks like:
> create table xyz (id serial, status char default 'N', ...);
>
> My function finds a row by doing:
> select id into my_id_variable from xyz where id
> = (select min(id) from xyz where status = 'N');
>
> and then marks that row as unavailable by setting status:
> update xyz set status = 'Y' where id = my_id_variable;
>
> Obviously, if a second client calls this function before the UPDATE takes
> place, it will pick up the same row as the first client, causing rending of
> hair and gnashing of teeth.
>
> How do I prevent this?
It's not a PL/pgSQL specific problem I think. Doing this in
two psql sessions:
S1: BEGIN;
S1: SELECT id FROM xyz WHERE id =
(SELECT min(id) FROM xyz WHERE status = 'N')
FOR UPDATE OF xyz;
-- Session 1 returns one id, say 42
S2: BEGIN;
S2: SELECT id FROM xyz WHERE id =
(SELECT min(id) FROM xyz WHERE status = 'N')
FOR UPDATE OF xyz;
-- Session 2 blocks now, but...
S1: UPDATE xyz SET status = 'Y' WHERE ID = 42;
S1: COMMIT;
-- Session 2 continues and returns id 42
The reason why session 2 returns the same id is that the
qualifying subselect finding the lowest id where status = 'N'
didn't block. The outer SELECT FOR UPDATE did, but that
didn't check the state any more.
Unfortunately a
SELECT min(id) FROM xyz WHERE status = 'N' FOR UPDATE OF xyz;
doesn't work either because FOR UPDATE isn't allowed with
aggregates.
From an application you could use a whole table lock before
looking up the id. Here now is a PL/pgSQL problem. LOCK
TABLE is a utility statement and PL/pgSQL cannot execute
them. I think I have to work on that for v6.6.
What you could do (if the number of rows with status = 'N'
isn't high) is the following:
CREATE FUNCTION my_func .... AS '
DECLARE
xyz_rec RECORD;
BEGIN
FOR xyz_rec IN SELECT * FROM xyz WHERE status = ''N''
ORDER BY id FOR UPDATE OF xyz
LOOP
-- If more changes in xyz are to be made than just setting
-- status to Y, do them all in one UPDATE. The record is
-- locked now and the lock will release only when our entire
-- transaction commits or rolls back - not when we update it.
UPDATE xyz SET status = ''Y'' WHERE id = xyz_rec.id;
...
-- Now we return from inside the loop at the first
-- row processed. This ensures we will process one
-- row at max per call.
RETURN _whatever_my_func_returns_;
END LOOP;
-- If we reach here, we did not find any row (left) with
-- status N. Hmmm - is this an error or not?
RAISE ERROR ''no (more) xyz rows with status N found'';
END;' LANGUAGE 'plpgsql';
Why it's important that the number of rows having status =
'N' isn't very big is because at the FOR xyz_rec IN time all
of them are fetched into memory, even if the loop will break
at the first one got.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck@debis.com (Jan Wieck) #
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [SQL] Are PL/pgSQL calls atomic?
@ 1999-06-03 14:47 Mark Wright <mwright@pro-ns.net>
0 siblings, 0 replies; 4+ messages in thread
From: Mark Wright @ 1999-06-03 14:47 UTC (permalink / raw)
To: pgsql-sql
Jan Wieck <wieck@debis.com> wrote:
|Mark Wright asked:
|
|>
|> If I call a PL/pgSQL function, and another client makes the same call,
does
|> the second client have to wait for the first to complete execution before
it
|> begins? If not, is there some sort of mechanism I can use to prevent
more
|> than one call to a function from happening at the same time (something
like
|> a mutex in Win32).
|>
|> I need to select a row and then mark it as unavailable for other clients
to
|> use. The table looks like:
|> create table xyz (id serial, status char default 'N', ...);
|>
|> My function finds a row by doing:
|> select id into my_id_variable from xyz where id
|> = (select min(id) from xyz where status = 'N');
|>
|> and then marks that row as unavailable by setting status:
|> update xyz set status = 'Y' where id = my_id_variable;
|>
|> Obviously, if a second client calls this function before the UPDATE takes
|> place, it will pick up the same row as the first client, causing rending
of
|> hair and gnashing of teeth.
|>
|> How do I prevent this?
[very helpful discussion deleted]
|
| What you could do (if the number of rows with status = 'N'
| isn't high) is the following:
|
| CREATE FUNCTION my_func .... AS '
| DECLARE
| xyz_rec RECORD;
| BEGIN
| FOR xyz_rec IN SELECT * FROM xyz WHERE status = ''N''
| ORDER BY id FOR UPDATE OF xyz
| LOOP
| -- If more changes in xyz are to be made than just setting
| -- status to Y, do them all in one UPDATE. The record is
| -- locked now and the lock will release only when our entire
| -- transaction commits or rolls back - not when we update it.
|
| UPDATE xyz SET status = ''Y'' WHERE id = xyz_rec.id;
| ...
|
| -- Now we return from inside the loop at the first
| -- row processed. This ensures we will process one
| -- row at max per call.
| RETURN _whatever_my_func_returns_;
| END LOOP;
|
| -- If we reach here, we did not find any row (left) with
| -- status N. Hmmm - is this an error or not?
|
| RAISE ERROR ''no (more) xyz rows with status N found'';
| END;' LANGUAGE 'plpgsql';
|
|
| Why it's important that the number of rows having status =
| 'N' isn't very big is because at the FOR xyz_rec IN time all
| of them are fetched into memory, even if the loop will break
| at the first one got.
Any off-the-cuff estimates for what 'too many' is? I would have anywhere
from 1 to 20 sessions executing the function, the number of rows should be
less than 10,000, and I'll be running this on a small to mid-range Pentium
linux box.
---
Mark Wright
mwright@pro-ns.net
mark_wright@datacard.com
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~1999-06-03 14:47 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-06-02 22:18 Are PL/pgSQL calls atomic? Mark Wright <mwright@pro-ns.net>
1999-06-03 01:24 ` Vadim Mikheev <vadim@krs.ru>
1999-06-03 10:24 ` Jan Wieck <wieck@debis.com>
1999-06-03 14:47 Re: [SQL] Are PL/pgSQL calls atomic? Mark Wright <mwright@pro-ns.net>
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