	>> test=> create table test (x int);
	>> CREATE
	>> test=> insert into test values (1);
	>> INSERT 
	>> test=> create temp table test (x int);  <--
	>> CREATE                                  <--
	>> test=> insert into test values (2);
	>> INSERT 19787 1
	>> test=> select * from test;
	>> x
	>> -
	>> 2
	>> (1 row)
	>> 
	>> test=> drop table test;
	>> DROP
	>> test=> select * from test;
	>> x
	>> -
	>> 1
	>> (1 row)
	>> 
	>> test=> drop table test;
	>> DROP

Do you really think that this should be allowed ? I think table names
including 
temp tables should (at least in combination with the owner) be unique. I
think your 
example above demonstrates how confusing the application code can get.

I think it is good, that temp tables are not really inserted into system
tables,
since this would be substantial overhead.
There could be a problem with GUI tools that rely on these rows
to format their output (like pgaccess or ODBC --> M$ Access) though.

Andreas


Fri, 29 Jan 1999 09:37:34 +0100
Zeugswetter Andreas IZ5 <Andreas.Zeugswetter@telecom.at>
AW: [HACKERS] Another TEMP table trick
> 	>> test=> create table test (x int);
> 	>> CREATE
> 	>> test=> insert into test values (1);
> 	>> INSERT 
> 	>> test=> create temp table test (x int);  <--
> 	>> CREATE                                  <--
> 	>> test=> insert into test values (2);
> 	>> INSERT 19787 1
> 	>> test=> select * from test;
> 	>> x
> 	>> -
> 	>> 2
> 	>> (1 row)
> 	>> 
> 	>> test=> drop table test;
> 	>> DROP
> 	>> test=> select * from test;
> 	>> x
> 	>> -
> 	>> 1
> 	>> (1 row)
> 	>> 
> 	>> test=> drop table test;
> 	>> DROP
> 
> Do you really think that this should be allowed ? I think table names
> including 
> temp tables should (at least in combination with the owner) be unique. I
> think your 
> example above demonstrates how confusing the application code can get.

I think it should be allowed.  Suppose someone has created a non-temp
table with a certain name, and you want a temp table with that name.  No
reason you shouldn't be allowed to do that.  Five people can all have
temp tables with the same name, so it doesn't matter if there is a
non-temp table with that name too.

> 
> I think it is good, that temp tables are not really inserted into system
> tables,
> since this would be substantial overhead.

Not really much overhead.

> There could be a problem with GUI tools that rely on these rows
> to format their output (like pgaccess or ODBC --> M$ Access) though.

Oh, never thought of that.  A select of pg_class will return no rows for
that table because it is a temp table.


-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Sat, 30 Jan 1999 00:13:47 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: AW: [HACKERS] Another TEMP table trick
Bruce Momjian wrote:
>
> > There could be a problem with GUI tools that rely on these rows
> > to format their output (like pgaccess or ODBC --> M$ Access) though.
> 
> Oh, never thought of that.  A select of pg_class will return no rows for
> that table because it is a temp table.

One more reson to move \d from psql to backend maybe with syntax like 
Oracle's "DESC xxx" unless there is something in ANSI standard for that.

Or implement the ANSI system tables (I think there were some ;) and
views.

Then the front-end tools can be advised to use these (and TEMP TABLES
can 
add rows to other (possibly structure-permanent) TEMP tables that are
UNIONed 
withe real pg_class to give them real values.

Or we can even implement just temp _rows_ for tables that exist in a 
session only (maybe like in independant uncommitted transactions), 
and add the info for temp tables to pg_class (and friends) as temp rows.

----------------
Hannu

Sat, 30 Jan 1999 13:44:17 +0200
Hannu Krosing <hannu@trust.ee>
Re: AW: [HACKERS] Another TEMP table trick
> Bruce Momjian wrote:
> >
> > > There could be a problem with GUI tools that rely on these rows
> > > to format their output (like pgaccess or ODBC --> M$ Access) though.
> > 
> > Oh, never thought of that.  A select of pg_class will return no rows for
> > that table because it is a temp table.
> 
> One more reson to move \d from psql to backend maybe with syntax like 
> Oracle's "DESC xxx" unless there is something in ANSI standard for that.
> 
> Or implement the ANSI system tables (I think there were some ;) and
> views.
> 
> Then the front-end tools can be advised to use these (and TEMP TABLES
> can 
> add rows to other (possibly structure-permanent) TEMP tables that are
> UNIONed 
> withe real pg_class to give them real values.
> 
 > Or we can even implement just temp _rows_ for tables that exist in a 
> session only (maybe like in independant uncommitted transactions), 
> and add the info for temp tables to pg_class (and friends) as temp rows.

I have thought some more about it, and I now want to create proper
pg_class rows for the temp tables.

The temp tables are named pg_temp.$pid.$seqno.  What I am going to do
for the temp table is to add an _extra_ entry in the system cache for
the user-supplied name RELNAME lookup.  All other lookups of pg_class by
oid, and pg_attribute, etc use just the relid, which works without any
translation.

The advantage is that I can keep the system tables consistent, have less
code overhead, and allow things like sequential scans of pg_class see
the table, even though it will not be under the user-supplied name.

Most interfaces already don't display pg_* tables, so this will be OK. 
I will add a new relkind for the temp tables.  I will also now be able
to test in vacuum if the temp table was orphaned after a backend crash,
and delete it.

I will prevent psql \dS from displaying the temp tables.

Should be a few more days.
-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Sat, 30 Jan 1999 20:24:49 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: AW: [HACKERS] Another TEMP table trick