Constraint Exclusion and Partition Locking

Adding a new partition is fairly simple, particularly if you don't care
about writing RULEs to direct data into it and can rely on a bulk loader
to figure that part out.

Removing a partition, on the other hand, is currently impossible to do
without blocking selects against the table as a whole.

The reason for this is that when planning to see if a partition needs to
be read an AccessShareLock is taken and held for the entire transaction.

If that lock was not there a partition (inherited table) could be
dropped without any interruptions to normal query processing.

Is it possible to release the AccessShareLock lock on
"measurement_yy04mm03" after planning has determined that the structure
will not be used for this query in a "READ COMMITTED" transaction?
Serializable obviously would have problems with the partitions
constraint definition changing.

Yes. During the DROP new queries would not be planned but the DROP
itself would not need to wait for all executing transactions to finish
before proceeding.

t=# CREATE TABLE measurement (
t(# city_id int not null,
t(# logdate date not null,
t(# peaktemp int,
t(# unitsales int
t(# );
CREATE TABLE
t=# CREATE TABLE measurement_yy04mm02 (
t(# CHECK ( logdate >= DATE '2004-02-01' AND logdate < DATE
'2004-03-01' )
t(# ) INHERITS (measurement);
CREATE TABLE
t=# CREATE TABLE measurement_yy04mm03 (
t(# CHECK ( logdate >= DATE '2004-03-01' AND logdate < DATE
'2004-04-01' )
t(# ) INHERITS (measurement);
CREATE TABLE
t=# EXPLAIN SELECT count(*) FROM measurement WHERE logdate =
'2004-02-06';
QUERY PLAN
---------------------------------------------------------------------------------------------
Aggregate (cost=60.79..60.80 rows=1 width=0)
-> Append (cost=0.00..60.75 rows=16 width=0)
-> Seq Scan on measurement (cost=0.00..30.38 rows=8 width=0)
Filter: (logdate = '2004-02-06'::date)
-> Seq Scan on measurement_yy04mm02 measurement
(cost=0.00..30.38 rows=8 width=0)
Filter: (logdate = '2004-02-06'::date)
(6 rows)

t=# CREATE INDEX measurement_yy04mm02_logdate ON measurement_yy04mm02
(logdate);
CREATE INDEX
t=# CREATE INDEX measurement_yy04mm03_logdate ON measurement_yy04mm03
(logdate);
CREATE INDEX
t=# EXPLAIN SELECT count(*) FROM measurement WHERE logdate =
'2004-02-06';
QUERY PLAN
-------------------------------------------------------------------------------------------------------
Aggregate (cost=41.44..41.45 rows=1 width=0)
-> Append (cost=0.00..41.40 rows=16 width=0)
-> Seq Scan on measurement (cost=0.00..30.38 rows=8 width=0)
Filter: (logdate = '2004-02-06'::date)
-> Bitmap Heap Scan on measurement_yy04mm02 measurement
(cost=1.03..11.03 rows=8 width=0)
Recheck Cond: (logdate = '2004-02-06'::date)
-> Bitmap Index Scan on measurement_yy04mm02_logdate
(cost=0.00..1.03 rows=8 width=0)
Index Cond: (logdate = '2004-02-06'::date)
(8 rows)

t=# begin;
BEGIN
t=# SELECT count(*) FROM measurement WHERE logdate = '2004-02-06';
count
-------
0
(1 row)

t=# select relname, mode from pg_locks join pg_class on (pg_class.oid =
relation);
relname | mode
----------------------+-----------------
measurement_yy04mm03 | AccessShareLock
measurement | AccessShareLock
pg_locks | AccessShareLock
measurement_yy04mm02 | AccessShareLock
pg_class | AccessShareLock
(5 rows)

--

Ühel kenal päeval, L, 2006-03-04 kell 13:16, kirjutas Rod Taylor:
> Adding a new partition is fairly simple, particularly if you don't care
> about writing RULEs to direct data into it and can rely on a bulk loader
> to figure that part out.
>
> Removing a partition, on the other hand, is currently impossible to do
> without blocking selects against the table as a whole.
>
> The reason for this is that when planning to see if a partition needs to
> be read an AccessShareLock is taken and held for the entire transaction.
>
> If that lock was not there a partition (inherited table) could be
> dropped without any interruptions to normal query processing.
>
> Is it possible to release the AccessShareLock lock on
> "measurement_yy04mm03" after planning has determined that the structure
> will not be used for this query in a "READ COMMITTED" transaction?
> Serializable obviously would have problems with the partitions
> constraint definition changing.

Ar you sure ? I suspect that the change will not be visible to a
serialisable trx.

-------------
Hannu