Thread: Re: Possible Bug regarding temp tables (sql or psql?)
Forgot to mention that adding DROP TABLE v_idx ; before the END WORK will fix things. However, I was under the impression that temporary tables would go away after a transaction in which they were created was committed.
> Forgot to mention that adding > DROP TABLE v_idx ; > before the END WORK will fix things. However, I was under the > impression that > temporary tables would go away after a transaction in which they > were created > was committed. No - they go away at the end of a _connection_. However, there is now a patch floating around on -hackers that would add an ' ON COMMIT DROP;' option to CREATE TEMP TABLE. Chris
Any idea why the table can't be seen with \d in psql then? Christopher Kings-Lynne <chriskl@familyhealth.com.au> [02/07/04 00:21]: > > No - they go away at the end of a _connection_. However, there is now a > patch floating around on -hackers that would add an ' ON COMMIT DROP;' > option to CREATE TEMP TABLE. -- In the event of an emergency, my ass can be used as a flotation device. - Bender
Mark Frazer wrote: > Any idea why the table can't be seen with \d in psql then? It is a known problem with temp tables. They aren't visible with \d because they are invisible system tables that are removed on exit. I think 7.3 will fix this. > > Christopher Kings-Lynne <chriskl@familyhealth.com.au> [02/07/04 00:21]: > > > > No - they go away at the end of a _connection_. However, there is now a > > patch floating around on -hackers that would add an ' ON COMMIT DROP;' > > option to CREATE TEMP TABLE. > > -- > In the event of an emergency, my ass can be used as a flotation > device. - Bender > > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > > > -- 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, Pennsylvania19026
IIRC they are only visible in the connection that created them? Am I right? Either way, I don't think \d will show them - there might be another command... Chris > Any idea why the table can't be seen with \d in psql then? > > Christopher Kings-Lynne <chriskl@familyhealth.com.au> [02/07/04 00:21]: > > > > No - they go away at the end of a _connection_. However, there is now a > > patch floating around on -hackers that would add an ' ON COMMIT DROP;' > > option to CREATE TEMP TABLE.
Christopher Kings-Lynne wrote: > IIRC they are only visible in the connection that created them? Am I right? > Either way, I don't think \d will show them - there might be another > command... > TODO has: * Allow psql \d to show temporary table structure Looks like it works fine now with schemas:test=> create temp table xx(x int);CREATE TABLEtest=> \d List of relations Name| Type | Owner ------+-------+---------- x | table | postgres xx | table | postgres(2 rows) I will mark the TODO as done. -- 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, Pennsylvania19026
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> TODO has:
> * Allow psql \d to show temporary table structure
> Looks like it works fine now with schemas:
More accurately, it's no more broken for temp tables than any other
tables at the moment.
regression=# create table foo (f1 int);
CREATE TABLE
regression=# create temp table foo (f1 text, f2 float);
CREATE TABLE
regression=# \d foo Table "foo"Column | Type | Modifiers
--------+------------------+-----------f1 | integer |f1 | text |f2 | double precision
|
regression=#
Even uglier cases occur if some other connection has also created
a temp table named foo. You should *not* see that in \d ... but
you will.
> I will mark the TODO as done.
It's not really done, but the remaining fixes will fall out of making
psql schema-aware.
regards, tom lane
Tom Lane wrote: > Even uglier cases occur if some other connection has also created > a temp table named foo. You should *not* see that in \d ... but > you will. > > > I will mark the TODO as done. > > It's not really done, but the remaining fixes will fall out of making > psql schema-aware. The original bug is gone. We just have new ones. -- 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, Pennsylvania19026
Bruce Momjian <pgman@candle.pha.pa.us> [02/07/04 22:10]: > > TODO has: > > * Allow psql \d to show temporary table structure > > Looks like it works fine now with schemas: > > I will mark the TODO as done. It doesn't work with select into though: config=> select 5 into temp v_tmp ; SELECT config=> \d v_tmp Did not find any relation named "v_tmp". config=> select 4 into temp v_tmp ; ERROR: Relation 'v_tmp' already exists config=> select version() ; version -------------------------------------------------------------PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.96 -- I heard one time you single-handedly defeated a hoard of rampaging somethings in the something something system. - Fry
It works, but only in CVS, not in 7.2.X. Marking something as done in TODO only means it is done and will be in the _next_ release. Sorry. --------------------------------------------------------------------------- Mark Frazer wrote: > > > Bruce Momjian <pgman@candle.pha.pa.us> [02/07/04 22:10]: > > > > TODO has: > > > > * Allow psql \d to show temporary table structure > > > > Looks like it works fine now with schemas: > > > > I will mark the TODO as done. > > It doesn't work with select into though: > > config=> select 5 into temp v_tmp ; > SELECT > config=> \d v_tmp > Did not find any relation named "v_tmp". > config=> select 4 into temp v_tmp ; > ERROR: Relation 'v_tmp' already exists > config=> select version() ; > version > ------------------------------------------------------------- > PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.96 > > > -- > I heard one time you single-handedly defeated a hoard of rampaging somethings > in the something something system. - Fry > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > > > -- 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, Pennsylvania19026