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.
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 |