Sample Header Ad - 728x90

Create table with foreign keys blocked by other Table-level Lock

0 votes
1 answer
1733 views
I'm not pretty sure it is database question or development-process question, but I will kindly give it a try. When creating a new table B with foreign keys to table A, the operation tries to acquires SHARE ROW EXCLUSIVE lock on the referenced table (table A). However if table A is being busy by various UPDATES (ROW EXCLUSIVE locks). This cause my database migration locks for too long time (15+ minutes) and risk my production. In Some occasions I manually assist my db migration by killing the workers directed to table A. Beside scheduling almost *every* db migration to occurs in the off-hours (1AM-5AM), can you suggest a better or creative solution to help my team deploy "*naive*" PR to production. References: 1. https://www.postgresql.org/docs/12/sql-createtable.html 2. https://www.postgresql.org/message-id/75218696-61be-4730-89f6-dd6058fa9eda@a28g2000prb.googlegroups.com
Asked by Cowabunga (145 rep)
Nov 5, 2021, 01:59 AM
Last activity: Nov 5, 2021, 05:05 AM