Good morning,
Hoping someone is able to help me here...
Had a strange issue in a production db this morning: records existed
in one table with a foreign key value which didn't exist in another
table.
TL;DR - many triggers for constraints (and more?) are gone; can they
be recreated easily?
For reference, the tables:
- users (has primary key: user_id)
- users_profile (has foreign key constraint: user_id)
The constraint actually exists for users_profile:
select conname,contype from pg_constraint where conname = 'users_profile_pkey';
> users_profile_pkey | p
So, if the constraint exists, perhaps the triggers are gone? (I'm
starting to get in to unfamiliar territory here)
Comparing pg_class values between the production db and an old
development one, there is a concerning difference..
select reltriggers from pg_class where relname='users';
> Development db: 80
> Live db: 2
The table users_profile is similar; dev db has 2 for reltrigger and
live db has 0. At this point I wondered about other triggers; it looks
like somewhere along the line a LOT of trigger values have been
zeroed.
select count(*) from pg_class where reltriggers!=0;
> Dev db: 141
> Live db: 15
Next check, pg_trigger...
select count(*) from pg_trigger;
> Dev db: 895
> Live db: 39
So it looks like the problem affects more than just this one table,
and probably affects other triggers in the database too...
The question:
At the very least, is there an automated way to recreate the triggers
for the constraints, which are still existing in the system? Even if
it were to dump the database, grep for foreign key lines, sed to a
file to create some SQL to create the triggers, and run the SQL
manually..
Although the database appears mostly to be functioning OK without the
integrity checks taking place (no cascades or anything are used), it
would be nice to have them back. I am aware, however, that there could
be some issues in recreating them at this point, especially with the
amount of data in there. There are over 150 tables, some of them with
well over 1 million records.
If it were a few tables, I'd just do it manually..
I had wondered about the possibility of dumping the triggers from the
dev db, and inserting them in to the live db (the live db was created
from a dump of the dev db at deployment), but it's possible that IDs
and counts, etc, have changed, and that'd just cause other issues.
If someone can provide any assistance or info, that'd be tops!
Thanks!
Dan Herbert