Thread: Connect without specifying a database?

Hi All,

Quick question:  Does PGSQL provide a mechanism by which to connect to
the server without specifying a database?  I saw a thread back in 2005
that said "no" (http://archives.postgresql.org//pgsql-interfaces/2005-02/msg00031.php
), but I was curious as to whether that's changed.  Basically I need
to be able to dynamically determine what databases exist etc before
performing certain actions in my application.

Thanks,
Michael

On Sat, Apr 11, 2009 at 11:30:00AM -0400, lists@mgreg.com wrote:
> Quick question:  Does PGSQL provide a mechanism by which to connect to
> the server without specifying a database?  I saw a thread back in 2005
> that said "no"
> (http://archives.postgresql.org//pgsql-interfaces/2005-02/msg00031.php ),
> but I was curious as to whether that's changed.  Basically I need  to be
> able to dynamically determine what databases exist etc before  performing
> certain actions in my application.

What's wrong with "template1" as in the above thread?

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

On 11/04/2009 16:30, lists@mgreg.com wrote:

> Quick question:  Does PGSQL provide a mechanism by which to connect to
> the server without specifying a database?  I saw a thread back in 2005
> that said "no"
> (http://archives.postgresql.org//pgsql-interfaces/2005-02/msg00031.php),
> but I was curious as to whether that's changed.  Basically I need to be
> able to dynamically determine what databases exist etc before performing
> certain actions in my application.

The answer is still "no". :-)

The usual thing it to connect to the "postgres" database (or to
"template1" in older versions of PG), and then you can issue queries to
see what's there. You're pretty much guaranteed that one of those
databases will exist (they're created by initdb), unless whoever
installed the server did something strange.

Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

"lists@mgreg.com" <lists@mgreg.com> writes:
> Hrm...Ok, well, for the record, I'm moving some systems from MySQL to
> Postgres and am in the process of getting familiar with it.  I was
> hoping there were some higher level management items like "show
> databases", and "show tables", etc in Postgres as well.

There are, although we don't spell the commands like that.  This is not
relevant to the question of whether it makes sense to connect without
being connected to a specific database.  It doesn't, because the
catalogs that you have to inspect to find out anything are
database-specific.

            regards, tom lane

On Sat, Apr 11, 2009 at 11:49:25AM -0400, lists@mgreg.com wrote:
> I was
> hoping there were some higher level management items like "show
> databases", and "show tables", etc in Postgres as well.

The standard way would be to use the "information_schema", it's in the
SQL standard and supported by more than just one database engine.

> I also didn't
> want to depend on there being a table there that I needed to "get in
> the door" with, so to speak.  I'm guessing no one in the community
> sees this addition as desirable (or feasible)?

Anything can be done, it's just getting people enthusiastic enough to do
it!

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

On Saturday 11 April 2009 9:10:33 am lists@mgreg.com wrote:
> On Apr 11, 2009, at 11:56 AM, Tom Lane wrote:
> > There are, although we don't spell the commands like that.  This is
> > not
> > relevant to the question of whether it makes sense to connect without
> > being connected to a specific database.  It doesn't, because the
> > catalogs that you have to inspect to find out anything are
> > database-specific.
> >
> >             regards, tom lane
>
> I'm sorry, I'm confused by your reply.  I may have said "table" when I
> meant "database".  I believe it absolutely *does* make sense to
> connect without specifying a database first.  What if you hit that
> edge case where there are in fact no databases?  I suppose you could
> always run "createdb" just in case before you do anything else, but
> that seems counterintuitive.
>
> What do you mean when you say the "catalogs...are database-
> specific" ?  If I'm understanding what you're saying, my whole point
> is that I don't want to be tied to a database to do any kind of
> querying about the PG engine itself.  Does that make sense?
>
> Best,
> Michael

Might try looking at the pg_database file in ~/DATADIR/global. It lists the
databases in the cluster.

--
Adrian Klaver
aklaver@comcast.net

"lists@mgreg.com" <lists@mgreg.com> writes:
> What do you mean when you say the "catalogs...are database-
> specific" ?  If I'm understanding what you're saying, my whole point
> is that I don't want to be tied to a database to do any kind of
> querying about the PG engine itself.  Does that make sense?

No, it does not.  This is perhaps an implementation quirk of Postgres's,
but it does have some advantages and we're not interested in giving them
up just because it confuses MySQLers ;-).  The main advantage is that
having separate catalogs in each database is more robust (no matter how
badly database A gets messed up, database B will be okay) and reduces
contention for catalog access.

There are a few catalogs that are visible in all databases of an
installation (pg_database itself being the most obvious one) but they
are not sufficient to support an operational backend.  So you have to
connect to some database even to query those catalogs.

We could have a convention that there is some database that you connect
to only for the purpose of inspecting pg_database, but there doesn't
seem a whole lot of point in trying to enforce that.  The standard
databases serve well enough.

            regards, tom lane

lists@mgreg.com wrote:
> I'm sorry, I'm confused by your reply.  I may have said "table" when I
> meant "database".  I believe it absolutely *does* make sense to
> connect without specifying a database first.  What if you hit that
> edge case where there are in fact no databases?  I suppose you could
> always run "createdb" just in case before you do anything else, but
> that seems counterintuitive.

that 'edge case' is one in which postgres isn't running.  you have to
run initdb to initialize the database cluster before you can start the
postmaster.  initdb creates template0, template1, and, in 8.x and newer,
postgres databases

the postgres database administrator account can do...

    $ psql -l

to list all databases, or...

    $ psql
    ....
    postgres=# \l

or, in sql, while connected to {template1 | template0 | postgres | ... }

    select datname,... from pg_catalog.pg_database;

[ forgot to respond to this bit... ]

"lists@mgreg.com" <lists@mgreg.com> writes:
> What if you hit that edge case where there are in fact no databases?

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.

> I suppose you could
> always run "createdb" just in case before you do anything else, but
> that seems counterintuitive.

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

On Saturday 11 April 2009 10:15:44 am 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?
>
> 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.  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.
>
> 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".

I believe that assumes the 'postgres' database is present. The problem is fairly
simple no database == no connection.

>
>
> Thanks,
> Michael



--
Adrian Klaver
aklaver@comcast.net

On Apr 11, 2009, at 10:15 AM, lists@mgreg.com wrote:
> So, how does needing to connect to a database before querying about
> existing databases make any sense?

Well, you have to connect to the database server, no matter what, in
order to check on the existence of a database (unless you are doing it
by groveling around in the data directories).  The fact that you have
to supply a database as part of that connection seems unremarkable.

Suppose the documentation said something along these lines:

"All connections are to a specific database.  For operations in which
a user-created database would be inappropriate (such as checking for
the existence of a user-created database), the database 'root' is
created by initdb as a known default for such operations."

That would seem to be utterly uncontroversial and normal, yes?

OK s/root/template1/, and you're set. :)

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/

Sam Mason <sam@samason.me.uk> writes:
> 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.

It's possible that that was true way back in Berkeley prehistory; there
is no one around the project now that would remember (unless maybe Elein
does).  But the key points here are that critical catalogs like pg_class
and pg_proc are per-database, which is a good thing for quite a number
of reasons, and PG is sufficiently catalog-driven that it's literally
impossible for the engine to do anything useful without having a set of
those catalogs available.  (Offhand, the only user-visible functionality
I can think of that isn't catalog-dependent is the GUC parameters, ie
SET/SHOW; and even within that there are some individual parameters
that can't meaningfully be set without catalog access.)

            regards, tom lane

On Apr 11, 2009, at 6:10 PM, lists@mgreg.com wrote:

> What do you mean when you say the "catalogs...are database-
> specific" ?  If I'm understanding what you're saying, my whole point
> is that I don't want to be tied to a database to do any kind of
> querying about the PG engine itself.  Does that make sense?

Look at it from the other side; You have a DBMS and you want to store
information about what databases and which users are available, who
can and can't connect, etc. It makes sense to store that in a
database, right?

To request that information you need to connect to the database
server. Considering that information is stored in a database, having
to specify that database to connect to makes sense. Whether that's a
named database (with a documented fixed name of course, in this case
'template1' or 'postgres') or an anonymous database doesn't make much
difference. You'll still have to specify several other connection
parameters (host & port at least), so why not also a valid user (quite
desirable from a security point of view) and a database name?

It may not make as much sense from a user point of view, but it makes
a lot of sense from a database point of view.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,49e1ca98129741055947028!