Re: Problem creating temporary tables . . .

El 20/09/2012, a las 12:36, Tom Lane escribió:

> =?iso-8859-1?Q?Rub=E9n_Crespo_Flores?= <ruben.crespo1@gmail.com> writes:
>>>> ERROR: could not find tuple for attrdef 299038853
>>>> SQL state: XX000
>>>> Context: SQL statement "create temporary table tmp_tablatotlin15 (...
>
>>> Hm ... that's a bit odd, but try reindexing pg_attrdef.
>
>> I tried reindexing pg_attrdef and pg_attribute but the result was the same.
>
> There's something awfully odd about that.  A look in the 9.0 sources
> says that that specific error text only appears in RemoveAttrDefault
> and getObjectDescription - and the latter is only used in error
> messages.  It's not apparent why a CREATE TABLE operation would either
> be dropping column defaults or issuing error messages that cite an
> already-existing default.  Could you show a complete example of this
> behavior?
>
> BTW, please keep the mailing list cc'd, so that more people can help you.
>
>             regards, tom lane

Here is an example :

CREATE OR REPLACE FUNCTION test.pruebas_tmp(pnumtablas integer)
  RETURNS integer AS
$BODY$
DECLARE
    lresp3 integer:=1;

begin
    while lresp3<=1 loop --pnumtablas loop

        begin
        delete from tmp_tablaprueba1;
        EXCEPTION
        WHEN  UNDEFINED_TABLE THEN
            begin
                RAISE NOTICE 'Before create ';
                create temporary table tmp_tablaprueba1 (campo1 integer, campo2 integer, campo3 numeric(1,0), campo4
smallint,campo5 varchar(6) ) ON COMMIT DROP; 
                 EXCEPTION
                  WHEN INTERNAL_ERROR then
                    RAISE NOTICE 'caught internal error';
            end;
        end;

        RAISE NOTICE 'After control . . .';

        lresp3:=lresp3 + 1;

    end loop;

    return lresp3;
end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION test.pruebas_tmp(integer) OWNER TO desarrollo;

From Pg_Admin I open a SQL Window and execute "select test.pruebas_tmp(1);"
I received the expected messages.

NOTICE:  Before create
NOTICE:  After control . . .
Tiempo total de ejecución de la consulta: 180 ms.
1 fila recuperada.

From this window I opened another one and from there I executed the same sentence.

I opened many windows and did the same until I got the error with this messages.

NOTICE:  Before create
NOTICE:  caught internal error
NOTICE:  despues del control . . .
Tiempo total de ejecución de la consulta: 60 ms.
1 fila recuperada.

Without error control I got this message :

NOTICE:  Before create
ERROR:  could not find tuple for attrdef 259154466
CONTEXT:  SQL statement "create temporary table tmp_tablaprueba1 (
              campo1 integer,
              campo2 integer,
              campo3 numeric(1,0),
              campo4 smallint,
              campo5 varchar(6)
              ) ON COMMIT DROP"
PL/pgSQL function "pruebas_tmp" line 14 at SQL statement

********** Error **********

ERROR: could not find tuple for attrdef 259154466
Estado SQL:XX000
Contexto:SQL statement "create temporary table tmp_tablaprueba1 (
              campo1 integer,
              campo2 integer,
              campo3 numeric(1,0),
              campo4 smallint,
              campo5 varchar(6)
              ) ON COMMIT DROP"
PL/pgSQL function "pruebas_tmp" line 14 at SQL statement


Thanks for your help.


Regards