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
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 |