Now I'm sorta PostgreSQL DBA/dev...
and faced the problem that looks a well known one:
alter table assessment -- quite simple new table: (event int, value bool)
add constraint assessment_event_fk
foreign key (event)
references rtls(id) -- it refs the primary key integer field
Adding not valid (in useless hope to validate it later on) is of no help either.
Yes, I know, it hangs (the rtls table is about 2M records now).
And one have to pg_dump the base, edit SQL, and load it back.
I saw this "advise" in posts since circa 2003...
It's 9.2.18 from standard deployment on CentOS 7, if it matters (it does!).
I cannot believe that it stuck here over decade...
What should I consider to work it around?
Use sorta check for exists (select id from rtls where id=?) ???
No way:
vad=> alter table assessment add constraint assessment_event_fk check(exists (select rtls.id from rtls where rtls.id=event));
ERROR: cannot use subquery in check constraint
