From: | hubert depesz lubaczewski <depesz(at)depesz(dot)com> |
---|---|
To: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
Cc: | PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: check constraint validation takes access exclusive locks |
Date: | 2012-02-27 13:02:57 |
Message-ID: | 20120227130257.GA28629@depesz.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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/
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Haas | 2012-02-27 13:08:04 | Re: Initial 9.2 pgbench write results |
Previous Message | Peter Eisentraut | 2012-02-27 12:29:54 | Re: overriding current_timestamp |