Обсуждение: Locking referenced table when creating and dropping tables with foreign key constraints
Locking referenced table when creating and dropping tables with foreign key constraints
От
frank joerdens
Дата:
It seems that whenever I create a new empty table with a foreign key constraint, the transaction will acquire an exclusive lock on the referenced table, locking out other writers (not sure if even readers as well), and I don't quite see why that is necessary if the new entity does not contain any rows since there is nothing to check or validate in terms of the presence of values in the referenced column. This is biting us particularly now (it took the site down for a few minutes each time for the last couple of days) because we have a number of tables, and intend to add more, that are partitioned by date, all of which reference the core "person" table, for which we make new partitions daily via cron, and there is just no way we can take the app offline each time. And we will also soon start dropping them (i.e. removing from the inheritance hierarchy, archiving the content and then dropping them) automatically on an ongoing basis to keep the core data set manageable. In fact, it even looks like the dropping also requires a lock on the referenced table which makes even less sense to me ... Am I confused, or is there a way around it? We are on 8.3.7 atm. Regards, Frank
frank joerdens <fiskadoro@gmail.com> writes: > It seems that whenever I create a new empty table with a foreign key > constraint, the transaction will acquire an exclusive lock on the > referenced table, locking out other writers (not sure if even readers > as well), and I don't quite see why that is necessary It involves an ALTER TABLE ADD TRIGGER command, which necessarily locks out writers to avoid race conditions. I think at the moment it may take an exclusive lock and thereby lock out readers as well. There has been some talk of trying to reduce the lock strength needed for ALTER operations, but you should not expect that it'll ever be possible to do that without blocking writers. regards, tom lane