Skip site navigation (1) Skip section navigation (2)

Re: check constraint validation takes access exclusive locks

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 (view raw or flat)
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/

In response to

Responses

pgsql-hackers by date

Next:From: Robert HaasDate: 2012-02-27 13:08:04
Subject: Re: Initial 9.2 pgbench write results
Previous:From: Peter EisentrautDate: 2012-02-27 12:29:54
Subject: Re: overriding current_timestamp

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group