Re: pg_dump problem - Mailing list pgsql-general

In article <9np0uc$ei9$1@news.tht.net>, "ljb"
<lbayuk@mindspring.com> wrote:

> lholt@greensand.net wrote:
>>Hi all,
>>    anybody seen this one?
>>------
>>pgsu@dsl-225:~>pg_dump mydb > xxx.dump getDatabase():
>>SELECT failed.  Explanation from backend: 'ERROR: More
>>than one tuple returned by a subselect used as an
>>expression. '.
>>-----------
>>I'm on Slackware 8.0 with postgres 7.1.2 compilied locally
>>and installed. Running AuthPg, mod_perl DBI serving
>>dynamic web pages. Everything appears to work ok except
>>pg_dump.
>>
>>Any Ideas?
>
> Check your list of databases and their owner IDs (select
> datname,datdba from pg_databases). Then check your
> pg_users table, looking for those owner ids (as usesysid).
>  Maybe you have a duplicate user ID in your user table
> (pg_user/pg_shadow) which owns a database. This query
> might help:
>    select datname,datdba,usename from pg_database,pg_user
>    where datdba=usesysid;
> For each database name, there must be exactly one usename.

Thank you, mystery person
here's what your suggested command produced:
template1=# select datname,datdba,usename from pg_database,pg_user
template1-#    where datdba=usesysid;
  datname  | datdba | usename
-----------+--------+---------
 template1 |   1002 | pgsu
 template0 |   1002 | pgsu
 mydb      |   1002 | pgsu
 vgn       |   1002 | pgsu
 pgdemo    |   1002 | pgsu
 uaccess   |   1002 | pgsu
 Pagen2db  |   1002 | pgsu
 w3_prefs  |   1002 | pgsu
 template1 |   1002 | w3
 template0 |   1002 | w3
 mydb      |   1002 | w3
 vgn       |   1002 | w3
 pgdemo    |   1002 | w3
 uaccess   |   1002 | w3
 Pagen2db  |   1002 | w3
 w3_prefs  |   1002 | w3
(16 rows)
-----------------------------
Pretty much screwed, I'd say. Don't have the foggest idea
of how it got that way, but I probably screwed up my
install somewhere. pgsu is my postgres user and w3 my
webmaster. w3 can create db's, but not add users. I think I
ran initdb as pgsu, then created all the other db's as w3,
then loaded them from a pg_dump output taken from a working
postgres 6.4 instalation. Everything worked as expected
until I tried to back stuff up.
    For what it's worth, I have destroyed the whole works and
reloaded one of the smaller db's and now  template 0 &1
are owned by pgsu and pg_dump works fine.

    Thank you very much. I've been running postgres for
several years and have gone thru at least 3 upgrades and
cannot imagine what I did to create that situation.

--lynn