I ran the select as instructed, and this is the output:
madisoncounty=# select ctid, usename, usesysid from pg_shadow;
ctid | usename | usesysid
-------+---------------+----------
(0,1) | postgres | 1
(0,2) | postgres | 1
(0,5) | madisoncounty | 100
(3 rows)
I then did:
delete from pg_shadow where ctid='(0,2)';
DELETE 1
I had already reindexed the tables. Now when I run the command, I get this:
pg_dump: [archiver (db)] connection to database "madisoncounty" failed:
FATAL: user "postgres" does not exist
Also, I have been connecting to the db as the postgres user. Now I am
unable to connect to the database with the postgres user, or with the
actual user for the site.
Tom Lane wrote:
Adam Dear <adear@xxxxxxxx> writes:
pg_dump: query to obtain list of schemas failed: ERROR: more than one
row returned by a subquery used as an expression
The pg_dump command is from version 7.4.11. This is the version of
postgres I am running on my old server.
Just FYI, when doing a version upgrade it's considered better practice
to dump with the newer version's pg_dump, if possible. Sometimes the
newer pg_dump knows things that will smooth the migration. However,
that's not related to your immediate problem.
A look at the pg_dump source code shows that the subquery it's
complaining about is trying to fetch the name of each view's owner.
Apparently you've got multiple rows in pg_shadow with the same usesysid
--- or perhaps just one row but the index on usesysid is corrupt and is
returning it more than once. Try
select ctid, usename, usesysid from pg_shadow;
and see if you see any duplicate usesysid's. If so, try removing the
extra copies (use WHERE ctid = '...' if there isn't any other difference
between the rows). If you see no dups in a query like this, then the
index is at fault --- try reindexing.
regards, tom lane
