Constraint Exclusion and Partition Locking - Mailing list pgsql-hackers

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.80rows=1 width=0)  ->  Append  (cost=0.00..60.75 rows=16 width=0)        ->  Seq Scan on measurement
(cost=0.00..30.38rows=8 width=0)              Filter: (logdate = '2004-02-06'::date)        ->  Seq Scan on
measurement_yy04mm02measurement
 
(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.45rows=1 width=0)  ->  Append  (cost=0.00..41.40 rows=16 width=0)        ->  Seq Scan on measurement
(cost=0.00..30.38rows=8 width=0)              Filter: (logdate = '2004-02-06'::date)        ->  Bitmap Heap Scan on
measurement_yy04mm02measurement
 
(cost=1.03..11.03 rows=8 width=0)              Recheck Cond: (logdate = '2004-02-06'::date)              ->  Bitmap
IndexScan 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 | AccessShareLockmeasurement          |
AccessShareLockpg_locks            | AccessShareLockmeasurement_yy04mm02 | AccessShareLockpg_class             |
AccessShareLock
(5 rows)



--