[Help] Temporary Table: Implicitely created index not shown in \d i

"Itzinger, Oskar" <oitzinger@opec.org> writes:

CREATE TEMPORARY TABLE junk (col1 SERIAL, <more cols>)
and check \ds, the implicitely created sequence is shown but \di doesn't
show the implicitely created index.

\d and \di don't show temp tables and indexes.

The sequence object should be temp as well, but is not at the moment.

regards, tom lane

Hi,

I'm running v7.1.2. In psql, if I

CREATE TEMPORARY TABLE junk (col1 SERIAL, <more cols>)

and check \ds, the implicitely created sequence is shown but \di doesn't
show
the implicitely created index.

You have found a good bug. The code knows how to create TEMPORARY
tables and indexes, but not temporary sequences. What is happening is
that the sequence created is not temporary and will not go away after
session termination.

I will work on fixing it.

The reason the table or index doesn't appear is because temporary stuff
doesn't appear in \d commands.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@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

Hi,

I'm running v7.1.2. In psql, if I

CREATE TEMPORARY TABLE junk (col1 SERIAL, <more cols>)

and check \ds, the implicitely created sequence is shown but \di doesn't
show
the implicitely created index.

OK, here is a patch that fixes it so the sequence is temporary like
everything else. It also adds CREATE TEMPORARY SEQUENCE to the syntax.

test=> create temp table kkk(x serial);
NOTICE: CREATE TABLE will create implicit sequence 'kkk_x_seq' for
SERIAL column 'kkk.x'
NOTICE: CREATE TABLE/UNIQUE will create implicit index 'kkk_x_key' for
table 'kkk'
CREATE
test=> \d
No relations found.
test=> \di
No relations found.
test=> \dS
List of relations
Name | Type | Owner
----------------+----------+----------
...
pg_temp_5396_0 | sequence | postgres
pg_temp_5396_1 | table | postgres

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@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 <pgman@candle.pha.pa.us> writes:

test=> \d
No relations found.
test=> \di
No relations found.
test=> \dS
List of relations
Name | Type | Owner
----------------+----------+----------
...
pg_temp_5396_0 | sequence | postgres
pg_temp_5396_1 | table | postgres

Hm. Shouldn't psql's \dS ignore temp items, since \d and \di do?
Otherwise this looks pretty good...

regards, tom lane

I said:

Hm. Shouldn't psql's \dS ignore temp items, since \d and \di do?

Wait a sec --- I was confusing \dS (display system tables) with \ds
(display sequences). It looks like the latter should get it right
already.

Possibly it does make sense to suppress temp items in \dS, but there
isn't precedent for it. We should discuss it on its own merits.

regards, tom lane

I said:

Hm. Shouldn't psql's \dS ignore temp items, since \d and \di do?

Wait a sec --- I was confusing \dS (display system tables) with \ds
(display sequences). It looks like the latter should get it right
already.

test=> \ds
No relations found.

Possibly it does make sense to suppress temp items in \dS, but there
isn't precedent for it. We should discuss it on its own merits.

If we don't show temp items with \dS we need another backslash command
to show temp stuff. Doesn't seem worth it, especially since we don't
show much info about temp stuff except that numeric name.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@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