I am working on a Symfony PHP project to migrate a mysql database to postgres and it requires to temporarily disable the foreign key checks.
I found several posts on this, but no proposed solution is working.
I tried:
pg_query("SET session_replication_role = 'replica'");
or
pg_query($connPg, 'ALTER TABLE ' . $table . ' DISABLE TRIGGER ALL' );
or
pg_query($connPg, "SET CONSTRAINTS ALL DEFERRED");
I am still getting the same message:
Warning: pg_query(): Query failed: ERROR: cannot truncate a table referenced in a foreign key constraint
DETAIL: Table "sector_hierarchy" references "sector". HINT: Truncate table "sector_hierarchy" at the same time, or use TRUNCATE ... CASCADE.
I was thinking about possible privileges issue and changed the user to superuser, but that did not help:
ALTER USER root WITH SUPERUSER
Also when I attempt to list the triggers I get nothing in return, though I can see the FK's on pgAdmin. So this returns no rows:
select * from information_schema.triggers
For information the version for Postgres is 13.7 and this is in a docker-compose set up.
EDIT:
This is one sequence queries that triggers the error:
pg_query($connPg, "ALTER USER root WITH SUPERUSER");
pg_query($connPg, 'ALTER TABLE sfadb.' . $table . ' DISABLE TRIGGER ALL');
pg_query($connPg, 'TRUNCATE TABLE sfadb.' . $table .' RESTART IDENTITY');
They are being run as part of transaction, but if I remove the transaction nothing seems to change.
As per the message above, the error is not being triggered on the target table as such, but in one of its relations.
I tried also disabling the triggers for that specific table, but still getting same error:
pg_query($connPg, 'ALTER TABLE sfadb.sector_hierarchy DISABLE TRIGGER ALL');