CODEWITHSUNDEEP
postgresqlpartitioning
Sam Schick
Sam Schick

Reputation: 430

Will Postgres enforce check constraints on columns that I didn't update?

When I update a column that has a check constraint, clearly postgres has to check and see if it still meets the constraint. but if I update some other value in the row and leave all columns with check constraints unchanged, will it still do some test on those? I'm trying to figure out how much of a performance hit it is to have extra check constraints on columns I won't be modifying so that I can get more utility out of constraint exclusion on a partitioned table. My table has a series of dimension columns, each of which (or at least many of which) would have check constraints that I know will be true based on the partitioning constraint. I never insert data into this table after a batch job, but I do a series of updates on a data column which has no constraints.

Upvotes: 3

Views: 1016

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247235

PostgreSQL will check the constraints whenever the row is changed, regardless if the values in the check expression are changed or not.

See this example:

CREATE TABLE log (
    id SERIAL PRIMARY KEY,
    checked integer NOT NULL
);

CREATE OR REPLACE FUNCTION checker(i integer) RETURNS boolean
    LANGUAGE plpgsql VOLATILE AS
$$BEGIN
    INSERT INTO log (checked) VALUES (i);
    RETURN i > 0;
END;$$;

CREATE TABLE checkme (
    id integer PRIMARY KEY,
    checked integer NOT NULL
        CHECK (checker(checked)),
    unchecked integer NOT NULL
);

The second UPDATE only changed the unchecked column:

INSERT INTO checkme (id, checked, unchecked)
    VALUES (1, 42, 45);

UPDATE checkme
    SET checked = 100
    WHERE id = 1;

UPDATE checkme
    SET unchecked = 100
    WHERE id = 1;

Now let's see when the constraint was checked:

SELECT * FROM log;

 id | checked
----+---------
  1 |      42
  4 |     100
  5 |     100

Upvotes: 8

Related Questions