BUG #9472: pg_dumpall fails with "unrecognized node type: 650"

Wilfried.Weiss@nsg.com writes:

pg_dumpall: query failed: ERROR: unrecognized node type: 650
pg_dumpall: query was: SELECT datname, coalesce(rolname, (select rolname
from pg_authid where oid=(select datdba from pg_database where
datname='template0'))), pg_encoding_to_char(d.encoding), datcollate,
datctype, datfrozenxid, datistemplate, datacl, datconnlimit, (SELECT spcname
FROM pg_tablespace t WHERE t.oid = d.dattablespace) AS dattablespace FROM
pg_database d LEFT JOIN pg_authid u ON (datdba = u.oid) WHERE datallowconn
ORDER BY 1

That's very bizarre. The implication of the error message is that
something examining a query parsetree found a Value node where it
wasn't expecting one. Ordinarily I'd think a parser or planner bug,
but since this is a standard query in pg_dumpall, it's hard to see
how such a bug could have escaped notice. Another possible theory
is system-catalog corruption, but I don't see how that would explain
this particular symptom. I wonder if you've got a corrupted postgres
executable.

In any case, since you're running a version that's a year or two
obsolete, updating to 9.2.latest would be a good first step.

What can I do to be able to unload the data again?

If upgrading doesn't help, I'd try telling pg_dumpall to connect
to a different database initially (see -d option). If it is some
bizarre species of catalog corruption, it probably is affecting
the postgres database only, so this might dodge the issue.

Failing that, try pg_dump'ing individual databases.

regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Hi Tom,

sorry for confusing you.
I found the reason for this issue.

Originally I compiled pg 9.2.2 using gcc and everything worked fine.
Then I compiled it again using xlc and restarted the engine without unloading and recreating the database.
It seems that are major differences even though it was the same software level.

Basically I prefer xlc as it runs better on AIX but there were some monetary reasons that made me try gcc.

Unfortunately this issue is so exotic that probably no one else can take advantage out of my experience.

Thank you for turning me into the right direction. It helped a lot.

Regards
Wilfried

-----Ursprüngliche Nachricht-----
Von: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Gesendet: Freitag, 7. März 2014 18:17
An: Weiss, Wilfried
Cc: pgsql-bugs@postgresql.org
Betreff: Re: [BUGS] BUG #9472: pg_dumpall fails with "unrecognized node type: 650"

Wilfried.Weiss@nsg.com writes:

pg_dumpall: query failed: ERROR: unrecognized node type: 650
pg_dumpall: query was: SELECT datname, coalesce(rolname, (select rolname
from pg_authid where oid=(select datdba from pg_database where
datname='template0'))), pg_encoding_to_char(d.encoding), datcollate,
datctype, datfrozenxid, datistemplate, datacl, datconnlimit, (SELECT spcname
FROM pg_tablespace t WHERE t.oid = d.dattablespace) AS dattablespace FROM
pg_database d LEFT JOIN pg_authid u ON (datdba = u.oid) WHERE datallowconn
ORDER BY 1

That's very bizarre. The implication of the error message is that
something examining a query parsetree found a Value node where it
wasn't expecting one. Ordinarily I'd think a parser or planner bug,
but since this is a standard query in pg_dumpall, it's hard to see
how such a bug could have escaped notice. Another possible theory
is system-catalog corruption, but I don't see how that would explain
this particular symptom. I wonder if you've got a corrupted postgres
executable.

In any case, since you're running a version that's a year or two
obsolete, updating to 9.2.latest would be a good first step.

What can I do to be able to unload the data again?

If upgrading doesn't help, I'd try telling pg_dumpall to connect
to a different database initially (see -d option). If it is some
bizarre species of catalog corruption, it probably is affecting
the postgres database only, so this might dodge the issue.

Failing that, try pg_dump'ing individual databases.

regards, tom lane

http://nsg.com/disclaimer

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

On Mon, Mar 10, 2014 at 10:25:39AM +0100, Weiss, Wilfried wrote:

I found the reason for this issue.

Originally I compiled pg 9.2.2 using gcc and everything worked fine.
Then I compiled it again using xlc and restarted the engine without unloading and recreating the database.
It seems that are major differences even though it was the same software level.

PostgreSQL stores facts in its control file sufficient to identify all known
variations of the on-disk binary format. Recompiling the same version with a
different compiler rarely requires a dump/reload, because compilers for the
same hardware often share a C language ABI. When that's not so, PostgreSQL
endeavors to emit an message explaining the incompatibility and refuse to
start against the incompatible data files. Failing to detect a
compiler-induced variation in the on-disk format would be a bug.

Unfortunately this issue is so exotic that probably no one else can take advantage out of my experience.

If this a reproducible problem for a particular compiler, it's worth fixing.

The PostgreSQL buildfarm currently has no active members running AIX or xlc.
If you're in a position to have a machine perform automated PostgreSQL test
runs, that would greatly increase the chance that PostgreSQL will work out of
the box on your configuration. More information:

http://buildfarm.postgresql.org/cgi-bin/register-form.pl

Thanks,
nm

--
Noah Misch
EnterpriseDB http://www.enterprisedb.com

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs