3

I am trying to disable triggers on Postgres on RDS with the following statement:

ALTER TABLE table_name DISABLE TRIGGER ALL;

But I get the following error:

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.InsufficientPrivilege) permission denied: "RI_ConstraintTrigger_a_16536" is a system trigger.

Is there any way to remove triggers on RDS, as I cannot give myself SUPERUSER privileges on RDS? I've looked at a bunch of articles and haven't found a suitable solution.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
Siddharth Sampath
  • 135
  • 3
  • 14
  • What do you mean by "disable triggers"? Are you talking about disabling the entire concept of "triggers" in postgres, or a single trigger, or some RDS-specific thing? What command are you executing to do so? – 404 Feb 23 '21 at 10:45
  • 1
    "ALTER TABLE table_name DISABLE TRIGGER ALL;" This is the command I am trying to run – Siddharth Sampath Feb 23 '21 at 10:50
  • 1
    Do you actually want to disable that specific trigger? As you might break something in the database. System triggers are for things like enforcing constraints. At any rate, if you're logged in with a role that has membership of `rds_superuser` group, then you already have the max permissions that you can have in RDS. If you still can't do what you need to do then it can't be done (or at least not in that way - for example you might need to drop the relevant constraint rather than disabling the trigger). – 404 Feb 23 '21 at 10:55
  • 1
    Basically, I am trying to delete the contents of multiple tables, which have circular foreign key dependencies. I use SQLAlchemy with Postgres. So to avoid foreign key errors, I am disabling triggers, and deleting the contents of the tables. Once everything has been deleted, I enable triggers for every table. Is there a better way to do this? – Siddharth Sampath Feb 24 '21 at 06:09
  • 1
    Some people seem to work around this issue by defining the DB as a replica: e.g. https://github.com/ankane/pgsync/issues/94. That feels hacky, but so far it's the best I've found... – JohnLBevan Mar 08 '21 at 17:18
  • 2
    There's a similar discussion here: https://stackoverflow.com/questions/48333319/how-to-disable-foreign-key-constraints-in-postgresql – JohnLBevan Mar 08 '21 at 17:23

1 Answers1

0

You will need to disable the triggers individually. The all option command does not work in RDS.

alter table schema.table_name disable trigger trigger_name;

Raul F
  • 1