Transaction locks on first insert into partitioned table partition

From: Martin Lund Askøe <martinlundaskoe(at)gmail(dot)com>
To: pgsql-novice(at)lists(dot)postgresql(dot)org
Subject: Transaction locks on first insert into partitioned table partition
Date: 2019-06-06 15:30:31
Message-ID: CAOvc5viG5cvKxUnyjSRLKCice1pO4L64OjLE1B3pkTkGZGxtFQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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.

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message David Rowley 2019-06-07 01:11:07 Re: Transaction locks on first insert into partitioned table partition
Previous Message Lars Gustafsson 2019-05-24 10:36:41 Re: bytea problems