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 ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match