Lists: | pgsql-general |
---|
From: | Tham Shiming <shiming(at)misatravel(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Dropping a database that does not exist |
Date: | 2006-02-10 09:42:40 |
Message-ID: | 43EC6010.70909@misatravel.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Tham Shiming <shiming(at)misatravel(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Dropping a database that does not exist |
Date: | 2006-02-10 15:11:19 |
Message-ID: | 12908.1139584279@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Tham Shiming <shiming(at)misatravel(dot)com> writes:
> I've been getting duplicate databases within my server.
What exactly makes you think you have that?
regards, tom lane
From: | "Uwe C(dot) Schroeder" <uwe(at)oss4u(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Cc: | Tham Shiming <shiming(at)misatravel(dot)com> |
Subject: | Re: Dropping a database that does not exist |
Date: | 2006-02-10 17:37:41 |
Message-ID: | 200602100937.41314.uwe@oss4u.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Uwe C(dot) Schroeder" <uwe(at)oss4u(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org, Tham Shiming <shiming(at)misatravel(dot)com> |
Subject: | Re: Dropping a database that does not exist |
Date: | 2006-02-10 20:24:35 |
Message-ID: | 1187.1139603075@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
"Uwe C. Schroeder" <uwe(at)oss4u(dot)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
From: | Tham Shiming <shiming(at)misatravel(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | "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-13 01:50:08 |
Message-ID: | 43EFE5D0.4060607@misatravel.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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(at)oss4u(dot)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
>
>
>
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Tham Shiming <shiming(at)misatravel(dot)com> |
Cc: | "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-13 14:35:36 |
Message-ID: | 28288.1139841336@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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?
regards, tom lane