How large are the given tables and is the databases in heavy use at the time? It sounds like either blocking is
occurringor 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
startto enforce it; however, existing data may not conform thus Postgres will report it as not valid. Then you can
validatethe foreign key which occurs concurrently. This is the approach I use on live production systems to avoid
blockingissues.
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.
