Re: observations about temporary tables and schemas

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
Cc: Kris Jurka <books(at)ejurka(dot)com>, Merlin Moncure <merlin(dot)moncure(at)rcsonline(dot)com>, pgsql-hackers(at)postgresql(dot)org, Rod Taylor <rbt(at)rbt(dot)ca>, Gavin Sherry <swm(at)linuxworld(dot)com(dot)au>
Subject: Re: observations about temporary tables and schemas
Date: 2003-09-19 21:10:57
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers pgsql-interfaces pgsql-odbc

Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)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.


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

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2003-09-19 21:24:32 Re: locking for unique hash indexes
Previous Message Nigel J. Andrews 2003-09-19 20:59:29 Re: [HACKERS] change of table name - any help

Browse pgsql-interfaces by date

  From Date Subject
Next Message RA Graffenberger 2003-09-20 08:06:06 libpq++ - How to use FieldType() ?
Previous Message Louise Cofield 2003-09-19 21:02:07 How to insert a string with single quotes in the text

Browse pgsql-odbc by date

  From Date Subject
Next Message Travel Jadoo 2003-09-20 12:56:04 LF <-> CR/LF conversion
Previous Message Mikhail Umorin 2003-09-19 14:56:41 Re: Delphi7, ADO, ODBC and PostgreSQL connection problems: