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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
