11/20/2023 0 Comments Postgres drop foreign key constraintSo change the query appropriately if you're using an old PostgreSQL. (For those on old versions: pg_stat_activity.pid used to be procpid. Which will show you the transaction(s) holding or waiting on locks in this table, what the locks are, what statement is currently being run by those transactions, etc. Left outer join pg_class c ON (l.relation = c.oid) The drop foreign key function generates the appropriate alter table drop foreign key SQL command for dropping the key from the table. Inner join pg_stat_activity psa ON (psa.pid = l.pid) You can find it by joining on pg_stat_activity: select This might just be a transaction that SELECTed from the table at some point. Another transaction holds a lock on this table that prevents the ALTER TABLE from acquiring the AccessExclusiveLock it needs to proceed. The fact that this lock has not been granted tells me that this is a locking problem. Where pg_locks.relation = 'auth_user'::regclass īy filtering for where not granted in your original query you're only showing outstanding locks, not the locks that're blocking them. Left outer join pg_class ON (pg_locks.relation = pg_class.oid) Even a read lock will prevent ALTER TABLE. There are no other locks and no processes waiting for locks.Ĭheck pg_locks and verify that no other transaction has a lock on the table. If you select a command, it lists the locks that it acquires, commands that. The pid above (17300) is simply the ALTER TABLE query itself. You need to find the constraint name in order to be able to drop it: select constraintname from userconstraints where tablename ABC and constrainttype R. This tool shows all commands and locks in postgres. There are other transactions open in the database (several-hour-long transactions migrating other very large tables) but none of those transactions are touching the follow table.Įdit: pg locks are as below: db=# select relation::regclass, * from pg_locks where not granted What could cause this query to be slow (or possibly not run at all at the time of this writing it is still ongoing) and how can I avoid it? But of course, since the transaction was not committed, I cannot find any trace of that data in the database. The only thing I can think of is that it is related to the large amount of data I wrote to the table and then did not commit, because the script crashed. However, the ALTER TABLE DROP CONSTRAINT command to drop the first foreign key constraint on the table is taking a very long time (tens of minutes), despite the table being completely empty. My intention was to recreate the constraints and indexes afterwards. Some research resulted in the conclusion that the crash was because of the foreign key constraints, so I verified that the table was empty (that is, the transaction that resulted in the process being killed did not complete) and modified my script to drop the foreign key constraints and indexes in order to insert the data. The DROP CONSTRAINT command is used to delete a UNIQUE, PRIMARY KEY, FOREIGN KEY, or CHECK constraint. My initial attempt caused my script to crash because system memory was exhausted. I am looking at it with the \d accounts command.I am trying to load several million rows of data into a table (a "follow" table that contains two foreign keys to the user table, and the associated indexes on those keys) in a single transaction. But PostgreSQL has a non-standard extension that lets you use multiple constraint clauses in a single SQL statement. Repeat for each foreign key you want to change. I get this error: ERROR: constraint "edits_account_id_fkey1" of relation "accounts" does not exist start a transaction, drop the foreign key, add a foreign key with on delete cascade, and finally. The only impact removing one can have on performance is a performance improvement due to the removal of foreign key checks. To remove a column that is referenced by a functional key part, the index must be removed. I want to drop this constraint but everytime I try to execute this command below: ALTER TABLE accounts DROP CONSTRAINT edits_account_id_fkey1 1 Answer Sorted by: 2 At time of writing (PostgreSQL 9.4 and below), PostgreSQL's optimiser doesn't use foreign key constraints to prove anything or allow for extra optimisations. Indexes that contain functional key parts do not have this feature. I have an accounts table that has this FOREIGN KEY constraint on it: TABLE "edits" CONSTRAINT "edits_account_id_fkey1" FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE CASCADE 1 Answer Sorted by: 87 It seems you want to drop the constraint, only if it exists.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |