Received: from makus.postgresql.org ([98.129.198.125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TEn4Q-0006N9-Qp for pgsql-admin@postgresql.org; Thu, 20 Sep 2012 20:09:59 +0000 Received: from mail-gg0-f174.google.com ([209.85.161.174]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TEn4N-0002EM-HR for pgsql-admin@postgresql.org; Thu, 20 Sep 2012 20:09:58 +0000 Received: by ggnr5 with SMTP id r5so275473ggn.19 for ; Thu, 20 Sep 2012 13:09:54 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=subject:mime-version:content-type:from:in-reply-to:date:cc :content-transfer-encoding:message-id:references:to:x-mailer; bh=CP2UuiSPl3c9KC05J/4PuUBbvMcu0F4xIoAmvYYuF/I=; b=MNpjMJKqd7849EhvTKLRICGcVSsPYMqajMkVOt/9Wq41K94mExxUbi7usnNYq7kkxl mPxY1qwQTsftoh3d5TGhMDuSuYyc7L6/OpzoR38CjLaNy0M6NY1kOu8ZKOyp1W/+HOge 966BZhqRzTJQgsDPSJTfxh7rBtBjAdykgpico3Mae86NI2D0t5DW2fPn36clVOkJf0xm 3733XJXjsj0PLlzF5KpOyYwyR1HWUDG0srDW5yPVlBFcstyV3q7fVAu3rtB4jGUQuOXG GIZzaxVWl3EMN5IjbNTsGavLVAx3n5Jghic+jN6uMd8rn1RziVo9/1Qu0xyQ08AEQ0+O eJ9Q== Received: by 10.236.73.198 with SMTP id v46mr3167607yhd.80.1348171794079; Thu, 20 Sep 2012 13:09:54 -0700 (PDT) Received: from [192.168.1.104] ([200.76.1.140]) by mx.google.com with ESMTPS id i3sm7066012anl.0.2012.09.20.13.09.50 (version=TLSv1/SSLv3 cipher=OTHER); Thu, 20 Sep 2012 13:09:52 -0700 (PDT) Subject: Re: Problem creating temporary tables . . . Mime-Version: 1.0 (Apple Message framework v1251.1) Content-Type: text/plain; charset=iso-8859-1 From: =?iso-8859-1?Q?Rub=E9n_Crespo_Flores?= In-Reply-To: <26932.1348162580@sss.pgh.pa.us> Date: Thu, 20 Sep 2012 15:09:32 -0500 Cc: pgsql-admin@postgresql.org Content-Transfer-Encoding: quoted-printable Message-Id: References: <6F6C406D-518A-4E6F-ACE9-D234B6EB2418@gmail.com> <11992.1348090218@sss.pgh.pa.us> <26932.1348162580@sss.pgh.pa.us> To: Tom Lane X-Mailer: Apple Mail (2.1251.1) X-Pg-Spam-Score: -2.3 (--) X-Archive-Number: 201209/116 X-Sequence-Number: 40057 El 20/09/2012, a las 12:36, Tom Lane escribi=F3: > =3D?iso-8859-1?Q?Rub=3DE9n_Crespo_Flores?=3D = writes: >>>> ERROR: could not find tuple for attrdef 299038853 >>>> SQL state: XX000 >>>> Context: SQL statement "create temporary table tmp_tablatotlin15 = (... >=20 >>> Hm ... that's a bit odd, but try reindexing pg_attrdef. >=20 >> I tried reindexing pg_attrdef and pg_attribute but the result was the = same. >=20 > 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? >=20 > BTW, please keep the mailing list cc'd, so that more people can help = you. >=20 > regards, tom lane Here is an example : CREATE OR REPLACE FUNCTION test.pruebas_tmp(pnumtablas integer) RETURNS integer AS $BODY$ DECLARE=20 lresp3 integer:=3D1; begin while lresp3<=3D1 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; =09 RAISE NOTICE 'After control . . .'; =09 =09 lresp3:=3Dlresp3 + 1; end loop; return lresp3; end; $BODY$ LANGUAGE plpgsql VOLATILE COST 100; ALTER FUNCTION test.pruebas_tmp(integer) OWNER TO desarrollo; =46rom Pg_Admin I open a SQL Window and execute "select = test.pruebas_tmp(1);"=20 I received the expected messages. NOTICE: Before create NOTICE: After control . . . Tiempo total de ejecuci=F3n de la consulta: 180 ms. 1 fila recuperada. =46rom 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=F3n 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=