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

On Wed, Jun 22, 2016 at 6:08 PM Vlad Arkhipov <arhipov(at)dc(dot)baikal(dot)ru> 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(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>
>> 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
>
>
> --
--
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