Received: from qw01.qwsecure.com (qw01.qwsecure.com [209.98.210.57]) by hub.org (8.9.3/8.9.3) with ESMTP id SAA31472 for ; Wed, 2 Jun 1999 18:18:47 -0400 (EDT) (envelope-from mwright@pro-ns.net) Received: from markw_compaq (204.124.82.48 [204.124.82.48]) by qw01.qwsecure.com with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2232.9) id L5DA9D20; Wed, 2 Jun 1999 17:40:28 -0500 Message-ID: <001f01bead45$d72f9100$c62812ac@markw_compaq> Reply-To: "Mark Wright" From: "Mark Wright" To: Subject: Are PL/pgSQL calls atomic? Date: Wed, 2 Jun 1999 17:18:19 -0500 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 4.72.3110.5 X-MimeOLE: Produced By Microsoft MimeOLE V4.72.3110.3 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