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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
