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

[Vlad Arkhipov <arhipov@xxxxxxxxxxxx>]

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.

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