Insert/delete/update on table is locking all child leaf partitions

Products

• VMware Tanzu Greenplum

Issue/Introduction

Starting with Greenplum versions 4.3.33.1 and 5.19.0, all DML operations (Insert/Update/Delete) on the root partition will take a RowExclusiveLock on all the child partitions.

Previously in earlier versions, Greenplum would take only a RowExclusiveLock on the root partition.

Examine the example below:

CREATE TABLE test (id integer, name text NOT NULL) 
DISTRIBUTED RANDOMLY PARTITION BY LIST(name) 
          (
          PARTITION ptn_bca VALUES('BCA') WITH (tablename='test_1_prt_ptn_bca', appendonly=false ), 
          PARTITION ptn_bcc VALUES('BCC') WITH (tablename='test_1_prt_ptn_bcc', appendonly=false ), 
          PARTITION ptn_bch VALUES('BCH') WITH (tablename='test_1_prt_ptn_bch', appendonly=false ), 
          PARTITION ptn_bci VALUES('BCI') WITH (tablename='test_1_prt_ptn_bci', appendonly=false ), 
          PARTITION ptn_bck VALUES('BCK') WITH (tablename='test_1_prt_ptn_bck', appendonly=false ), 
          PARTITION ptn_bcl VALUES('BCL') WITH (tablename='test_1_prt_ptn_bcl', appendonly=false )
);

Insert some rows:

insert into test SELECT generate_series (1,100000000), 'BCC' FROM generate_series (1,1) AS x(n) ;

5.17.0 / 4.3.32.0 and Earlier:

gpadmin=# select a.*,b.relname from pg_locks a, pg_class b where a.relation = b.oid and pid = 31204; 
 locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction |  pid  |       mode       | granted | mppsessionid | mppiswriter | gp_segment_id | relname 
----------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+------------------+---------+--------------+-------------+---------------+---------
 relation |    16384 |    16385 |      |       |            |               |         |       |          | 3/14               | 31204 | RowExclusiveLock | t       |           12 | t           |            -1 | test
(1 row)

5.19.0 / 4.3.33.1 and Later:

gdw_prod=# select a.*,b.relname from pg_locks a, pg_class b where a.relation = b.oid and pid = 20289;                                                                        locktype | database | relation | page | tuple | gpadmin=# select a.*,b.relname from pg_locks a, pg_class b where a.relation = b.oid and pid = 20289;
 locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction |  pid  |       mode       | granted | mppsessionid | mppiswriter | gp_segment_id |      relname       
----------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+------------------+---------+--------------+-------------+---------------+--------------------
 relation |    16384 |   145283 |      |       |            |               |         |       |          | 3/146              | 20289 | RowExclusiveLock | t       |           83 | t           |            -1 | test_1_prt_ptn_bci
 relation |    16384 |   145290 |      |       |            |               |         |       |          | 3/146              | 20289 | RowExclusiveLock | t       |           83 | t           |            -1 | test_1_prt_ptn_bck
 relation |    16384 |   145297 |      |       |            |               |         |       |          | 3/146              | 20289 | RowExclusiveLock | t       |           83 | t           |            -1 | test_1_prt_ptn_bcl
 relation |    16384 |   145261 |      |       |            |               |         |       |          | 3/146              | 20289 | RowExclusiveLock | t       |           83 | t           |            -1 | test_1_prt_ptn_bca
 relation |    16384 |   145255 |      |       |            |               |         |       |          | 3/146              | 20289 | RowExclusiveLock | t       |           83 | t           |            -1 | test
 relation |    16384 |   145269 |      |       |            |               |         |       |          | 3/146              | 20289 | RowExclusiveLock | t       |           83 | t           |            -1 | test_1_prt_ptn_bcc
 relation |    16384 |   145276 |      |       |            |               |         |       |          | 3/146              | 20289 | RowExclusiveLock | t       |           83 | t           |            -1 | test_1_prt_ptn_bch
(7 rows)

Resolution

We would expect since only 'BCC' partition is chosen, only a lock on that child partition would be taken.

However, at the time of Query Dispatch during plan generation, QD will not be able to determine which child leaf partition to lock, so in the past we would lock just the root partition.

This would lead to the following bug when doing inserts on AO partition tables while simultaneously doing a vacuum operation on a child leaf partition:

2019-01-08 00:01:37.983966 EST,"kettle","aeon",p36293,th1002440896,"45.12.80.70","45374",2019-01-08 00:01:37 EST,377153549,con4216831,cmd4,seg-1,,dx8329849,x377153549,sx1,"ERROR","XX000","cannot insert into segno (1) for AO relid 183045056 that is in state AOSEG_STATE_AWAITING_DROP (aocsam.c:719)  (seg8 shrbgpd004:40000 pid=45823) (cdbdisp.c:1322)",,,,,,"

29752 - Server
An INSERT operation on an append-optimized partitioned table returned an error when a concurrent VACUUM operation was performed on the table. The error occurred because INSERT did not lock the child partition tables. This issue has been resolved. Now INSERT locks the child partition tables.

This design change was implemented for both Heap and AO tables.