Re: Problem creating temporary tables . . .

=?iso-8859-1?Q?Rub=E9n_Crespo_Flores?= <ruben(dot)crespo1(at)gmail(dot)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