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

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?

I think this is a very good idea. I've come up against this need once or
twice before.. And the fact that stuff in template1 gets propagated out
to all newly created databases can be a major pain when this happens.

A shared database for this stuff would be great - then each tool could
just create a schema for it's own stuff.

How does pgAdmin deal with this today?

//Magnus

I also think it is useful and make things easier.
A connection on template1 also prevent others to create new databases.

connection1:
template1#=

connection2:
foo=# create database bar;
ERROR: source database template1 is being accessed by other users

Magnus Hagander wrote:
fer 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?

I think this is a very good idea. I've come up against this need once or
twice before.. And the fact that stuff in template1 gets propagated out
to all newly created databases can be a major pain when this happens.

A shared database for this stuff would be great - then each tool could
just create a schema for it's own stuff.

How does pgAdmin deal with this today?

Not at all. pgAdmin II did store some information in the current db,
pgAdmin III remembers everything locally. Extended feature functions are
taken from the "initial DB", by default template1 (most of them need to
be in the db under investigation anyway).

I'd be glad to see the utility database, this would unleash several
ideas (e.g. a profiling agent I have in mind).

Regards,
Andreas

I think this is a very good idea. I've come up against this need once or
twice before.. And the fact that stuff in template1 gets propagated out
to all newly created databases can be a major pain when this happens.

A shared database for this stuff would be great - then each tool could
just create a schema for it's own stuff.

How does pgAdmin deal with this today?

In phpPgAdmin the default db to connect to can be specified per-server
in the config file. It defaults to template1. It actually is not
relevant at all which db it is, so long as they can connect to it.

Chris

Christopher Kings-Lynne wrote:

In phpPgAdmin the default db to connect to can be specified per-server
in the config file. It defaults to template1. It actually is not
relevant at all which db it is, so long as they can connect to it.

I wonder how many users actually change that value for php/pgadmin or
simply leave it default. Observing myself, <10 % I'd guess.

Regards,
Andreas

In phpPgAdmin the default db to connect to can be specified per-server
in the config file. It defaults to template1. It actually is not
relevant at all which db it is, so long as they can connect to it.

I wonder how many users actually change that value for php/pgadmin or
simply leave it default. Observing myself, <10 % I'd guess.

Only people who ever change it are those whose dba's have disallowed
connections to template1.

Chris

Christopher Kings-Lynne wrote:

In phpPgAdmin the default db to connect to can be specified
per-server in the config file. It defaults to template1. It
actually is not relevant at all which db it is, so long as they can
connect to it.

I wonder how many users actually change that value for php/pgadmin or
simply leave it default. Observing myself, <10 % I'd guess.

Only people who ever change it are those whose dba's have disallowed
connections to template1.

Probably, though the create db issue is a good reason not to use template1.

So may I propose to have a pg_system database created by initdb, as a
copy from template1 in 8.1?

Regards,
Andreas

Probably, though the create db issue is a good reason not to use template1.

Create db issue?

So may I propose to have a pg_system database created by initdb, as a
copy from template1 in 8.1?

But then dbas will block off access to that db, or drop it and we're
back to square one...

Chris

-----Original Message-----
From: Christopher Kings-Lynne [mailto:chriskl@familyhealth.com.au]
Sent: Fri 6/17/2005 9:47 AM
To: Magnus Hagander
Cc: Dave Page; Josh Berkus; pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Utility database (Was: RE: Autovacuum in the backend)

In phpPgAdmin the default db to connect to can be specified per-server
in the config file. It defaults to template1. It actually is not
relevant at all which db it is, so long as they can connect to it.

That's how pgAdmin does it (though you set the default on the server dialog), however it's not good having to default to a database that 99% of sysadmins probably don't want their users anywhere near.

/D

-----Original Message-----
From: Christopher Kings-Lynne [mailto:chriskl@familyhealth.com.au]
Sent: Fri 6/17/2005 11:00 AM
To: Andreas Pflug
Cc: Magnus Hagander; Dave Page; Josh Berkus; pgsql-hackers@postgresql.org; Tom Lane
Subject: Re: [HACKERS] Utility database (Was: RE: Autovacuum in the backend)

Probably, though the create db issue is a good reason not to use template1.

Create db issue?

You can't create a db from template1 if other users are connected to it, which means the most simple form of create database will fail.

So may I propose to have a pg_system database created by initdb, as a
copy from template1 in 8.1?

But then dbas will block off access to that db, or drop it and we're
back to square one...

That's their choice though, and it would then be up to them to provide an alternative for their users (there's nothing to stop them doing the same with template1 iirc). At least we would have a standard, non-template database for utilities to connect to, whose purpose could be documented.

Regards Dave

Christopher Kings-Lynne wrote:

Probably, though the create db issue is a good reason not to use
template1.

Create db issue?

CREATE TABLE (implicitely using TEMPLATE template1) often fails because
template1 has connections exceeding the current one.

So may I propose to have a pg_system database created by initdb, as a
copy from template1 in 8.1?

But then dbas will block off access to that db, or drop it and we're
back to square one...

Sure, some dbas also might like to drop INFORMATION_SCHEMA, or modify
system catalogs or worse to bend the system as they like, effectively
disabling common tools. But if we create this db with initdb, I'd
expect to find it in the vast majority of installations. If not, we
could fall back to template1 for admin tools.

Regards,
Andreas

Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:

So may I propose to have a pg_system database created by initdb, as a
copy from template1 in 8.1?

Seems like a bizarre choice of name. Why not "default"?

But then dbas will block off access to that db, or drop it and we're
back to square one...

Don't see why they would. Let's review what we have here:

Database Function(s)

template0 guaranteed-virgin template for CREATE DATABASE

template1 installation-default template for CREATE DATABASE
default database to connect to for clients

(I don't think I'm missing anything --- can anyone think of a purpose
I have forgotten?)

If we split template1's functions as

template1 installation-default template for CREATE DATABASE

default default database to connect to for clients

then it becomes fairly reasonable for DBAs to block access to template1
after they've installed any installation-default stuff they want in it.
There isn't any particular reason to block access to "default", unless
you don't want to have a shared database at all --- in which case you'd
probably just drop it.

One argument against this is that it'd mean another copy of the system
catalogs in a standard installation. That's been running three to five
megabytes over the last few releases. Disk space is pretty cheap these
days, but we do get occasional complaints from people who wish the
footprint was smaller.

regards, tom lane

But then dbas will block off access to that db, or drop it

and we're

back to square one...

Don't see why they would. Let's review what we have here:

Database Function(s)

template0 guaranteed-virgin template for CREATE DATABASE

template1 installation-default template for
CREATE DATABASE
default database to connect to for clients

(I don't think I'm missing anything --- can anyone think of a
purpose I have forgotten?)

If we split template1's functions as

template1 installation-default template for
CREATE DATABASE

default default database to connect to
for clients

then it becomes fairly reasonable for DBAs to block access to
template1 after they've installed any installation-default
stuff they want in it.
There isn't any particular reason to block access to
"default", unless you don't want to have a shared database at
all --- in which case you'd probably just drop it.

It wouldn't just be "default to connect to", it would also be "location
for tools to store cluster-wide information". Which makes pg_system a
slightly more reasonable name in that context, but i certainly have no
problem with "default" as a name.

One argument against this is that it'd mean another copy of
the system catalogs in a standard installation. That's been
running three to five megabytes over the last few releases.
Disk space is pretty cheap these days, but we do get
occasional complaints from people who wish the footprint was smaller.

As long as you can drop it without hosing your system completely, that
can always be a solution for the ppl who are that space constrained.

//Magnus

"Magnus Hagander" <mha@sollentuna.net> writes:

It wouldn't just be "default to connect to", it would also be "location
for tools to store cluster-wide information". Which makes pg_system a
slightly more reasonable name in that context, but i certainly have no
problem with "default" as a name.

Well, where a tool chooses to install stuff is the business of that
tool; there isn't any particular reason to think that default would
suddenly become a preferred choice, I think.

I dislike the name pg_system because it implies that that DB is somehow
special from the point of view of the system ... which is exactly what
it would *not* be.

regards, tom lane

It wouldn't just be "default to connect to", it would also be
"location for tools to store cluster-wide information". Which makes
pg_system a slightly more reasonable name in that context, but i
certainly have no problem with "default" as a name.

Well, where a tool chooses to install stuff is the business
of that tool; there isn't any particular reason to think that
default would suddenly become a preferred choice, I think.

One of the two main reasons to do this was to have a place for tools to
store persistant data in a standard way. At least it was in Daves mail
;-) Actually, two out of three points were data storage.
It is, as you say, up to the tool where to put it. But we should provide
a standard place for tools to do it, to make it easier for both tool
makers and end users.

I dislike the name pg_system because it implies that that DB
is somehow special from the point of view of the system ...
which is exactly what it would *not* be.

That I can certainly agree with.

//Magnus

Magnus Hagander wrote:

I dislike the name pg_system because it implies that that DB
is somehow special from the point of view of the system ...
which is exactly what it would *not* be.

That I can certainly agree with.

I suggested the name to indicate that it's a db used by system tools. So
from a normal db user's point of view, it says "don't fool with this db,
you might break some tools you're using.

Regards,
Andreas

Tom Lane wrote:

One argument against this is that it'd mean another copy of the system
catalogs in a standard installation. That's been running three to five
megabytes over the last few releases. Disk space is pretty cheap these
days, but we do get occasional complaints from people who wish the
footprint was smaller.

In this case, a dba would drop anything not neccessary, including
INFORMATION_SCHEMA. We also could provide an initdb switch to omit that
pg_system db (and more non-vital stuff).

I particularly dislike the name "default" for that database, because
we'd have to expect users to place their user data there regularly (as
in the public schema), which is just what should *not* happen. So the
pg_ prefix should be used, the docs say clearly enough "don't touch pg_%
objects unless you know exactly what you do".

Regards,
Andreas

Andreas Pflug <pgadmin@pse-consulting.de> writes:

I particularly dislike the name "default" for that database, because
we'd have to expect users to place their user data there regularly (as
in the public schema), which is just what should *not* happen.

Why not?

Any tools using this database for their own purposes should surely be
smart enough to put all their stuff in a tool-specific schema with
a name chosen to be unlikely to collide with user names. So I see no
reason at all that users couldn't use the database too.

If your intent is to have a database reserved for tool use only, you
can certainly have an agreement among tool authors to create "pg_tools"
or some such if it's not there already. But there are no potential uses
of such a database in the standard distribution, and so I see no reason
to load down the standard distribution by creating a database that may
go completely unused.

regards, tom lane

Tom Lane wrote:

Andreas Pflug <pgadmin@pse-consulting.de> writes:

I particularly dislike the name "default" for that database, because
we'd have to expect users to place their user data there regularly (as
in the public schema), which is just what should *not* happen.

Why not?

Any tools using this database for their own purposes should surely be
smart enough to put all their stuff in a tool-specific schema with
a name chosen to be unlikely to collide with user names. So I see no
reason at all that users couldn't use the database too.

If your intent is to have a database reserved for tool use only, you
can certainly have an agreement among tool authors to create "pg_tools"
or some such if it's not there already. But there are no potential uses
of such a database in the standard distribution, and so I see no reason
to load down the standard distribution by creating a database that may
go completely unused.

The whole point if it is to have a database that is nearly guaranteed to
be there right from the start, i.e. right after initdb, not to need some
decent script executed (or not) later.

Regards,
Andreas