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

> -----Original Message-----
> From: Josh Berkus [mailto:josh@agliodbs.com]
> Sent: 16 June 2005 17:29
> To: Dave Page
> Cc: pgsql-hackers@postgresql.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.

> 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
toa 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
databasewill 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
tostop them doing the same with template1 iirc). At least we would have a standard, non-template database for utilities
toconnect 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

> -----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.

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: 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.

"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

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
andsolve 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
withmore 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
perhapsit would be better to keep them seperate - the first db a real first-time-newbie will see is 'default', so
perhapshaving 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