system view corrupted, i get "unexpected right parenthesis" for many system tables.

i encountered this when trying to do a pg_dumpall in preparation for moving
from 8.0 to 8.1.

Jaymale ~ # pg_dump -U postgres -d administration > psql-dbs.jan2006.dump
pg_dump: SQL command failed
pg_dump: Error message from server: ERROR: unexpected right parenthesis
pg_dump: The command was: SELECT tableoid, oid, nspname, (select usename
from pg_user where nspowner = usesysid) as usename, nspacl FROM pg_namespace

administration=# \dt
ERROR: unexpected right parenthesis
administration=# \d
ERROR: unexpected right parenthesis
administration=# select * from pg_tables;
ERROR: unexpected right parenthesis

narrowing it down to:

(normal output below, broke follows)

postgres=# \d pg_user;
********* QUERY **********
SELECT c.oid,
n.nspname,
c.relname
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname ~ '^pg_user$'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 2, 3;
**************************

********* QUERY **********
SELECT relhasindex, relkind, relchecks, reltriggers, relhasrules,
relhasoids , reltablespace
FROM pg_catalog.pg_class WHERE oid = '10320'
**************************

********* QUERY **********
SELECT a.attname,
pg_catalog.format_type(a.atttypid, a.atttypmod),
(SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
FROM pg_catalog.pg_attrdef d
WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),
a.attnotnull, a.attnum
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = '10320' AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum
**************************

********* QUERY **********
SELECT pg_catalog.pg_get_viewdef('10320'::pg_catalog.oid, true)
**************************

********* QUERY **********
SELECT r.rulename, trim(trailing ';' from pg_catalog.pg_get_ruledef(r.oid,
true))
FROM pg_catalog.pg_rewrite r
WHERE r.ev_class = '10320' AND r.rulename != '_RETURN' ORDER BY 1
**************************

View "pg_catalog.pg_user"
Column | Type | Modifiers
-------------+---------+-----------
usename | name |
usesysid | oid |
usecreatedb | boolean |
usesuper | boolean |
usecatupd | boolean |
passwd | text |
valuntil | abstime |
useconfig | text[] |
View definition:
SELECT pg_shadow.usename, pg_shadow.usesysid, pg_shadow.usecreatedb,
pg_shadow.usesuper, pg_shadow.usecatupd, '********'::text AS passwd,
pg_shadow.valuntil, pg_shadow.useconfig
FROM pg_shadow;

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++

broken one:

administration-# \d pg_user;
********* QUERY **********
SELECT c.oid,
n.nspname,
c.relname
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE pg_catalog.pg_table_is_visible(c.oid)
AND c.relname ~ '^pg_user$'
ORDER BY 2, 3;
**************************

********* QUERY **********
SELECT relhasindex, relkind, relchecks, reltriggers, relhasrules,
relhasoids , reltablespace
FROM pg_catalog.pg_class WHERE oid = '16762'
**************************

********* QUERY **********
SELECT a.attname,
pg_catalog.format_type(a.atttypid, a.atttypmod),
(SELECT substring(d.adsrc for 128) FROM pg_catalog.pg_attrdef d
WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),
a.attnotnull, a.attnum
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = '16762' AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum
**************************

********* QUERY **********
SELECT pg_catalog.pg_get_viewdef('16762'::pg_catalog.oid, true)
**************************

ERROR: unexpected right parenthesis

the view rule for this is:

++++++++++++++++++++++++++++++++++++++++++++++++++

administration=# select ev_action from pg_rewrite where oid=16764;
[...]
({QUERY :commandType 1 :querySource 0 :canSetTag true :utilityStmt <>
:resultRelation 0 :into <> :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 16762 :inh false
:inFromCl false :requiredPerms 0 :checkAsUser 1} {RTE :alias {ALIAS
:aliasname *NEW* :colnames <>} :eref {ALIAS :aliasname *NEW* :colnames
("usename" "usesysid" "usecreatedb" "usesuper" "usecatupd" "passwd"
"valuntil" "useconfig")} :rtekind 0 :relid 16762 :inh false :inFromCl false
:requiredPerms 0 :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 :requiredPerms 2 :checkAsUser 1}) :jointree {FROMEXPR
:fromlist ({RANGETBLREF :rtindex 3}) :quals <>} :rowMarks <> :targetList
({TARGETENTRY :resdom {RESDOM :resno 1 :restype 19 :restypmod -1 :resname
usename :ressortgroupref 0 :resorigtbl 1260 :resorigcol 1 :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 :ressortgroupref 0 :resorigtbl 1260
:resorigcol 2 :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
:ressortgroupref 0 :resorigtbl 1260 :resorigcol 3 :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 :ressortgroupref 0 :resorigtbl 1260 :resorigcol 4
: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 :ressortgroupref 0
:resorigtbl 1260 :resorigcol 5 :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 :ressortgroupref 0 :resorigtbl 0 :resorigcol 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
:ressortgroupref 0 :resorigtbl 1260 :resorigcol 7 :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 :ressortgroupref 0 :resorigtbl 1260
:resorigcol 8 :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 <>})
(1 row)

can anyone help me restore this correct function of this view rule in the
backend for pg_rewrite?

thank you,
david

--
It's the ideals of Linux and Open Source that are amazing, it embodies what
WE want, not what is marketed

Once you lose the greatest of all things, it's the memories you cherish for
all time. He was the best, I could have been better.