Skip site navigation (1) Skip section navigation (2)

Re: Dropping a database that does not exist

From: Tham Shiming <shiming(at)misatravel(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Dave Page <dpage(at)vale-housing(dot)co(dot)uk>, "Uwe C(dot) Schroeder" <uwe(at)oss4u(dot)com>, pgsql-general(at)postgresql(dot)org
Subject: Re: Dropping a database that does not exist
Date: 2006-02-14 01:32:34
Message-ID: 43F13332.3010600@misatravel.com (view raw or flat)
Thread:
Lists: pgsql-general
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
>
>
>   


In response to

Responses

pgsql-general by date

Next:From: Russell SmithDate: 2006-02-14 02:55:54
Subject: Object ownership in a new database
Previous:From: Eric ChapdelaineDate: 2006-02-13 22:04:09
Subject: Unhandled Exception - Executing Samples under win32

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group