Sorry, I wasn't being clear. For the sample output from pgAdmin below, I
just did a select * from pg_database, then just typed out the 2 columns
that I thought would be relevant.
OK, here's the output for select ctid, xmin, xmax, datname from pg_database;
ctid(tid) xmin(xid) xmax(xid) datname(name)
(0,1) 2 0 postgres
(0,2) 2 0 postgres8
(0,4) 746707934 0 db1
(0,5) 2 2213800494 db2 (pgAdmin says this DB does not
exist)
(0,6) 2 0 template1
(0,7) 2 0 template0
(0,8) 2 2214815770 db3 (pgAdmin says this DB does not
exist)
(0,9) 746707976 0 db4
(0,10) 2 2213853192 db5 (repeat)
(0,11) 746867758 0 db6
(0,13) 2 0 db5 (repeat)
(0,14) 2 0 db7
(0,16) 144476800 0 db8
(0,17) 144476893 0 db9
(0,19) 730724276 0 db10
(0,20) 741565079 0 db11
(actual output, i just changed the names of the databases)
As a form of clarification, for db2 and db3, we saw the duplicate
databases and tried to drop the duplicate by simply executing DROP
DATABASE db2 and DROP DATABASE db3. The command executed without errors,
but there is still one copy of db2 and db3 left. We tried to drop this
copy as well, but PostgreSQL then told us both databases did not exist.
Regards,
Shiming
Tom Lane wrote:
"Dave Page" <dpage@xxxxxxxxxxxxxxxxxx> writes:
Tham Shiming <shiming@xxxxxxxxxxxxxx> writes:
OK, checking pg_shadow, the usesysid for each entry is unique.
pg_database, however, showed the duplicate databases. A
short sample output from pgAdmin.
datname datdba
db1 101
db1 101
db2 102
db3 103
db3 103
Does anyone know what the underlying query is that pgadmin uses for
this display?
pgAdmin wouldn't display anything like that unless the user entered the
query themselves, or did a 'view data' on pg_database (in which case it
would just be a select *, possibly with a user entered WHERE restriction
or an ORDER BY).
Hmm. If it's not a join, the only explanation that comes to mind for
phantom rows is transaction ID wraparound. Could we see the output of
select ctid, xmin, xmax, datname from pg_database;
regards, tom lane
