Search Postgresql Archives

Re: Locking to restrict rowcounts.

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

 



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

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