Re: Foreign key bugs + other problems - Mailing list pgsql-hackers

> 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 that schemas might help, if you assume that at creation time of a
rule or constraint you must qualify any tables being used in a way that
prevents misunderstandings, since temporary tables live in a different
system defined schema assuming that schema.tablename is not
shadowed, only the unadorned tablename. In the FK case, I think
that the idea of moving to keeping oids would probably be the way
to go (that way the table is very explicitly defined as a particular one).
Not that this will help right now since I don't think we can make an
SPI request that will handle it.

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.

-----

In an unrelated problem.  Well, I was thinking, well, maybe we could for
this transaction during the execution of the trigger, rename the temp table
and then rename it back.  Noone else should see the change (right?) because
we're not comitted and that user isn't doing anything else while we're
checking.
However, this tickles another problem.  It seems that you can't rename a
temp
table.  In fact it does something bad:

create table z (a int);
create temp table z (a int);
alter table z rename to zz;
select * from z;
ERROR: relation 'z' does not exist
select * from zz;
- 0 rows
\q
<enter again>
select * from z;
NOTICE:  mdopen: couldn't open z: No such file or directory
NOTICE:  RelationIdBuildRelation: smgropen(z): Input/output error
NOTICE:  mdopen: couldn't open z: No such file or directory
NOTICE:  mdopen: couldn't open z: No such file or directory
NOTICE:  mdopen: couldn't open z: No such file or directory
NOTICE:  mdopen: couldn't open z: No such file or directory
ERROR:  cannot open relation z
select * from zz;
- 0 rows