July 1st, 2011 by depesz | Tags: , , , , , , , | 3 comments »
Did it help? If yes - maybe you can help me?

On 30th of June, Alvaro Herrera committed patch:

Enable CHECK constraints to be declared NOT VALID
 
This means that they can initially be added to a large existing table
without checking its initial contents, but new tuples must comply to
them; a separate pass invoked by ALTER TABLE / VALIDATE can verify
existing data and ensure it complies with the constraint, at which point
it is marked validated and becomes a normal part of the table ecosystem.
 
An non-validated CHECK constraint is ignored in the planner for
constraint_exclusion purposes; when validated, cached plans are
recomputed so that partitioning starts working right away.
 
This patch also enables domains to have unvalidated CHECK constraints
attached to them as well by way of ALTER DOMAIN / ADD CONSTRAINT / NOT
VALID, which can later be validated with ALTER DOMAIN / VALIDATE
CONSTRAINT.
 
Thanks to Thom Brown, Dean Rasheed and Jaime Casanova for the various
reviews, and Robert Hass for documentation wording improvement
suggestions.
 
This patch was sponsored by Enova Financial.

Some time ago I wrote about NOT VALID FOREIGN KEYS in 9.1. Now for 9.2, we'll be getting the same mechanism for CHECK constraints.

Normally one would add check constraint with:

ALTER TABLE test ADD CHECK ( field >= 0 );

But now we can do it in two steps:

ALTER TABLE test ADD CHECK ( field >= 0 ) NOT VALID;
ALTER TABLE test VALIDATE CONSTRAINT test_field_check;

So. Let's see if, and if yes – why it's cool.

For tests, I created simple tables:

$ create table test (id serial, some_text text, field int4);
NOTICE:  CREATE TABLE will create implicit sequence "test_id_seq" for serial column "test.id"
CREATE TABLE
 
$ insert into test (some_text, field)
    select repeat('depesz', 50), cast( random() * 120000 as int4)
    from generate_series(1,1000000);
INSERT 0 1000000
 
$ alter table test add primary key (id);
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "test_pkey" for table "test"
ALTER TABLE
 
$ create table test2 (id serial, some_text text, field int4);
NOTICE:  CREATE TABLE will create implicit sequence "test2_id_seq" for serial column "test2.id"
CREATE TABLE
 
$ insert into test2 (id, some_text, field) select * from test;
INSERT 0 1000000
 
$ alter table test2 add primary key (id);
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "test2_pkey" for table "test2"
ALTER TABLE

Structure and data in both tables are exactly the same. Each table, with indexes and toast takes ~ 360MB.

So, let's see speed and locks:

$ begin;
BEGIN
 
*$ ALTER TABLE test ADD CHECK ( field >= 0 );
ALTER TABLE
Time: 206.138 ms
 
*$ select * from pg_locks where pid = pg_backend_pid();
   locktype    | database | relation |  page  | tuple  | virtualxid | transactionid | classid | objid  | objsubid | virtualtransaction | pid  |           mode           | granted
---------------+----------+----------+--------+--------+------------+---------------+---------+--------+----------+--------------------+------+--------------------------+---------
 relation      |    16404 |    26486 | [null] | [null] | [null]     |        [null] |  [null] | [null] |   [null] | 2/295              | 9149 | ShareUpdateExclusiveLock | t
 relation      |    16404 |    26486 | [null] | [null] | [null]     |        [null] |  [null] | [null] |   [null] | 2/295              | 9149 | ShareRowExclusiveLock    | t
 transactionid |   [null] |   [null] | [null] | [null] | [null]     |          1399 |  [null] | [null] |   [null] | 2/295              | 9149 | ExclusiveLock            | t
 relation      |    16404 |    11000 | [null] | [null] | [null]     |        [null] |  [null] | [null] |   [null] | 2/295              | 9149 | AccessShareLock          | t
 virtualxid    |   [null] |   [null] | [null] | [null] | 2/295      |        [null] |  [null] | [null] |   [null] | 2/295              | 9149 | ExclusiveLock            | t
(5 rows)
 
*$ select 11000::regclass, 26486::regclass;
 regclass | regclass
----------+----------
 pg_locks | test
(1 row)

OK. It took 206ms, and for this time it took ShareUpdateExclusiveLock and ShareRowExclusiveLock locks. Which, thanks to this table we know that it conflicts with everything except Access Share and Row Share – which basically turns table into read-only.

Now. What about new method?

First operation:

$ begin;
BEGIN
Time: 0.249 ms
 
*$ ALTER TABLE test2 ADD CHECK ( field >= 0 ) NOT VALID;
ALTER TABLE
Time: 0.871 ms
 
*$ select * from pg_locks where pid = pg_backend_pid();
   locktype    | database | relation |  page  | tuple  | virtualxid | transactionid | classid | objid  | objsubid | virtualtransaction | pid  |           mode           | granted
---------------+----------+----------+--------+--------+------------+---------------+---------+--------+----------+--------------------+------+--------------------------+---------
 virtualxid    |   [null] |   [null] | [null] | [null] | 2/296      |        [null] |  [null] | [null] |   [null] | 2/296              | 9149 | ExclusiveLock            | t
 relation      |    16404 |    26497 | [null] | [null] | [null]     |        [null] |  [null] | [null] |   [null] | 2/296              | 9149 | ShareUpdateExclusiveLock | t
 relation      |    16404 |    26497 | [null] | [null] | [null]     |        [null] |  [null] | [null] |   [null] | 2/296              | 9149 | ShareRowExclusiveLock    | t
 transactionid |   [null] |   [null] | [null] | [null] | [null]     |          1400 |  [null] | [null] |   [null] | 2/296              | 9149 | ExclusiveLock            | t
 relation      |    16404 |    11000 | [null] | [null] | [null]     |        [null] |  [null] | [null] |   [null] | 2/296              | 9149 | AccessShareLock          | t
(5 rows)
 
*$ select 26497::regclass;
 regclass
----------
 test2
(1 row)

OK. So we had the same level of locks, buit only for 0.871ms.

Now. In another transaction, I can run the validation:

$ begin;
BEGIN
Time: 0.139 ms
 
*$ ALTER TABLE test2 VALIDATE CONSTRAINT test2_field_check;
ALTER TABLE
Time: 201.310 ms
 
*$ select * from pg_locks where pid = pg_backend_pid();
   locktype    | database | relation |  page  | tuple  | virtualxid | transactionid | classid | objid  | objsubid | virtualtransaction | pid  |           mode           | granted
---------------+----------+----------+--------+--------+------------+---------------+---------+--------+----------+--------------------+------+--------------------------+---------
 relation      |    16404 |    26497 | [null] | [null] | [null]     |        [null] |  [null] | [null] |   [null] | 2/297              | 9149 | ShareUpdateExclusiveLock | t
 transactionid |   [null] |   [null] | [null] | [null] | [null]     |          1401 |  [null] | [null] |   [null] | 2/297              | 9149 | ExclusiveLock            | t
 virtualxid    |   [null] |   [null] | [null] | [null] | 2/297      |        [null] |  [null] | [null] |   [null] | 2/297              | 9149 | ExclusiveLock            | t
 relation      |    16404 |    11000 | [null] | [null] | [null]     |        [null] |  [null] | [null] |   [null] | 2/297              | 9149 | AccessShareLock          | t
(4 rows)

OK. Time is the same as in traditional way. But the locking is different – we have only ShareUpdateExclusiveLock, and no more ShareRowExclusiveLock. This change means that all modifications on the table will work just fine during the validation phase!

Let's see if it's really the case.

First, I'll drop the constraint:

$ alter table test2 drop CONSTRAINT test2_field_check;

Now, let's re-add invalid check:

$ ALTER TABLE test2 ADD CHECK ( field >= 0 ) NOT VALID;

With this in place, in first psql, I run:

psql-1 $ begin;
BEGIN
 
psql-1 1*$ ALTER TABLE test2 VALIDATE CONSTRAINT test2_field_check;
ALTER TABLE

and leave it in transaction.

Afterwards, I run another psql, and let's test some updates:

psql-2 $ update test2 set field = 50 where id < 20;
UPDATE 19
 
psql-2 $ delete from test2 where id < 10;
DELETE 9

All works. Sweet. That's really cool stuff.

  1. 3 comments

  2. # BobH
    Jul 6, 2011

    Thanks for the write up. This features is really cool, now we can add new constraints to huge tables with little relative impact!

  3. will we be able to add NOT VALID UNIQUE constraints? or will we have to fake it?

  4. Dec 7, 2011

    @Caleb: not yet. Maybe in some future patches in 9.2, but not yet.

Leave a comment