Re: default database creation with initdb
Umm. Tiny item, but your comment still refers to the database as
pg_system ;-)
//Magnus
Show quoted text
-----Original Message-----
From: pgsql-patches-owner@postgresql.org
[mailto:pgsql-patches-owner@postgresql.org] On Behalf Of Andreas Pflug
Sent: Saturday, June 18, 2005 10:42 AM
To: PostgreSQL-patches
Subject: [PATCHES] default database creation with initdbAs 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
Attachments:
initdb-default.patchtext/x-patch; name=initdb-default.patchDownload
Index: src/bin/initdb/initdb.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/bin/initdb/initdb.c,v
retrieving revision 1.83
diff -u -r1.83 initdb.c
--- src/bin/initdb/initdb.c 30 Apr 2005 08:08:51 -0000 1.83
+++ src/bin/initdb/initdb.c 18 Jun 2005 08:54:07 -0000
@@ -177,6 +177,7 @@
static void set_info_version(void);
static void setup_schema(void);
static void vacuum_db(void);
+static void make_default(void);
static void make_template0(void);
static void trapsig(int signum);
static void check_ok(void);
@@ -1828,6 +1829,38 @@
}
/*
+ * copy template1 to default
+ */
+static void
+make_default(void)
+{
+ PG_CMD_DECL;
+ char **line;
+ static char *default_setup[] = {
+ "CREATE DATABASE \"default\";\n",
+ "REVOKE CREATE,TEMPORARY ON DATABASE \"default\" FROM public;\n",
+ NULL
+ };
+
+ fputs(_("copying template1 to default ... "), stdout);
+ fflush(stdout);
+
+ snprintf(cmd, sizeof(cmd),
+ "\"%s\" %s template1 >%s",
+ backend_exec, backend_options,
+ DEVNULL);
+
+ PG_CMD_OPEN;
+
+ for (line = default_setup; *line; line++)
+ PG_CMD_PUTS(*line);
+
+ PG_CMD_CLOSE;
+
+ check_ok();
+}
+
+/*
* copy template1 to template0
*/
static void
@@ -2606,6 +2639,8 @@
vacuum_db();
+ make_default();
+
make_template0();
if (authwarning != NULL)
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.
Import Notes
Resolved by subject fallback
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
Show quoted text
Regards, Dave.
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
Import Notes
Reply to msg id not found: 42B5DA34.1010603@pse-consulting.de
-----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 initdbAndreas 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.
Import Notes
Resolved by subject fallback
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 initdbTom 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.
Import Notes
Resolved by subject fallback
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&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
-----Original Message-----
From: Andrew Dunstan [mailto:andrew@dunslane.net]
Sent: 20 June 2005 10:41
To: Dave Page
Cc: Andreas Pflug; Tom Lane; Robert Treat; Magnus Hagander;
pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] [PATCHES] default database creation with initdbDave 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 droppedwith 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.
No, it's mainly things like pgAdmin that create persistent connections
that are the problem. Createuser et al. connect and disconnect so
quickly it's unlikely to cause any problems.
Regards, Dave.
Import Notes
Resolved by subject fallback
Andrew Dunstan <andrew@dunslane.net> writes:
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.
+1. If we still have the problem that CREATE DATABASE is likely to fail
because of random connections to template1 from other tools, then we
haven't fixed one of the problems this was advertised to fix.
I don't see that much of a problem with having createdb etc. hardwire
postgres instead of template1 as the db-to-connect-to. What that
implies is that if you do have to drop and recreate it, you have to do
it the hard way:
psql template1
DROP DATABASE postgres;
CREATE DATABASE postgres;
But this isn't *that* hard, certainly way easier than recreating
template1 from template0 which is the procedure that you have to follow
now in comparable circumstances.
Alternatively one could imagine special-casing dropdb and createdb to
connect to template1 if the target database is postgres, and to postgres
in all other cases.
regards, tom lane
Andreas Pflug <pgadmin@pse-consulting.de> writes:
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.
If it's a server-side failure it should have a SQLSTATE code. I think
it'd be OK to look for ERRCODE_UNDEFINED_DATABASE to determine this.
However that still leaves us with an issue:
$ psql -U foo
psql: FATAL: database "foo" does not exist
The "real" problem here is that there's no user foo, but the backend is
currently coded in such a way that it detects the bad implied database
name first (at least in non-password-based auth methods). Not sure if
this is a big problem for code that's not defaulting the database name
though.
regards, tom lane
Tom Lane wrote:
Andreas Pflug <pgadmin@pse-consulting.de> writes:
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.If it's a server-side failure it should have a SQLSTATE code. I think
it'd be OK to look for ERRCODE_UNDEFINED_DATABASE to determine this.
SQLSTATE?
Specifically, I'm talking about
"no pg_hba.conf entry for ....", "Ident authentication failed.." (both
server sice) and "Is the server running on host ..." from libpq which I
receive from PQerrorMessage after PQconnectdb; I only receive
CONNECTION_BAD from PQstatus where I'd like it a little more specific
(maybe an PQextendedStatus)
Regards,
Andreas
Andreas Pflug <pgadmin@pse-consulting.de> writes:
Tom Lane wrote:
If it's a server-side failure it should have a SQLSTATE code.
Specifically, I'm talking about
"no pg_hba.conf entry for ....",
ERRCODE_INVALID_AUTHORIZATION_SPECIFICATION
"Ident authentication failed.." (both server sice)
Ditto. Do you need to know the difference? What exactly would client
code do differently for these two cases?
and "Is the server running on host ..." from libpq
libpq doesn't currently assign SQLSTATEs to internally detected errors
... someday someone should fix that.
regards, tom lane
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: 20 June 2005 14:19
To: Andrew Dunstan
Cc: Dave Page; Andreas Pflug; Robert Treat; Magnus Hagander;
pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] [PATCHES] default database creation
with initdbI don't see that much of a problem with having createdb etc. hardwire
postgres instead of template1 as the db-to-connect-to.
OK, new patch posted to -patches that updates all the utilities as well.
The only change I didn't make was in line 3458 (in StartChildProcess) of
postmaster.c - template1 seemed the more sensible option to leave there.
Let me know if you disagree and I'll change it :-)
Regards, Dave.
Import Notes
Resolved by subject fallback
Tom Lane wrote:
Andreas Pflug <pgadmin@pse-consulting.de> writes:
Tom Lane wrote:
If it's a server-side failure it should have a SQLSTATE code.
Specifically, I'm talking about
"no pg_hba.conf entry for ....",
ERRCODE_INVALID_AUTHORIZATION_SPECIFICATION
"Ident authentication failed.." (both server sice)
Ditto. Do you need to know the difference? What exactly would client
code do differently for these two cases?
Display different hints how to cope with this. The hint will arise for
newbies, and it would be confusing to discuss ident auth when a missing
pg_hba.conf line is the problem, and vice versa.
and "Is the server running on host ..." from libpq
libpq doesn't currently assign SQLSTATEs to internally detected errors
... someday someone should fix that.
Ok, I'm blind. Where in h**l do I find the SQLSTATE from a PGconn?!?
Regards,
Andreas
"Dave Page" <dpage@vale-housing.co.uk> writes:
I don't see that much of a problem with having createdb etc. hardwire
postgres instead of template1 as the db-to-connect-to.
OK, new patch posted to -patches that updates all the utilities as well.
I'm going to apply this this evening (ie, before any of the code drifts
under it) unless I hear squawks in the next hour or so, or find some
fatal problem while reviewing.
The only change I didn't make was in line 3458 (in StartChildProcess) of
postmaster.c - template1 seemed the more sensible option to leave there.
Let me know if you disagree and I'll change it :-)
Will look at it. I'm sure there are a few references that *should* be
template1 ...
regards, tom lane
"Dave Page" <dpage@vale-housing.co.uk> writes:
OK, new patch posted to -patches that updates all the utilities as well.
Applied.
One thing that neither Dave nor I wanted to touch is pg_autovacuum.
If that gets integrated into the backend by feature freeze then the
question is moot, but if it doesn't then we'll have to decide whether
autovac should preferentially connect to template1 or postgres. Neither
choice seems real appealing to me: if autovac connects to template1
then it could interfere with CREATE DATABASE, but if it connects to
postgres then it could fail if postgres isn't there.
Now the latter does not bother me if autovac is considered a client,
but it does bother me if autovac is considered part of the backend.
I think that template1 and template0 can reasonably be considered
special from the point of view of the backend --- but I really don't
want postgres to be special in that way.
Another point is that Dave added code to pg_dumpall to not dump the
postgres database. This seems mistaken to me, so I did not include it
in the applied patch: if someone is doing real work in postgres then
they'll be pretty annoyed if it's not backed up. But perhaps the
question needs debate.
Any thoughts?
regards, tom lane
Tom Lane wrote:
Another point is that Dave added code to pg_dumpall to not dump the
postgres database. This seems mistaken to me, so I did not include it
in the applied patch: if someone is doing real work in postgres then
they'll be pretty annoyed if it's not backed up. But perhaps the
question needs debate.Any thoughts?
You are correct, in my opinion. If one is allowed to add objects to the
"postgres" database, than it must obviously be backuped. Otherwise this is
just another way to shoot yourself in the foot. From an "outsiders" point of
view, the postgres database could just look like roots home directory in
/root,... would you exclude that from backups?
Best Regards,
Michael Paesold
On Tuesday 21 June 2005 00:12, Tom Lane wrote:
"Dave Page" <dpage@vale-housing.co.uk> writes:
OK, new patch posted to -patches that updates all the utilities as well.
If I read the code correctly, the database name will be hardwired to
"postgres" regardless of the default super user name correct?
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
-----Original Message-----
From: Robert Treat [mailto:xzilla@users.sourceforge.net]
Sent: 21 June 2005 08:10
To: Tom Lane
Cc: Dave Page; Andrew Dunstan; Andreas Pflug; Magnus
Hagander; pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] [PATCHES] default database creation with initdbOn Tuesday 21 June 2005 00:12, Tom Lane wrote:
"Dave Page" <dpage@vale-housing.co.uk> writes:
OK, new patch posted to -patches that updates all the
utilities as well.
If I read the code correctly, the database name will be hardwired to
"postgres" regardless of the default super user name correct?
Yes - that's intentional so that pgAdmin/phpPgAdmin et al. can
reasonably expect it to be there.
Regards, Dave
Import Notes
Resolved by subject fallback
Yes - that's intentional so that pgAdmin/phpPgAdmin et al. can
reasonably expect it to be there.
Problem is, how the hell do I know it's there before I connect?
Chris
-----Original Message-----
From: Christopher Kings-Lynne [mailto:chriskl@familyhealth.com.au]
Sent: 21 June 2005 08:57
To: Dave Page
Cc: Robert Treat; Tom Lane; Andrew Dunstan; Andreas Pflug;
Magnus Hagander; pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] [PATCHES] default database creation with initdbYes - that's intentional so that pgAdmin/phpPgAdmin et al. can
reasonably expect it to be there.Problem is, how the hell do I know it's there before I connect?
Well obviously you don't (any more than you know that template1 is
accessible until you try), but in time it will be on more and more
systems as people upgrade by when it will make a reasonable default for
clients.
Alternatively, try to connect to it first, and then fall back to
template1 (much as libpq negotiates protocol versions with the server).
Regards, Dave
Import Notes
Resolved by subject fallback
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: 21 June 2005 05:13
To: Dave Page
Cc: Andrew Dunstan; Andreas Pflug; Robert Treat; Magnus
Hagander; pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] [PATCHES] default database creation
with initdb"Dave Page" <dpage@vale-housing.co.uk> writes:
OK, new patch posted to -patches that updates all the
utilities as well.
Applied.
Thanks.
Another point is that Dave added code to pg_dumpall to not dump the
postgres database. This seems mistaken to me, so I did not include it
in the applied patch: if someone is doing real work in postgres then
they'll be pretty annoyed if it's not backed up. But perhaps the
question needs debate.Any thoughts?
My reading of that code was that I merely stopped it dumping the CREATE
DATABASE statement (and the ACL) for the database, /not/ the actual
contents - in the same way as is done for template1. The theory being
that if you are reloading from into a freshing initdb'ed cluster,
postgres will already exist so doesn't need to be recreated.
Regards, Dave
Import Notes
Resolved by subject fallback
Tom Lane wrote:
One thing that neither Dave nor I wanted to touch is pg_autovacuum.
If that gets integrated into the backend by feature freeze then the
question is moot, but if it doesn't then we'll have to decide whether
autovac should preferentially connect to template1 or postgres. Neither
choice seems real appealing to me: if autovac connects to template1
then it could interfere with CREATE DATABASE, but if it connects to
postgres then it could fail if postgres isn't there.Now the latter does not bother me if autovac is considered a client,
but it does bother me if autovac is considered part of the backend.
I think that template1 and template0 can reasonably be considered
special from the point of view of the backend --- but I really don't
want postgres to be special in that way.
I'm still hoping that autovac will get integrated so this will be moot,
but just in case.....
Perhaps pg_autovacuum should try to connect to the postgres database and
if the connection fails, then it will try to connect to template1. This
way autovacuum will work whether the postgres database is there or not.
On Tuesday 21 June 2005 04:01, Dave Page wrote:
-----Original Message-----
From: Christopher Kings-Lynne [mailto:chriskl@familyhealth.com.au]
Sent: 21 June 2005 08:57
To: Dave Page
Cc: Robert Treat; Tom Lane; Andrew Dunstan; Andreas Pflug;
Magnus Hagander; pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] [PATCHES] default database creation with initdbYes - that's intentional so that pgAdmin/phpPgAdmin et al. can
reasonably expect it to be there.Problem is, how the hell do I know it's there before I connect?
Well obviously you don't (any more than you know that template1 is
accessible until you try), but in time it will be on more and more
systems as people upgrade by when it will make a reasonable default for
clients.Alternatively, try to connect to it first, and then fall back to
template1 (much as libpq negotiates protocol versions with the server).
You know, since we don't maintain static connections (http is our friend)
connecting to template1 really isn't a problem for phppgadmin users. At
least I can't remember anyone ever having complained about it.
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
"Dave Page" <dpage@vale-housing.co.uk> writes:
Another point is that Dave added code to pg_dumpall to not dump the
postgres database.
My reading of that code was that I merely stopped it dumping the CREATE
DATABASE statement (and the ACL) for the database, /not/ the actual
contents - in the same way as is done for template1. The theory being
that if you are reloading from into a freshing initdb'ed cluster,
postgres will already exist so doesn't need to be recreated.
D'oh ... you're right of course. Will fix (and add some comments).
I wonder though if this code isn't a little broken. It should skip the
CREATE DATABASE certainly, but what about the ACL and dumpDatabaseConfig
parts? I suspect those got added in at a handy place without enough
thought taken as to whether they should be excluded for template1.
regards, tom lane
Robert Treat <xzilla@users.sourceforge.net> writes:
You know, since we don't maintain static connections (http is our friend)
connecting to template1 really isn't a problem for phppgadmin users. At
least I can't remember anyone ever having complained about it.
Sure you have: people have complained about CREATE DATABASE failing with
"source database "template1" is being accessed by other users" often
enough.
regards, tom lane
Tom Lane wrote:
Robert Treat <xzilla@users.sourceforge.net> writes:
You know, since we don't maintain static connections (http is our friend)
connecting to template1 really isn't a problem for phppgadmin users. At
least I can't remember anyone ever having complained about it.Sure you have: people have complained about CREATE DATABASE failing with
"source database "template1" is being accessed by other users" often
enough.
And other GUIs certainly do keep nailed up connections.
cheers
andrew
On Tuesday 21 June 2005 10:04, Tom Lane wrote:
Robert Treat <xzilla@users.sourceforge.net> writes:
You know, since we don't maintain static connections (http is our friend)
connecting to template1 really isn't a problem for phppgadmin users. At
least I can't remember anyone ever having complained about it.Sure you have: people have complained about CREATE DATABASE failing with
"source database "template1" is being accessed by other users" often
enough.
I meant wrt phppgadmin.... for us theres no real need to change the default
connection. obviously others will differ.
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL