On Wed, 17 Sep 2003, Tom Lane wrote: > Kris Jurka <[EMAIL PROTECTED]> 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. ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
