Using PG 8.4.2 with Ubuntu 10.04.2 LTS.
Inside a cursor of FUNCTION-A, I call another function (FUNCTION-B).
Function-B has a "Drop ,Table" command followed by a CREATE TEMPORARY TABLE
command.
The cursor loops but when it runs out, it breaks, giving error: "Cannot DROP
TABLE "tt_cms_alerts47" because it is being used by active queries in this
session"
I tried eliminating the DROP TABLE command since I thought the CREATE TABLE
would be skipped if the table already exists, but then it gives error:
"relation "tt_cms_alerts47" already exists".
I thought the temporary table would be dropped at the end of each separate
PERFORM function. I even added the DROP TABLE command again to the bottom of
FUNCTION-B but that didn't affect anything.
I also tried adding a COMMIT; after the PERFORM line of FUNCTION-A but that
broke everything.
[Code]
-- FUNCTION-A loop
OPEN InactivationCursor;
FETCH InactivationCursor INTO v_CompanyIDvar,v_ReasonInactivevar;
WHILE FOUND LOOP
BEGIN
--SELECT * INTO SWV_RCur,SWV_RCur2,SWV_RCur3,SWV_RCur4 FROM
pr_tbl_Persons_AutoActivate(null,v_CompanyIDvar,null,null,v_ReasonInactivevar,2,v_UserIDvar);
PERFORM
pr_tbl_Persons_AutoActivate(null,v_CompanyIDvar,null,null,v_ReasonInactivevar,2,v_UserIDvar);
END;
FETCH InactivationCursor INTO v_CompanyIDvar,v_ReasonInactivevar;
END LOOP;
CLOSE InactivationCursor;
[/Code]
[Code]
-- FUNCTION-B temporary table code
BEGIN
CREATE TEMP SEQUENCE tt_CMS_ALERTS_seq INCREMENT BY 1 START WITH 1;
EXCEPTION WHEN OTHERS THEN NULL;
END;
DROP TABLE IF EXISTS tt_CMS_ALERTS47 CASCADE;
BEGIN
CREATE TEMPORARY TABLE tt_CMS_ALERTS47
( AlertID INTEGER DEFAULT NEXTVAL('tt_CMS_ALERTS_seq') NOT NULL,
CMS_ALERT_MESSAGE VARCHAR(350) NOT NULL,
ALERT_LEVEL VARCHAR(10) NOT NULL
) WITH OIDS;
exception when others THEN TRUNCATE TABLE tt_CMS_ALERTS47;
END;
[/Code]
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Can-t-drop-temp-table-in-subfunction-during-cursor-loop-being-used-by-active-queries-tp4482806p4482806.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
