On Thu, 2019-11-28 at 18:55 +0530, Nandakumar M wrote:
> I am using PG version 10.5.
>
> Saw a table where we have foreign key defined but few thousand rows
> violate the foreign key constraint.
>
> I understand that one possibility of this happening is if we had
> manually disabled the triggers that do FK integrity checks and re
> enabled them afterwards. Is there any way to confirm if this has
> happened -- does PG internally maintain something like an audit
> history of DDL changes?
>
> I am not sure if this is due to some mistake in our end or if there
> are any known issues in PG 10.5 that would cause this.
>
> I tried the ALTER TABLE ... VALIDATE CONSTRAINT for this particular FK
> and it doesn't report any errors. The documentation pretty clearly
> mentions that 'VALIDATE CONSTRAINT' is used only to check those
> constraints created with 'NOT VALID' clause.
>
> It might be useful to have an option to check integrity of any FK (not
> just ones created with NOT VALID clause). Please let me know if there
> is already any way to do this.
>
> Also, is there any way to make sure the FK checking trigger can never
> be disabled (so that such a case will never arise)?
>
> How do I proceed from here - Do I just delete the inconsistent rows or
> is there something more I have to do?
>
> Thanks for your help.
It could be that somebody disabled the triggers, but that would have to
be a superuser. And I hope that people randomly disabling system triggers
on tables don't have superuser access to your database.
There is no way to ascertain that that did happen.
It could be in the log if you have "log_statement = 'ddl'" and keep old
logs around.
The other option is that you are suppering from data corruption, perhaps
because of a software bug, but most likely because of hardware problems.
If you don't know better, assume the worst.
I would test the hardware for problems.
Once you are sure the hardware is fine, manually fix the corruption
by deleting rows that violate the constraint.
Then create a new PostgreSQL cluster with "initdb", dump the original
database with "pg_dumpall" and restore it to the new cluster.
That should get rid of all data corruption.
Make sure you upgrade to 10.11.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com