Обсуждение: Can't drop temp table in subfunction during cursor loop (being used by active queries)

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.

jonathansfl wrote:
> 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.
>
>
Any chance of just passing in "++i" and using that as part of the temp table name


>
>

please explain, as I have no idea what that means. thank you for your reply!!

--
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-tp4482806p4484134.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

How would I create a dynamic table name, using the loop increment as a
parameter?

DROP TABLE IF EXISTS tt_PERSONSTODEACTIVATE || v_iteration CASCADE;

that doesn't work. don't think i can use a dynamic variable as a table name,
so i can't build the table name as a variable. perhaps if the entire DROP
TABLE is inside dynamic SQL and I execute entire script. could try that,
although it's messy for such a simple problem.

i still don't understand still why the TEMP tables are not acting TEMPORARY
and are not going away when their loop ends.

--
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-tp4482806p4484358.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On Mon, Jun 13, 2011 at 8:35 AM, jonathansfl <jonathanbrinkman@yahoo.com> wrote:
> How would I create a dynamic table name, using the loop increment as a
> parameter?
>
> DROP TABLE IF EXISTS tt_PERSONSTODEACTIVATE || v_iteration CASCADE;
>
> that doesn't work. don't think i can use a dynamic variable as a table name,
> so i can't build the table name as a variable. perhaps if the entire DROP
> TABLE is inside dynamic SQL and I execute entire script. could try that,
> although it's messy for such a simple problem.
>
> i still don't understand still why the TEMP tables are not acting TEMPORARY
> and are not going away when their loop ends.

'execute' is for that:
http://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

temp tables go away when the database session exits.

merlin