To: Stephan Szabo <sszabo@megazone.bigpanda.com>
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: <20030917075737.P33923@megazone.bigpanda.com> 
References: <Pine.LNX.4.33.0309161858470.30978-100000@leary.csoft.net>
	<2054.1063808792@sss.pgh.pa.us>
	<20030917075737.P33923@megazone.bigpanda.com>
Comments: In-reply-to Stephan Szabo <sszabo@megazone.bigpanda.com>
	message dated "Wed, 17 Sep 2003 08:08:00 -0700"
Date: Fri, 19 Sep 2003 17:10:57 -0400
Message-ID: <8360.1064005857@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> On Wed, 17 Sep 2003, Tom Lane wrote:
>> 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.

> I think the first is probably better all in all.

Done.

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

Okay, I have made it do this.  It's sort of an indirect thing: when you
try to set up the reference, the end-of-transaction ON COMMIT DELETE
ROWS barfs:

regression=# create temp table t1 (f1 int primary key) on commit delete rows;
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1"
CREATE TABLE
regression=# create temp table t2(f1 int references t1);
NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
ERROR:  cannot truncate a table referenced in a foreign key constraint
DETAIL:  Table "t2" references "t1" via foreign key constraint "$1".

I'm not sure if it's worth trying to improve the error message by
detecting the conflict more directly (ie, checking for ON COMMIT DELETE
ROWS status when creating the FK in the first place).

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

Okay, I just documented the existing behavior.

			regards, tom lane