Re: Foreign key bugs + other problems

Stephan Szabo wrote:
> > Stephan Szabo wrote:
> > >
> > > Also, I realized something else that is a little wierd. When a
> temporary
> > > table shadows a permanent table that you've made a foreign key reference
> > > to, which table should it be going to check the constraint?
>
> > Outch - that hurts. Haven't checked it yet, but from what I
> > have in memory it should be a possibility to violate
> > constraints.
>
> Yeah, I realized it when I was going in on AlterTableAddConstraint that I
> need to prevent constraints referencing temporary tables on permanent
> tables, and then I realized that the shadowing is a problem. Also, this is
> a problem for other users too, what about people who log things to
> other tables via rules and triggers? At least you can't shadow the
> system catalogs :-)

I think triggers are in general problematic in this context.
They usually use SPI and name the tables in the querystring.
If a trigger uses saved plans (as RI does as much as
possible), the problem is gone if the trigger has been
invoked once and prepared all the plans. But if it's invoked
the first time while a temp table exists, it'll do the wrong
things and save the wrong plan for future invocations.

Rules aren't affected, because they refer to tables by OID
allways.

> [...]
>
> Or, you might be able to make a case that you CANNOT shadow a table
> that is referenced by a constraint (due to the permanent table constraints
> cannot reference a temporary table restriction). Since the creation of
> the temp table would break the restriction, it is illegal.

Good point. What does the standard say about it? Can a table,
referred to by foreign key constraints or referential
actions, be shadowed by a temp table?

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #