Lists: | pgsql-interfacespgsql-sql |
---|
From: | Mark Frazer <mark(at)somanetworks(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org, pgsql-interfaces(at)somanetworks(dot)com |
Subject: | Possible Bug regarding temp tables (sql or psql?) |
Date: | 2002-07-03 20:59:03 |
Message-ID: | 20020703165903.A17844@somanetworks.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-interfaces pgsql-sql |
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
Attachment | Content-Type | Size |
---|---|---|
bug.sql | text/plain | 537 bytes |
From: | Mark Frazer <mark(at)somanetworks(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org, pgsql-interfaces(at)postgresql(dot)org |
Subject: | Re: Possible Bug regarding temp tables (sql or psql?) |
Date: | 2002-07-03 21:12:32 |
Message-ID: | 20020703171232.Z1375@somanetworks.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-interfaces pgsql-sql |
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.
From: | "Christopher Kings-Lynne" <chriskl(at)familyhealth(dot)com(dot)au> |
---|---|
To: | "Mark Frazer" <mark(at)somanetworks(dot)com>, <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: Possible Bug regarding temp tables (sql or psql?) |
Date: | 2002-07-04 01:38:33 |
Message-ID: | GNELIHDDFBOCMGBFGEFOOEONCCAA.chriskl@familyhealth.com.au |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-interfaces pgsql-sql |
> 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
From: | Mark Frazer <mark(at)somanetworks(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Cc: | pgsql-interfaces(at)postgresql(dot)org |
Subject: | Re: [SQL] Possible Bug regarding temp tables (sql or psql?) |
Date: | 2002-07-04 11:23:18 |
Message-ID: | 20020704072318.A22194@somanetworks.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-interfaces pgsql-sql |
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
From: | Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> |
---|---|
To: | Mark Frazer <mark(at)somanetworks(dot)com> |
Cc: | pgsql-sql(at)postgresql(dot)org, pgsql-interfaces(at)postgresql(dot)org |
Subject: | Re: [SQL] Possible Bug regarding temp tables (sql or psql?) |
Date: | 2002-07-04 12:26:39 |
Message-ID: | 200207041226.g64CQeA04330@candle.pha.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-interfaces pgsql-sql |
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
From: | "Christopher Kings-Lynne" <chriskl(at)familyhealth(dot)com(dot)au> |
---|---|
To: | "Mark Frazer" <mark(at)somanetworks(dot)com>, <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: Possible Bug regarding temp tables (sql or psql?) |
Date: | 2002-07-05 02:02:02 |
Message-ID: | GNELIHDDFBOCMGBFGEFOOEPICCAA.chriskl@familyhealth.com.au |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-interfaces pgsql-sql |
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.
From: | Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> |
---|---|
To: | Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au> |
Cc: | Mark Frazer <mark(at)somanetworks(dot)com>, pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Possible Bug regarding temp tables (sql or psql?) |
Date: | 2002-07-05 02:09:27 |
Message-ID: | 200207050209.g6529RT19334@candle.pha.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-interfaces pgsql-sql |
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
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: | Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au>, Mark Frazer <mark(at)somanetworks(dot)com>, pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Possible Bug regarding temp tables (sql or psql?) |
Date: | 2002-07-05 02:32:56 |
Message-ID: | 7538.1025836376@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-interfaces pgsql-sql |
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
From: | Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au>, Mark Frazer <mark(at)somanetworks(dot)com>, pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Possible Bug regarding temp tables (sql or psql?) |
Date: | 2002-07-05 02:39:30 |
Message-ID: | 200207050239.g652dU822949@candle.pha.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-interfaces pgsql-sql |
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
From: | Mark Frazer <mark(at)somanetworks(dot)com> |
---|---|
To: | Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> |
Cc: | Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au>, pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Possible Bug regarding temp tables (sql or psql?) |
Date: | 2002-07-05 13:38:30 |
Message-ID: | 20020705093830.A32285@somanetworks.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-interfaces pgsql-sql |
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
From: | Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> |
---|---|
To: | Mark Frazer <mark(at)somanetworks(dot)com> |
Cc: | Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au>, pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Possible Bug regarding temp tables (sql or psql?) |
Date: | 2002-07-05 18:52:23 |
Message-ID: | 200207051852.g65IqNm01276@candle.pha.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-interfaces pgsql-sql |
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