Обсуждение: psql -l gives bad output
Hi, I am running PostgreSQL 7.1.3 on Solaris 8.
The output of psql -l seems wrong. In the following, their are 2
databases named "pay-test" and one named "/usr/local/pgsql/bi". I can
dropdb one of the pay-tests but not the other, and I can't dropdb the
/usr/local/pgsql/bi database. I can't access these databases either.
vacuumdb -a gives me an error too, when attempting to access these
databases.
# psql -U postgres -l
List of databases
Database | Owner
---------------------------------+----------
pay | postgres
pay-test | postgres
pay-test
/usr/local/pgsql/bi | postgres
template0 | postgres
template1 | postgres
(5 rows)
Does anyone have any ideas?
Thanks,
Jeff
> ---------------------------------+----------
> pay | postgres
> pay-test | postgres
> pay-test
> /usr/local/pgsql/bi | postgres
It looks to me like this 'second paytest' database is actually named
'pay-test\n/usr/local/pgsql/bi' , possibly created by trying to create a
database without having a closing quote:
User# createdb 'pay-test
(and then beginning to type some /usr/local/pgsql/bin command.)
The command dropdb doesn't seem (at least for me) to work the exact same
way, so the only way I could recover is the following:
(First create the problem)
bash-2.05$ createdb 'blah
> this is a test'
CREATE DATABASE
bash-2.05$ psql -l
List of databases
Name | Owner
---------------------+----------
blah
this is a test | pg721tmp
quickview | pg721tmp
template0 | pg721tmp
template1 | pg721tmp
(4 rows)
template1=# update pg_database set datname='foo' where datname like 'blah%';
UPDATE 1
bash-2.05$ psql -l
List of databases
Name | Owner
-----------+----------
foo | pg721tmp
quickview | pg721tmp
template0 | pg721tmp
template1 | pg721tmp
(4 rows)
-ron
Ron Snyder <snyder@roguewave.com> writes:
> It looks to me like this 'second paytest' database is actually named
> 'pay-test\n/usr/local/pgsql/bi' , possibly created by trying to create a
> database without having a closing quote:
I concur.
> The command dropdb doesn't seem (at least for me) to work the exact same
> way,
It works for me in current sources:
$ createdb 'ab
> cd'
CREATE DATABASE
$ psql -l
List of databases
Name | Owner | Encoding
------------+----------+-----------
ab
cd | postgres | SQL_ASCII
regression | postgres | SQL_ASCII
template0 | postgres | SQL_ASCII
template1 | postgres | SQL_ASCII
(4 rows)
$ dropdb 'ab
> cd'
DROP DATABASE
$ psql -l
List of databases
Name | Owner | Encoding
------------+----------+-----------
regression | postgres | SQL_ASCII
template0 | postgres | SQL_ASCII
template1 | postgres | SQL_ASCII
(3 rows)
But I recall a number of rounds of bug-fixes concerning quoting in
the pgsql shell scripts, so I'd not be surprised in the least to hear
that pre-7.2 PG releases get this wrong. Or for that matter, we might
still have some problems in this line on some platforms with oddball
shells. If you find that dropdb messes up with weird names in 7.2,
please send details about the test case and your platform...
regards, tom lane
> > The command dropdb doesn't seem (at least for me) to work
> the exact same
> > way,
>
> It works for me in current sources:
Current from cvs, or current 7.2.1?
bash-2.05$ createdb `ab
> cd
bash-2.05$ createdb 'ab
> cd'
CREATE DATABASE
bash-2.05$ dropdb 'ab
> cd'
ERROR: DROP DATABASE: database "ab cd" does not exist
dropdb: database removal failed
bash-2.05$ psql template1 -c 'select version();'
version
-------------------------------------------------------------
PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.96
(1 row)
bash-2.05$ which psql dropdb
/usr/local/pgsql-7.2.1/bin/psql
/usr/local/pgsql-7.2.1/bin/dropdb
It's definitely not a problem for me, but it does seem odd that it works for
you and not for me. (I suppose since all I'm doing is sitting here sort of
watching nypd blue, I'll see if I can figure it out.) :)
-ron
>
> $ createdb 'ab
> > cd'
> CREATE DATABASE
> $ psql -l
> List of databases
> Name | Owner | Encoding
> ------------+----------+-----------
> ab
> cd | postgres | SQL_ASCII
> regression | postgres | SQL_ASCII
> template0 | postgres | SQL_ASCII
> template1 | postgres | SQL_ASCII
> (4 rows)
>
> $ dropdb 'ab
> > cd'
> DROP DATABASE
> $ psql -l
> List of databases
> Name | Owner | Encoding
> ------------+----------+-----------
> regression | postgres | SQL_ASCII
> template0 | postgres | SQL_ASCII
> template1 | postgres | SQL_ASCII
> (3 rows)
>
>
> But I recall a number of rounds of bug-fixes concerning quoting in
> the pgsql shell scripts, so I'd not be surprised in the least to hear
> that pre-7.2 PG releases get this wrong. Or for that matter, we might
> still have some problems in this line on some platforms with oddball
> shells. If you find that dropdb messes up with weird names in 7.2,
> please send details about the test case and your platform...
>
> regards, tom lane
>
> >
> > But I recall a number of rounds of bug-fixes concerning quoting in
> > the pgsql shell scripts, so I'd not be surprised in the
> least to hear
> > that pre-7.2 PG releases get this wrong. Or for that
> matter, we might
> > still have some problems in this line on some platforms with oddball
> > shells. If you find that dropdb messes up with weird names in 7.2,
> > please send details about the test case and your platform...
RH 7.2; Postgres 7.2.1
bash-2.05$ BLAH='ab
> cd'
bash-2.05$ echo $BLAH
ab cd
bash-2.05$ echo "$BLAH"
ab
cd
[root@vault bin]# diff -c dropdb.orig dropdb
*** dropdb.orig Tue May 21 22:40:33 2002
--- dropdb Tue May 21 22:40:46 2002
***************
*** 131,137 ****
fi
! dbname=`echo $dbname | sed 's/\"/\\\"/g'`
${PATHNAME}psql $PSQLOPT -d template1 -c "DROP DATABASE \"$dbname\""
if [ "$?" -ne 0 ]; then
--- 131,137 ----
fi
! dbname=`echo "$dbname" | sed 's/\"/\\\"/g'`
${PATHNAME}psql $PSQLOPT -d template1 -c "DROP DATABASE \"$dbname\""
if [ "$?" -ne 0 ]; then
Ron Snyder <snyder@roguewave.com> writes:
>> It works for me in current sources:
> Current from cvs, or current 7.2.1?
CVS tip. Looking at the logs, it appears that Bruce made a further
round of quoting fixups on 12-Apr.
regards, tom lane
Tom Lane wrote: > Ron Snyder <snyder@roguewave.com> writes: > >> It works for me in current sources: > > > Current from cvs, or current 7.2.1? > > CVS tip. Looking at the logs, it appears that Bruce made a further > round of quoting fixups on 12-Apr. Yes, and fixing whitespace in dbnames was the purpose. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026