Thread: Dropping a database that does not exist

Dropping a database that does not exist

From
Tham Shiming
Date:
Hi,

I've been getting duplicate databases within my server. Dropping one of
them works, but when I try to drop the other, psql will say that the
database does not exist.

For example:

db1
db1
db2
db3
db4
db4
db5

Running DROP DATABASE db1 for the first time works and I'm left with:

db1
db2
db3
db4
db4
db5

Attempting to run DROP DATABASE db1 again will just give me "FATAL:
database "db1" does not exist"

Same scenario for db4.

I could ignore the error, but because of the duplicate database, I
cannot make a dump of the server.

Any one has any ideas why this is happening and how I can solve it? A
similar thing happened previously, but it was with tables within a
database. The only way we solved that was by dropping the database and
recreating the structure, which wasn't the ideal way I wanted to use.
I've got PostgreSQL 8.0.4 running on SuSE 9.3.

Regards,
Shiming




Re: Dropping a database that does not exist

From
Tom Lane
Date:
Tham Shiming <shiming@misatravel.com> writes:
> I've been getting duplicate databases within my server.

What exactly makes you think you have that?

            regards, tom lane

Re: Dropping a database that does not exist

From
"Uwe C. Schroeder"
Date:
I don't think you have duplicate databases - that would be the first time I
heard that postgreSQL supports that.
Are you sure you didn't create the database with a name that just doesn't
print?
I.e. a simple

createdb test

works.
do another

createdb "test "

(note the space)
works too, but when you list the db's you'll see test twice, although the one
of them is created with a space at the end, so you can't connect to it or
drop it unless you use quotes. Did you use some kind of tool that may have a
done this? Most tools use quotes when creating sql statements, so if you
accidentially added a space in the dialog you end up with a scenario like you
describe.


On Friday 10 February 2006 01:42, Tham Shiming wrote:
> Hi,
>
> I've been getting duplicate databases within my server. Dropping one of
> them works, but when I try to drop the other, psql will say that the
> database does not exist.
>
> For example:
>
> db1
> db1
> db2
> db3
> db4
> db4
> db5
>
> Running DROP DATABASE db1 for the first time works and I'm left with:
>
> db1
> db2
> db3
> db4
> db4
> db5
>
> Attempting to run DROP DATABASE db1 again will just give me "FATAL:
> database "db1" does not exist"
>
> Same scenario for db4.
>
> I could ignore the error, but because of the duplicate database, I
> cannot make a dump of the server.
>
> Any one has any ideas why this is happening and how I can solve it? A
> similar thing happened previously, but it was with tables within a
> database. The only way we solved that was by dropping the database and
> recreating the structure, which wasn't the ideal way I wanted to use.
> I've got PostgreSQL 8.0.4 running on SuSE 9.3.
>
> Regards,
> Shiming
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq

--
    UC

--
Open Source Solutions 4U, LLC    1618 Kelly St
Phone:  +1 707 568 3056        Santa Rosa, CA 95401
Cell:   +1 650 302 2405        United States
Fax:    +1 707 568 6416

Re: Dropping a database that does not exist

From
Tom Lane
Date:
"Uwe C. Schroeder" <uwe@oss4u.com> writes:
> I don't think you have duplicate databases - that would be the first time I
> heard that postgreSQL supports that.
> Are you sure you didn't create the database with a name that just doesn't
> print?

That's one possibility that I was wondering about; another was there
somehow being two entries in pg_shadow for the same usesysid, which'd
result in apparent "duplicate" output from queries that involve joining
pg_database and pg_shadow.  That's why I wanted to know exactly what
was leading to the conclusion that there were duplicate databases.

            regards, tom lane

Re: Dropping a database that does not exist

From
Tham Shiming
Date:
Well, not exactly support. We didn't create the duplicate databases, it
just appear suddenly out of nowhere, which was also odd.

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

Whether there is a significance, I am not really sure. Hopefully, one of
you can help us.

None of our code will create a database, and we will not manually create
one unless requirements forces us to do it.

Regards,
Shiming

Tom Lane wrote:
> "Uwe C. Schroeder" <uwe@oss4u.com> writes:
>
>> I don't think you have duplicate databases - that would be the first time I
>> heard that postgreSQL supports that.
>> Are you sure you didn't create the database with a name that just doesn't
>> print?
>>
>
> That's one possibility that I was wondering about; another was there
> somehow being two entries in pg_shadow for the same usesysid, which'd
> result in apparent "duplicate" output from queries that involve joining
> pg_database and pg_shadow.  That's why I wanted to know exactly what
> was leading to the conclusion that there were duplicate databases.
>
>             regards, tom lane
>
>
>


Re: Dropping a database that does not exist

From
Tom Lane
Date:
Tham Shiming <shiming@misatravel.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?

            regards, tom lane

Re: Dropping a database that does not exist

From
"Dave Page"
Date:

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Tom Lane
> Sent: 13 February 2006 14:36
> To: Tham Shiming
> Cc: Uwe C. Schroeder; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Dropping a database that does not exist
>
> Tham Shiming <shiming@misatravel.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

Re: Dropping a database that does not exist

From
Tom Lane
Date:
"Dave Page" <dpage@vale-housing.co.uk> writes:
>> Tham Shiming <shiming@misatravel.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

Re: Dropping a database that does not exist

From
Tham Shiming
Date:
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@vale-housing.co.uk> writes:
>
>>> Tham Shiming <shiming@misatravel.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
>
>
>


Re: Dropping a database that does not exist

From
Tom Lane
Date:
Tham Shiming <shiming@misatravel.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

Re: Dropping a database that does not exist

From
Tham Shiming
Date:
 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@misatravel.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
>
>
>