Hello hackers,
I stumbled upon a case and wanted to raise a quick thread to get some feedback on this.
Basically, dropping a foreign key constraint or a table that owns an FK currently blocks reads on the other table due to AccessExclusive locks taken while removing the FK’s internal triggers and constraint metadata. In busy systems, this short full-read outage can cause user-visible timeouts for otherwise read-only traffic. Similar topic discussed here as well [1]
Example setup
- pktable(id primary key)
- fktable(id primary key, fk references pktable(id))
Where reads get blocked today
- ALTER TABLE fktable DROP CONSTRAINT fkname:
- The FK lives on fktable, and its RI action triggers live on pktable.
- Both tables see AccessExclusive-level effects during removal, so SELECTs on either can be blocked while the FK and triggers are dropped.
- DROP TABLE fktable:
- fktable is dropped with AccessExclusive (expected).
- While removing RI action triggers on pktable, pktable also sees an AccessExclusive lock, so SELECTs on pktable can be blocked even though pktable is not being dropped.
- DROP TABLE pktable CASCADE:
- pktable is dropped with AccessExclusive (expected).
- FK removal on fktable (check triggers) also introduces an AccessExclusive effect on fktable, so SELECTs on fktable can be blocked.
Proposal
I wanted to see if we could reduce the relation-level lock used specifically for FK/trigger removal from AccessExclusive to ShareRowExclusive. This keeps readers moving while still blocking writers during the small window where RI triggers/constraint rows are removed and relcache is invalidated. Ideally, with a change like this `ALTER TABLE fktable DROP CONSTRAINT fkname` should take ShareRowExclusive for fktable and pktable, and `DROP TABLE fktable` should take ShareRowExclusive for fktable and AccessExclusive for pktable.
Next, dropping fktable would take ShareRowExclusive for fktable and AccessExclusive for pktable. And DROP TABLE pktable CASCADE would take AccessExclusive for pktable and ShareRowExclusive for fktable.
My understanding is that the table being dropped still uses AccessExclusive; reads/writes on that table remain blocked as today while it updates relcache, updating metadata/catalog entries and other cleanup tasks.
Also, I believe ShareRowExclusive would still serialize writers, so there should be no window where DML can bypass enforcement during removal. So this change should not affect correctness?
I just attached a small patch to get the idea across. If folks think this direction makes sense, I am happy to work on a more complete patch as well. If I’ve missed any critical cases that truly need AccessExclusive at the relation level during FK/trigger removal, especially around partitions, pending trigger events, hot standby, logical decoding, event triggers or something else perhaps please let me know.
[1]
https://www.postgresql.org/message-id/flat/2bdf0fe5679f44ebf56890dfd5c5ead21a0c0bc3.camel%40cybertec.at#c50baf640c680c9e3f4def34b565d3b9
Thanks for your time and feedback.
Shayon
