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

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(at)consultorpc(dot)com

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(at)consultorpc(dot)com

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

drop database " skynet";

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com

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/

Adrian Klaver <adrian(dot)klaver(at)gmail(dot)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

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

> Adrian Klaver <adrian(dot)klaver(at)gmail(dot)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(at)consultorpc(dot)com

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(at)consultorpc(dot)com

Can you connect to it?

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com

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(at)consultorpc(dot)com
>
> Can you connect to it?
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)gmail(dot)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(at)consultorpc(dot)com

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(at)consultorpc(dot)com
>>
>> Can you connect to it?
>>
>> --
>> Adrian Klaver
>> adrian(dot)klaver(at)gmail(dot)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(dot)klaver(at)gmail(dot)com

On Fri, Jan 8, 2010 at 9:58 AM, Adrian Klaver <adrian(dot)klaver(at)gmail(dot)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(dot)klaver(at)gmail(dot)com
>

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com

On Fri, Jan 8, 2010 at 17:44, Sam Mason <sam(at)samason(dot)me(dot)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/

Rikard Bosnjakovic <rikard(dot)bosnjakovic(at)gmail(dot)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

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(at)depesz(dot)com / aim:depeszhdl / skype:depesz_hdl / gg:6749007

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(dot)klaver(at)gmail(dot)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 with one 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 client names that i don't want to share here.

Best Regards,
---

Fernando Marcelo
www.consultorpc.com
fernando(at)consultorpc(dot)com

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(at)depesz(dot)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(at)consultorpc(dot)com

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(at)depesz(dot)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(at)depesz(dot)com / aim:depeszhdl / skype:depesz_hdl / gg:6749007

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(at)depesz(dot)com / aim:depeszhdl / skype:depesz_hdl / gg:6749007

Hello,

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

Regards,
---

Fernando Marcelo
www.consultorpc.com
fernando(at)consultorpc(dot)com

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(at)depesz(dot)com / aim:depeszhdl / skype:depesz_hdl / gg:6749007

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(at)depesz(dot)com / aim:depeszhdl /
> > skype:depesz_hdl / gg:6749007
>
> Hello,
>
> Same result: http://pastebin.ca/1746714
>
> Regards,
> ---
>
> Fernando Marcelo
> www.consultorpc.com
> fernando(at)consultorpc(dot)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(dot)klaver(at)gmail(dot)com