Utility database (Was: RE: Autovacuum in the backend)

> -----Original Message-----
> From: Josh Berkus [mailto:josh(at)agliodbs(dot)com]
> Sent: 16 June 2005 17:29
> To: Dave Page
> Cc: pgsql-hackers(at)postgresql(dot)org
> Subject: Re: [HACKERS] Autovacuum in the backend
>
> Dave,
>
> > In previous discussions on -hackers when ppl raised the idea of
> > something like pgAgent being built into the backend, istm that the
> > majority of people were against the idea.
>
> Well, you're up against the minimalist approach to core
> PostgreSQL there. It
> would pretty much *have* to be an optional add-in, even if it
> was stored in
> pg_catalog. I can see a lot of uses for a back-end job
> scheduler myself, but
> it would need to go through the gauntlet of design criticism
> first <wry
> grin>.

And as we all know, optional means pgFoundry or someplace else. To be
honest, I simply couldn't be bothered on this one because even if I
could convince everyone to allow such a beast on the backend, the
arguments about how it should work would probably go on forever.
Consequently it's well and truly part of pgAdmin now :-).

One related idea that I have been meaning to moot for a while now
though, is that of a 'utility' database. One of the problems we've
always had in pgAdmin (and presumably phpPgAdmin as well), is that the
only database we know exists with any reasonable surety is template1,
and consequently, this is the default database that pgAdmin connects to.
There are obvious problems with this - in particular:

- Newbies may not realise the significance of making their initial
experiments in template1
- Administrators may not want users connecting to template1
- We don't want to create utility objects in template1 to offer enhanced
functionality in the client.

To overcome this, a alternative database created by initdb would be very
useful. This would be roughly the equivalent of SQL Server's 'msdb'
database and would allow:

- A default non-template database for apps to connect to initially
- A standard place for apps like pgAgent to store their cluster-specific
configuration & data
- A standard place for apps like pgAdmin to store utility objects

What are peoples thoughts on this?

Regards, Dave.