Postgres Pro
Downloads
Home   >   mailing lists

Thread: psql -l gives bad output

psql -l gives bad output

From
Jeff Beck
Date:
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

Re: psql -l gives bad output

From
Ron Snyder
Date:
> ---------------------------------+----------
>  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

Re: psql -l gives bad output

From
Tom Lane
Date:
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

Re: psql -l gives bad output

From
Ron Snyder
Date:
> > 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
>

Re: psql -l gives bad output

From
Ron Snyder
Date:
> >
> > 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

Re: psql -l gives bad output

From
Tom Lane
Date:
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

Re: psql -l gives bad output

From
Bruce Momjian
Date:
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