Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm trying to run the following Postgres migration script

DROP TABLE some_table;
DROP TABLE another_table;

The problem is that this script causes excessive locking when run on the production database and therefore has to be aborted. Is there a way to reduce the locking? I've considered removing all indices on these tables and/or foreign keys before executing the DROP statements, but I don't have an easy way to simulate the production environment in order to test whether this will have a positive or negative influence on the locking.

share|improve this question

migrated from stackoverflow.com Oct 29 '12 at 17:42

This question came from our site for professional and enthusiast programmers.

    
If your dropping these tables they're not needed. Could you revoke all rights to these tables besides the admin account? I assume your locking issues are from users or processes going at the tables while you are trying to drop it or vice verse users already starting transactions on this table and you coming in to drop it. –  Bob Oct 29 '12 at 14:18
3  
What exactly do you mean with "excessive locking". Apart from the tables being dropped, nothing else should be locked. Btw: you can drop both tables with a single statement: drop table some_table, another_table –  a_horse_with_no_name Oct 29 '12 at 15:16
    
do you mean you cannot drop the tables because they are locked? –  Jack Douglas Oct 29 '12 at 18:22
add comment

1 Answer 1

My best guess about what you're talking about is that the process of obtaining locks on the tables to drop them takes too long. You're running the changes in a transaction, so you obtain an exclusive lock on some_table before moving on to try to obtain an exclusive lock on other_table. At this point, some_table is inaccessible to clients, but other_table still has queries running against it so the migration script can't get a lock yet. Queries start to block on some_table and production operations begin to stall. You might even have transactions deadlocking because they already have a read lock on other_table and are trying to acquire a read lock on some_table, the reverse order to your DDL migration script.

Presumably your migration script is going to re-create the tables with a new definition after dropping them, then re-populate them, since you wouldn't be dropping tables that are in production use otherwise.

If that's the issue, your options are limited. You can rearrange your migration script into a set of individual transactions that don't depend on each other, like:

BEGIN;
DROP TABLE some_table;
CREATE TABLE some_table ( new_definition );
INSERT INTO some_table (...) VALUES (...);
COMMIT;


BEGIN;
DROP TABLE other_table;
CREATE TABLE other_table ( new_definition );
INSERT INTO other_table (...) VALUES (...);
COMMIT;

... or do the same thing with ALTER TABLE transformations instead of a drop, re-create and re-load.

Alternately, you can REVOKE access to the tables then query pg_locks and pg_cancel_backend all connections that currently hold locks on the tables you want to modify, apply the migration, and then GRANT access again. That'll ensure the migration runs promptly at the cost of aborting some queries. A well-written application will just re-try, so this shouldn't be particularly disruptive.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.