X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 725949DC81C for ; Mon, 9 Jan 2006 15:33:27 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 07410-03 for ; Mon, 9 Jan 2006 15:33:27 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from zproxy.gmail.com (zproxy.gmail.com [64.233.162.206]) by postgresql.org (Postfix) with ESMTP id 097B79DC86A for ; Mon, 9 Jan 2006 15:33:23 -0400 (AST) Received: by zproxy.gmail.com with SMTP id 4so4647012nzn for ; Mon, 09 Jan 2006 11:33:25 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:mime-version:content-type; b=ilzEv0rd9cvN8K5qwYcF1POWoL0AnF+ggiy45B2rBLBDYo1Ul2KbO3qN+fo5xRdZOfe9Kd4copuJY6eUUuZsVMDb1w8E/IJcxhp7Y008yaqSbiV9qYLnh4R3KYmYSZGpCl9vg56Lpm3/1nc0nEa927tpXmUIAeoH6u8nQjubS/g= Received: by 10.65.237.13 with SMTP id o13mr2547934qbr; Mon, 09 Jan 2006 11:33:24 -0800 (PST) Received: by 10.65.249.18 with HTTP; Mon, 9 Jan 2006 11:33:24 -0800 (PST) Message-ID: <523e55a00601091133u5769dedcy73a77a04ee85f917@mail.gmail.com> Date: Mon, 9 Jan 2006 14:33:24 -0500 From: David Ford To: pgsql-sql@postgresql.org Subject: system view corrupted, i get "unexpected right parenthesis" for many system tables. MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_17459_1095254.1136835204050" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.946 required=5 tests=[HTML_10_20=0.945, HTML_MESSAGE=0.001] X-Spam-Score: 0.946 X-Spam-Level: X-Archive-Number: 200601/35 X-Sequence-Number: 23824 ------=_Part_17459_1095254.1136835204050 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline 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 =3D usesysid) as usename, nspacl FROM pg_namesp= ace administration=3D# \dt ERROR: unexpected right parenthesis administration=3D# \d ERROR: unexpected right parenthesis administration=3D# select * from pg_tables; ERROR: unexpected right parenthesis narrowing it down to: (normal output below, broke follows) postgres=3D# \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 =3D 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 =3D '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 =3D a.attrelid AND d.adnum =3D a.attnum AND a.atthasdef)= , a.attnotnull, a.attnum FROM pg_catalog.pg_attribute a WHERE a.attrelid =3D '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 =3D '10320' AND r.rulename !=3D '_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 =3D 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 =3D '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 =3D a.attrelid AND d.adnum =3D a.attnum AND a.atthasdef)= , a.attnotnull, a.attnum FROM pg_catalog.pg_attribute a WHERE a.attrelid =3D '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=3D# select ev_action from pg_rewrite where oid=3D16764; [...] ({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 2= 3 :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 :resdo= m {RESDOM :resno 3 :restype 16 :restypmod -1 :resname usecreatedb :ressortgroupref 0 :resorigtbl 1260 :resorigcol 3 :resjunk false} :expr {VA= R :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 :varattn= o 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 {VA= R :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 <> :limitCoun= t <> :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. ------=_Part_17459_1095254.1136835204050 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline 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 usenam= e from pg_user where nspowner =3D usesysid) as usename, nspacl FROM pg_name= space


administration=3D# \dt
ERROR:  unexpected right pa= renthesis
administration=3D# \d
ERROR:  unexpected right parenthesis
a= dministration=3D# select * from pg_tables;
ERROR:  unexpected right= parenthesis

narrowing it down to:

(normal output below, brok= e follows)

postgres=3D# \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 = =3D 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, reltrigge= rs, relhasrules,
relhasoids , reltablespace
FROM pg_catalog.pg_class WHERE oid =3D '10320'
*********************= *****

********* QUERY **********
SELECT a.attname,
  pg_c= atalog.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 =3D a.attrelid AND d.adnum =3D a.attnum AND = a.atthasdef),
  a.attnotnull, a.attnum
FROM pg_catalog.pg_attrib= ute a
WHERE a.attrelid =3D '10320' AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnu= m
**************************

********* 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 =3D '10320' AND r.rulename !=3D '_RETURN' ORDER BY 1<= br> **************************

     View "pg_ca= talog.pg_user"
   Column    |  Type&n= bsp;  | Modifiers
-------------+---------+-----------
 usen= ame     | name    |
 usesysid&nb= sp;   | oid     |
 usecreatedb | bool= ean |
 usesuper    | boolean |
 usecatupd &n= bsp; | boolean |
 passwd      | text =    |
 valuntil    | abstime |
 use= config   | text[]  |
View definition:
 SELECT pg_= shadow.usename, pg_shadow.usesysid, pg_shadow.usecreatedb, pg_shadow.usesup= er, pg_shadow.usecatupd, '********'::text AS passwd, pg_shadow.valuntil, pg= _shadow.useconfig
   FROM pg_shadow;

+++++++++++++++++++++++++++++++++++= ++++++++++++++++++++++

broken one:

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

********* QUERY **********
SELECT relhasindex, relkind, relcheck= s, reltriggers, relhasrules,
relhasoids , reltablespace
FROM pg_catal= og.pg_class WHERE oid =3D '16762'
**************************

****= ***** QUERY **********
SELECT a.attname,
  pg_catalog.format_type(a.atttypid, a.atttyp= mod),
  (SELECT substring(d.adsrc for 128) FROM pg_catalog.pg_attrd= ef d
   WHERE d.adrelid =3D a.attrelid AND d.adnum =3D a.attnu= m AND a.atthasdef),
  a.attnotnull, a.attnum
FROM pg_catalog.pg_attribute a
WHERE a.= attrelid =3D '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 parenth= esis

the view rule for this is:

+++++++++++++++++++++++++++++= +++++++++++++++++++++

administration=3D# select ev_action from pg_re= write where oid=3D16764;
[...]
({QUERY :commandType 1 :querySource 0 :canSetTag true :utility= Stmt <> :resultRelation 0 :into <> :hasAggs false :hasSubLinks = false :rtable ({RTE :alias {ALIAS :aliasname *OLD* :colnames <>} :ere= f {ALIAS :aliasname *OLD* :colnames ("usename" "usesysid&quo= t; "usecreatedb" "usesuper" "usecatupd" "= ;passwd" "valuntil" "useconfig")} :rtekind 0 :reli= d 16762 :inh false :inFromCl false :requiredPerms 0 :checkAsUser 1} {RTE :a= lias {ALIAS :aliasname *NEW* :colnames <>} :eref {ALIAS :aliasname *N= EW* :colnames ("usename" "usesysid" "usecreatedb&q= uot; "usesuper" "usecatupd" "passwd" "va= luntil" "useconfig")} :rtekind 0 :relid 16762 :inh false :in= FromCl false :requiredPerms 0 :checkAsUser 1} {RTE :alias <> :eref {A= LIAS :aliasname pg_shadow :colnames ("usename" "usesysid&quo= t; "usecreatedb" "usesuper" "usecatupd" "= ;passwd" "valuntil" "useconfig")} :rtekind 0 :reli= d 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 :re= stypmod -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 :r= esno 2 :restype 23 :restypmod -1 :resname usesysid :ressortgroupref 0 :reso= rigtbl 1260 :resorigcol 2 :resjunk false} :expr {VAR :varno 3 :varattno 2 := vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 3 :varoattno 2}} {TARGETE= NTRY :resdom {RESDOM :resno 3 :restype 16 :restypmod -1 :resname usecreated= b :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 :restyp= mod -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 :resn= o 5 :restype 16 :restypmod -1 :resname usecatupd :ressortgroupref 0 :resori= gtbl 1260 :resorigcol 5 :resjunk false} :expr {VAR :varno 3 :varattno 5 :va= rtype 16 :vartypmod -1 :varlevelsup 0 :varnoold 3 :varoattno 5}} {TARGETENT= RY :resdom {RESDOM :resno 6 :restype 25 :restypmod -1 :resname passwd :ress= ortgroupref 0 :resorigtbl 0 :resorigcol 0 :resjunk false} :expr {CONST :con= sttype 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 :resorigt= bl 1260 :resorigcol 7 :resjunk false} :expr {VAR :varno 3 :varattno 7 :vart= ype 702 :vartypmod -1 :varlevelsup 0 :varnoold 3 :varoattno 7}} {TARGETENTR= Y :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 <> :distinctClaus= e <> :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 che= rish for all time.  He was the best, I could have been better. ------=_Part_17459_1095254.1136835204050--