Re: check constraint validation takes access exclusive locks

On Sun, Feb 26, 2012 at 03:04:28PM +0100, Pavel Stehule wrote:
> Hello
> 
> I rechecked Depesz's article -
> http://www.depesz.com/2011/07/01/waiting-for-9-2-not-valid-checks/
> 
> The behave of current HEAD is different than behave described in article.
> 
> "alter table a validate constraint a_a_check" needs a access exclusive
> locks and blocks table modification - I tested inserts.
> 
> Is it expected behave.
> 
> session one:
> 
> postgres=# create table a(a int);
> CREATE TABLE
> postgres=# alter table a add check (a > 0) not valid;
> ALTER TABLE
> postgres=# begin;
> BEGIN
> postgres=# alter table a validate constraint a_a_check;
> ALTER TABLE
> 
> session two:
> 
> postgres=# update a set a = 100; -- it waits to commit in session one

yes, looks like we have revert to access exclusive lock:

$ begin;
BEGIN
Time: 0.352 ms

*$ ALTER TABLE test2 ADD CHECK ( field >= 0 ) NOT VALID;
ALTER TABLE
Time: 0.662 ms

*$ select * from pg_locks where pid = pg_backend_pid();  locktype    │ database │ relation │  page  │ tuple  │
virtualxid│ transactionid │ classid │ objid  │ objsubid │ virtualtransaction │ pid  │        mode         │ granted │
fastpath

───────────────┼──────────┼──────────┼────────┼────────┼────────────┼───────────────┼─────────┼────────┼──────────┼────────────────────┼──────┼─────────────────────┼─────────┼──────────relation
    │    16387 │    11070 │ [null] │ [null] │ [null]     │        [null] │  [null] │ [null] │   [null] │ 2/174
   │ 8975 │ AccessShareLock     │ t       │ tvirtualxid    │   [null] │   [null] │ [null] │ [null] │ 2/174      │
[null] │  [null] │ [null] │   [null] │ 2/174              │ 8975 │ ExclusiveLock       │ t       │ ttransactionid │
[null]│   [null] │ [null] │ [null] │ [null]     │           854 │  [null] │ [null] │   [null] │ 2/174              │
8975│ ExclusiveLock       │ t       │ frelation      │    16387 │    18653 │ [null] │ [null] │ [null]     │
[null]│  [null] │ [null] │   [null] │ 2/174              │ 8975 │ AccessExclusiveLock │ t       │ f
 
(4 rows)

Time: 0.921 ms

Relation 18653 is table test2, of course.

*$ commit;
COMMIT

$ begin;
BEGIN
Time: 0.271 ms

*$ ALTER TABLE test2 VALIDATE CONSTRAINT test2_field_check;
ALTER TABLE
Time: 286.035 ms

*$ select * from pg_locks where pid = pg_backend_pid();  locktype    │ database │ relation │  page  │ tuple  │
virtualxid│ transactionid │ classid │ objid  │ objsubid │ virtualtransaction │ pid  │        mode         │ granted │
fastpath

───────────────┼──────────┼──────────┼────────┼────────┼────────────┼───────────────┼─────────┼────────┼──────────┼────────────────────┼──────┼─────────────────────┼─────────┼──────────relation
    │    16387 │    11070 │ [null] │ [null] │ [null]     │        [null] │  [null] │ [null] │   [null] │ 2/175
   │ 8975 │ AccessShareLock     │ t       │ tvirtualxid    │   [null] │   [null] │ [null] │ [null] │ 2/175      │
[null] │  [null] │ [null] │   [null] │ 2/175              │ 8975 │ ExclusiveLock       │ t       │ ttransactionid │
[null]│   [null] │ [null] │ [null] │ [null]     │           855 │  [null] │ [null] │   [null] │ 2/175              │
8975│ ExclusiveLock       │ t       │ frelation      │    16387 │    18653 │ [null] │ [null] │ [null]     │
[null]│  [null] │ [null] │   [null] │ 2/175              │ 8975 │ AccessExclusiveLock │ t       │ f
 
(4 rows)

Time: 0.631 ms

And it clearly shows that validation of constraint did lock the table
using AccessExclusiveLock, which kinda defeats the purpose of
INVALID/VALIDATE.

Best regards,

depesz

-- 
The best thing about modern society is how easy it is to avoid contact with it.
                  http://depesz.com/