Re: Dropped database still shows in the database list.

girish R G peetle wrote:
> Dropped database 'RestoreDB' is still being listed in pg_database.
> Any idea why this behavior is seen ? Also I don't see any open connections to this database.
> pg_stat_activity has no rows for this database.
>
> * postgres=# create database ResotreDB;
> CREATE DATABASE

This is a typo, and it should be "RestoreDB", right?
When reporting problems of this kind, please always copy and paste from the screen,
rather than typing it by hand. Often typos cause problems like that, and additional
typos in the report make it harder to figure out the cause of the problem.

> * postgres=# select oid, * from pg_database where datname like 'RestoreDB';
> oid | datname | datdba | encoding | datcollate | datctype | datistemplate | datallowconn |
> datconnlimit | datlastsysoid | datfrozenxid | datminmxid | dattablespace | datacl
> --------+-----------+--------+----------+-------------+-------------+---------------+--------------+--
> ------------+---------------+--------------+------------+---------------+--------
> 177109 | RestoreDB | 10 | 6 | en_US.UTF-8 | en_US.UTF-8 | f | t |
> -1 | 12891 | 1800 | 1 | 1663 |
> (1 row)

But this is a different database!

The database you created above (modulo typo) is "restoredb", all in lower case,
because PostgreSQL folds unquoted strings to lower case.

So now, if you run "SELECT datname FROM pg_database;", you should have both
"restoredb" and "RestoreDB" in there. Is that so?

If you want mixed case, use double quotes:
CREATE DATABASE "RestoreDB";
That statement should fail because the database already exists.

> * postgres=# drop database RestoreDB;
> DROP DATABASE

You dropped "restoredb".

> * postgres=# select oid, * from pg_database where datname like 'RestoreDB';
> oid | datname | datdba | encoding | datcollate | datctype | datistemplate | datallowconn |
> datconnlimit | datlastsysoid | datfrozenxid | datminmxid | dattablespace | datacl
> --------+-----------+--------+----------+-------------+-------------+---------------+--------------+--
> ------------+---------------+--------------+------------+---------------+--------
> 177109 | RestoreDB | 10 | 6 | en_US.UTF-8 | en_US.UTF-8 | f | t |
> -1 | 12891 | 1800 | 1 | 1663 |

... and "RestoreDB" is still around.

I urge you to only use lower case for all database objects in PostgreSQL.
It will save you a lot of trouble.

Yours,
Laurenz Albe