Re: How to do a "CREATE DATABASE" and then connect to it?

On Sun, Jun 04, 2006 at 02:24:32PM -0700, Joseph Brenner wrote:
> I think there are two different "connects" we're talking about here,
> one is the connection to the postgresql, the other is the "connection"
> to the "database" (i.e. the "dbname", which probably should've been
> called the "catalog").
>
> My guess (and it's only a guess) is that connecting to the postmaster
> is relatively expensive, and that a (hypothetical) "CONNECT <dbname>"
> command would be much faster.

You'd think that, but it's not as straight-forward as all that. Quite a
bit of code assume the current database, for example the cache of
system for tables and attributes. The same tables with will have the
same OIDs but different data.

Once you get around to clearing all the caches and reloading them,
flushing data from buffers, etc, it's not all that clear what you're
saving over a full backend restart. It's been considered but just
no-ones tried it yet.

And in the mean time, something like pgpool will beat the pants off you
because the shutdown of the old database and the connection of the new
can overlap, and you'll be talking to the new database before the old
has even noticed you're gone...

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.