Обсуждение: Problem creating temporary tables . . .

Hi, I'm using PostgreSQL 9.0.4, with the replication activated to one server.

About 3 weeks ago, we have a disk space problem on both servers. I recovered the DB but since then (or at least had not
noticedbefore),  in 10 executions of functions that generate temporary tables, 3 returns the following error message: 

ERROR: could not find tuple for attrdef 299038853
SQL state: XX000
Context: SQL statement "create temporary table tmp_tablatotlin15 (...

We apply vaccum analize to pg_class, pg_attribute, pg_attrdef and pg_depends, but did not help much.

I appreciate your attention. Any suggestions are welcome.

regards and thanks

=?iso-8859-1?Q?Rub=E9n_Crespo_Flores?= <ruben.crespo1@gmail.com> writes:
> Hi, I'm using PostgreSQL 9.0.4, with the replication activated to one server.
> About 3 weeks ago, we have a disk space problem on both servers. I recovered the DB but since then (or at least had
notnoticed before),  in 10 executions of functions that generate temporary tables, 3 returns the following error
message:

> 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.

BTW, 9.0.4 is a bit old, you really ought to update to 9.0.latest.

            regards, tom lane

=?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

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

=?iso-8859-1?Q?Rub=E9n_Crespo_Flores?= <ruben.crespo1@gmail.com> writes:
> El 20/09/2012, a las 12:36, Tom Lane escribi�:
>> 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?

> Here is an example :

Hmm ... unsurprisingly, this doesn't fail for me.  I also tried setting
debugger breakpoints at the two places where the message could be
generated, and neither of them are ever reached while running this
function.  So something's pretty broken at your end.

After thinking for awhile, though, I do have a theory, and it squares
with your observation that you need to open a lot of connections to see
the problem.  I think that there's a broken partial table definition in
a high-numbered pg_temp_nnn schema.  When a new session first tries to
use a temp table, it either creates or cleans out the pg_temp_nnn schema
corresponding to its session number.  If some previous user of that
schema had left it not-cleaned-out as a result of crashing, that would
result in table drop attempts, and from there it's not hard to see why
you'd get to RemoveAttrDefault.  If things are sufficiently confused
this could end up trying to remove column defaults that aren't there.

What I'd suggest doing is looking in pg_depend for the row with classid
= 2604 and objid = 259154466, and manually removing that row.  That
should let the schema drop get past this problem.  There might be some
more, similar, problems.

Also, it'd probably be a smart thing to reindex pg_depend, just in case
this isn't a data problem but an index problem.

            regards, tom lane