Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id EA3CD476353 for ; Wed, 13 Nov 2002 17:00:19 -0500 (EST) Received: from mail1.ihs.com (mail1.ihs.com [170.207.70.222]) by postgresql.org (Postfix) with ESMTP id 0020E476348 for ; Wed, 13 Nov 2002 17:00:18 -0500 (EST) Received: from css120.ihs.com (css120.ihs.com [170.207.105.120]) by mail1.ihs.com (8.12.6/8.12.6) with ESMTP id gADLxh4h004646; Wed, 13 Nov 2002 14:59:43 -0700 (MST) Date: Wed, 13 Nov 2002 15:01:19 -0700 (MST) From: "scott.marlowe" To: David Busby Cc: Subject: Re: Need to select and update with the same sql statement In-Reply-To: <02a401c28b5e$0af80bd0$4000000a@busbydev> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-MailBodyFilter: Message body has not been filtered X-MailScanner: Found to be clean X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200211/59 X-Sequence-Number: 1641 Yeah, the standard way of doing such things would be to create a parent table with a serial ID, and a set of child tables that refer to that id. then, you would do something like this: begin; insert into parent (field1, field2, fieldn) values (.... select currval('seqforparenttable'); insert into child1 (pid,field1...) values ('idfrompreviouscurrval','data1'.. repeat for other children commit; If it's a row that already exists, then your method is the way to do it. On Wed, 13 Nov 2002, David Busby wrote: > Scott,; List, > The transaction didn't work (for some reason) > What I ended up having to do (which isn't that bad really) is to > lock the table exclusively while doing the read/write. So my code looks > like > > begin; > lock table "chunks" exclusive mode; > select * from "chunks" order "lastchecked" limit 1; > # Do some PHP code here, couple 20 lines or so > if ($success) pg_exec("update "chunks"; commit;"); > else pg_exec("rollback;"); > > This seems to work and removes the race condition. > As a note, I tried the incantation that was provided by Scott below (thx) > and some other modifications to it as well. All still had the race (though > not as bad) but the above code eliminated the condition entirely. Don't > know about it's performance implications. > > /B > > > ----- Original Message ----- > From: "scott.marlowe" > To: "David Busby" > Cc: > Sent: Wednesday, November 13, 2002 12:03 > Subject: Re: [PHP] Need to select and update with the same sql statement > > > > On Wed, 13 Nov 2002, David Busby wrote: > > > > > List, > > > I need to do a command like: > > > > > > select * from "table" where "id"=54; update "table" set "col"=value > > > where "id"=just selected id > > > > > > Is that possible? How would I work that into a StoredProcedure? I'm > > > getting a race condition where two+ clients are asking for data but > getting > > > the same record, (each record is a datachunk for a distributed client). > > > This results in each client working on the same data, not good. Any > ideas? > > > I'm posting to the php/sql list cause the clients ask for the datachunk > via > > > SOAP request that is processed via PHP. Any assistance would be great > > > > It's time for transactions! > > > > You should be able to do this in a transaction: > > > > (pg_exec the SQL code here) > > begin; > > select * from table where "id"=54; > > (assign the id to a var $id here) > > update "table" set "col"=$value where "id"=$id; > > end; > > > > This should happen in such a way that other users can't see what's > > happening until it's done. > >