Re: BUG when migrating from 8.0 to 8.1 with create temp table:SORRY MY - Mailing list pgsql-general

The log was not written I think

Sorry for the noise
/David


David Gagnon wrote:

> Hi all,
>
>  I just migrated from 8.0 to 8.1 and one of my stored procecure just
> hang when trying to create a temp table
>
> Here is my call:
>
> select * from usp_Comptabilite_AgeDeCompteClient('M', null,
> '2005-01-28', '1', '1', '0', null)
>
> If you look below in the log you will see that the function just hang
> when creating T_CP1 table.
>
> Is that a know bug .. this code worked with 8.0
>
> Thanks for your help
> Best Regards
> /David
>
>
>
> --- FUNCTION
> CREATE OR REPLACE FUNCTION usp_Comptabilite_AgeDeCompteClient(VARCHAR,
> VARCHAR, DATE, BOOLEAN, BOOLEAN, INT, VARCHAR) RETURNS refcursor  AS $$
> DECLARE
>
>     companyId ALIAS FOR $1;
>     Compte ALIAS FOR $2;
>     DateRef ALIAS FOR $3;
>
>     DateType ALIAS FOR $4;
>     Tri ALIAS FOR $5;
>     NBJour ALIAS FOR $6;
>     BORRNUMR ALIAS FOR $7;
>     DateRef_ DATE;
>
>     ref refcursor;
>     statement varchar(4000);
>     temp RECORD;
>
> BEGIN
>
>    IF (DateRef IS NOT NULL) THEN
>        DateRef_:=DateRef;
>    ELSE
>        DateRef_:=CURRENT_DATE;
>    END IF;
>
>
>
>  --  Toutes les ressources présentes (incluant le regroupement comptable)
>    EXECUTE '
>    CREATE TEMP TABLE T_RR  (
>        RRNUM VARCHAR(10) PRIMARY KEY
>    ) ON COMMIT DROP';
>
>    EXECUTE '
>    CREATE TEMP TABLE T_CR1  (
>        CRNUM INT PRIMARY KEY,
>        CRYPNUM VARCHAR(10),
>        CRMONT DECIMAL,
>        CRDATE DATE,
>        CRRRNUM VARCHAR(10),
>        CRACNUM VARCHAR(10),
>        GLNUM VARCHAR(10),
>        GLDESC_PRI varchar (100),
>        GLDESC_SEC varchar (100),
>        RRRRNUM VARCHAR(10),
>        RRGROUP INT
>    ) ON COMMIT DROP';
>
>    EXECUTE '
>    CREATE TEMP TABLE T_CP1  (
>        CRNUM INT PRIMARY KEY,
>        Paye DECIMAL
>    ) ON COMMIT DROP';
>
>    EXECUTE '
>    CREATE TEMP TABLE T_RA  (
>        RRNUM VARCHAR(10),
>        RANUM INTEGER
>    ) ON COMMIT DROP';
>
>    EXECUTE '
>    CREATE TEMP TABLE T_CR2  (
>        CRNUM INT,
>        CRMONT NUMERIC,
>        CRDATE DATE,
>        CRRRNUM VARCHAR(10),
>        CRACNUM VARCHAR(10),
>        Solde NUMERIC,
>        GLNUM VARCHAR(10),
>        GLDESC_PRI VARCHAR(100),
>        GLDESC_SEC VARCHAR(100),
>        RRRRNUM VARCHAR(10),
>        RRGROUP INT
>    ) ON COMMIT DROP';
>
>
>     --  Sélection des infos des comptes à recevoir
>     statement := ' INSERT INTO T_CR1 ( CRNUM, CRYPNUM, CRMONT, CRDATE,
> CRRRNUM, CRACNUM,
>                        GLNUM, GLDESC_PRI, GLDESC_SEC,
>                        RRRRNUM,
>                        RRGROUP)
>
>
>
> ----LOG-----------------
> OG:  statement: select * from usp_Comptabilite_AgeDeCompteClient('M',
> null, '2005-01-28', '1', '1', '0', null)
> NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
> "t_rr_pkey" for table "t_rr"
> CONTEXT:  SQL statement "
>
>        CREATE TEMP TABLE T_RR  (
>
>            RRNUM VARCHAR(10) PRIMARY KEY
>
>        ) ON COMMIT DROP"
>    PL/pgSQL function "usp_comptabilite_agedecompteclient" line 28 at
> execute statement
> NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
> "t_cr1_pkey" for table "t_cr1"
> CONTEXT:  SQL statement "
>
>        CREATE TEMP TABLE T_CR1  (
>
>            CRNUM INT PRIMARY KEY,
>
>            CRYPNUM VARCHAR(10),
>
>            CRMONT DECIMAL,
>
>            CRDATE DATE,
>
>            CRRRNUM VARCHAR(10),
>
>            CRACNUM VARCHAR(10),
>
>            GLNUM VARCHAR(10),
>
>            GLDESC_PRI varchar (100),
>
>            GLDESC_SEC varchar (100),
>
>            RRRRNUM VARCHAR(10),
>            RRGROUP INT
>
>        ) ON COMMIT DROP"
>    PL/pgSQL function "usp_comptabilite_agedecompteclient" line 33 at
> execute statement
> NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
> "t_cp1_pkey" for table "t_cp1"
> CONTEXT:  SQL statement "
>
>        CREATE TEMP TABLE T_CP1  (
>
>            CRNUM INT PRIMARY KEY,
>
>            Paye DECIMAL
>
>        ) ON COMMIT DROP"
>    PL/pgSQL function "usp_comptabilite_agedecompteclient" line 48 at
> execute statement
>