Re: observations about temporary tables and schemas

On Wed, 17 Sep 2003, Tom Lane wrote:

> 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:
>
> 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?

I think the first is probably better all in all.

> > 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?

Since it's documented as doing a truncate, I think disallowing non-self
referential FK constraints is probably a good idea. I'm not sure that
doing all the work on commit to make the table rows delete individually
for the trigger/foreign key case is really worth it.

> 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.

Oracle doesn't seem to list those as being a problem (it explicitly lists
the foreign key constraint). It looks like they have a separate action
type for truncate so you can make before/after truncate triggers.