Re: [HACKERS] 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/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] check constraint validation takes access exclusive locks
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 alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] check constraint validation takes access exclusive locks
2012/2/27 Alvaro Herrera alvhe...@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 -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] check constraint validation takes access exclusive locks
Excerpts from Pavel Stehule's message of lun feb 27 10:41:32 -0300 2012: 2012/2/27 Alvaro Herrera alvhe...@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 alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] check constraint validation takes access exclusive locks
2012/2/27 Alvaro Herrera alvhe...@commandprompt.com: Excerpts from Pavel Stehule's message of lun feb 27 10:41:32 -0300 2012: 2012/2/27 Alvaro Herrera alvhe...@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 -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
