Re: Possible Bug regarding temp tables (sql or psql?)

When using the attached script in psql, the temp variables disappear as
far as \distv shows, but running the script a second time fails.

To reproduce, save the following script as bug.sql, then start psql on an
test database.
\i bug.sql
\distv
-- no relations should be shown
\i bug.sql
-- this will fail.

However, if you run psql again, you can
\i bug.sql
successfully. It will only fail if run twice in the same script.

cheers
-mark

--
Hardy Boys: too easy. Nancy Drew: too hard! - Fry

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(at)familyhealth(dot)com(dot)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(at)familyhealth(dot)com(dot)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(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>
>
>

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

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(at)familyhealth(dot)com(dot)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 TABLE
test=> \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(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026