Mysterious empty database name?

Azlin Rahim <azlin.rahim@gmail.com> writes:

In our database list, there is one 'mysterious' database with a blank name.
We don't know how it got there.

Your mail client has done you no favors as far as preserving the
formatting of the SELECT output, but it looks to me like the name of the
weird database is probably not blank but rather contains some control
characters (perhaps a carriage return?). Depending on how old your psql
is, that could result in wacky formatting, which is what it looks like
you've got here. Another theory is that it's an encoding problem:
non-ASCII database names are troublesome if you don't use the same
encoding in each database.

I'd suggest trying the SELECT under some other output format, perhaps
\pset format unaligned, to see if it gets any more readable.

Depending on what the name really is, you might be able to type it as a
double-quoted identifier, in which case ALTER DATABASE RENAME would
work to fix it. If all else fails, you could try getting the OID
of the database and then
UPDATE pg_database SET datname = 'something_sane' WHERE oid = nnn;
as superuser should fix it. (If it's pre-8.1 PG, you might need another
ALTER DATABASE RENAME to be sure subsidiary files are updated.)

regards, tom lane

Excerpts from Azlin Rahim's message of mar may 25 18:23:13 -0400 2010:

In our database list, there is one 'mysterious' database with a blank name.
We don't know how it got there.

Maybe somebody SQL-injected you and created it.

Below is the output of pg_database. Does anyone have any idea why this is
happening? Any way to remove the 'mysterious' database safely.

I'd investigate a bit more what's inside, before deleting it. Maybe the
name has only whitespace or control chars. You can use double quotes to
refer to such names. For example, this is a name a space and a
backspace:

=# create database " ^?";
CREATE DATABASE

You can see the ASCII chars that make up the name with something like this:

=# select datname, ascii(substring(datname, generate_series(1, length(datname)), 1)) from pg_database;

\x7F | 32
\x7F | 127

Now if somebody messed with an Unicode control char, I don't know what
to tell you.

--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

I tried to do another SELECT on pg_database with the OID and here's
what I get. Seems like there is no OID for the weird database. I'm
stumped.

Btw, our Postgresql version is 8.1.11.

# select oid,* from pg_database;
oid | datname | datdba | encoding |
datistemplate | datallowconn | datconnlimit | datlastsysoid |
datvacuumxid | datfrozenxid | dattablespace | datconfig |
datacl
--------+---------------------------+--------+----------+---------------+--------------+--------------+---------------+--------------+--------------+---------------+-----------+------------------------
10793 | postgres | 10 | 6 | f
| t | -1 | 10792 | 38260524 |
3259485997 | 1663 | |
138208 | jboss-ktj-2007-09-02 | 10 | 6 | f
| t | -1 | 10792 | 38260579 |
3259486052 | 1663 | |
134606 | jboss-ktj | 10 | 6 | f
| t | -1 | 10792 | 38261114 |
3259486587 | 1663 | |
208645 | jboss-ktj-test-2010-03-28 | 10 | 6 | f
| t | -1 | 10792 | 38261842 |
3259487315 | 1663 | |
185623 | jboss-warestore | 10 | 6 | f
| t | -1 | 10792 | 38262572 |
3259488045 | 1663 | |
1 | template1 | 10 | 6 | t
| t | -1 | 10792 | 38262629 |
3259488102 | 1663 | | {postgres=CT/postgres}
10792 | template0 | 10 | 6 | t
| f | -1 | 10792 | 499 |
499 | 1663 | | {postgres=CT/postgres}
245497 | jboss-ktj-test | 10 | 6 | f
| t | -1 | 10792 | 38262684 |
3259488157 | 1663 | |
| 10 | 6 | f | t |
-1 | 10792 | 499 | 499 | 1663 |
|
(9 rows)

On Wed, May 26, 2010 at 6:40 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Azlin Rahim <azlin.rahim@gmail.com> writes:

In our database list, there is one 'mysterious' database with a blank name.
We don't know how it got there.

Your mail client has done you no favors as far as preserving the
formatting of the SELECT output, but it looks to me like the name of the
weird database is probably not blank but rather contains some control
characters (perhaps a carriage return?).  Depending on how old your psql
is, that could result in wacky formatting, which is what it looks like
you've got here.  Another theory is that it's an encoding problem:
non-ASCII database names are troublesome if you don't use the same
encoding in each database.

I'd suggest trying the SELECT under some other output format, perhaps
\pset format unaligned, to see if it gets any more readable.

Depending on what the name really is, you might be able to type it as a
double-quoted identifier, in which case ALTER DATABASE RENAME would
work to fix it.  If all else fails, you could try getting the OID
of the database and then
       UPDATE pg_database SET datname = 'something_sane' WHERE oid = nnn;
as superuser should fix it.  (If it's pre-8.1 PG, you might need another
ALTER DATABASE RENAME to be sure subsidiary files are updated.)

                       regards, tom lane

--

Azlin

Azlin Rahim <azlin.rahim@gmail.com> writes:

I tried to do another SELECT on pg_database with the OID and here's
what I get. Seems like there is no OID for the weird database. I'm
stumped.

You didn't absorb the point about funny formatting. This makes it
look even more probable that the database name contains a carriage
return. See Alvaro's reply for one way to look closer.

regards, tom lane

Thanks Tom, Alvaro. It was indeed caused by the carriage return. I've
renamed the weird db and the problem is resolved.

On Wed, May 26, 2010 at 7:14 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Azlin Rahim <azlin.rahim@gmail.com> writes:

I tried to do another SELECT on pg_database with the OID and here's
what I get. Seems like there is no OID for the weird database. I'm
stumped.

You didn't absorb the point about funny formatting.  This makes it
look even more probable that the database name contains a carriage
return.  See Alvaro's reply for one way to look closer.

                       regards, tom lane

--

Azlin