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

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?

Vlad Arkhipov wrote:
> 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?

Can you describe your check in more detail?
Why don't you use simple unique constraints?

Yours,
Laurenz Albe

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 Wed, Jun 22, 2016 at 5:10 PM Vlad Arkhipov <arhipov@dc.baikal.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@postgresql.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

Sameer Kumar wrote:
> On Wed, Jun 22, 2016 at 6:08 PM Vlad Arkhipov <arhipov@dc.baikal.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.

You cannot use subqueries in a check constraint:

ALTER TABLE t
   ADD CONSTRAINT name_count
      CHECK ((SELECT count(*) FROM t t1 WHERE t1.name = name) <= 3);
ERROR:  cannot use subquery in check constraint

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

Yes, triggers is the way to go:

CREATE OR REPLACE FUNCTION check_t() RETURNS trigger LANGUAGE plpgsql AS
$$BEGIN
   IF (SELECT count(*) FROM t WHERE name = NEW.name) > 3 THEN
      RAISE EXCEPTION 'More than three values!';
   END IF;
   RETURN NEW;
END;$$;

CREATE TRIGGER check_t AFTER INSERT OR UPDATE ON t FOR EACH ROW
   EXECUTE PROCEDURE check_t();

But be warned that this will only work if all transactions involved use
the isolation level SERIALIZABLE.

Otherwise two concurrent INSERTs would not see each other's entry, and the
triggers would not raise an error even if there are more than three entries
after COMMIT.

Yours,
Laurenz Albe

That is why I need to lock the table before. The transactions are
running at the READ COMMITTED isolation level.

On 06/22/2016 06:49 PM, Albe Laurenz wrote:
> But be warned that this will only work if all transactions involved use
> the isolation level SERIALIZABLE.

Le Wed, 22 Jun 2016 10:49:13 +0000,
Albe Laurenz <laurenz.albe@wien.gv.at> a écrit :

> Sameer Kumar wrote:
> > On Wed, Jun 22, 2016 at 6:08 PM Vlad Arkhipov <arhipov@dc.baikal.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.
>
> You cannot use subqueries in a check constraint:
>
> ALTER TABLE t
>    ADD CONSTRAINT name_count
>       CHECK ((SELECT count(*) FROM t t1 WHERE t1.name = name) <= 3);
> ERROR:  cannot use subquery in check constraint
>
> > A last resort could be using triggers. But either of these approaches will
> > cause issues if you have high concurrency.
>
> Yes, triggers is the way to go:
>
> CREATE OR REPLACE FUNCTION check_t() RETURNS trigger LANGUAGE plpgsql AS
> $$BEGIN
>    IF (SELECT count(*) FROM t WHERE name = NEW.name) > 3 THEN
>       RAISE EXCEPTION 'More than three values!';
>    END IF;
>    RETURN NEW;
> END;$$;
>
> CREATE TRIGGER check_t AFTER INSERT OR UPDATE ON t FOR EACH ROW
>    EXECUTE PROCEDURE check_t();
>
> But be warned that this will only work if all transactions involved use
> the isolation level SERIALIZABLE.
>
> Otherwise two concurrent INSERTs would not see each other's entry, and the
> triggers would not raise an error even if there are more than three entries
> after COMMIT.

Use advisory locks to be able to use this in any isolation level:

  CREATE OR REPLACE FUNCTION check_t() RETURNS trigger LANGUAGE plpgsql AS
  $$BEGIN
     PERFORM pg_advisory_xact_lock(hashtext(NEW.name));

     IF (SELECT count(*) FROM t WHERE name = NEW.name) > 3 THEN
        RAISE EXCEPTION 'More than three values!';
     END IF;
     RETURN NEW;
  END;$$;

Le Wed, 22 Jun 2016 18:07:46 +0800,
Vlad Arkhipov <arhipov@dc.baikal.ru> a écrit :

> I am running PostgreSQL 9.5.
>
> CREATE TABLE t (id BIGINT NOT NULL PRIMARY KEY, name TEXT);

I guess this is not your definitive definition of the table and you might
have some other fields isn't it ?

I can see multiple way to change this schema that seems broken, but we probably
lack informations to pick the right one...

> 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@dc.baikal.ru
> > <mailto:arhipov@dc.baikal.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).

On 6/22/2016 3:07 AM, Vlad Arkhipov wrote:
>
> 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.

     select name from t group by name having count(id)>3

will return all names with more than 3 records in a single query...  now
the question is, what do you want to do with this information ?



--
john r pierce, recycling bits in santa cruz

On Wed, 22 Jun 2016 10:20:38 +0000
Sameer Kumar <sameer.kumar@ashnik.com> wrote:

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

An exclusion constraint might be a better solution.

--
Bill Moran