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

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

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

Tomer Praizler
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
Reply | Threaded
Open this post in threaded view
|

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

Laurenz Albe
Tomer Praizler wrote:
> I am running Postgres 10.X

Yes, dropping a table with a foreign key will take an ACCESS EXCLUSIVE lock
on the referenced table.  I believe that is because it will drop a (system)
trigger on that table.

Yours,
Laurenz Albe