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 │ t
virtualxid │ [null] │ [null] │ [null] │ [null] │ 2/174 │ [null] │ [null] │ [null] │ [null] │ 2/174 │ 8975 │ ExclusiveLock │ t │ t
transactionid │ [null] │ [null] │ [null] │ [null] │ [null] │ 854 │ [null] │ [null] │ [null] │ 2/174 │ 8975 │ ExclusiveLock │ t │ f
relation │ 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 │ t
virtualxid │ [null] │ [null] │ [null] │ [null] │ 2/175 │ [null] │ [null] │ [null] │ [null] │ 2/175 │ 8975 │ ExclusiveLock │ t │ t
transactionid │ [null] │ [null] │ [null] │ [null] │ [null] │ 855 │ [null] │ [null] │ [null] │ 2/175 │ 8975 │ ExclusiveLock │ t │ f
relation │ 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/