Re: default database creation with initdb

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

//Magnus

> -----Original Message-----
> From: pgsql-patches-owner(at)postgresql(dot)org
> [mailto:pgsql-patches-owner(at)postgresql(dot)org] On Behalf Of Andreas Pflug
> Sent: Saturday, June 18, 2005 10:42 AM
> To: PostgreSQL-patches
> Subject: [PATCHES] default database creation with initdb
>
> As per discussion on -hackers the attached patch creates the
> 'default'
> database at initdb time as a default target for initial
> connections to keep template1 free from connections and
> available as template source.
>
> I consider this DB a system object, so it's created before
> make_template0 sets the last_system_oid (wondering why
> template0 isn't considered a system db too)
>
> Regards,
> Andreas
>

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(at)users(dot)sourceforge(dot)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(at)users(dot)sourceforge(dot)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(at)users(dot)sourceforge(dot)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

Josh Berkus <josh(at)agliodbs(dot)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