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

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

> Sameer Kumar wrote:
> > 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.
>
> 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;$$;