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