pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Need to select and update with the same sql statement
7+ messages / 3 participants
[nested] [flat]

* Need to select and update with the same sql statement
@ 2002-11-13 19:29 David Busby <busby@pnts.com>
  2002-11-13 20:03 ` Re: Need to select and update with the same sql statement scott.marlowe <scott.marlowe@ihs.com>
  0 siblings, 1 reply; 7+ messages in thread

From: David Busby @ 2002-11-13 19:29 UTC (permalink / raw)
  To: pgsql-php

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

/B




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Need to select and update with the same sql statement
  2002-11-13 19:29 Need to select and update with the same sql statement David Busby <busby@pnts.com>
@ 2002-11-13 20:03 ` scott.marlowe <scott.marlowe@ihs.com>
  2002-11-13 21:45   ` Re: Need to select and update with the same sql statement David Busby <busby@pnts.com>
  0 siblings, 1 reply; 7+ messages in thread

From: scott.marlowe @ 2002-11-13 20:03 UTC (permalink / raw)
  To: David Busby <busby@pnts.com>; +Cc: pgsql-php

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.




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Need to select and update with the same sql statement
  2002-11-13 19:29 Need to select and update with the same sql statement David Busby <busby@pnts.com>
  2002-11-13 20:03 ` Re: Need to select and update with the same sql statement scott.marlowe <scott.marlowe@ihs.com>
@ 2002-11-13 21:45   ` David Busby <busby@pnts.com>
  2002-11-13 22:01     ` Re: Need to select and update with the same sql statement scott.marlowe <scott.marlowe@ihs.com>
  0 siblings, 1 reply; 7+ messages in thread

From: David Busby @ 2002-11-13 21:45 UTC (permalink / raw)
  To: scott.marlowe <scott.marlowe@ihs.com>; +Cc: pgsql-php

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.




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Need to select and update with the same sql statement
  2002-11-13 19:29 Need to select and update with the same sql statement David Busby <busby@pnts.com>
  2002-11-13 20:03 ` Re: Need to select and update with the same sql statement scott.marlowe <scott.marlowe@ihs.com>
  2002-11-13 21:45   ` Re: Need to select and update with the same sql statement David Busby <busby@pnts.com>
@ 2002-11-13 22:01     ` scott.marlowe <scott.marlowe@ihs.com>
  2002-11-13 22:26       ` Re: Need to select and update with the same sql statement scott.marlowe <scott.marlowe@ihs.com>
  0 siblings, 1 reply; 7+ messages in thread

From: scott.marlowe @ 2002-11-13 22:01 UTC (permalink / raw)
  To: David Busby <busby@pnts.com>; +Cc: pgsql-php

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.
> 
> 




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Need to select and update with the same sql statement
  2002-11-13 19:29 Need to select and update with the same sql statement David Busby <busby@pnts.com>
  2002-11-13 20:03 ` Re: Need to select and update with the same sql statement scott.marlowe <scott.marlowe@ihs.com>
  2002-11-13 21:45   ` Re: Need to select and update with the same sql statement David Busby <busby@pnts.com>
  2002-11-13 22:01     ` Re: Need to select and update with the same sql statement scott.marlowe <scott.marlowe@ihs.com>
@ 2002-11-13 22:26       ` scott.marlowe <scott.marlowe@ihs.com>
  2002-11-13 22:29         ` Re: Need to select and update with the same sql statement David Busby <busby@pnts.com>
  2002-11-14 13:45         ` Re: Need to select and update with the same sql statement Bruno Wolff III <bruno@wolff.to>
  0 siblings, 2 replies; 7+ messages in thread

From: scott.marlowe @ 2002-11-13 22:26 UTC (permalink / raw)
  To: David Busby <busby@pnts.com>; +Cc: pgsql-php


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
> 




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Need to select and update with the same sql statement
  2002-11-13 19:29 Need to select and update with the same sql statement David Busby <busby@pnts.com>
  2002-11-13 20:03 ` Re: Need to select and update with the same sql statement scott.marlowe <scott.marlowe@ihs.com>
  2002-11-13 21:45   ` Re: Need to select and update with the same sql statement David Busby <busby@pnts.com>
  2002-11-13 22:01     ` Re: Need to select and update with the same sql statement scott.marlowe <scott.marlowe@ihs.com>
  2002-11-13 22:26       ` Re: Need to select and update with the same sql statement scott.marlowe <scott.marlowe@ihs.com>
@ 2002-11-13 22:29         ` David Busby <busby@pnts.com>
  1 sibling, 0 replies; 7+ messages in thread

From: David Busby @ 2002-11-13 22:29 UTC (permalink / raw)
  To: scott.marlowe <scott.marlowe@ihs.com>; +Cc: pgsql-php

Scott; List,
    Actually running with the "for update" on my SQL statement was one of
the things that I tried, didn't work...It was one that limited by race, so
it only happened on one out of 20/30 messages.  Lock works 100%.

/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 14:26
Subject: Re: [PHP] 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
> >




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Need to select and update with the same sql statement
  2002-11-13 19:29 Need to select and update with the same sql statement David Busby <busby@pnts.com>
  2002-11-13 20:03 ` Re: Need to select and update with the same sql statement scott.marlowe <scott.marlowe@ihs.com>
  2002-11-13 21:45   ` Re: Need to select and update with the same sql statement David Busby <busby@pnts.com>
  2002-11-13 22:01     ` Re: Need to select and update with the same sql statement scott.marlowe <scott.marlowe@ihs.com>
  2002-11-13 22:26       ` Re: Need to select and update with the same sql statement scott.marlowe <scott.marlowe@ihs.com>
@ 2002-11-14 13:45         ` Bruno Wolff III <bruno@wolff.to>
  1 sibling, 0 replies; 7+ messages in thread

From: Bruno Wolff III @ 2002-11-14 13:45 UTC (permalink / raw)
  To: scott.marlowe <scott.marlowe@ihs.com>; +Cc: David Busby <busby@pnts.com>; pgsql-php

On Wed, Nov 13, 2002 at 15:26:42 -0700,
  "scott.marlowe" <scott.marlowe@ihs.com> wrote:
> 
> 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.

This came up in a discussion recently and it really isn't better. If you
do this you have to worry about the select for update returning zero
rows (even with the limit 1 clause) and retry the query if it does.



^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~2002-11-14 13:45 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-11-13 19:29 Need to select and update with the same sql statement David Busby <busby@pnts.com>
2002-11-13 20:03 ` scott.marlowe <scott.marlowe@ihs.com>
2002-11-13 21:45   ` David Busby <busby@pnts.com>
2002-11-13 22:01     ` scott.marlowe <scott.marlowe@ihs.com>
2002-11-13 22:26       ` scott.marlowe <scott.marlowe@ihs.com>
2002-11-13 22:29         ` David Busby <busby@pnts.com>
2002-11-14 13:45         ` Bruno Wolff III <bruno@wolff.to>

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