Re: observations about temporary tables and schemas

On Tue, 16 Sep 2003, Kris Jurka wrote:
> Something else I've noticed about temp tables is that you are
prohibited
> from having a permanent table contain a foreign key reference to a
temp
> table, but 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 had no idea this was even possible. IHRTS (I haven't read the spec),
but again here temp tables seem to break rules in strange and unexpected
ways. From the docs, we know that temp tables are 'dropped' after the
connection is broken. With RI in mind, (and more generally, triggers)
the nature and execution of that drop is important.

All that being said, using RI in temp tables (especially with regular
tables) is asking for trouble. The reason I was hoping to keep them in
a special schema is that I use them as a kind of a database enforced
mutex object using the name of the table as the mutex identifier. By
holding them in a special schema (e.g. locks) that is not in the schema
search path I can bypass name resolution problems that happened when the
'mutex' id is the same name as another table (actually, there are no
problems, it simply isn't allowed!).

Merlin