observations about temporary tables and schemas

From: "Merlin Moncure" <merlin(dot)moncure(at)rcsonline(dot)com>
To: <pgsql-hackers(at)postgresql(dot)org>
Subject: observations about temporary tables and schemas
Date: 2003-09-16 18:37:32
Message-ID: 303E00EBDD07B943924382E153890E5434A9D2@cuthbert.rcsinc.local
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-interfaces pgsql-odbc

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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2003-09-16 18:55:14 Re: observations about temporary tables and schemas
Previous Message Mendola Gaetano 2003-09-16 18:24:21 FOR$X not work anymore with 7.4beta

Browse pgsql-interfaces by date

  From Date Subject
Next Message Tom Lane 2003-09-16 18:55:14 Re: observations about temporary tables and schemas
Previous Message Sergio Ramos 2003-09-16 14:58:34 Connection refused. HELP ME!!!!

Browse pgsql-odbc by date

  From Date Subject
Next Message Tom Lane 2003-09-16 18:55:14 Re: observations about temporary tables and schemas
Previous Message Brian Furey 2003-09-16 08:22:37 ODBC API