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

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(at)dc(dot)baikal(dot)ru
> <mailto:arhipov(at)dc(dot)baikal(dot)ru>> 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(at)postgresql(dot)org
> <mailto:pgsql-general(at)postgresql(dot)org>)
> 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
>