From: Tom Lane <tgl@sss.pgh.pa.us>
To: 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 10:26:32 -0400
Message-ID: <2054.1063808792@sss.pgh.pa.us> (raw)
In-Reply-To: <Pine.LNX.4.33.0309161858470.30978-100000@leary.csoft.net>
References: <Pine.LNX.4.33.0309161858470.30978-100000@leary.csoft.net>
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:
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
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: 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: <2054.1063808792@sss.pgh.pa.us>
* 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