13

I'm using AWS Aurora Postgres and using DMS to migrate from RDS postgres to Aurora PG. In order to perform the FULL LOAD I want to disable foreign key constraints and triggers on all the objects. I'm able to disable triggers but couldn't find a way to disable constraints.

Below doesn't work:

ALTER TABLE so_items DISABLE CONSTRAINT so_items_so_id_fkey;

It throws:

ERROR: syntax error at or near "CONSTRAINT" LINE 1: ALTER TABLE so_items DISABLE CONSTRAINT so_items_so_id_fkey; ^ SQL state: 42601 Character: 30

Setting "session_replication_role" = "replica" in the parameter group didn't work. While the DMS task tries to truncate the table part of preparation it still fails with foreign key violation errors.

Please advise any workarounds.

Note: I couldn't do below since in RDS I do not have permissions to do so even with master account:

alter table so_items disable trigger ALL;

ERROR: permission denied: "RI_ConstraintTrigger_c_16520" is a system trigger SQL state: 42501

T.S.
  • 18,195
  • 11
  • 58
  • 78
RMu
  • 817
  • 2
  • 17
  • 41

3 Answers3

12

You shouldn't modify the triggers a Postgres constraint relies on. This is an implementation detail for which you shouldn't care about.

You cannot disable constraints, really.

To turn constraints temporarily off, you can defer the constraint check to the end of transactions:

ALTER TABLE so_items ALTER CONSTRAINT so_items_so_id_fkey DEFERRABLE INITIALLY DEFERRED;

With that modification the constraint is evaluated after a modification at the end of the current transaction. This will allow you to break the constraint inside of a transaction.

You may DROP CONSTRAINTs

ALTER TABLE so_items DROP CONSTRAINT so_items_so_id_fkey;

which will delete it permanently.

Edit: It is also possible to disable the triggers which also affects the foreign key constraints of the table

ALTER TABLE so_items DISABLE TRIGGER ALL;

But when you are re-enabling the triggers afterwards, the foreign keys are not checked. This might lead to invalid / inconsistent foreign keys in the database.

clemens
  • 16,716
  • 11
  • 50
  • 65
  • 7
    You say it would make no sense, and generally that is true, but there are instances where this is useful, like populating a large database through migration. The question was likely asked because this function is supported in MySQL – Kyle Waid Oct 15 '20 at 04:11
  • 1
    MySQL may support this, but that does not necessarily mean it is a good idea. If you want to restore a database, you should first create the schema without constraints, then import the data and finally create the constraints. AFAIK `pg_dump` does exactly that. – clemens Oct 15 '20 at 11:06
  • 2
    It's not just MySQL that supports this. Oracle, SQL Server do as well. It makes perfect sense to them. And it's done for more than just restoring a database. Migrations, as Kyle points out. – EDanaII Oct 27 '20 at 19:37
  • Not that you misunderstand me. I see your problem, but I think it is dangerous to turn off constraints. Instead, you can probably write a script that alters all constraints with `ALTER TABLE t ALTER CONSTRAINT c DEFERRABLE INITIALLY DEFERRED`, where _t_ and _c_ are read from `information_schema.constraint_column_usage` and `information_schema.constraint_table_usage`. Than you can do all updates inside of a transaction, and all checks are done at the end of the transaction. If you are interested I can update my post. – clemens Oct 28 '20 at 07:13
  • 2
    disable constraints makes a lot sense when you want to re enable it later. – Charlie 木匠 Mar 03 '21 at 19:45
  • Thanks for the Spec. – Charlie 木匠 Mar 03 '21 at 19:52
  • 4
    Actually, you can disable constraints, and [PostgreSQL docs](https://www.postgresql.org/docs/8.1/sql-altertable.html) specifically mention since 8.1 (2005) that `disable trigger all` disables system triggers that enforce foreign key constraints which is what OP wanted, so your answer is just wrong and misleads people coming here from search engines. Also, saying that OP should not care about his correctly diagnosed problem is totally unhelpful and patronising. – SanD Apr 02 '21 at 03:22
  • @SanD: Yes, you're right. I have extended my post. However, I did not say that the OP should not take care of the problem, but even suggested alternatives to solve it. The constraints are supposed to ensure the consistency of the data, which is why I don't think turning them off is a good idea. Of course, you can switch off the triggers, but that is different from just deactivating the constraints. – clemens Apr 02 '21 at 08:18
2

I'm going to make the assumption that you've used pg_dump with the --disable-triggers flag and now you're trying to restore that dump to your RDS instance.

The easiest thing to do is run your dump command then open the dump file (should be a large SQL file) and put this in the very first line:

SET session_replication_role = 'replica';

Then put this in the very last line:

SET session_replication_role = 'origin';

There are downsides to this as mentioned in the other answers but this is the easiest way to get around disabling the triggers table by table.

jcity
  • 826
  • 1
  • 9
  • 13
0

For Postgres :-

It is easier to disable all triggers with:

SET session_replication_role = 'replica';

And after migration reenable all with

SET session_replication_role = 'origin';

Vaibhav Jain
  • 305
  • 3
  • 8
  • Probably safer to check what the constraints are use EXPLAIN on the delete statements and then if it is safe, run the set session_replication_role as suggested. – zaxishere Mar 31 '23 at 12:02
  • It works for me when i just need to import the data without caring about consistency. – Tai Vu Jun 15 '23 at 05:26