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

Oh, I just thought of the better way than locking the whole table is to
use a "select for update" on the row you want to lock.  I think that'll do
what you want and without locking the whole table.  Of course, 20 lines of
PHP code runs pretty fast, so unless you're handling lotsa traffic locking
the table probably works fine too.

On Wed, 13 Nov 2002, scott.marlowe wrote:

> 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.marlowe@ihs.com>
> > To: "David Busby" <busby@pnts.com>
> > Cc: <pgsql-php@postgresql.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.
> >
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>