Обсуждение: temp tables not dropping at end of script
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
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 explicitdrop of the temp table at the end of the script, or it will blow up the next time it runs, saying it cannot createthe 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, yetwe cannot rerun the script unless we stop and restart the database. This never happened with pg 8.0, so the definitionof "when a session ends" seems to have changed (isn't a temp table supposed to automatically disappear at the endof 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 DROPTABLE 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 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 puttinga 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 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 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.