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@Yahoo.com #