Thread: Table appears on listing but can't drop it

Table appears on listing but can't drop it

From

Fernando Morgenstern

Date:

08 January 2010, 15:39:24

Hello,

I'm running version 8.4.1 and  have a table that appears on listing ( when i run \l ) but i can't drop it. Example:

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |  Collation  |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
  skynet    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 t1        | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
                                                             : postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
                                                             : postgres=CTc/postgres

postgres=# drop database skynet;
ERROR:  database "skynet" does not exist

I intentionally removed other databases name.

Also, i verified that i can run CREATE DATABASE skynet having two databases with the same name.

Any ideas of what causes this problem?

Regards,
---

Fernando Marcelo
www.consultorpc.com
fernando@consultorpc.com

Re: Table appears on listing but can't drop it

From

Adrian Klaver

Date:

08 January 2010, 15:42:06

On 01/08/2010 08:39 AM, Fernando Morgenstern wrote:
> Hello,
>
> I'm running version 8.4.1 and  have a table that appears on listing ( when i run \l ) but i can't drop it. Example:
>
> postgres=# \l
>                                    List of databases
>     Name    |  Owner   | Encoding |  Collation  |    Ctype    |   Access privileges
> -----------+----------+----------+-------------+-------------+-----------------------
>    skynet    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
>   t1        | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
>   template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
>                                                               : postgres=CTc/postgres
>   template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
>                                                               : postgres=CTc/postgres
>
> postgres=# drop database skynet;
> ERROR:  database "skynet" does not exist
>
> I intentionally removed other databases name.
>
> Also, i verified that i can run CREATE DATABASE skynet having two databases with the same name.
>
> Any ideas of what causes this problem?
>
> Regards,
> ---
>
> Fernando Marcelo
> www.consultorpc.com
> fernando@consultorpc.com

You have a space at the beginning of the name. Try:

drop database " skynet";

--
Adrian Klaver
adrian.klaver@gmail.com

Re: Table appears on listing but can't drop it

From

Sam Mason

Date:

08 January 2010, 15:44:29

On Fri, Jan 08, 2010 at 02:39:03PM -0200, Fernando Morgenstern wrote:
> postgres=# \l
>                                   List of databases
>    Name    |  Owner   | Encoding |  Collation  |    Ctype    |   Access privileges
> -----------+----------+----------+-------------+-------------+-----------------------
>   skynet    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
>  t1        | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
>  template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
>                                                              : postgres=CTc/postgres
>  template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
>                                                              : postgres=CTc/postgres

There's an extra space at the beginning of the "skynet" line, could it
be that you created it with special characters in the name?  To check,
I'd try:

  select quote_ident(datname) from pg_database;

--
  Sam  http://samason.me.uk/

Re: Table appears on listing but can't drop it

From

Tom Lane

Date:

08 January 2010, 15:48:48

Adrian Klaver <adrian.klaver@gmail.com> writes:
> On 01/08/2010 08:39 AM, Fernando Morgenstern wrote:
>> Name    |  Owner   | Encoding |  Collation  |    Ctype    |   Access privileges
>> -----------+----------+----------+-------------+-------------+-----------------------
>>  skynet    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
>> t1        | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
>> template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
>> : postgres=CTc/postgres
>> template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
>> : postgres=CTc/postgres

> You have a space at the beginning of the name. Try:
> drop database " skynet";

I'm not sure about that, because the whole row seems to be offset in
his email.  That could be just copy-and-paste sloppiness.  Still,
some sort of non-printing character in the name seems to be indicated,
else he'd not have been able to create another db with name "skynet".

Try something like
    select '"' || datname || '"' from pg_database
to get a clearer view of what's really in there.

            regards, tom lane

Re: Table appears on listing but can't drop it

From

Fernando Morgenstern

Date:

08 January 2010, 15:55:49

Em 08/01/2010, às 14:48, Tom Lane escreveu:

> Adrian Klaver <adrian.klaver@gmail.com> writes:
>> On 01/08/2010 08:39 AM, Fernando Morgenstern wrote:
>>> Name    |  Owner   | Encoding |  Collation  |    Ctype    |   Access privileges
>>> -----------+----------+----------+-------------+-------------+-----------------------
>>> skynet    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
>>> t1        | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
>>> template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
>>> : postgres=CTc/postgres
>>> template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
>>> : postgres=CTc/postgres
>
>> You have a space at the beginning of the name. Try:
>> drop database " skynet";
>
> I'm not sure about that, because the whole row seems to be offset in
> his email.  That could be just copy-and-paste sloppiness.  Still,
> some sort of non-printing character in the name seems to be indicated,
> else he'd not have been able to create another db with name "skynet".
>
> Try something like
>     select '"' || datname || '"' from pg_database
> to get a clearer view of what's really in there.
>
>             regards, tom lane


Hello,

Thanks for your quick answers. The extra space is indeed a copy-and-paste issue. Here it is the select that you
suggested:

postgres=# select '"' || datname || '"' from pg_database;
  ?column?
-------------
 "template1"
 "template0"
 "t1"
 "skynet"


Best Regards,
---

Fernando Marcelo
www.consultorpc.com
fernando@consultorpc.com

Re: Table appears on listing but can't drop it

From

Adrian Klaver

Date:

08 January 2010, 16:49:46

On 01/08/2010 08:55 AM, Fernando Morgenstern wrote:

> Hello,
>
> Thanks for your quick answers. The extra space is indeed a copy-and-paste issue. Here it is the select that you
suggested:
>
> postgres=# select '"' || datname || '"' from pg_database;
>    ?column?
> -------------
>   "template1"
>   "template0"
>   "t1"
>   "skynet"
>
>
> Best Regards,
> ---
>
> Fernando Marcelo
> www.consultorpc.com
> fernando@consultorpc.com

Can you connect to it?

--
Adrian Klaver
adrian.klaver@gmail.com

Re: Table appears on listing but can't drop it

From

Fernando Morgenstern

Date:

08 January 2010, 16:54:02

Em 08/01/2010, às 15:49, Adrian Klaver escreveu:

> On 01/08/2010 08:55 AM, Fernando Morgenstern wrote:
>
>> Hello,
>>
>> Thanks for your quick answers. The extra space is indeed a copy-and-paste issue. Here it is the select that you
suggested:
>>
>> postgres=# select '"' || datname || '"' from pg_database;
>>   ?column?
>> -------------
>>  "template1"
>>  "template0"
>>  "t1"
>>  "skynet"
>>
>>
>> Best Regards,
>> ---
>>
>> Fernando Marcelo
>> www.consultorpc.com
>> fernando@consultorpc.com
>
> Can you connect to it?
>
> --
> Adrian Klaver
> adrian.klaver@gmail.com

No, i get this:

$ psql skynet
psql: FATAL:  database "skynet" does not exist

I can create a database with the same name:

postgres=# create database skynet;
CREATE DATABASE

postgres=# select '"' || datname || '"' from pg_database;
  ?column?
-------------
 "template1"
 "template0"
 "postgres"
 "t1"
 "skynet"
 "skynet"

And drop the newly created database:

postgres=# drop database skynet;
DROP DATABASE
postgres=# select '"' || datname || '"' from pg_database;
  ?column?
-------------
 "template1"
 "template0"
 "postgres"
 "t1"
 "pgpool"
 "skynet"

Strange, isn't it?

Regards,
---

Fernando Marcelo
www.consultorpc.com
fernando@consultorpc.com

Re: Table appears on listing but can't drop it

From

Adrian Klaver

Date:

08 January 2010, 16:58:52

On 01/08/2010 09:53 AM, Fernando Morgenstern wrote:
> Em 08/01/2010, às 15:49, Adrian Klaver escreveu:
>
>> On 01/08/2010 08:55 AM, Fernando Morgenstern wrote:
>>
>>> Hello,
>>>
>>> Thanks for your quick answers. The extra space is indeed a copy-and-paste issue. Here it is the select that you
suggested:
>>>
>>> postgres=# select '"' || datname || '"' from pg_database;
>>>    ?column?
>>> -------------
>>>   "template1"
>>>   "template0"
>>>   "t1"
>>>   "skynet"
>>>
>>>
>>> Best Regards,
>>> ---
>>>
>>> Fernando Marcelo
>>> www.consultorpc.com
>>> fernando@consultorpc.com
>>
>> Can you connect to it?
>>
>> --
>> Adrian Klaver
>> adrian.klaver@gmail.com
>
> No, i get this:
>
> $ psql skynet
> psql: FATAL:  database "skynet" does not exist
>
> I can create a database with the same name:
>
> postgres=# create database skynet;
> CREATE DATABASE
>
> postgres=# select '"' || datname || '"' from pg_database;
>    ?column?
> -------------
>   "template1"
>   "template0"
>   "postgres"
>   "t1"
>   "skynet"
>   "skynet"
>
> And drop the newly created database:
>
> postgres=# drop database skynet;
> DROP DATABASE
> postgres=# select '"' || datname || '"' from pg_database;
>    ?column?
> -------------
>   "template1"
>   "template0"
>   "postgres"
>   "t1"
>   "pgpool"
>   "skynet"
>
> Strange, isn't it?
>
>

Actually what is strange is that your previous listing :
postgres=# select '"' || datname || '"' from pg_database;
    ?column?
  -------------
   "template1"
   "template0"
   "t1"
   "skynet"

is not the same as the one above:

postgres=# select '"' || datname || '"' from pg_database;
   ?column?
-------------
  "template1"
  "template0"
  "postgres"
  "t1"
  "pgpool"
  "skynet"

In particular the presence of postgres,t1 and pgpool.

Are you sure which cluster you are pointing at and whether the psql
version matches the server version?

--
Adrian Klaver
adrian.klaver@gmail.com

Re: Table appears on listing but can't drop it

From

Adrian Klaver

Date:

08 January 2010, 17:49:43

On Fri, Jan 8, 2010 at 9:58 AM, Adrian Klaver <adrian.klaver@gmail.com> wrote:

On 01/08/2010 09:53 AM, Fernando Morgenstern wrote:

Actually what is strange is that your previous listing :

postgres=# select '"' || datname || '"' from pg_database;
?column?
-------------
"template1"
"template0"
"t1"
"skynet"

is not the same as the one above:

Oops should be "is not the same as the one below"

postgres=# select '"' || datname || '"' from pg_database;
?column?
-------------
"template1"
"template0"
"postgres"
"t1"
"pgpool"
"skynet"

In particular the presence of postgres,t1 and pgpool.

Are you sure which cluster you are pointing at and whether the psql version matches the server version?

--
Adrian Klaver
adrian.klaver@gmail.com

--
Adrian Klaver
adrian.klaver@gmail.com

Re: Table appears on listing but can't drop it

From

Rikard Bosnjakovic

Date:

09 January 2010, 10:58:36

On Fri, Jan 8, 2010 at 17:44, Sam Mason <sam@samason.me.uk> wrote:

> There's an extra space at the beginning of the "skynet" line, could it

Is there a particular reason space is allowed in name identifiers? I
see nothing but confusion if a space exists.


--
- Rikard - http://bos.hack.org/cv/

Re: Table appears on listing but can't drop it

From

Tom Lane

Date:

09 January 2010, 16:20:06

Rikard Bosnjakovic <rikard.bosnjakovic@gmail.com> writes:
> Is there a particular reason space is allowed in name identifiers? I
> see nothing but confusion if a space exists.

The SQL standard requires that double-quoted identifiers be allowed to
contain anything.

            regards, tom lane

Re: Table appears on listing but can't drop it

From

hubert depesz lubaczewski

Date:

09 January 2010, 20:40:28

On Fri, Jan 08, 2010 at 02:39:03PM -0200, Fernando Morgenstern wrote:
> postgres=# drop database skynet;
> ERROR:  database "skynet" does not exist

do:

psql -l | hexump -C
and examine output.

Best regards,

depesz

--
Linkedin: http://www.linkedin.com/in/depesz  /  blog: http://www.depesz.com/
jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007

Re: Table appears on listing but can't drop it

From

Fernando Morgenstern

Date:

11 January 2010, 09:55:48

Em 08/01/2010, às 15:58, Adrian Klaver escreveu:

>
> Actually what is strange is that your previous listing :
> postgres=# select '"' || datname || '"' from pg_database;
>   ?column?
> -------------
>  "template1"
>  "template0"
>  "t1"
>  "skynet"
>
> is not the same as the one above:
>
> postgres=# select '"' || datname || '"' from pg_database;
>  ?column?
> -------------
> "template1"
> "template0"
> "postgres"
> "t1"
> "pgpool"
> "skynet"
>
> In particular the presence of postgres,t1 and pgpool.
>
> Are you sure which cluster you are pointing at and whether the psql version matches the server version?
>
> --
> Adrian Klaver
> adrian.klaver@gmail.com

Hi,

The reason for pgpool is that we were using it, but decided to stop due to some problems. At this moment we have pgpool
withone node only. Also, i am connecting directly to postgres in order to verify this problem. 

And the difference between this and previous listing is because i am manually removing databases name as they contain
clientnames that i don't want to share here. 

Best Regards,
---

Fernando Marcelo
www.consultorpc.com
fernando@consultorpc.com

Re: Table appears on listing but can't drop it

From

Fernando Morgenstern

Date:

11 January 2010, 09:59:28

Em 09/01/2010, às 19:40, hubert depesz lubaczewski escreveu:

> On Fri, Jan 08, 2010 at 02:39:03PM -0200, Fernando Morgenstern wrote:
>> postgres=# drop database skynet;
>> ERROR:  database "skynet" does not exist
>
> do:
>
> psql -l | hexump -C
> and examine output.
>
> Best regards,
>
> depesz
>
> --
> Linkedin: http://www.linkedin.com/in/depesz  /  blog: http://www.depesz.com/
> jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007


Hi,

I have done:

# psql -U postgres -p 4000 -l | hexdump -C

And got the two databases: http://pastebin.ca/1746711

I couldn't find any difference here.

Best Regards,
---

Fernando Marcelo
www.consultorpc.com
fernando@consultorpc.com

Re: Table appears on listing but can't drop it

From

hubert depesz lubaczewski

Date:

11 January 2010, 10:05:12

On Mon, Jan 11, 2010 at 08:58:57AM -0200, Fernando Morgenstern wrote:
> Em 09/01/2010, às 19:40, hubert depesz lubaczewski escreveu:
>
> > On Fri, Jan 08, 2010 at 02:39:03PM -0200, Fernando Morgenstern wrote:
> >> postgres=# drop database skynet;
> >> ERROR:  database "skynet" does not exist
> >
> > do:
> >
> > psql -l | hexump -C
> > and examine output.
> >
> > Best regards,
> >
> > depesz
> >
> > --
> > Linkedin: http://www.linkedin.com/in/depesz  /  blog: http://www.depesz.com/
> > jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007
>
>
> Hi,
>
> I have done:
>
> # psql -U postgres -p 4000 -l | hexdump -C
>
> And got the two databases: http://pastebin.ca/1746711
>
> I couldn't find any difference here.

Could you add -qAt to psql options and rerun the command?

Best regards,

depesz

--
Linkedin: http://www.linkedin.com/in/depesz  /  blog: http://www.depesz.com/
jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007

Re: Table appears on listing but can't drop it

From

Fernando Morgenstern

Date:

11 January 2010, 10:08:54

Em 11/01/2010, às 09:04, hubert depesz lubaczewski escreveu:

>> Hi,
>>
>> I have done:
>>
>> # psql -U postgres -p 4000 -l | hexdump -C
>>
>> And got the two databases: http://pastebin.ca/1746711
>>
>> I couldn't find any difference here.
>
> Could you add -qAt to psql options and rerun the command?
>
> Best regards,
>
> depesz
>
> --
> Linkedin: http://www.linkedin.com/in/depesz  /  blog: http://www.depesz.com/
> jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007

Hello,

Same result: http://pastebin.ca/1746714

Regards,
---

Fernando Marcelo
www.consultorpc.com
fernando@consultorpc.com

Re: Table appears on listing but can't drop it

From

hubert depesz lubaczewski

Date:

11 January 2010, 10:24:38

On Mon, Jan 11, 2010 at 09:08:27AM -0200, Fernando Morgenstern wrote:
> Same result: http://pastebin.ca/1746714

It looks like there is problem with system catalogs. I would suggest to
pg_dump what you can, rm $PGDATA, initdb, and load from backup.

Best regards,

depesz

--
Linkedin: http://www.linkedin.com/in/depesz  /  blog: http://www.depesz.com/
jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007

Re: Table appears on listing but can't drop it

From

Adrian Klaver

Date:

11 January 2010, 13:48:30

On Monday 11 January 2010 3:08:27 am Fernando Morgenstern wrote:
> Em 11/01/2010, às 09:04, hubert depesz lubaczewski escreveu:
> >> Hi,
> >>
> >> I have done:
> >>
> >> # psql -U postgres -p 4000 -l | hexdump -C
> >>
> >> And got the two databases: http://pastebin.ca/1746711
> >>
> >> I couldn't find any difference here.
> >
> > Could you add -qAt to psql options and rerun the command?
> >
> > Best regards,
> >
> > depesz
> >
> > --
> > Linkedin: http://www.linkedin.com/in/depesz  /  blog:
> > http://www.depesz.com/ jid/gtalk: depesz@depesz.com / aim:depeszhdl /
> > skype:depesz_hdl / gg:6749007
>
> Hello,
>
> Same result: http://pastebin.ca/1746714
>
> Regards,
> ---
>
> Fernando Marcelo
> www.consultorpc.com
> fernando@consultorpc.com

The only thing I can think of at this point is look at what is in the
pg_database flat file in $PGDATA/pg_global. At a guess the issue is related to
the problems you had with pgpool. You may end up having to do as Hubert
suggested.

--
Adrian Klaver
adrian.klaver@gmail.com