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

Поиск
Список
Период
Сортировка

Can't drop temp table in subfunction during cursor loop (being used by active queries)

От
jonathansfl
Дата:
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.

Re: Can't drop temp table in subfunction during cursor loop (being used by active queries)

От
Rob Sargent
Дата:
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


>
>

Re: Can't drop temp table in subfunction during cursor loop (being used by active queries)

От
jonathansfl
Дата:
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.

Re: Can't drop temp table in subfunction during cursor loop (being used by active queries)

От
jonathansfl
Дата:
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.

Re: Re: Can't drop temp table in subfunction during cursor loop (being used by active queries)

От
Merlin Moncure
Дата:
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