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.
- [SQL] system view corrupted, i get "unexpected right paren... David Ford
