Re: Ideas to deal with table corruption

How large are the given tables and is the databases in heavy use at the time? It sounds like either blocking is occurring or you’re dealing with large tables and the validation is take a long time; which, in both case is normal.

Try creating the foreign key without validation, i.e. use the “not valid” clause. That will create the foreign key and start to enforce it; however, existing data may not conform thus Postgres will report it as not valid. Then you can validate the foreign key which occurs concurrently. This is the approach I use on live production systems to avoid blocking issues.

i.e.:

alter table member_outline
add constraint member_outline_fkey1
foreign key (dimension, member) references member(dimension, member)
on update cascade not valid
;

alter table member_outline
validate constraint member_outline_fkey1
;

Nothing that you stated points to any table corruption.