Re: Connect without specifying a database?

On Sat, Apr 11, 2009 at 01:15:44PM -0400, lists@mgreg.com wrote:
>
> On Apr 11, 2009, at 12:56 PM, Tom Lane wrote:
> >
> >There is no such edge case.  DROP DATABASE has to be issued while
> >connected to some database, and it won't let you drop the DB you're
> >connected to.
> >
> >And CREATE DATABASE has to be issued while connected to some database,
> >so createdb still has to have a default database to connect to.  There
> >really is no state in Postgres corresponding to "connected but not
> >connected to any particular database".
> >
> >It does all hang together.  You will need to lose a lot of MySQL
> >preconceptions along the way, I fear.
> >
> >            regards, tom lane
>
>
> I think our first issue is semantics and our second is paradigm.
> Hopefully I'm simply misunderstanding what you're saying, but what
> sense does it make to have to connect to an unrelated DB in order to
> query about others?

Because most of the time you don't need to do this; the user will have
specified the parameters (or they will be known some other way, i.e.
defaults) and you'll just connect like you do in every other database by
specifying a connection string.

> Basically, I have some applications that will simply use PG as a
> backend.  That application needs to ask the engine manager (whatever
> that means in in postgres speak) and see if relevant databases already
> exist.  If they don't then it needs to create them.

This is a bad precedent to set; is somebody accidentally points it at
the wrong place it should complain there's nothing there and fail to
start.  Creating things should normally only be with explicit consent
from the user.

> So, how does
> needing to connect to a database before querying about existing
> databases make any sense?  MySQL aside, it seems an extra constraint/
> step for naught.

Yes it does, but it's rarely a problem in practise.

> Perhaps I asked the wrong question in the beginning -- I do
> apologize.  Maybe I should have asked for an external application that
> has the ability to talk to the PG engine.  I believe John R. Pierce
> provided me with what I need in his last post -- that of listing DBs
> via a "psql -l".

As Adrian said, all psql -l does is to connect to the "postgres"
database and run the following SQL:

  SELECT d.datname as "Name", r.rolname as "Owner",
      pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding"
  FROM pg_catalog.pg_database d
    JOIN pg_catalog.pg_roles r ON d.datdba = r.oid
  ORDER BY 1;

As others have said; the design of PG is such that it's built to assume
you're always connected to exactly one database.  I'd guess this is an
artifact from a long time ago when PG didn't have multiple databases.

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