Thread: check constraint validation takes access exclusive locks

check constraint validation takes access exclusive locks

From

Pavel Stehule

Date:

26 February 2012, 10:05:30

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

Re: check constraint validation takes access exclusive locks

From

hubert depesz lubaczewski

Date:

27 February 2012, 09:03:17

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/

Re: check constraint validation takes access exclusive locks

From

Alvaro Herrera

Date:

27 February 2012, 09:17:44

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

Re: check constraint validation takes access exclusive locks

From

Pavel Stehule

Date:

27 February 2012, 09:42:27

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

Re: check constraint validation takes access exclusive locks

From

Alvaro Herrera

Date:

27 February 2012, 09:52:54

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

Re: check constraint validation takes access exclusive locks

From

Pavel Stehule

Date:

27 February 2012, 10:00:26

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