Re: Need to select and update with the same sql statement

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" <scott(dot)marlowe(at)ihs(dot)com>
> To: "David Busby" <busby(at)pnts(dot)com>
> Cc: <pgsql-php(at)postgresql(dot)org>
> 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.
>
>