Re: BUG when migrating from 8.0 to 8.1 with create temp table:SORRY MY

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
>