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

In the last exciting episode, dpage@vale-housing.co.uk ("Dave Page") wrote:

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.

At one time, it was uncommon to have "computing sites" that did not
have some sort of "priesthood" of system operators that would be
formally responsible for managing the local environment.

They would do things like:

- Install TeX and LaTeX, in some common area, and provide a
"Local Users' Guide To Using TeX and LaTeX" that might even
tell you which printers to use, and in what rooms you can find
the TeX-compatible printers...

- Install all sorts of *supported* system extensions, commonly
under /usr/local

In an environment with that sort of staffing, it surely is reasonable
to anticipate that these (usually harassed) heroes might set up a
PostgreSQL instance with a "generic" default database of this sort.

Today, many users are in environments where there is no DBA, there is
no "system administrator," there is no one that really understands
their computer system.

What we provide as a default ought to try to be suited to both of
those purposes, and it is unsafe to assume either the presence or
absence of a DBA, as both are common conditions...
--
let name="cbbrowne" and tld="gmail.com" in String.concat "@" [name;tld];;
http://cbbrowne.com/info/slony.html
Editing is a rewording activity.
-- Alan Perlis
[And EMACS a rewording editor. Ed.]

Martha Stewart called it a Good Thing when pgadmin@pse-consulting.de (Andreas Pflug) wrote:

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.

I would tend to agree with the reasons not to use a "pg_" prefix...
Perhaps something like "sys_" or "def_" (short for "system" or
"default") would be better.

It strikes me as a useful thing to make sure the name contains the
word "share" or "shared" somewhere, as that would give even the most
hapless user that accesses it some suggestion that this database is
"shared", and hence should be treated with some care and with some
attempt to try to "play well" with others. Alternatively, the word
"commons", of the "Tragedy of the Commons", might fit.

Thus, "sys_shared", "def_share", "user_commons" are all sorts of names
that suggest that this is some sort of default/shared area.
--
output = ("cbbrowne" "@" "gmail.com")
http://linuxdatabases.info/info/wp.html
"People who don't use computers are more sociable, reasonable, and ...
less twisted" -- Arthur Norman

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: 17 June 2005 15:09
To: Christopher Kings-Lynne
Cc: Andreas Pflug; Magnus Hagander; Dave Page; Josh Berkus;
pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Utility database (Was: RE: Autovacuum
in the backend)

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.

Yeah, but those people could easily drop it to save that space. They'd
have to offer an alternative default db for their users, but then I
guess they probably have pretty unusual requirements anyway so I doubt
that would add any pain.

Regards, Dave.

-----Original Message-----
From: Andreas Pflug [mailto:pgadmin@pse-consulting.de]
Sent: 17 June 2005 18:45
To: Tom Lane
Cc: Christopher Kings-Lynne; Magnus Hagander; Dave Page; Josh
Berkus; pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Utility database (Was: RE: Autovacuum
in the backend)

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.

OK, so it sounds like noone is really against this idea. Is anyone going
to object to it being applied if I post a suitable patch?

Assuming not, it seems like the only bone of contention is the name...
So:

pg_system - Implies it's a 'true' PostgreSQL system object, but also
implies 'don't mess with me'
default - Implies a standard 'default' database.
pgdb - Blagged from the Microsoft equivalent, msdb.

Others?

Personally I prefer the first or last, as default implies to me that
it's a kindof general use database - which, as Tom points out it could
be, however I think it's better to encourage users to only use it as
directed by tool providers, and not for general purpose.

Regards, Dave.

-----Original Message-----
From: pgsql-hackers-owner@postgresql.org
[mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of
Christopher Browne
Sent: 17 June 2005 19:59
To: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Utility database

Thus, "sys_shared", "def_share", "user_commons" are all sorts of names
that suggest that this is some sort of default/shared area.

I like the first. The second and third seem less obvious to me.
'default_shared' should definitely get the point across, though it's a
little long.

Regards, Dave.

Dave Page wrote:

Thus, "sys_shared", "def_share", "user_commons" are all sorts of names
that suggest that this is some sort of default/shared area.

I like the first. The second and third seem less obvious to me.
'default_shared' should definitely get the point across, though it's a
little long.

It strikes me that these names just might have some significance to
developers but will have none at all for users. I don't heve a better
alternative ... maybe because the purpose has been expressed somewhat
fuzzily.

cheers

andrew

Andrew Dunstan wrote:

It strikes me that these names just might have some significance to
developers but will have none at all for users. I don't heve a better
alternative ... maybe because the purpose has been expressed somewhat
fuzzily.

I'd define the purpose like this:

- being a db that's existing reliably right after initdb, unless deleted
by an ( evil-minded :-) admin.

- contain data for cluster wide system services, e.g. pgAgent schedules,
configuration for autovacuumV2, profiling data

regards,
Andreas

On Fri, 17 Jun 2005, Andrew Dunstan wrote:

Thus, "sys_shared", "def_share", "user_commons" are all sorts of names
that suggest that this is some sort of default/shared area.

I like the first. The second and third seem less obvious to me.
'default_shared' should definitely get the point across, though it's a
little long.

It strikes me that these names just might have some significance to
developers but will have none at all for users. I don't heve a better
alternative ... maybe because the purpose has been expressed somewhat
fuzzily.

It seems that far and away the most common use of this database will be as
the default database to connect to with any of the client apps. Thus Tom's
suggestion of "default" makes the most sense to me.

Jon

--
Jon Jensen
End Point Corporation
http://www.endpoint.com/

"Dave Page" <dpage@vale-housing.co.uk> writes:

Personally I prefer the first or last, as default implies to me that
it's a kindof general use database - which, as Tom points out it could
be, however I think it's better to encourage users to only use it as
directed by tool providers, and not for general purpose.

If that is what you want then the database should surely not become the
default connection target for clients.

The proposal I thought was being made was that we separate the
default-connection-target property from the default-CREATE-DATABASE-source
property. This business about where tool authors can dump random junk
of their own devising does not seem to me to fit at all with either of
those properties. I think what you are really asking for is yet another
"standard" database named something like TOOLS_ONLY_KEEP_OUT.

But I do not see the argument for having that created by default,
because any tool that is capable of creating random junk is surely
capable of creating a database to put it in. Furthermore, if it's
created by default and completely unused in the default installation,
lots of DBAs will immediately drop it --- so I entirely fail to see
the argument that tools could expect it to be there without any
expenditure of their own effort.

I still say the most that's needed here is some agreement among tool
authors about a common choice of database name to create if their tool
is installed.

regards, tom lane

"Dave Page" <dpage@vale-housing.co.uk> writes:

I like the first. The second and third seem less obvious to me.
'default_shared' should definitely get the point across, though it's a
little long.

I think "shared" would give the wrong impression to many people ---
nowadays the connotation of that is something that you are exposing
to at least your local network, maybe the entire internet (think
"Windows shares"). I realize that the meaning you had in mind was
"shared among authorized users of this Postgres cluster", but I doubt
that implication will come through to very many newbies.

regards, tom lane

On Saturday 18 June 2005 01:36, Tom Lane wrote:

"Dave Page" <dpage@vale-housing.co.uk> writes:

Personally I prefer the first or last, as default implies to me that
it's a kindof general use database - which, as Tom points out it could
be, however I think it's better to encourage users to only use it as
directed by tool providers, and not for general purpose.

If that is what you want then the database should surely not become the
default connection target for clients.

The proposal I thought was being made was that we separate the
default-connection-target property from the default-CREATE-DATABASE-source
property. This business about where tool authors can dump random junk
of their own devising does not seem to me to fit at all with either of
those properties. I think what you are really asking for is yet another
"standard" database named something like TOOLS_ONLY_KEEP_OUT.

But I do not see the argument for having that created by default,
because any tool that is capable of creating random junk is surely
capable of creating a database to put it in. Furthermore, if it's
created by default and completely unused in the default installation,
lots of DBAs will immediately drop it --- so I entirely fail to see
the argument that tools could expect it to be there without any
expenditure of their own effort.

I still say the most that's needed here is some agreement among tool
authors about a common choice of database name to create if their tool
is installed.

I was gradually drifting toward this idea. Do we really need the blessing of
the postgresql core to make this happen? ISTM we don't. Right now we
(phppgadmin) already tell users that, if they want to make use of our
"reports" functionality, they must create a "phppgadmin" database that also
creates a table to hold the report information; a script is provided to help
ease this setup requirement.

But what if we all just agreed that we would use a common database called
"pg_addons", and that each tool would install thier information into an
appropriatly named schema within that database; phppgadmin for us, pgadminiii
for pgadmin for examples. This means that, if you install pgadmin, it
creates this database and puts its information into its own schema. If you
then wanted phppgadmin reporting, we'd look for this database and, since it
exists, we'd just install our needed information into a phppgadmin schema
within that database. Any other addons/tool makers out there that wanted to
jump on the bandwagon could do so, just by following this basic agreement.

--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Sat 6/18/2005 6:36 AM
To: Dave Page
Cc: Andreas Pflug; Christopher Kings-Lynne; Magnus Hagander; Josh Berkus; pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Utility database (Was: RE: Autovacuum in the backend)

The proposal I thought was being made was that we separate the
default-connection-target property from the default-CREATE-DATABASE-source
property. This business about where tool authors can dump random junk
of their own devising does not seem to me to fit at all with either of
those properties. I think what you are really asking for is yet another
"standard" database named something like TOOLS_ONLY_KEEP_OUT.

Keeping people out of template1 is my major concern, however it seemed like a good way to kill 2 birds with one stone and solve both problems at once.

I'll knock up a patch to create a database called 'default' at initdb time given that that appears to be the only name with more than one person backing it.

We (the tool makers), can argue over whether we will use it, or pg_addons (as Robert has suggested) later. In some ways perhaps it would be better to keep them seperate - the first db a real first-time-newbie will see is 'default', so perhaps having lots of tool data where he might fiddle is not such a good idea.

Any objections (he says, looking for a smooth patch->CVS before 8.1 :-) )?

Regards, Dave

I was gradually drifting toward this idea. Do we really need
the blessing of the postgresql core to make this happen? ISTM
we don't.

I think not, but I would perhaps make things easier ;-)

But what if we all just agreed that we would use a common
database called "pg_addons", and that each tool would install
thier information into an appropriatly named schema within
that database; phppgadmin for us, pgadminiii for pgadmin for
examples. This means that, if you install pgadmin, it
creates this database and puts its information into its own
schema. If you then wanted phppgadmin reporting, we'd look
for this database and, since it exists, we'd just install our
needed information into a phppgadmin schema within that
database. Any other addons/tool makers out there that wanted to
jump on the bandwagon could do so, just by following this
basic agreement.

Seems reasonable. The only argument agains it vs having it in the
"default" (whatevr it's named) database is that you'll have two more
databases. But with them coming in at 5-6Mb (I think it was), I don't
see that as a big problem.

It has to be documented somewhere though, so "new tool vendors" know how
to create it. You'll get in a lot of trouble if it starts showing up
with different encodings depending on which tool created it, for
example. But that should be easy enough.

//Magnus

Magnus Hagander wrote:

I was gradually drifting toward this idea. Do we really need
the blessing of the postgresql core to make this happen? ISTM
we don't.

I think not, but I would perhaps make things easier ;-)

But what if we all just agreed that we would use a common
database called "pg_addons", and that each tool would install
thier information into an appropriatly named schema within
that database; phppgadmin for us, pgadminiii for pgadmin for
examples. This means that, if you install pgadmin, it
creates this database and puts its information into its own
schema. If you then wanted phppgadmin reporting, we'd look
for this database and, since it exists, we'd just install our
needed information into a phppgadmin schema within that
database. Any other addons/tool makers out there that wanted to
jump on the bandwagon could do so, just by following this
basic agreement.

Seems reasonable. The only argument agains it vs having it in the
"default" (whatevr it's named) database is that you'll have two more
databases. But with them coming in at 5-6Mb (I think it was), I don't
see that as a big problem.

It has to be documented somewhere though, so "new tool vendors" know how
to create it. You'll get in a lot of trouble if it starts showing up
with different encodings depending on which tool created it, for
example. But that should be easy enough.

I just posted a patch for an initdb time default db creation, with
public creation rights removed. I'm still unhappy about that name
(though I understand Tom, psql localhost default looks good), but I
consider the default db a system db, so it wouldn't shown up in pgAdmin
unless explicitely enabled and users would be kept out.

Regards,
Andreas

"Dave Page" <dpage@vale-housing.co.uk> writes:

Keeping people out of template1 is my major concern, however it seemed like
a good way to kill 2 birds with one stone and solve both problems at once.

FWIW here's a "me too" on keeping people out of template1 by default. I've
more than once accidentally created objects in template1. A couple times I've
actually restored an entire database in template1. And it's a bit of a pain to
clean out if you have any objects you actually want there.

I would agree with keeping things simple and naming it "default".

--
greg