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/

Excerpts from hubert depesz lubaczewski's message of lun feb 27 10:02:57 -0300 2012:

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.

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

See commits
2c3d9db56d5d49bdc777b174982251c01348e3d8
and
a195e3c34f1eeb6a607c342121edf48e49067ea9

--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

2012/2/27 Alvaro Herrera <alvherre@commandprompt.com>:

Excerpts from hubert depesz lubaczewski's message of lun feb 27 10:02:57 -0300 2012:

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.

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

See commits
2c3d9db56d5d49bdc777b174982251c01348e3d8
and
a195e3c34f1eeb6a607c342121edf48e49067ea9

this block a sense of NOT VALIDATE constraints. Is it final behave or
will be fixed on 9.2?

Regards

Pavel

Excerpts from Pavel Stehule's message of lun feb 27 10:41:32 -0300 2012:

2012/2/27 Alvaro Herrera <alvherre@commandprompt.com>:

Excerpts from hubert depesz lubaczewski's message of lun feb 27 10:02:57 -0300 2012:

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.

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

See commits
2c3d9db56d5d49bdc777b174982251c01348e3d8
and
a195e3c34f1eeb6a607c342121edf48e49067ea9

this block a sense of NOT VALIDATE constraints.

Yeah :-(

Is it final behave or will be fixed on 9.2?

It's final for 9.2 AFAIK. It's supposed to get fixed during the 9.3
timeframe.

--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

2012/2/27 Alvaro Herrera <alvherre@commandprompt.com>:

Excerpts from Pavel Stehule's message of lun feb 27 10:41:32 -0300 2012:

2012/2/27 Alvaro Herrera <alvherre@commandprompt.com>:

Excerpts from hubert depesz lubaczewski's message of lun feb 27 10:02:57 -0300 2012:

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.

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

See commits
2c3d9db56d5d49bdc777b174982251c01348e3d8
and
a195e3c34f1eeb6a607c342121edf48e49067ea9

 this block a sense of NOT VALIDATE constraints.

Yeah :-(

Is it final behave or will be fixed on 9.2?

It's final for 9.2 AFAIK.  It's supposed to get fixed during the 9.3
timeframe.

ok

thank you for info

Pavel