Thread: 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.<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.  

David Ford <firefighterblu3@gmail.com> writes:
> i encountered this when trying to do a pg_dumpall in preparation for moving
> from 8.0 to 8.1.

Would it be too far off to guess that you're running Gentoo?  This looks
like ye olde problem with that hierarchical-queries patch that they push
on people.  Either adding or removing the patch breaks your database :-(.
Since your rewrite rule looks standard, I'm guessing that you rebuilt
and added the patch at some point after initially building your database.
Reconfigure 8.0 the way you originally built it and you should be OK ...
unless you have views that you created after changing ...
        regards, tom lane

you are correct and we've gone down that road.  i build my db with 8.0.1, and i've upgraded through to 8.0.5 as time went by and i never noticed an issue with it until i went to bump to 8.1 today.

i've tried with and without the hier patch and neither seems to work.  i have a few options left to try but i might be reduced to stitching together COPYs and system tables from an older backup.




--
Gay/Firefighter/EMT/Geek in 06451, USA
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.

David Ford <firefighterblu3@gmail.com> writes:
> i've tried with and without the hier patch and neither seems to work.

I was afraid of that: you've got some views created with the patch
installed and some with it not installed.

> i have a few options left to try but i might be reduced to stitching together
> COPYs and system tables from an older backup.

It should be possible to manually correct the bogus entries.  Basically
what the patch does is add a field to the QUERY structure, whose name I
forget but comparing working and non-working ev_action strings should
expose it.  (It's probably near the end of the string.)  If you delete
the field (text ":fieldname <> ") from the non-working ev_action strings
you should be able to get to a state where all the views will dump in a
non-hier build.
        regards, tom lane

that's ok, i found a working solution with 8.0.5 +pg-hier (8.0.1 wouldn't work + or -pg-hier), so i got things dumped, upgraded, and running.  thank you to you and the guys on #postgresql very much for your assistance :)

david




--
Gay/Firefighter/EMT/Geek in 06451, USA
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.