Обсуждение: UPDATE w/ subselect doing locking

Would someone mind setting my head on straight with a proper query
please.  Here is my table and the query I have thusfar.  I need it to
match and update specifically only the string I specify for the regex.

bluelist=> select * from spamcontrol;
  id | logical group | count |           add time            |    added
by    |                     regex                     | comment

----+---------------+-------+-------------------------------+----------------+-----------------------------------------------+---------
     |               |     5 | 2001-11-12 00:09:41.326116-05 |
208.179.59.203 | MERCATI FINANZIARI                            |
     |               |     5 | 2001-11-12 00:10:24.292267-05 |
208.179.59.203 | setup fees                                    |


bluelist=> UPDATE spamcontrol SET count=foo.count+1 FROM (SELECT count
FROM spamcontrol WHERE regex='setup fees' FOR UPDATE) as foo;

How do I get it to only update that one row?

Thank you,
David

On Wed, Nov 14, 2001 at 02:34:51AM -0500, David Ford wrote:
> bluelist=> UPDATE spamcontrol SET count=foo.count+1 FROM (SELECT count
> FROM spamcontrol WHERE regex='setup fees' FOR UPDATE) as foo;
>
> How do I get it to only update that one row?

What's the problem with:

UPDATE spamcontrol SET count=count+1 WHERE regex='setup fees';

HTH,
--
Martijn van Oosterhout <kleptog@svana.org>
http://svana.org/kleptog/
> Magnetism, electricity and motion are like a three-for-two special offer:
> if you have two of them, the third one comes free.

On Wed, 14 Nov 2001, David Ford wrote:

> Would someone mind setting my head on straight with a proper query
> please.  Here is my table and the query I have thusfar.  I need it to
> match and update specifically only the string I specify for the regex.
>
> bluelist=> select * from spamcontrol;
>   id | logical group | count |           add time            |    added
> by    |                     regex                     | comment
>
----+---------------+-------+-------------------------------+----------------+-----------------------------------------------+---------
>      |               |     5 | 2001-11-12 00:09:41.326116-05 |
> 208.179.59.203 | MERCATI FINANZIARI                            |
>      |               |     5 | 2001-11-12 00:10:24.292267-05 |
> 208.179.59.203 | setup fees                                    |
>
>
> bluelist=> UPDATE spamcontrol SET count=foo.count+1 FROM (SELECT count
> FROM spamcontrol WHERE regex='setup fees' FOR UPDATE) as foo;
>
> How do I get it to only update that one row?

Wouldn't update spamcontrol set count=count+1 where regex='setup fees';
do the same thing or am I missing something?

Yes, I know that works.  I want to figure out how to use SELECT ... FOR
UPDATE with UPDATE because I will have a need to lock these certain rows
and do a bit more than just 'count'.

Thank you,
David

Marc SCHAEFER wrote:

>On Wed, 14 Nov 2001, David Ford wrote:
>
>>bluelist=> UPDATE spamcontrol SET count=foo.count+1 FROM (SELECT count
>>FROM spamcontrol WHERE regex='setup fees' FOR UPDATE) as foo;
>>
>
>You want to increment atomically the count of the row where regex is setup
>fees ?
>
>UPDATE spamcontrol SET count = count + 1 WHERE regex = 'setup fees';
>

On Thu, 15 Nov 2001, David Ford wrote:

> Yes, I know that works.  I want to figure out how to use SELECT ... FOR
> UPDATE with UPDATE because I will have a need to lock these certain rows
> and do a bit more than just 'count'.
>
> >>bluelist=> UPDATE spamcontrol SET count=foo.count+1 FROM (SELECT count
> >>FROM spamcontrol WHERE regex='setup fees' FOR UPDATE) as foo;

In general the problem you're running into in your query is that you're
not specifying which values in foo go with which values in spamcontrol.
You're not limiting the update to only the rows that have 'setup fees'
you're updating all rows with a value from the row that has 'setup fees'.

On 15 Nov 2001 at 12:12 (-0500), David Ford wrote:
| Yes, I know that works.  I want to figure out how to use SELECT ... FOR
| UPDATE with UPDATE because I will have a need to lock these certain rows
| and do a bit more than just 'count'.

AFAIK, you need to do the following the use the SELECT ... FOR UPDATE

 BEGIN;
  SELECT ... FOR UPDATE;
  UPDATE ... SET ...;
 COMMIT;

Any other session attempting an update on the selected for update rows
will block on the locked rows until the end of that xact.

hth.
  brent

--
"Develop your talent, man, and leave the world something. Records are
really gifts from people. To think that an artist would love you enough
to share his music with anyone is a beautiful thing."  -- Duane Allman