Re: duplicate templates, wrong users

On Thu, Dec 06, 2001 at 01:32:56PM -0500, Yanek Korff wrote:
> > Here's your problem: user1 has the same userid as pgsql. this
> > used to happen in 7.0, I think. Is that what you're running?
> I'm sure it's what I used to be running. I am running this now:
> postgresql-7.1.2_2
> according to FreeBSD ports.
>
> > As to fixing it - have you created a lot of db objects (tables, views,
> > functions, etc.) as user1 that need to be kept, or is this a test db?
> There is data in a variety of tables belonging to user1 in the db1 database
> that CANNOT be lost. I have tried using pg_dump and pg_dumpall with little
> to no success:
> yanek:~> pg_dump db1 > dump
> getDatabase(): SELECT failed. Explanation from backend: 'ERROR: More than
> one tuple returned by a subselect used as an expression.'.
>
> > I _think_ you can get away with connecting to the db as a superuser,
> > and updating the pg_shadow table so that user1 has a unique
> > id. This means that everything user1 used to own will be owned by
> > pgsql. Now you can drop and recreate the db that user1 is supposed to
> > own.
> Anyone can give confirmation on this procedure? I really cannot afford to
> lose this data, though I have done an offline tar -zcvf and will again
> before I do any dropping. So... just update the 'usesysid' in pg_shadow
> (what about pg_user?), drop the database (which is now only owned by
> pgsql?), and re-create that DB as user1?

No, it'll remove the duplicate id: there's really only one of each database,
it just looks like two when you use psql -l because psql does a join to the
pg_user table to get the owner name. Here:

reedstrm(at)wallace:~$ psql -E -l
********* QUERY *********
SELECT pg_database.datname as "Database",
pg_user.usename as "Owner",
pg_encoding_to_char(pg_database.encoding) as "Encoding"FROM pg_database, pg_user
WHERE pg_database.datdba = pg_user.usesysid

UNION

SELECT pg_database.datname as "Database",
NULL as "Owner",
pg_encoding_to_char(pg_database.encoding) as "Encoding"FROM pg_database
WHERE pg_database.datdba NOT IN (SELECT usesysid FROM pg_user)
ORDER BY "Database"
*************************

List of databases
Database | Owner | Encoding
------------+----------+-----------
bioinfo | reedstrm | SQL_ASCII
ifs_test | reedstrm | SQL_ASCII
nsf | reedstrm | SQL_ASCII
reedstrm | reedstrm | SQL_ASCII
repository | reedstrm | SQL_ASCII
template0 | postgres | SQL_ASCII
template1 | postgres | SQL_ASCII
test | reedstrm | SQL_ASCII
(8 rows)

So, the first part there will return two rows for each database owned by
'72' (in your case), one for each user with id = 72. You can verify this
by doing a select * from pg_database; You'll see one of each database.

By breaking that duplication, you should then be able to pg_dump the
database. You'll then have to go through the schema by hand, and find
everything that's supposed to be owned by user1 (which will dump as
being owned by pgsql) and fix it. There's no automatic way to do this,
since your system beleives that 'user1' and 'pgsql' are both '72' and
that's how the items are actually tracked: by usesysid.

I just tested this by artificially creating a user with duplicate usesysid
(I had to UPDATE pg_shadow to do it : the CREATE USER command is guarded
against this) and got exactly the symptoms you report:

reedstrm(at)wallace:~$ pg_dump ifs_test >ifs_test.sql
reedstrm(at)wallace:~$ psql template1 -c "update pg_shadow set usesysid=32 where usename='user1';"
UPDATE 1
reedstrm(at)wallace:~$ pg_dump ifs_test > ifs_test.sql
getDatabase(): SELECT failed. Explanation from backend: 'ERROR: More than one tuple returned by a subselect used as an expression.
'.
reedstrm(at)wallace:~$ psql template1 -c "update pg_shadow set usesysid=33 where usename='user1';"
UPDATE 1
reedstrm(at)wallace:~$ pg_dump ifs_test > ifs_test.sql
reedstrm(at)wallace:~$

Ross
--
Ross Reedstrom, Ph.D. reedstrm(at)rice(dot)edu
Executive Director phone: 713-348-6166
Gulf Coast Consortium for Bioinformatics fax: 713-348-6182
Rice University MS-39
Houston, TX 77005