From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Kris Jurka <books@ejurka.com>
Cc: Merlin Moncure <merlin.moncure@rcsonline.com>
Cc: pgsql-hackers@postgresql.org, Rod Taylor <rbt@rbt.ca>
Cc: Gavin Sherry <swm@linuxworld.com.au>
Subject: Re: observations about temporary tables and schemas
Date: Wed, 17 Sep 2003 08:08:00 -0700 (PDT)
Message-ID: <20030917075737.P33923@megazone.bigpanda.com> (raw)
In-Reply-To: <2054.1063808792@sss.pgh.pa.us>
References: <Pine.LNX.4.33.0309161858470.30978-100000@leary.csoft.net>
<2054.1063808792@sss.pgh.pa.us>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-interfaces@postgresql.org
Cc: sszabo@megazone.bigpanda.com, tgl@sss.pgh.pa.us, books@ejurka.com, merlin.moncure@rcsonline.com, rbt@rbt.ca, swm@linuxworld.com.au
Subject: Re: observations about temporary tables and schemas
In-Reply-To: <20030917075737.P33923@megazone.bigpanda.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox