Skip site navigation (1) Skip section navigation (2)

Re: Temporary indexes

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
Cc: PostgreSQL-development <pgsql-hackers(at)postgreSQL(dot)org>
Subject: Re: Temporary indexes
Date: 2000-04-06 18:05:56
Message-ID: 27917.955044356@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> Do we have temporary indexes?
> 	test=> CREATE  TABLE temptest(col INTEGER);
> 	CREATE
> 	test=> create index ix on temptest (col);
> 	CREATE
> 	test=> CREATE TEMP TABLE masktest (col INTEGER);
> 	CREATE
> 	test=> create index ix on temptest (col);
> 	ERROR:  Cannot create index: 'ix' already exists

> Seems we don't.  Should I add it to the TODO list?

It seems to work when you use the right table names ;-)

regression=# create table foo (f1 int);
CREATE
regression=# create index foo_i on foo(f1);
CREATE
regression=# create temp table foo (f1t int);
CREATE
regression=# create index foo_i on foo(f1);
ERROR:  DefineIndex: attribute "f1" not found
regression=# create index foo_i on foo(f1t);
CREATE
regression=# explain select * from foo where f1t = 33;
NOTICE:  QUERY PLAN:

Index Scan using foo_i on foo  (cost=0.00..8.14 rows=10 width=4)

EXPLAIN
-- reconnect to drop temp tables
regression=# \connect regression
You are now connected to database regression.
regression=# explain select * from foo where f1t = 33;
ERROR:  Attribute 'f1t' not found
regression=# explain select * from foo where f1 = 33;
NOTICE:  QUERY PLAN:

Index Scan using foo_i on foo  (cost=0.00..8.14 rows=10 width=4)

EXPLAIN
regression=#


I do observe a minor glitch though, which is that psql's \d command
doesn't pay attention to temp-table aliases:

regression=# \d foo
          Table "foo"
 Attribute |  Type   | Modifier
-----------+---------+----------
 f1        | integer |
Index: foo_i

regression=#
regression=# create temp table foo (f1t int);
CREATE
regression=# \d foo
          Table "foo"
 Attribute |  Type   | Modifier
-----------+---------+----------
 f1        | integer |
Index: foo_i

I should be shown the temp table here, but I'm not.

			regards, tom lane

In response to

pgsql-hackers by date

Next:From: Bruce MomjianDate: 2000-04-06 18:06:50
Subject: Re: pg_dumplo, thanks :) (fwd)
Previous:From: Bruce MomjianDate: 2000-04-06 18:05:55
Subject: Re: pg_dumplo, thanks :) (fwd)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group