Date: Wed, 17 Sep 2003 08:08:00 -0700 (PDT)
From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Kris Jurka <books@ejurka.com>,
	Merlin Moncure <merlin.moncure@rcsonline.com>,
	pgsql-hackers@postgresql.org, Rod Taylor <rbt@rbt.ca>,
	Gavin Sherry <swm@linuxworld.com.au>
Subject: Re: observations about temporary tables and schemas 
In-Reply-To: <2054.1063808792@sss.pgh.pa.us>
Message-ID: <20030917075737.P33923@megazone.bigpanda.com>
References: <Pine.LNX.4.33.0309161858470.30978-100000@leary.csoft.net>
	<2054.1063808792@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

On Wed, 17 Sep 2003, Tom Lane wrote:

> Kris Jurka <books@ejurka.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.