I'm experiencing something I cannot explain with regards to partitioned tables.
I create a partitioned table and create a partition on it in one transaction.
I then insert a record into the partition, somehow taking a lock on the master table.
Then I rollback. I then insert a record again, but this time I do not take a lock on the master table.
Why is this happening?
This is my reproduction of the problem.
CREATE TABLE master_table (
id bigint NOT NULL,
some_info varchar NOT NULL,
e_timestamp timestamp NOT NULL
) PARTITION BY RANGE (e_timestamp);
create index on master_table (id);
create index on master_table (e_timestamp);
CREATE TABLE partition_table_2019_01_01 PARTITION OF master_table FOR VALUES FROM ('2019-01-01') TO ('2019-01-02');
-- locks taken are (amongs others)
-- master_table AccessExclusiveLock
-- partition_table_2019_01_01 ShareLock
-- okay so far
commit;
insert into partition_table_2019_01_01 (id, some_info, e_timestamp) VALUES (1, 'hello', '2019-01-01 10:00:00'::timestamp);
-- locks taken are (amongs others)
-- master_table AccessShareLock
-- partition_table_2019_01_01 RowExclusiveLock
-- why a lock on the master_table ?
rollback;
insert into partition_table_2019_01_01 (id, some_info, e_timestamp) VALUES (1, 'hello', '2019-01-01 10:00:00'::timestamp);
-- locks taken are (amongs others)
-- partition_table_2019_01_01 RowExclusiveLock
-- this time no lock on the master_table ???? Did something happen during the transaction that was rolled back?
Any input would be appreciated.
Regards, Martin.