Locking to restrict rowcounts.

From: "Shakil Shaikh" <sshaikh(at)hotmail(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Locking to restrict rowcounts.
Date: 2009-05-19 17:55:25
Message-ID: BAY117-DS963528810A4240924FAD0AC5B0@phx.gbl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Merlin Moncure 2009-05-19 18:01:24 Re: Get block of N numbers from sequence
Previous Message Richard Huxton 2009-05-19 17:25:13 Re: origins/destinations