Does DROP TABLE on table A with foreign key to table B locks SELECT queries on table B?

From: Tomer Praizler <tomer(dot)praizler(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Does DROP TABLE on table A with foreign key to table B locks SELECT queries on table B?
Date: 2019-05-07 11:49:48
Message-ID: CAD=kdR-UDZA4PHX=8bVBNL1xB9JbgX+fn-Gd_gNfjJPwM+KR5Q@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Hi,

I am running Postgres 10.X, and I am using pg_partman for managing my
partitions.
Here is a simplified structure:

db=> \d+ my_table_partitioned_template
Table
"public.my_table_partitioned_template"
Column | Type | Collation |
Nullable | Default | Storage | Stats target | Description
--------------------------------+-----------------------------+-----------+----------+---------+----------+--------------+-------------
id | integer |
| not null | | plain | |
creation_time | timestamp without time zone |
| | | plain |
special_id | integer |
| | | plain | |

Indexes:
"my_table_partitioned_template_pkey" PRIMARY KEY, btree (id)
"ix_my_table_partitioned_template_sensor_id" btree (sensor_id)
Foreign-key constraints:
*"my_table_partitioned_template_sensor_id_fkey" FOREIGN KEY (sensor_id)
REFERENCES sensor(id) ON DELETE CASCADE*

db=> \d+ my_table_partitioned
Table
"public.my_table_partitioned"
Column | Type | Collation |
Nullable | Default | Storage | Stats target | Description
--------------------------------+-----------------------------+-----------+----------+---------+----------+--------------+-------------
id | integer |
| not null | | plain | |
creation_time | timestamp without time zone |
| | | plain |
special_id | integer |
| | | plain | |

Partition key: RANGE (creation_time)
Partitions: my_table_partitioned_p2019_04_17 FOR VALUES FROM ('2019-04-17
00:00:00') TO ('2019-04-24 00:00:00'),
my_table_partitioned_p2019_04_24 FOR VALUES FROM ('2019-04-24
00:00:00') TO ('2019-05-01 00:00:00'),
my_table_partitioned_p2019_05_01 FOR VALUES FROM ('2019-05-01
00:00:00') TO ('2019-05-08 00:00:00'),
my_table_partitioned_p2019_05_08 FOR VALUES FROM ('2019-05-08
00:00:00') TO ('2019-05-15 00:00:00'),
my_table_partitioned_p2019_05_15 FOR VALUES FROM ('2019-05-15
00:00:00') TO ('2019-05-22 00:00:00')

pg_partman, once in a while wakes up and apply the partitions
configuration, which means adding new ones and dropping old ones.
During that maintenance time, most of my database queries are in Locked
state.
I would expect queries on *my_table_partitioned* to be blocked, because of
a *DROP TABLE *statements going on. But what I don't understand is why
queries on *sensor* table are being locked.
Can it be because the partitioned table has a foreign key to that table?

What am I missing?

Thanks,
Tomer

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Laurenz Albe 2019-05-07 16:57:44 Re: Does DROP TABLE on table A with foreign key to table B locks SELECT queries on table B?
Previous Message Mark Wallace 2019-04-23 17:29:19 Re: SELECTing for group membership of many groups?