SELECT INTO TEMPORARY problem
I have a servlet which gets its data through a DataSource (Tomcat 5.5)
and starts each request as a new SQL transaction and either commits
the transaction or rolls it back at the end of each request.
In one of the requests I do a SELECT ... INTO TEMPORARY t1 ..., which
works just fine when I first use it, but from then on it objects saying
that t1 already exists. When I read the documentation (8.1 as that is
what I am using) I thought I understood that the table would disappear
at the end of the transaction. Other than deleting it, is there something
else I need to do or have I missunderstood that into temporary does?
David
David Goodenough wrote:
I thought I understood that the table would disappear
at the end of the transaction. Other than deleting it, is there something
else I need to do or have I missunderstood that into temporary does?
Dropped when the connection is closed normally, but see the ON COMMIT.
http://www.postgresql.org/docs/8.1/static/sql-createtable.html
You might have to do CREATE TABLE then INSERT INTO ... SELECT though.
--
Richard Huxton
Archonet Ltd
David Goodenough <david.goodenough@btconnect.com> writes:
In one of the requests I do a SELECT ... INTO TEMPORARY t1 ..., which
works just fine when I first use it, but from then on it objects saying
that t1 already exists. When I read the documentation (8.1 as that is
what I am using) I thought I understood that the table would disappear
at the end of the transaction.
No, the default is to last until end of session.
There's an ON COMMIT DROP option in the CREATE TEMP TABLE syntax,
but I don't think it's possible to stick it into an INTO TEMP clause.
regards, tom lane
am Wed, dem 17.01.2007, um 16:04:10 +0000 mailte David Goodenough folgendes:
I have a servlet which gets its data through a DataSource (Tomcat 5.5)
and starts each request as a new SQL transaction and either commits
the transaction or rolls it back at the end of each request.In one of the requests I do a SELECT ... INTO TEMPORARY t1 ..., which
works just fine when I first use it, but from then on it objects saying
that t1 already exists. When I read the documentation (8.1 as that is
what I am using) I thought I understood that the table would disappear
at the end of the transaction. Other than deleting it, is there something
else I need to do or have I missunderstood that into temporary does?
known problem, FAQ. Use EXECUTE for your DDL-Statements.
http://www.postgresql.org/docs/faqs.FAQ.html#item4.19
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
On 1/17/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
David Goodenough <david.goodenough@btconnect.com> writes:
In one of the requests I do a SELECT ... INTO TEMPORARY t1 ..., which
works just fine when I first use it, but from then on it objects saying
that t1 already exists. When I read the documentation (8.1 as that is
what I am using) I thought I understood that the table would disappear
at the end of the transaction.No, the default is to last until end of session.
There's an ON COMMIT DROP option in the CREATE TEMP TABLE syntax,
but I don't think it's possible to stick it into an INTO TEMP clause.regards, tom lane
IIRC, you can do it using CREATE TEMP TABLE t1 ON COMMIT DROP AS query
but i think this new in 8.2
why not extending this to SELECT INTO TEMP?
--
regards,
Jaime Casanova
"Programming today is a race between software engineers striving to
build bigger and better idiot-proof programs and the universe trying
to produce bigger and better idiots.
So far, the universe is winning."
Richard Cook