Thread: Locking to restrict rowcounts.
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 (or at
least no adds to have occurred I think I read something about predicate
locking which sounds relevant but isn't supported in PostgreSQL. Ideally I
want some kind of lock only relevant to "name" above.
Any strategies to deal with this?
Shak
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
Glen Parker wrote: > Richard Huxton wrote: >> 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. If it's in an AFTER INSERT/UPDATE trigger then whatever transaction takes you beyond 10 rows you will always get a failure. If you get a lot of insert/delete pairings then you could spend a lot of time counting rows, but that's all. > 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. Of course, if you're going to have a separate table then you might as well store the count in there and actually update it on every insert/update/delete. Assuming you might find the count of some use somewhere. Set the fill-factor for the lock table and HOT should prevent the table bloating too. -- Richard Huxton Archonet Ltd
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
Richard Huxton wrote: > Glen Parker wrote: >> Richard Huxton wrote: >> >> #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. > > If it's in an AFTER INSERT/UPDATE trigger then whatever transaction > takes you beyond 10 rows you will always get a failure. If you get a lot > of insert/delete pairings then you could spend a lot of time counting > rows, but that's all. I still don't think it will work. Two concurrent transactions could still screw the data up. Before: 9 rows. Tx 1: See 9 rows, issue insert, see 10 rows. Tx 2: See 9 rows, issue insert, see 10 rows. After: 11 rows. -Glen
-------------------------------------------------- From: "Richard Huxton" <dev@archonet.com> > > Of course, if you're going to have a separate table then you might as well > store the count in there and actually update it on every > insert/update/delete. Assuming you might find the count of some use > somewhere. Set the fill-factor for the lock table and HOT should prevent > the table bloating too. > I think PERFORM * FROM items WHERE owner = name FOR UPDATE; sounds like it should work the best. What are the downsides for this that would require the further table of counts? FWIW items has a SERIAL primary key so FOR UPDATE should work on it. Shak
Just realised I didn't cc: the list on this. Was obviously having a good evening. Richard Huxton wrote: > Glen Parker wrote: > > I still don't think it will work. Two concurrent transactions could >> still screw the data up. >> >> Before: 9 rows. >> Tx 1: See 9 rows, issue insert, see 10 rows. >> Tx 2: See 9 rows, issue insert, see 10 rows. >> After: 11 rows. > > True, and well corrected. > > Serves me right for posting at the end of a long day. Particularly > irritating as its almost identical to the example I've used myself when > showing the limitations of PG's low-locking MVCC model. > -- Richard Huxton Archonet Ltd