Following tests were made in linux server running pg 7.4 beta 2.
I have been playing with temporary tables a little bit and noticed some
interesting things. I'm not sure if this is a part of the standard
canon or not but I thought it worth mentioning. Sorry if I'm bleating
out the obvious!
According to the docs, all temporary tables are local and scoped to the
current backend. AFAICT, this principle can be violated in two ways,
one expected and one not. The first and obvious way is to make a query
vs. pg_class and you can see temporary tables from other users. This is
expected and IMO a useful property of temporary tables.
The other and more interesting way is to manually jump into the
temporary schema (eg. pg_temp_x) that hosts a temp table constructed by
another backend. While this is unlikely to happen in a normal setting,
the server does allow it. Following this, backend X can both see and
manipulate a temporary table set up by backend Y, including table schema
manipulation.
Both backends can insert records into the table but each can only see
the records they inserted. However, changes to table structure (e.g.
alter table) are visible to both backends. Interestingly, if backend X
does a drop column on a temp table, this shows up as ..pg.droppped.1...
in a psql \d on backend Y. Following this, a count(*) from backend Y
counts the records from backend X even though the records are not
visible.
Also, Is the prohibition of using schemas for temp tables a SQL spec
requirement or a technical consideration?
Regards,
Merlin