Search Postgresql Archives

Re: Locking to restrict rowcounts.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Richard Huxton wrote:
Shakil Shaikh wrote:
Hi,

Consider the following scenario:

CREATE FUNCTION test(name)
select into cnt count(id) from items where owner = name;
--suppose someone inserts during this point? then next check will succeed when it should not.
if (cnt < 10) then
   insert into items values ('new item', name);
end;
end;

What is the best way to stop this function from entering too many items in a concurrent context? I think a lock is needed, although I'm not sure which would be most appropriate since the count requires the whole table

Well, you can't use FOR UPDATE with aggregates, otherwise you could do:
  SELECT into cnt count(id) FROM items WHERE owner = name FOR UPDATE;
So, you'd need:
  PERFORM * FROM items WHERE owner = name FOR UPDATE;

That won't stop someone blindly inserting into items, but it will act as an effective lock on calling test() concurrently.

The other options would be:
1. Lock the relevant row in the users table (not so good if you have an items2 table with similar requirements)
2. Advisory locks at the application level
3. Check after an insert on the items table and raise an exception if there are 11+ items.

I'd be tempted by #3 - assuming most of the time you won't breach this limit.


#3 won't work unless the other transactions have all committed by the time you do the check. It is guaranteed to fail at some point.

In cases like this, in the unusual case that I don't already have some suitable row locked, I just pick a row in some table that makes sense to lock. There would be nothing wrong with creating a table with rows that exist solely for the purpose of locking. This is a (usually) better version of option #2 above.

-Glen


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux