Another TEMP table trick

I don't know how I posted this, but it is wrong. I have added the
missing lines.

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

-- 
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

-- 
  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

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

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

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

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