Skip site navigation (1)
Skip section navigation (2)
Peripheral Links
Re: Dropping a database that does not exist
> -----Original Message----- > From: pgsql-general-owner(at)postgresql(dot)org > [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Tom Lane > Sent: 13 February 2006 14:36 > To: Tham Shiming > Cc: Uwe C. Schroeder; pgsql-general(at)postgresql(dot)org > Subject: Re: [GENERAL] Dropping a database that does not exist > > Tham Shiming <shiming(at)misatravel(dot)com> 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). Regards, Dave
"Dave Page" <dpage(at)vale-housing(dot)co(dot)uk> writes: >> Tham Shiming <shiming(at)misatravel(dot)com> 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
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(at)vale-housing(dot)co(dot)uk> writes: > >>> Tham Shiming <shiming(at)misatravel(dot)com> 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 > > >
Tham Shiming <shiming(at)misatravel(dot)com> writes: > ctid(tid) xmin(xid) xmax(xid) datname(name) > (0,5) 2 2213800494 db2 (pgAdmin says this DB does not > exist) > (0,8) 2 2214815770 db3 (pgAdmin says this DB does not > exist) > (0,10) 2 2213853192 db5 (repeat) > (0,13) 2 0 db5 (repeat) This does start to look like an XID wraparound problem. What is your current XID counter? (The "NextXID" line from pg_controldata output is close enough.) What *exactly* do you mean by "pgAdmin says this DB does not exist"? regards, tom lane
From pg_controldata Latest checkpoint NextXID: 746940488 Under the database tree that is displayed in pgAdmin, when I click on the database, an error dialog pops up with this message "An error has occured: FATAL: database "db2" does not exist". Even if I disconnect and reconnect, the database tree still shows db2 but if I just click on it, the same error dialog will appear. Regards, Shiming Tom Lane wrote: > Tham Shiming <shiming(at)misatravel(dot)com> writes: > >> ctid(tid) xmin(xid) xmax(xid) datname(name) >> (0,5) 2 2213800494 db2 (pgAdmin says this DB does not >> exist) >> (0,8) 2 2214815770 db3 (pgAdmin says this DB does not >> exist) >> (0,10) 2 2213853192 db5 (repeat) >> (0,13) 2 0 db5 (repeat) >> > > This does start to look like an XID wraparound problem. What is your > current XID counter? (The "NextXID" line from pg_controldata output > is close enough.) What *exactly* do you mean by "pgAdmin says this > DB does not exist"? > > regards, tom lane > > >