Обсуждение: 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@consultorpc.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@consultorpc.com You have a space at the beginning of the name. Try: drop database " skynet"; -- Adrian Klaver adrian.klaver@gmail.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.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
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
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
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
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
On Fri, Jan 8, 2010 at 9:58 AM, Adrian Klaver <adrian.klaver@gmail.com> wrote:
Oops should be "is not the same as the one below"
On 01/08/2010 09:53 AM, Fernando Morgenstern wrote:
Actually what is strange is that your previous listing :is not the same as the one above:
postgres=# select '"' || datname || '"' from pg_database;
?column?
-------------
"template1"
"template0"
"t1"
"skynet"
Oops should be "is not the same as the one below"
In particular the presence of postgres,t1 and pgpool.
postgres=# select '"' || datname || '"' from pg_database;
?column?
-------------
"template1"
"template0"
"postgres"
"t1"
"pgpool"
"skynet"
Are you sure which cluster you are pointing at and whether the psql version matches the server version?
--
Adrian Klaver
adrian.klaver@gmail.com
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/
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
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
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
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
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
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
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
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