Re: observations about temporary tables and schemas

Kris Jurka <books(at)ejurka(dot)com> writes:
> ... you are allowed to reference a permanent table from a temp
> table. The triggers don't work correctly when the table is
> modified by another backend:

Hmm, yeah. That worked when we put in the temp-vs-permanent check in
foreign key creation, but it doesn't work anymore because temp table
pages are now kept in per-backend local buffers; so there's no guarantee
that another backend can see recent changes to the contents of a temp
table.

I think we have two choices: disallow foreign-key references from temp
tables to permanent tables, or take out the optimization of storing
temp table pages in private memory. (That would leave the whole "local
buffer manager" module as dead code, I think.) I'm kinda leaning
towards the first; does anyone feel that it's a valuable feature to keep?

> After some further investigation this problem can also be generated by two
> temp tables:

That is not the same bug; the problem here is that ON COMMIT DELETE ROWS
simply does an unconditional heap_truncate without bothering to run any
deletion triggers. We could make it apply the same checks TRUNCATE
TABLE does, whereupon you'd get some sort of "can't truncate table"
error when you try to set up a foreign key reference to it. That could
be extended to disallowing the FK reference in the first place, perhaps.
Or we could turn it into a "DELETE FROM temptable", which would be a lot
slower but would "do the right thing". Comments?

BTW, it occurs to me that TRUNCATE TABLE refuses to truncate relations
referenced by foreign keys, but this is really not a correct/complete
test. What about user-defined deletion triggers? Arguably it should
refuse to truncate if there are any ON DELETE triggers at all.

regards, tom lane