Why can't temporary tables reference regular (non-temp) tables in PostgreSQL?

Question

Why can't temporary tables references non-temp tables in PostgreSQL? When I run the following

CREATE TABLE foo ( a int PRIMARY KEY );
CREATE TEMP TABLE bar ( a int REFERENCES foo );

I get,

ERROR: constraints on temporary tables may reference only temporary tables

I thought this used to work, so I went back to PostgreSQL 8.4 and I see even then it used to error,

ERROR: cannot reference permanent table from temporary table constraint

Why is this though? Why can't a temporary table reference a non-temporary table.

Answer 1

There are two sides to a foreign key constraint:

• When inserting a child record (or updating its foreign key), the parent record must exist
• When deleting a parent record (or updating its primary key), a child record must not exist

Postgres implements this by installing triggers on both tables to perform the existence checks. But the parent trigger simply won't work if the child is a temp table: the trigger needs to fire for all database users in order to guarantee referential integrity, but the temp table data is only visible within the session which created it, so the constraint cannot be enforced.

Even if it were possible, it's probably a bit too intrusive; it's rarely desirable to have a DELETE on the parent fail just because some other user is referencing it from a temporary dataset. But if you really need to maintain integrity in this case, you can get much the same effect by locking the parent records with a SELECT ... FOR KEY SHARE.