system view corrupted, i get "unexpected right parenthesis" for many system tables. - Mailing list pgsql-sql

i encountered this when trying to do a pg_dumpall in preparation for moving from 8.0 to 8.1.<br /><br />Jaymale ~ #
pg_dump-U postgres -d administration > psql-dbs.jan2006.dump<br />pg_dump: SQL command failed<br />pg_dump: Error
messagefrom server: ERROR:  unexpected right parenthesis <br />pg_dump: The command was: SELECT tableoid, oid, nspname,
(selectusename from pg_user where nspowner = usesysid) as usename, nspacl FROM pg_namespace<br /><br /><br
/>administration=#\dt<br />ERROR:  unexpected right parenthesis <br />administration=# \d<br />ERROR:  unexpected right
parenthesis<br/>administration=# select * from pg_tables;<br />ERROR:  unexpected right parenthesis<br /><br
/>narrowingit down to:<br /><br />(normal output below, broke follows) <br /><br />postgres=# \d pg_user;<br
/>*********QUERY **********<br />SELECT c.oid,<br />  n.nspname,<br />  c.relname<br />FROM pg_catalog.pg_class c<br
/>    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace<br />WHERE c.relname ~ '^pg_user$'<br />      AND
pg_catalog.pg_table_is_visible(c.oid)<br/>ORDER BY 2, 3;<br />**************************<br /><br />********* QUERY
**********<br/>SELECT relhasindex, relkind, relchecks, reltriggers, relhasrules,<br />relhasoids , reltablespace <br
/>FROMpg_catalog.pg_class WHERE oid = '10320'<br />**************************<br /><br />********* QUERY **********<br
/>SELECTa.attname,<br />  pg_catalog.format_type(a.atttypid, a.atttypmod),<br />  (SELECT
substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)<br />   FROM pg_catalog.pg_attrdef d<br />   WHERE
d.adrelid= a.attrelid AND d.adnum = a.attnum AND a.atthasdef),<br />  a.attnotnull, a.attnum<br />FROM
pg_catalog.pg_attributea<br />WHERE a.attrelid = '10320' AND a.attnum > 0 AND NOT a.attisdropped<br />ORDER BY
a.attnum<br/>**************************<br /><br />********* QUERY **********<br />SELECT
pg_catalog.pg_get_viewdef('10320'::pg_catalog.oid,true)<br />************************** <br /><br />********* QUERY
**********<br/>SELECT r.rulename, trim(trailing ';' from pg_catalog.pg_get_ruledef(r.oid, true))<br />FROM
pg_catalog.pg_rewriter<br />WHERE r.ev_class = '10320' AND r.rulename != '_RETURN' ORDER BY 1<br />
**************************<br/><br />     View "pg_catalog.pg_user"<br />   Column    |  Type   | Modifiers<br
/>-------------+---------+-----------<br/> usename     | name    |<br /> usesysid    | oid     |<br /> usecreatedb |
boolean| <br /> usesuper    | boolean |<br /> usecatupd   | boolean |<br /> passwd      | text    |<br /> valuntil    |
abstime|<br /> useconfig   | text[]  |<br />View definition:<br /> 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<br />   FROM pg_shadow;<br /><br />+++++++++++++++++++++++++++++++++++++++++++++++++++++++++<br
/><br/>broken one:<br /><br />administration-# \d pg_user;<br />********* QUERY **********<br />SELECT c.oid,<br /> 
n.nspname,<br/>  c.relname<br /> FROM pg_catalog.pg_class c<br />     LEFT JOIN pg_catalog.pg_namespace n ON n.oid =
c.relnamespace<br/>WHERE pg_catalog.pg_table_is_visible(c.oid)<br />      AND c.relname ~ '^pg_user$'<br />ORDER BY 2,
3;<br/>************************** <br /><br />********* QUERY **********<br />SELECT relhasindex, relkind, relchecks,
reltriggers,relhasrules,<br />relhasoids , reltablespace<br />FROM pg_catalog.pg_class WHERE oid = '16762'<br
/>**************************<br/><br />********* QUERY ********** <br />SELECT a.attname,<br /> 
pg_catalog.format_type(a.atttypid,a.atttypmod),<br />  (SELECT substring(d.adsrc for 128) FROM pg_catalog.pg_attrdef
d<br/>   WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),<br />   a.attnotnull, a.attnum<br />FROM
pg_catalog.pg_attributea<br />WHERE a.attrelid = '16762' AND a.attnum > 0 AND NOT a.attisdropped<br />ORDER BY
a.attnum<br/>**************************<br /><br />********* QUERY **********<br />SELECT
pg_catalog.pg_get_viewdef('16762'::pg_catalog.oid,true) <br />**************************<br /><br />ERROR:  unexpected
rightparenthesis<br /><br />the view rule for this is:<br /><br />++++++++++++++++++++++++++++++++++++++++++++++++++<br
/><br/>administration=# select ev_action from pg_rewrite where oid=16764; <br />[...]<br />({QUERY :commandType 1
:querySource0 :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
:inFromClfalse :requiredPerms 0 :checkAsUser 1} {RTE :alias {ALIAS :aliasname *NEW* :colnames <>} :eref {ALIAS
:aliasname*NEW* :colnames ("usename" "usesysid" "usecreatedb" "usesuper" "usecatupd" "passwd" "valuntil" "useconfig")}
:rtekind0 :relid 16762 :inh false :inFromCl false :requiredPerms 0 :checkAsUser 1} {RTE :alias <> :eref {ALIAS
:aliasnamepg_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
:resno1 :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
:resorigcol4 :resjunk false} :expr {VAR :varno 3 :varattno 4 :vartype 16 :vartypmod -1 :varlevelsup 0 :varnoold 3
:varoattno4}} {TARGETENTRY :resdom {RESDOM :resno 5 :restype 16 :restypmod -1 :resname usecatupd :ressortgroupref 0
:resorigtbl1260 :resorigcol 5 :resjunk false} :expr {VAR :varno 3 :varattno 5 :vartype 16 :vartypmod -1 :varlevelsup 0
:varnoold3 :varoattno 5}} {TARGETENTRY :resdom {RESDOM :resno 6 :restype 25 :restypmod -1 :resname passwd
:ressortgroupref0 :resorigtbl 0 :resorigcol 0 :resjunk false} :expr {CONST :consttype 25 :constlen -1 :constbyval false
:constisnullfalse :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
:restype1009 :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 <>}) <br />(1 row)<br /><br /><br />can anyone help me restore this
correctfunction of this view rule in the backend for pg_rewrite?<br /><br />thank you,<br />david<br clear="all" /><br
/>--<br />It's the ideals of Linux and Open Source that are amazing, it embodies what WE want, not what is marketed <br
/><br/>Once you lose the greatest of all things, it's the memories you cherish for all time.  He was the best, I could
havebeen better.