Re: default database creation with initdb

Magnus Hagander wrote:

Umm. Tiny item, but your comment still refers to the database as
pg_system ;-)

:-)

Regards,
Andreas

On Saturday 18 June 2005 04:55, Andreas Pflug wrote:

Magnus Hagander wrote:

Umm. Tiny item, but your comment still refers to the database as
pg_system ;-)

What is the purpose of this database? A generalized, shared resource for tool
makers and add-on packages to store information in PostgreSQL, or a working
database that is usable (and to be used) out of the box for new users? I
really don't think we want the latter... I can see users connecting via psql
and then playing around with different add/create type statements. It is all
too common a question from newbies... "does postgresql have a default
database to get started with?" They'll see this database and begin creating
schema and using this as thier main database, and I think we ought to avoid
that. If people don't like pg_system, pg_addons seem like a much safer name
to go with imho.

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

Robert Treat wrote:

On Saturday 18 June 2005 04:55, Andreas Pflug wrote:

Magnus Hagander wrote:

Umm. Tiny item, but your comment still refers to the database as
pg_system ;-)

What is the purpose of this database? A generalized, shared resource for tool
makers and add-on packages to store information in PostgreSQL, or a working
database that is usable (and to be used) out of the box for new users? I
really don't think we want the latter... I can see users connecting via psql
and then playing around with different add/create type statements. It is all
too common a question from newbies... "does postgresql have a default
database to get started with?"

A sample DB would be nice...

They'll see this database and begin creating
schema and using this as thier main database, and I think we ought to avoid
that. If people don't like pg_system, pg_addons seem like a much safer name
to go with imho.

To avoid people creating stuff in it, the patch revokes CREATE from
public (won't help against admins),
which in turn Tom thinks is "not a sane default".

Regards,
Andreas

[ redirected back to hackers, since it seems this is far from a finished
discussion ]

Robert Treat <xzilla@users.sourceforge.net> writes:

What is the purpose of this database? A generalized, shared resource for tool
makers and add-on packages to store information in PostgreSQL, or a working
database that is usable (and to be used) out of the box for new users? I
really don't think we want the latter... I can see users connecting via psql
and then playing around with different add/create type statements. It is all
too common a question from newbies... "does postgresql have a default
database to get started with?" They'll see this database and begin creating
schema and using this as thier main database, and I think we ought to avoid
that. If people don't like pg_system, pg_addons seem like a much safer name
to go with imho.

pg_addons or pg_tools or something like that seems like a fine name *for
the purpose of a tools-only database* ... but that is only one of the
issues being tossed around here. To me the much more interesting aspect
of this is reducing the extent to which template1 is serving multiple
not-very-compatible purposes. I like the idea of a default database
because it would eliminate two perennial issues:
* newbies mistakenly cluttering template1 with junk
* CREATE DATABASE failing because there are other connections to the
template database.

To be newbie-friendly, such a default database *should* be writable,
I think. The whole point is to let people play without having to learn
how to create a database first. If they clutter it up, so what? They
can always drop it and recreate it --- there won't be anything at all
special about it. (Thus, Andreas' desire to have it be considered a
"system object" seems misplaced to me.)

This vision immediately brings up another issue: for most client tools
the default database-to-connect-to is *not* template1, it is the
database named after the connecting user. If we invent a default
database, should we change things to remove the username dependence
and always connect to "default" by default? I think you could argue
this either way --- it may be too much of a non-backwards-compatible
change, but if we were designing the behavior in a green field today,
I suspect that's what we'd make it do.

regards, tom lane

On Sat, Jun 18, 2005 at 09:27:49 -0400,
Robert Treat <xzilla@users.sourceforge.net> wrote:

On Saturday 18 June 2005 04:55, Andreas Pflug wrote:

Magnus Hagander wrote:

Umm. Tiny item, but your comment still refers to the database as
pg_system ;-)

What is the purpose of this database? A generalized, shared resource for tool
makers and add-on packages to store information in PostgreSQL, or a working
database that is usable (and to be used) out of the box for new users? I
really don't think we want the latter... I can see users connecting via psql
and then playing around with different add/create type statements. It is all
too common a question from newbies... "does postgresql have a default
database to get started with?" They'll see this database and begin creating
schema and using this as thier main database, and I think we ought to avoid
that. If people don't like pg_system, pg_addons seem like a much safer name
to go with imho.

I believe the intention is that things that need to connect to some
database to do their work (e.g. psql -l, createuser) will connect to that
database. createdb will still connect to template1, but now will be less
likely to have a conflict with another user being connected to template1
at the same time. I didn't check the patch to see if the behavior of the
psql -l and createuser were changed or if just the initdb behavior was
changed.

I don't think it will be a big deal if people put stuff in that database.

Am Samstag, den 18.06.2005, 10:12 -0400 schrieb Tom Lane:

[ redirected back to hackers, since it seems this is far from a finished
discussion ]

...

pg_addons or pg_tools or something like that seems like a fine name *for
the purpose of a tools-only database* ... but that is only one of the
issues being tossed around here. To me the much more interesting aspect
of this is reducing the extent to which template1 is serving multiple
not-very-compatible purposes. I like the idea of a default database
because it would eliminate two perennial issues:
* newbies mistakenly cluttering template1 with junk
* CREATE DATABASE failing because there are other connections to the
template database.

To be newbie-friendly, such a default database *should* be writable,
I think. The whole point is to let people play without having to learn
how to create a database first. If they clutter it up, so what? They
can always drop it and recreate it --- there won't be anything at all
special about it. (Thus, Andreas' desire to have it be considered a
"system object" seems misplaced to me.)

This vision immediately brings up another issue: for most client tools
the default database-to-connect-to is *not* template1, it is the
database named after the connecting user. If we invent a default
database, should we change things to remove the username dependence
and always connect to "default" by default? I think you could argue
this either way --- it may be too much of a non-backwards-compatible
change, but if we were designing the behavior in a green field today,
I suspect that's what we'd make it do.

Looks like 2 entirely different targets. So the only solution
I see would 2 databases created by default:

1 tooldb or systemdb or whatever a good name there is for
the tools to store their information

1 default DB (which could be optional? or initdb asks interactively?
or psql/tools output a hint to create one?)

If thats too much I think it could really be considered
to stop connections to template0 (as to avoid clutter here)
but allow it as default template for new databases.
Which seems more consistent imho.
(And so it would make template1 optional)

Tom Lane wrote:

[ redirected back to hackers, since it seems this is far from a finished
discussion ]

Robert Treat <xzilla@users.sourceforge.net> writes:

What is the purpose of this database? A generalized, shared resource for tool
makers and add-on packages to store information in PostgreSQL, or a working
database that is usable (and to be used) out of the box for new users? I
really don't think we want the latter... I can see users connecting via psql
and then playing around with different add/create type statements. It is all
too common a question from newbies... "does postgresql have a default
database to get started with?" They'll see this database and begin creating
schema and using this as thier main database, and I think we ought to avoid
that. If people don't like pg_system, pg_addons seem like a much safer name
to go with imho.

pg_addons or pg_tools or something like that seems like a fine name *for
the purpose of a tools-only database* ... but that is only one of the
issues being tossed around here. To me the much more interesting aspect
of this is reducing the extent to which template1 is serving multiple
not-very-compatible purposes. I like the idea of a default database
because it would eliminate two perennial issues:
* newbies mistakenly cluttering template1 with junk
* CREATE DATABASE failing because there are other connections to the
template database.

To be newbie-friendly, such a default database *should* be writable,
I think. The whole point is to let people play without having to learn
how to create a database first. If they clutter it up, so what? They
can always drop it and recreate it --- there won't be anything at all
special about it. (Thus, Andreas' desire to have it be considered a
"system object" seems misplaced to me.)

This contradicts my intention to have users *not* to write to it, but
reserve it for system like stuff. You might take everything that's not
in postgres binary as non-system, but the average user's perception is
different.

Apparently we really need two initdb created databases for all purposes.

Regards,
Andreas

What about just calling the new database postgres by default?

For true newbies, the first thing that happens if you try just
running psql with no arguments is that you discover there's no
database named postgres. For most first-time users, I suspect the
postgres user is the super-user and the first user used to access any
database.

Just throwing out another suggestion.

--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC

Strategic Open Source: Open Your i™

http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005

Thomas,

What about just calling the new database postgres by default?

Hey, works for me. A great idea really.

Hmmmm .... except ... on BSD platforms, due to history with Ports, the
superuser is "pgsql". Fortunately, the BSDs only account for a small
minority of new users, so we could just ignore it.

--
Josh Berkus
Aglio Database Solutions
San Francisco

-----Original Message-----
From: pgsql-hackers-owner@postgresql.org on behalf of Andreas Pflug
Sent: Sun 6/19/2005 12:23 AM
To: Tom Lane
Cc: Robert Treat; Magnus Hagander; pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] [PATCHES] default database creation with initdb

This contradicts my intention to have users *not* to write to it, but
reserve it for system like stuff. You might take everything that's not
in postgres binary as non-system, but the average user's perception is
different.

The main intention of my original post was to suggest a way of keeping users out of template1. This still remains the main issue imo, and one that a 'default' database would resolve perfectly well.

Apparently we really need two initdb created databases for all purposes.

Whether or not users should write to the default db is another issue altogether, and one that I'd rather not see causing this idea to be rejected or get delayed past freeze. If 'default' is writeable, then so what if users use it? It won't stop pgAdmin from working, and it won't stop CREATE DATABASE from working. If we /really/ don't want them looking at what we're writing to the cluster, then we can just as easily agree a standard name for a database with phpPgAdmin and any other interested projects, and all co-exist in that. The first time any of the products is used, it creates the database if required.

The important thing is that we have a default database for users to connect to from initdb time, and that it isn't template1.

Now, as you (Andreas) already seem to have written the easy part of the patch, are you going to check the rest of the docs & sources for references to template1 (and correct where required), or shall I take some time tomorrow? :-)

Regards, Dave.

Dave Page wrote:

Whether or not users should write to the default db is another issue
altogether, and one that I'd rather not see causing this idea to be
rejected or get delayed past freeze.

+1

If 'default' is writeable, then

so what if users use it? It won't stop pgAdmin from working, and it
won't stop CREATE DATABASE from working. If we /really/ don't want
them looking at what we're writing to the cluster, then we can just
as easily agree a standard name for a database with phpPgAdmin and
any other interested projects, and all co-exist in that. The first
time any of the products is used, it creates the database if
required.

We can hide that db from using by declaring it a system db anyway, which
would prevent most users from using it.

The important thing is that we have a default database for users to
connect to from initdb time, and that it isn't template1.

Jup.

Now, as you (Andreas) already seem to have written the easy part of
the patch, are you going to check the rest of the docs & sources for
references to template1 (and correct where required), or shall I take
some time tomorrow? :-)

There's still the interesting idea of naming that db "postgres", which
I'd even prefer over default because it includes the idea of default db
since dbname=username is a common assumption in pgsql tools and also
indicates "this is not for everybody, but just for me, the admin". I'm
fine with any name though.

Can't tell whether I could find time for reviewing the docs the next
days (more interesting for feature freeze is having fixed the
implementation anyway). I'm much more concerned about the
instrumentation patch which apparently is going to be ignored until
after feature freeze again as it was last year for 8.0.

Regards,
Andreas

Josh Berkus <josh@agliodbs.com> writes:

What about just calling the new database postgres by default?

Hey, works for me. A great idea really.

Yeah, that seems like a pretty good compromise to me too. I was
thinking last night that we'd end up with documentation statements
like "you connect to the default database by default" which would
be kinda confusing :-(.

Hmmmm .... except ... on BSD platforms, due to history with Ports, the
superuser is "pgsql". Fortunately, the BSDs only account for a small
minority of new users, so we could just ignore it.

Perhaps the Ports guys will get motivated to become more standard ;-).

regards, tom lane

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

Can't tell whether I could find time for reviewing the docs the next
days (more interesting for feature freeze is having fixed the
implementation anyway).

Of the sixty-odd files that mention template1 in current CVS, only about
half are documentation. If you think a patch that patches only initdb
is enough to get this "feature" in, you are very mistaken ... even if we
were inclined to accept patches that blatantly omit documentation, which
as a rule we do not.

regards, tom lane

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: 20 June 2005 03:46
To: Andreas Pflug
Cc: Dave Page; Robert Treat; Magnus Hagander;
pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] [PATCHES] default database creation
with initdb

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

Can't tell whether I could find time for reviewing the docs

the next

days (more interesting for feature freeze is having fixed the
implementation anyway).

Of the sixty-odd files that mention template1 in current CVS,
only about
half are documentation. If you think a patch that patches only initdb
is enough to get this "feature" in, you are very mistaken ...
even if we
were inclined to accept patches that blatantly omit
documentation, which
as a rule we do not.

... And rightly so imho :-). I will spend some time on this today.

Regards, Dave.

Tom Lane wrote:

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

Can't tell whether I could find time for reviewing the docs the next
days (more interesting for feature freeze is having fixed the
implementation anyway).

Of the sixty-odd files that mention template1 in current CVS, only about
half are documentation.

The decision which files should be changed must be taken. e.g. createdb,
dropdb will use template1 hardcoded. Is it acceptable that those tools
fail if the "postgres" database isn't present any more?

Regards,
Andreas

-----Original Message-----
From: Andreas Pflug [mailto:pgadmin@pse-consulting.de]
Sent: 20 June 2005 10:14
To: Tom Lane
Cc: Dave Page; Robert Treat; Magnus Hagander;
pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] [PATCHES] default database creation with initdb

Tom Lane wrote:

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

Can't tell whether I could find time for reviewing the docs

the next

days (more interesting for feature freeze is having fixed the
implementation anyway).

Of the sixty-odd files that mention template1 in current

CVS, only about

half are documentation.

The decision which files should be changed must be taken.
e.g. createdb,
dropdb will use template1 hardcoded. Is it acceptable that
those tools
fail if the "postgres" database isn't present any more?

That's what I'm working on atm, and given Tom's previous comment about
small-footprint users not wanting an extra 5/6MB on the size of a new
cluster, I'm leaving most things using template1 and mainly just
updating docs and examples. 'postgres' can then be dropped with no ill
effects other than a return to the old template1 etc. issues.

Regards, Dave.

Andreas Pflug wrote:

Tom Lane wrote:

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

Can't tell whether I could find time for reviewing the docs the next
days (more interesting for feature freeze is having fixed the
implementation anyway).

Of the sixty-odd files that mention template1 in current CVS, only about
half are documentation.

The decision which files should be changed must be taken. e.g.
createdb, dropdb will use template1 hardcoded. Is it acceptable that
those tools fail if the "postgres" database isn't present any more?

How about template1 as a fallback?

cheers

andrew

Andrew Dunstan wrote:

The decision which files should be changed must be taken. e.g.
createdb, dropdb will use template1 hardcoded. Is it acceptable that
those tools fail if the "postgres" database isn't present any more?

How about template1 as a fallback?

Fallback is a fine idea, but this brings up another problem I'm
currently facing: how to identify the problem the connection has from
libpq? If the problem is a wrong password, we certainly don't want to
try again. I browsed the sources over and over, but apparently there's
no machine readable return code to distinguish the reason of connection
failure apart from examining the errormessage string. I have the same
problem in pgAdmin, where I try to give extended messages like
http://svn.pgadmin.org/cgi-bin/viewcvs.cgi/trunk/pgadmin3/docs/en_US/hints/conn-listen.html?rev=4056&amp;view=markup

Regards,
Andreas

Dave Page wrote:

That's what I'm working on atm, and given Tom's previous comment about
small-footprint users not wanting an extra 5/6MB on the size of a new
cluster, I'm leaving most things using template1 and mainly just
updating docs and examples. 'postgres' can then be dropped with no ill
effects other than a return to the old template1 etc. issues.

I'm confused. I thought avoiding those issues was one of the main
purposes for this.

cheers

andrew