3

A more precise version of my question might be, does the fact that PostgreSQL requires a database connection (as opposed to some sort of generic database cluster connection) follow directly from some basic design decisions that could be understood by a novice database user?

I am one such novice database user; I don't have a good understanding of database internals. The SQL database I'm most familiar with is SQLite (although I wouldn't say I'm very familiar with how SQLite works either) and I also use PostgreSQL occasionally.

Something that I find conceptually distressing about PostgreSQL is that it requires you to connect to a specific database in order to execute commands even if the commands in question are database agnostic. For example, even if I all I want to do is to print a list of the databases in a database cluster (i.e. psql -l) I'm still required to connect to a specific database within that cluster; why?

Another case which I find somewhat more upsetting is that some operations require a connection to one database in order to perform operations on another, such as database restoration with the pg_restore utility. From the pg_restore man page:

-C, --create

    Create the database before restoring into it. If --clean is also
    specified, drop and recreate the target database before connecting to it.

    When this option is used, the database named with -d is used only to
    issue the initial DROP DATABASE and CREATE DATABASE commands. All data
    is restored into the database name that appears in the archive.

Apparently the connection database does not matter if the -C flag is used, but does matter if the -C flag is not used. This just strikes me as a needlessly confusing and roundabout way of doing things.

  • 1
    I am not aware of the considerations behind that design choice, but I would guess it's about SQL standard compliance and keeping complexity in connection handling at a reasonable level. I assume you know about the maintenance database named "postgres", which can be used for db-agnostic tasks or operations on other database objects? dba.stackexchange.com/a/68087/3684 – Erwin Brandstetter Aug 21 '15 at 17:29
  • @ErwinBrandstetter Thanks for the comment. Yes, I am aware of the postgres maintenance database. I just don't understand why such a thing is necessary; it seems like a crutch. Shouldn't it be possible to connect to the database cluster directly in order to perform operations on the cluster itself (e.g. adding or removing databases)? – igal Aug 21 '15 at 18:48
  • I would assume it's simpler to handle. You can't switch databases for connections, so you'll just connect to one in the beginning and that's it. This way there is no need to handle the special case when you don't want any database to be used. But I also don't have any real knowledge about why this was done. – Sami Kuhmonen Aug 21 '15 at 20:08
2

I don't think there's a fundamental limitation there, and it's something I'd like to see addressed. I agree with you that it's a bit ugly.

That said, the convention to just use the postgres database as a maintenance database is so strong that the real world impact is negligible.


PostgreSQL has shared catalogs, things like pg_authid and pg_database. It's already possible to start a backend or background worker that connects to no particular database by passing InvalidOID as the database oid to InitPostgres(...) or the bgworker function wrappers. Such a backend can query and AFAIK modify shared catalogs, so I don't see why the executor couldn't be used to service frontend requests instead. Transaction state, user identity, databases, etc are all global.

It might be issues with pg_class and similar relations. Despite the fact that pg_database is a global shared catalog relation, it's listed in pg_class, which gets cloned from the template database. There's no global shared variant of pg_class.

So it's possible the relation cache doesn't handle the "no database" case. I haven't investigated closely. I'll be doing more in 9.5, since some fixes to the background worker API will let me avoid doing ugly hacks relating to shared catalog access by starting a shared-relations-only background worker.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.