Transaction locks on first insert into partitioned table partition
Martin Lund Askøe <[email protected]> Thu, 6 Jun 2019 17:30:31 +0200
| Newsgroups | gmane.comp.db.postgresql.novice |
|---|---|
| Message-ID | <CAOvc5viG5cvKxUnyjSRLKCice1pO4L64OjLE1B3pkTkGZGxtFQ@mail.gmail.com> |
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.