pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Are 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>
  1999-06-03 01:24 ` Re: [SQL] Are PL/pgSQL calls atomic? Vadim Mikheev <vadim@krs.ru>
  1999-06-03 10:24 ` Re: [SQL] Are PL/pgSQL calls atomic? Jan Wieck <wieck@debis.com>
  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-02 22:18 Are PL/pgSQL calls atomic? Mark Wright <mwright@pro-ns.net>
@ 1999-06-03 01:24 ` Vadim Mikheev <vadim@krs.ru>
  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-02 22:18 Are PL/pgSQL calls atomic? Mark Wright <mwright@pro-ns.net>
@ 1999-06-03 10:24 ` Jan Wieck <wieck@debis.com>
  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