Re: Locking to restrict rowcounts.

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.

--
Richard Huxton
Archonet Ltd