ERROR: nodeRead: did not find '}' [x2]

Hello,
it seems my postgresql data has somehow become corrupted (by a forced
shutdown I think):

psql template1 -U shadow
Password:
ERROR: nodeRead: did not find '}' at end of plan node
Welcome to psql 7.3.4, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit

template1=> \d
ERROR: current transaction is aborted, queries ignored until end of
transaction block
template1=> abort;
ROLLBACK
template1=> \d
ERROR: nodeRead: did not find '}' at end of plan node

Every command that tries to access a table gives this error...

pg_dumpall says:

pg_dump: SQL command failed
pg_dump: Error message from server: ERROR: nodeRead: did not find '}' at
end of plan node
pg_dump: The command was: select (select usename from pg_user where
usesysid = datdba) as dba, encoding, datpath from pg_database where
datname = 'cinema'
pg_dumpall: pg_dump failed on cinema, exiting

What can I do ?

(postgresql 7.3.4 on a gentoo linux box, with psql from the same package)

Andrei Ivanov <andrei(dot)ivanov(at)ines(dot)ro> writes:
> it seems my postgresql data has somehow become corrupted (by a forced
> shutdown I think):

> psql template1 -U shadow
> Password:
> ERROR: nodeRead: did not find '}' at end of plan node

and

> pg_dump: SQL command failed
> pg_dump: Error message from server: ERROR: nodeRead: did not find '}' at
> end of plan node
> pg_dump: The command was: select (select usename from pg_user where
> usesysid = datdba) as dba, encoding, datpath from pg_database where
> datname = 'cinema'
> pg_dumpall: pg_dump failed on cinema, exiting

These both look like the pg_user view is corrupted.

It seems hard to believe that identical corruption could have affected
two databases at once. Do you see this same problem with all your
databases (if you have any more than template1 and cinema)? If so,
I wonder whether the postgres executable got corrupted --- it'd not be
very common for a system failure to corrupt files that aren't being
modified, but perhaps it could happen. If it is that, you could fix it
with a quick reinstall of the RPM.

regards, tom lane

I've reinstalled postgres, but the same errors happen.
I've tried connection to more databases, but the error show everytime.

psql -U shadow dc
Password:
ERROR: nodeRead: did not find '}' at end of plan node
Welcome to psql 7.3.4, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit

dc=> \dt
ERROR: current transaction is aborted, queries ignored until end of
transaction block
dc=>

???

This is the log from when the forced system shutdown happened:

LOG: pq_recvbuf: unexpected EOF on client connection
LOG: pq_recvbuf: unexpected EOF on client connection
LOG: pq_recvbuf: unexpected EOF on client connection
LOG: fast shutdown request
LOG: shutting down
LOG: database system is shut down
LOG: database system was shut down at 2003-11-06 23:21:03 EET
LOG: checkpoint record is at 0/7CDAC94
LOG: redo record is at 0/7CDAC94; undo record is at 0/0; shutdown TRUE
LOG: next transaction id: 115743; next oid: 205535
LOG: database system is ready
LOG: database system was interrupted at 2003-11-06 23:21:05 EET
LOG: checkpoint record is at 0/7CDAC94
LOG: redo record is at 0/7CDAC94; undo record is at 0/0; shutdown TRUE
LOG: next transaction id: 115743; next oid: 205535
LOG: database system was not properly shut down; automatic recovery in
progress
LOG: ReadRecord: record with zero length at 0/7CDACD4
LOG: redo is not required
LOG: database system is ready

On Thu, 20 Nov 2003, Tom Lane wrote:

> Andrei Ivanov <andrei(dot)ivanov(at)ines(dot)ro> writes:
> > it seems my postgresql data has somehow become corrupted (by a forced
> > shutdown I think):
>
> > psql template1 -U shadow
> > Password:
> > ERROR: nodeRead: did not find '}' at end of plan node
>
> and
>
> > pg_dump: SQL command failed
> > pg_dump: Error message from server: ERROR: nodeRead: did not find '}' at
> > end of plan node
> > pg_dump: The command was: select (select usename from pg_user where
> > usesysid = datdba) as dba, encoding, datpath from pg_database where
> > datname = 'cinema'
> > pg_dumpall: pg_dump failed on cinema, exiting
>
> These both look like the pg_user view is corrupted.
>
> It seems hard to believe that identical corruption could have affected
> two databases at once. Do you see this same problem with all your
> databases (if you have any more than template1 and cinema)? If so,
> I wonder whether the postgres executable got corrupted --- it'd not be
> very common for a system failure to corrupt files that aren't being
> modified, but perhaps it could happen. If it is that, you could fix it
> with a quick reinstall of the RPM.
>
> regards, tom lane
>

Andrei Ivanov <andrei(dot)ivanov(at)ines(dot)ro> writes:
> I've reinstalled postgres, but the same errors happen.
> I've tried connection to more databases, but the error show everytime.

Hm, that's very odd. Could we see the results of

select * from pg_rewrite where ev_class = 'pg_user'::regclass;
and
select pg_get_viewdef('pg_user'::regclass);

You'll probably need to do a "rollback" to get psql out of its
transaction-aborted state after startup, but then I'd expect the
first of these to work. The second will likely fail, but we should
confirm that ...

regards, tom lane

select * from pg_rewrite where ev_class = 'pg_user'::regclass gives
(extended display):

rulename | _RETURN
ev_class | 16681
ev_attr | -1
ev_type | 1
is_instead | t
ev_qual | <>
ev_action | ({ QUERY :command 1 :source 0 :utility <> :resultRelation 0
:into <> :isPortal false :isBinary false :hasAggs false :hasSubLinks false
:rtable ({ RTE :alias { ALIAS :aliasname *OLD* :colnames <>} :eref { ALIAS
:aliasname *OLD* :colnames ( "usename" "usesysid" "usecreatedb"
"usesuper" "usecatupd" "passwd" "valuntil" "useconfig" )} :rtekind
0 :relid 16681 :inh false :inFromCl false :checkForRead false
:checkForWrite false :checkAsUser 1} { RTE :alias { ALIAS :aliasname *NEW*
:colnames <>} :eref { ALIAS :aliasname *NEW* :colnames ( "usename"
"usesysid" "usecreatedb" "usesuper" "usecatupd" "passwd"
"valuntil" "useconfig" )} :rtekind 0 :relid 16681 :inh false :inFromCl
false :checkForRead false :checkForWrite false :checkAsUser 1} { RTE
:alias <> :eref { ALIAS :aliasname pg_shadow :colnames ( "usename"
"usesysid" "usecreatedb" "usesuper" "usecatupd" "passwd"
"valuntil" "useconfig" )} :rtekind 0 :relid 1260 :inh true :inFromCl
true :checkForRead true :checkForWrite false :checkAsUser 1}) :jointree {
FROMEXPR :fromlist ({ RANGETBLREF 3 }) :quals <>} :rowMarks () :targetList
({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 19 :restypmod -1
:resname usename :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false }
:expr { VAR :varno 3 :varattno 1 :vartype 19 :vartypmod -1 :varlevelsup 0
:varnoold 3 :varoattno 1}} { TARGETENTRY :resdom { RESDOM :resno 2
:restype 23 :restypmod -1 :resname usesysid :reskey 0 :reskeyop 0
:ressortgroupref 0 :resjunk false } :expr { VAR :varno 3 :varattno 2
:vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 3 :varoattno 2}} {
TARGETENTRY :resdom { RESDOM :resno 3 :restype 16 :restypmod -1 :resname
usecreatedb :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false }
:expr { VAR :varno 3 :varattno 3 :vartype 16 :vartypmod -1 :varlevelsup 0
:varnoold 3 :varoattno 3}} { TARGETENTRY :resdom { RESDOM :resno 4
:restype 16 :restypmod -1 :resname usesuper :reskey 0 :reskeyop 0
:ressortgroupref 0 :resjunk false } :expr { VAR :varno 3 :varattno 4
:vartype 16 :vartypmod -1 :varlevelsup 0 :varnoold 3 :varoattno 4}} {
TARGETENTRY :resdom { RESDOM :resno 5 :restype 16 :restypmod -1 :resname
usecatupd :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr
{ VAR :varno 3 :varattno 5 :vartype 16 :vartypmod -1 :varlevelsup 0
:varnoold 3 :varoattno 5}} { TARGETENTRY :resdom { RESDOM :resno 6
:restype 25 :restypmod -1 :resname passwd :reskey 0 :reskeyop 0
:ressortgroupref 0 :resjunk false } :expr { CONST :consttype 25 :constlen
-1 :constbyval false :constisnull false :constvalue 12 [ 12 0 0 0 42 42
42 42 42 42 42 42 ] }} { TARGETENTRY :resdom { RESDOM :resno 7 :restype
702 :restypmod -1 :resname valuntil :reskey 0 :reskeyop 0 :ressortgroupref
0 :resjunk false } :expr { VAR :varno 3 :varattno 7 :vartype 702
:vartypmod -1 :varlevelsup 0 :varnoold 3 :varoattno 7}} { TARGETENTRY
:resdom { RESDOM :resno 8 :restype 1009 :restypmod -1 :resname useconfig
:reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR
:varno 3 :varattno 8 :vartype 1009 :vartypmod -1 :varlevelsup 0 :varnoold
3 :varoattno 8}}) :groupClause <> :havingQual <> :distinctClause <>
:sortClause <> :limitOffset <> :limitCount <> :setOperations <>
:resultRelations ()})

The second query indeed fails...

On Thu, 20 Nov 2003, Tom Lane wrote:

> Andrei Ivanov <andrei(dot)ivanov(at)ines(dot)ro> writes:
> > I've reinstalled postgres, but the same errors happen.
> > I've tried connection to more databases, but the error show everytime.
>
> Hm, that's very odd. Could we see the results of
>
> select * from pg_rewrite where ev_class = 'pg_user'::regclass;
> and
> select pg_get_viewdef('pg_user'::regclass);
>
> You'll probably need to do a "rollback" to get psql out of its
> transaction-aborted state after startup, but then I'd expect the
> first of these to work. The second will likely fail, but we should
> confirm that ...
>
> regards, tom lane
>

Andrei Ivanov <andrei(dot)ivanov(at)ines(dot)ro> writes:
> select * from pg_rewrite where ev_class = 'pg_user'::regclass gives
> [ lots of cruft ]

Well, that's interesting, because I get exactly the same data from my
perfectly-functional 7.3.4 installation.

I think there must be some incompatibility between your data directory
and your Postgres executable, but it's hard to think what. Have you
verified that your executable really is 7.3.4 (try "select version()")?

I suppose it could also be that the problem is not in pg_user after all,
but some other view. I didn't think pg_get_viewdef() would use any
other views, but I might be mistaken. Can you successfully do
pg_get_viewdef on any other views --- pg_tables or pg_indexes for
example?

Also, how did you get into this state, exactly? I cannot believe that
it's a matter of a forced shutdown; I think there must be some kind of
software compatibility issue involved. What system updates have you
done recently?

regards, tom lane

On Thu, 20 Nov 2003, Tom Lane wrote:

> Andrei Ivanov <andrei(dot)ivanov(at)ines(dot)ro> writes:
> > select * from pg_rewrite where ev_class = 'pg_user'::regclass gives
> > [ lots of cruft ]
>
> Well, that's interesting, because I get exactly the same data from my
> perfectly-functional 7.3.4 installation.
>
> I think there must be some incompatibility between your data directory
> and your Postgres executable, but it's hard to think what. Have you
> verified that your executable really is 7.3.4 (try "select version()")?

PostgreSQL 7.3.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.2
20031022 (Gentoo Linux 3.3.2-r2, propolice)

>
> I suppose it could also be that the problem is not in pg_user after all,
> but some other view. I didn't think pg_get_viewdef() would use any
> other views, but I might be mistaken. Can you successfully do
> pg_get_viewdef on any other views --- pg_tables or pg_indexes for
> example?
>

template1=> select pg_get_viewdef('pg_tables'::regclass);
ERROR: nodeRead: did not find '}' at end of plan node
template1=> select pg_get_viewdef('pg_indexes'::regclass);
ERROR: nodeRead: did not find '}' at end of plan node
template1=>

> Also, how did you get into this state, exactly? I cannot believe that
> it's a matter of a forced shutdown; I think there must be some kind of
> software compatibility issue involved. What system updates have you
> done recently?
>
> regards, tom lane

I really can't think of anything else that could have brought postgresql
in this state... (could on minor update to glibc cause this ? I don't
remember if updated glibc before or after the corruption...)

Andrei Ivanov <andrei(dot)ivanov(at)ines(dot)ro> writes:
> template1=> select pg_get_viewdef('pg_tables'::regclass);
> ERROR: nodeRead: did not find '}' at end of plan node
> template1=> select pg_get_viewdef('pg_indexes'::regclass);
> ERROR: nodeRead: did not find '}' at end of plan node

Bizarre. I just checked, and this query will not result in trying to
open any views other than the one named by the get_viewdef argument.
So it sure appears that reading any view rule will cause the failure.

> I really can't think of anything else that could have brought postgresql
> in this state... (could on minor update to glibc cause this ? I don't
> remember if updated glibc before or after the corruption...)

Doesn't seem real likely to me either ... but we seem to have eliminated
all the other possibilities. The data in the database doesn't appear to
be corrupted, and you already tried reinstalling Postgres, so the
executable file isn't corrupted, which seems to leave the libraries.
Are you in a position to back out the glibc update and see what happens?

regards, tom lane

Andrei Ivanov <andrei(dot)ivanov(at)ines(dot)ro> writes:
> I'd rather not downgrade glibc because of some bad experience with it...

> Any way, I've installed the same version of postgresql on another computer
> and copied the data directory the, and, surprise, no errors...

Sounds like the smoking gun to me, for sure.

Exactly which glibc version was this?

regards, tom lane

I'd rather not downgrade glibc because of some bad experience with it...

Any way, I've installed the same version of postgresql on another computer
and copied the data directory the, and, surprise, no errors...

I've managed to dump my data, and now, off I go to update to postgres
7.4...

Thanks for your help, although this remains a mistery...

On Fri, 21 Nov 2003, Tom Lane wrote:

> Andrei Ivanov <andrei(dot)ivanov(at)ines(dot)ro> writes:
> > template1=> select pg_get_viewdef('pg_tables'::regclass);
> > ERROR: nodeRead: did not find '}' at end of plan node
> > template1=> select pg_get_viewdef('pg_indexes'::regclass);
> > ERROR: nodeRead: did not find '}' at end of plan node
>
> Bizarre. I just checked, and this query will not result in trying to
> open any views other than the one named by the get_viewdef argument.
> So it sure appears that reading any view rule will cause the failure.
>
> > I really can't think of anything else that could have brought postgresql
> > in this state... (could on minor update to glibc cause this ? I don't
> > remember if updated glibc before or after the corruption...)
>
> Doesn't seem real likely to me either ... but we seem to have eliminated
> all the other possibilities. The data in the database doesn't appear to
> be corrupted, and you already tried reinstalling Postgres, so the
> executable file isn't corrupted, which seems to leave the libraries.
> Are you in a position to back out the glibc update and see what happens?
>
> regards, tom lane
>

On Fri, 21 Nov 2003, Tom Lane wrote:

> Andrei Ivanov <andrei(dot)ivanov(at)ines(dot)ro> writes:
> > I'd rather not downgrade glibc because of some bad experience with it...
>
> > Any way, I've installed the same version of postgresql on another computer
> > and copied the data directory the, and, surprise, no errors...
>
> Sounds like the smoking gun to me, for sure.
>
> Exactly which glibc version was this?

glibc-2.3.2-r9 (from gentoo, changelog snip: Update CVS branch to 20031115)

I've managed to import the dump in the new 7.4 version, and everything
seems to work smoothly... again, thanks for the help.