pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Rubén Crespo Flores <ruben.crespo1@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-admin@postgresql.org
Subject: Re: Problem creating temporary tables . . .
Date: Thu, 20 Sep 2012 15:09:32 -0500
Message-ID: <D5173823-CCA8-4323-BBFF-2E493A7B071B@gmail.com> (raw)
In-Reply-To: <26932.1348162580@sss.pgh.pa.us>
References: <6F6C406D-518A-4E6F-ACE9-D234B6EB2418@gmail.com>
	<11992.1348090218@sss.pgh.pa.us>
	<CF523163-E84B-4D07-B98A-2ED5E0DD4E70@gmail.com>
	<26932.1348162580@sss.pgh.pa.us>


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



view thread (5+ messages)  latest in thread

Message-ID: <D5173823-CCA8-4323-BBFF-2E493A7B071B@gmail.com>
Permalink:  ../D5173823-CCA8-4323-BBFF-2E493A7B071B@gmail.com/
Also on:    postgresql.org/message-id/D5173823-CCA8-4323-BBFF-2E493A7B071B@gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: ruben.crespo1@gmail.com, tgl@sss.pgh.pa.us
  Subject: Re: Problem creating temporary tables . . .
  In-Reply-To: <D5173823-CCA8-4323-BBFF-2E493A7B071B@gmail.com>
  References: <6F6C406D-518A-4E6F-ACE9-D234B6EB2418@gmail.com> <11992.1348090218@sss.pgh.pa.us> <CF523163-E84B-4D07-B98A-2ED5E0DD4E70@gmail.com> <26932.1348162580@sss.pgh.pa.us> <D5173823-CCA8-4323-BBFF-2E493A7B071B@gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox