-1

I have a PostgreSQL 9.3 database with two tables, table B with 100 million records linked via a FK constraint to another table A with around 10 million records.

Occasionally I need to delete a record in table A, and due to the FK constraint, Postgres requires that I first delete all referencing records in table B. However, since this link represents a semantic "source" relationship, my application doesn't need this strict referential integrity, so I'm trying to drop the constraint.

However, I'm finding this very difficult. When I try and run:

 alter table tableB drop constraint fk_constraint_to_tableA;

it seems to time out. I let it run for an hour and it didn't complete, so I killed my psql terminal. Unfortunately, that apparently didn't stop it running on the server, which became unresponsive and wouldn't let me connect back in, forcing me to restart it.

I understand that PostgreSQL doesn't scale well, and isn't a good database for large-data applications, but is there any way to delete this constraint, or am I stuck with it forever?

1

I may be reading your question wrong, but I think you're saying that you want to remove the record in Table A, but keep all the records that referred to it in Table B. In many cases, in such circumstances, you would want the column that pointed to the deleted record in Table A to be set to NULL.

Postgres does allow ON DELETE SET NULL as part of the foreign key declaration.

However, if you can't drop the current FK constraint, then it's unlikely you'll be able to switch to a different one.

Depending on the amount of down time you can have, and on the speed of your database, the simplest solution might be to create a new table with all the constraints the way you want them, and to move all the data into the new table. Then, drop the original table and rename the new table to match the original name. NOTE: I am not personally a PostgreSQL user, so confirm this works in a test environment first!

1

Dropping a foreign key constraint is nearly instantaneous, once it obtains the necessary locks on both tables. It is probably blocked waiting on those locks.

I let it run for an hour and it didn't complete, so I killed my psql terminal.

How did you do that? A simple ctrl-C should interrupt the query without needing to kill the entire terminal. Killing the terminal with something like "kill -9" is a bad idea, because it leaves the process running on the backend where it is still waiting on locks. This wouldn't make the server "unresponsive" in general, but may make it seem that way if everything else you try to do then gets caught up on those locks.

I understand that PostgreSQL doesn't scale well,

Like kill -9, trolling the people you are asking for help is rarely a good idea.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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