Search Postgresql Archives

Re: Protect a table against concurrent data changes while allowing to vacuum it

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

 





On Wed, Jun 22, 2016 at 6:08 PM Vlad Arkhipov <arhipov@xxxxxxxxxxxx> wrote:
I am running PostgreSQL 9.5.

CREATE TABLE t (id BIGINT NOT NULL PRIMARY KEY, name TEXT);

The constraint that the data must satisfy is `there is no more than 3 records with the same name`.

I am not in control of queries that modify the table, so advisory locks can hardly be of help to me.

Define a function which does a count of the rows and if count is 3 it return false if count is less it returns true.

Use check constraint with this function. I have not tried this so not sure if you can use function with SELECT on same table in CHECK constraint. So test it out first.

If this works, any insert trying to get the 4th record in table would fail.

A last resort could be using triggers. But either of these approaches will cause issues if you have high concurrency.

 


On 06/22/2016 05:20 PM, Sameer Kumar wrote:


On Wed, Jun 22, 2016 at 5:10 PM Vlad Arkhipov <arhipov@xxxxxxxxxxxx> wrote:
Hello,

I have a constraint that requires a table to be locked before checking
it (i.e. no more than 2 records with the same value in the same column).
If I lock the table in the SHARE ROW EXCLUSIVE mode, any vacuuming (or
autovacuuming) process prevents me from checking the constraint. What
are possible solutions?

May be you would like to share-
- Table Structure
- PostgreSQL version

This will help people who would try to help you.

I think you might want to consider an optimistic way of locking your records, instead of locking them. Or look at advisory locks (but that depends on your Postgres version).
 


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
--
Best Regards
Sameer Kumar | DB Solution Architect 
ASHNIK PTE. LTD.

101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533

T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com


--
--
Best Regards
Sameer Kumar | DB Solution Architect 
ASHNIK PTE. LTD.

101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533

T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com


[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