Re: pg 8.0.1-r3 killls pgadmin3 and phppgadmin - Mailing list pgsql-general

> You might try comparing the results of
>   select ev_action from pg_rewrite where ev_class = 'pg_user'::regclass;
> from the two builds.
>
>             regards, tom lane
>

    Well...

    I'll spare your eyes and tell you right away that the results are
identical... they're at the bottom of the email.

    I tried recompiling postgres 8.0.1-r2 with :
CFLAGS="-O2 -pipe -march=pentium3"
    which is quite conservative (I have a Pentium-M, this is my dev laptop).

$ gcc --version
gcc (GCC) 3.3.5  (Gentoo Linux 3.3.5-r1, ssp-3.3.2-3, pie-8.7.7.1)

    And I still get (in 8.0.1-r2) :

test=> SELECT pdb.datname AS datname, pu.usename AS datowner,
pg_encoding_to_char(encoding) AS datencoding,
test-> (SELECT description FROM pg_description pd WHERE pdb.oid=pd.objoid)
AS datcomment,
test-> (SELECT spcname FROM pg_catalog.pg_tablespace pt WHERE
pt.oid=pdb.dattablespace) AS tablespace
test-> FROM pg_database pdb, pg_user pu
test-> WHERE pdb.datdba = pu.usesysid AND NOT pdb.datistemplate
test-> ORDER BY pdb.datname;
ERREUR:  unexpected right parenthesis

    Let's try something else :

postgresql $ createuser -P peufeu
Entrez le mot de passe pour le nouvel utilisateur :
Entrez-le de nouveau :
Le nouvel utilisateur a-t'il le droit de créer des bases de données ?
(y/n) o
(note that I have to type "o" and not "y" as it's in French... although it
still displays y/n, "y" means "no" ! it bit me !)
Le nouvel utilisateur a-t'il le droit de créer des utilisateurs ? (y/n) n
Mot de passe :
CREATE USER

peufeu $ createdb
Mot de passe :
CREATE DATABASE
peufeu@nyuu peufeu $ psql
Mot de passe :
Bienvenue dans psql 8.0.1, l'interface interactive de PostgreSQL.

Tapez:  \copyright pour les termes de distribution
         \h pour l'aide-mémoire sur les commandes SQL
         \? pour l'aide-mémoire sur les commandes internes
         \g ou terminez avec un point-virgule pour exécuter une requête
         \q pour quitter

peufeu=> SELECT pdb.datname AS datname, pu.usename AS datowner,
pg_encoding_to_char(encoding) AS datencoding,
peufeu-> (SELECT description FROM pg_description pd WHERE
pdb.oid=pd.objoid) AS datcomment,
peufeu-> (SELECT spcname FROM pg_catalog.pg_tablespace pt WHERE
pt.oid=pdb.dattablespace) AS tablespace
peufeu-> FROM pg_database pdb, pg_user pu
peufeu-> WHERE pdb.datdba = pu.usesysid AND NOT pdb.datistemplate
peufeu-> ORDER BY pdb.datname;
ERREUR:  unexpected right parenthesis

    ka-blam.

    I checked there was no invisible UTF-8 character inside the query...
there isn't, everything's normal...
    I cut bits of the query, trying to get to the bug, and to my surprise, I
got :

peufeu=> SELECT pdb.datname FROM pg_database pdb, pg_user pu;
ERREUR:  unexpected right parenthesis

    Which is interesting, as there is no parenthesis in this query ;)
    I'll copypaste the rest of the session :

peufeu=> SELECT pdb.datname FROM pg_database pdb, pg_user pux;
ERREUR:  unexpected right parenthesis
peufeu=> SELECT pdb.datname FROM pg_database pdb, pg_user pu;
ERREUR:  unexpected right parenthesis
peufeu=> SELECT pdb.datname FROM pg_database pdb, pg_user;
ERREUR:  unexpected right parenthesis
peufeu=> SELECT foo.datname FROM pg_database foo, pg_user;
ERREUR:  unexpected right parenthesis
peufeu=> SELECT foo.datname FROM pg_database foo;
   datname
-----------
  test
  peufeu
  template1
  template0
(4 lignes)

peufeu=> SELECT foo.datname FROM pg_user, pg_database foo;
ERREUR:  unexpected right parenthesis
peufeu=> SELECT * FROM pg_user LIMIT 1;
ERREUR:  unexpected right parenthesis
peufeu=> SELECT * FROM pg_user LIMIT 1;
pg_user
peufeu=> SELECT * FROM pg_user LIMIT 1;
ERREUR:  unexpected right parenthesis
peufeu=> SELECT * FROM pg_user;
ERREUR:  unexpected right parenthesis
peufeu=> SELECT 1 FROM pg_user;
ERREUR:  unexpected right parenthesis

    Seems I have a cursed table.
    I login as postgres :

$ psql -U postgres peufeu
Mot de passe :
Bienvenue dans psql 8.0.1, l'interface interactive de PostgreSQL.

Tapez:  \copyright pour les termes de distribution
         \h pour l'aide-mémoire sur les commandes SQL
         \? pour l'aide-mémoire sur les commandes internes
         \g ou terminez avec un point-virgule pour exécuter une requête
         \q pour quitter

peufeu=# SELECT 1 FROM pg_user;
ERREUR:  unexpected right parenthesis
peufeu=# ANALYZE VERBOSE pg_user;
ERREUR:  unexpected right parenthesis
peufeu=# ANALYZE VERBOSE "pg_user";
ERREUR:  unexpected right parenthesis

peufeu@nyuu peufeu $ echo "ANALYZE VERBOSE" | psql -U postgres peufeu 2>log
Mot de passe :
ANALYZE
peufeu@nyuu peufeu $ grep pg_catalog log
INFO:  Analyse de "pg_catalog.pg_shadow"
INFO:  Analyse de "pg_catalog.pg_opclass"
INFO:  Analyse de "pg_catalog.pg_am"
INFO:  Analyse de "pg_catalog.pg_amop"
etc...

peufeu@nyuu peufeu $ grep user log
peufeu@nyuu peufeu $

    I'm totally perplexed. Have you got any idea ?
    I'm itching to do an initdb...

8.0.0 :
  ({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 :resjunkfalse} :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 <>})

8.0.1-r2:
  ({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 :resjunkfalse} :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 <>})