temp tables not dropping at end of script

On 04/06/2011 08:12 AM, Davenport, Julie wrote:

Since we upgraded from postgres 8.0 to 8.4, every script where we have
queries that use temp tables now has to have an explicit drop of the
temp table at the end of the script, or it will blow up the next time it
runs, saying it cannot create the temp table because it already exists
(these are coldfusion 8 scripts running queries on postgres 8.4
database). When we get the error, if we try to drop the table at the
command line, it says the table does not exist, yet we cannot rerun the
script unless we stop and restart the database. This never happened with
pg 8.0, so the definition of �when a session ends� seems to have changed
(isn�t a temp table supposed to automatically disappear at the end of
the session)? Is there some easier or better way to clear these
temporary areas?

Thanks,

Julie

julie.davenport@ctcd.edu

The connection running the script actually terminates?

On Wed, Apr 06, 2011 at 09:12:55AM -0500, Davenport, Julie wrote:

postgres 8.4 database). When we get the error, if we try to drop
the table at the command line, it says the table does not exist, yet
we cannot rerun the script unless we stop and restart the database.

What if you stop your connection? This sounds like under 8.0 you were
closing the connection (thereby ending a session), but that under 8.4
your connection isn't actually closing (so your session remains open,
so the temp table hangs around).

A

--
Andrew Sullivan
ajs@crankycanuck.ca

Since we upgraded from postgres 8.0 to 8.4, every script where we have queries that use temp tables now has to have an explicit drop of the temp table at the end of the script, or it will blow up the next time it runs, saying it cannot create the temp table because it already exists (these are coldfusion 8 scripts running queries on postgres 8.4 database).  When we get the error, if we try to drop the table at the command line, it says the table does not exist, yet we cannot rerun the script unless we stop and restart the database.  This never happened with pg 8.0, so the definition of "when a session ends" seems to have changed (isn't a temp table supposed to automatically disappear at the end of the session)?  Is there some easier or better way to clear these temporary areas?
Thanks,
Julie
julie.davenport@ctcd.edu

I ran in to a similar issue with our scripts. I took the easy way out and before creating each temp table, I added a DROP TABLE IF EXISTS statement. If the table doesn't exist, I get a warning but my script doesn't fail.

Mike

On Wed, Apr 06, 2011 at 09:12:55AM -0500, Davenport, Julie wrote:

postgres 8.4 database). When we get the error, if we try to drop
the table at the command line, it says the table does not exist, yet
we cannot rerun the script unless we stop and restart the database.

What if you stop your connection? This sounds like under 8.0 you were
closing the connection (thereby ending a session), but that under 8.4
your connection isn't actually closing (so your session remains open,
so the temp table hangs around).

A

--

<Andrew Sullivan
<ajs(at)crankycanuck(dot)ca

We've never explicitly closed the connection, it just seemed to close automatically when the coldfusion script ended. Not sure how I would even do that from a script since this is run automatically, not from the command line. Other than putting a quit inside a cfquery tag?
Thanks,
Julie
julie.davenport@ctcd.edu

On Wed, Apr 06, 2011 at 10:47:55AM -0500, Davenport, Julie wrote:

We've never explicitly closed the connection, it just seemed to close automatically when the coldfusion script ended. Not sure how I would even do that from a script since this is run automatically, not from the command line. Other than putting a quit inside a cfquery tag?

Is it possible that the older driver closed automatically?

Anyway, you could set a savepoint, try to create the temp table, and
then rollback to savepoint if it doesn't work or else continue if it
does.

A

--
Andrew Sullivan
ajs@crankycanuck.ca

On Apr 6, 2011, at 9:47 AM, Davenport, Julie wrote:

We’ve never explicitly closed the connection, it just seemed to close automatically when the coldfusion script ended.

My guess is you've also upgraded coldfusion, or changed its config, and now it's caching connections.

--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice

we're having a similar situation, where FunctionA calls FunctionB inside a
cursor. FunctionB DROPs Temp table, then creates temp table. FunctionA runs
through the cursor fine but breaks after the last loop, unable to DROP
temporary table "because it is being used by active queries in this
session."

Those sessions should have been closed after each loop!

Here is my ticket on this:
http://postgresql.1045698.n5.nabble.com/Can-t-drop-temp-table-in-subfunction-during-cursor-loop-being-used-by-active-queries-td4482806.html#a4484134

Here is another similar (and unanswered) ticket:
http://forums.enterprisedb.com/posts/list/849.page

--
View this message in context: http://postgresql.1045698.n5.nabble.com/temp-tables-not-dropping-at-end-of-script-tp4286391p4484759.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.