Обсуждение: check constraint validation takes access exclusive locks
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 Regards Pavel Stehule
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/
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 > > -- > Álvaro Herrera <alvherre@commandprompt.com> > The PostgreSQL Company - Command Prompt, Inc. > PostgreSQL Replication, Consulting, Custom Development, 24x7 support
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 > > -- > Álvaro Herrera <alvherre@commandprompt.com> > The PostgreSQL Company - Command Prompt, Inc. > PostgreSQL Replication, Consulting, Custom Development, 24x7 support