Thread: Optional postgres database not so optional in 8.1

Optional postgres database not so optional in 8.1

From
"John Hansen"
Date:
On a fresh installation of postgrsql 8.1    if you drop the
'postgres' database,
psql, createdb, etc. no longer works.

psql -l; ignores -dtemplate1, and createdb doesn't have such an option.

Maybe it should fallback to template1 if the postgres database doesn't
exist?

... John



Re: Optional postgres database not so optional in 8.1

From
Martijn van Oosterhout
Date:
On Fri, Nov 18, 2005 at 12:01:31AM +1100, John Hansen wrote:
> On a fresh installation of postgrsql 8.1    if you drop the
> 'postgres' database,
> psql, createdb, etc. no longer works.
>
> psql -l; ignores -dtemplate1, and createdb doesn't have such an option.

What distribution? I've never seen this "postgres" database you speak
of. It certainly not on any systems I've used. Do you have a PGDATABASE
variable in your environment?

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

Re: Optional postgres database not so optional in 8.1

From
"Michael Paesold"
Date:
Martijn van Oosterhout wrote:
> What distribution? I've never seen this "postgres" database you speak
> of. It certainly not on any systems I've used.

It's new in 8.1 and is used as the default connection database for createdb, 
etc.

Best Regards,
Michael Paesold




Re: Optional postgres database not so optional in 8.1

From
Andrew Dunstan
Date:

Martijn van Oosterhout wrote:

>On Fri, Nov 18, 2005 at 12:01:31AM +1100, John Hansen wrote:
>  
>
>>On a fresh installation of postgrsql 8.1    if you drop the
>>'postgres' database,
>>psql, createdb, etc. no longer works.
>>
>>psql -l; ignores -dtemplate1, and createdb doesn't have such an option.
>>    
>>
>
>What distribution? I've never seen this "postgres" database you speak
>of. It certainly not on any systems I've used. Do you have a PGDATABASE
>variable in your environment?
>
>  
>

The 8.1 release notes have this:
   *
     Make initdb create a new standard database called postgres, and     convert utilities to use postgres rather than
template1for     standard lookups (Dave)
 
     In prior releases, template1 was used both as a default connection     for utilities like createuser, and as a
templatefor new     databases. This caused CREATE DATABASE to sometimes fail, because     a new database cannot be
createdif anyone else is in the template     database. With this change, the default connection database is now
postgres,meaning it is much less likely someone will be using     template1 during CREATE DATABASE.
 


cheers

andrew




Re: Optional postgres database not so optional in 8.1

From
Jaime Casanova
Date:
On 11/17/05, Martijn van Oosterhout <kleptog@svana.org> wrote:
> On Fri, Nov 18, 2005 at 12:01:31AM +1100, John Hansen wrote:
> > On a fresh installation of postgrsql 8.1      if you drop the
> > 'postgres' database,
> > psql, createdb, etc. no longer works.
> >
> > psql -l; ignores -dtemplate1, and createdb doesn't have such an option.
>
> What distribution? I've never seen this "postgres" database you speak
> of. It certainly not on any systems I've used. Do you have a PGDATABASE
> variable in your environment?
>
> Have a nice day,
> --
> Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> > tool for doing 5% of the work and then sitting around waiting for someone
> > else to do the other 95% so you can sue them.
>
>

That database exists beginning with 8.1... I don't think it's optional...

i remember a comments from Tom telling that this database will be used
to connect to for CREATE DATABASE porpouses:

http://archives.postgresql.org/pgsql-committers/2005-06/msg00302.php

--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)


Re: Optional postgres database not so optional in 8.1

From
Tom Lane
Date:
"John Hansen" <john@geeknet.com.au> writes:
> Maybe it should fallback to template1 if the postgres database doesn't
> exist?

[ shrug... ]  template1 can be dropped, too.  More to the point, if
they fall back to connecting to template1, then we lose the very thing
we wanted to gain, which is that there aren't transient connections to
template1 that might draw CREATE DATABASE's ire.

None of these programs are essential -- you can always do "psql mydb"
and then execute the operations from within mydb -- so I don't see a
pressing need to complicate them to deal with nonstandard environments.
        regards, tom lane


Re: Optional postgres database not so optional in 8.1

From
"Joshua D. Drake"
Date:
John Hansen wrote:
> On a fresh installation of postgrsql 8.1    if you drop the
> 'postgres' database,
> psql, createdb, etc. no longer works.
>
> psql -l; ignores -dtemplate1, and createdb doesn't have such an option.
>   
I can't test createdb here but on the psql... what happens if you just:

psql -Upostgres template1?



> Maybe it should fallback to template1 if the postgres database doesn't
> exist?
>
> ... John
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match
>   



Re: Optional postgres database not so optional in 8.1

From
"Michael Paesold"
Date:
Joshua D. Drake wrote:

>> psql -l; ignores -dtemplate1, and createdb doesn't have such an option.
>>
> I can't test createdb here but on the psql... what happens if you just:
>
> psql -Upostgres template1?

It's the '-l' option (list all databases) that does not honor the database 
given on the command line.

This does not work, if the postgres database is dropped in 8.1:

psql -l template1
psql -l -d template1

of course "psql template1" will just work fine.

Best Regards,
Michael Paesold 




Re: Optional postgres database not so optional in 8.1

From
Tom Lane
Date:
"Michael Paesold" <mpaesold@gmx.at> writes:
> It's the '-l' option (list all databases) that does not honor the database 
> given on the command line.
> This does not work, if the postgres database is dropped in 8.1:

> psql -l template1

It does seem a bit inconsistent that psql wouldn't connect to the
specified database in order to do -l, if one is specified.
Anyone want to look and see if it's easy to change?
        regards, tom lane


Re: Optional postgres database not so optional in 8.1

From
Andrew Dunstan
Date:

Tom Lane wrote:

>"Michael Paesold" <mpaesold@gmx.at> writes:
>  
>
>>It's the '-l' option (list all databases) that does not honor the database 
>>given on the command line.
>>This does not work, if the postgres database is dropped in 8.1:
>>    
>>
>
>  
>
>>psql -l template1
>>    
>>
>
>It does seem a bit inconsistent that psql wouldn't connect to the
>specified database in order to do -l, if one is specified.
>Anyone want to look and see if it's easy to change?
>  
>


The relevant code in startup.c reads:

       pset.db = PQsetdbLogin(options.host, options.port, NULL, NULL,           options.action == ACT_LIST_DB ?
"postgres": options.dbname,                              username, password);
 


I think we could probably just change the expression to something like:
      options.action == ACT_LIST_DB  && options.dbname == NULL ? 
"postgres" : options.dbname


cheers

andrew



Re: Optional postgres database not so optional in 8.1

From
Philip Yarra
Date:
On Fri, 18 Nov 2005 05:29 am, Tom Lane wrote:
> It does seem a bit inconsistent that psql wouldn't connect to the
> specified database in order to do -l, if one is specified.
> Anyone want to look and see if it's easy to change?

It also breaks the ability to psql -l against older installations: e.g. this 
is psql 8.1 trying to list databases on a 8.0.3 DB server:

$ psql -l -hdev2 -dtemplate1
psql: FATAL:  database "postgres" does not exist

I'm told we never promise to make \l and \d work across client/server 
versions, but if this is related, it'd be nice if the fix made this work 
again too.

Regards, Philip.
-- 

"Debugging is twice as hard as writing the code in the first place.
Therefore, if you write the code as cleverly as possible, you are,
by definition, not smart enough to debug it." - Brian W. Kernighan

-----------------
Utiba Pty Ltd 
This message has been scanned for viruses and
dangerous content by Utiba mail server and is 
believed to be clean.



Re: Optional postgres database not so optional in 8.1

From
Andrew Dunstan
Date:

Andrew Dunstan wrote:

>>
>> It does seem a bit inconsistent that psql wouldn't connect to the
>> specified database in order to do -l, if one is specified.
>> Anyone want to look and see if it's easy to change?
>>  
>>
>
>
>       options.action == ACT_LIST_DB  && options.dbname == NULL ? 
> "postgres" : options.dbname
>

Tested, and worked fine.

Committed on Head and 8.1 branches.

cheers

andrew


Re: Optional postgres database not so optional in 8.1

From
Andrew Dunstan
Date:
I now notice that "pg_ctl -w start" fails if the postgres db is missing. 
I am not sure that changing pg_ctl to use this rather than template1 was 
a good thing, and it can't be overridden. I suggest we revert that 
particular change - it seems to me to confer little to no benefit, 
unlike the case with createdb etc.

cheers

andrew



Andrew Dunstan wrote:

>
>
> Andrew Dunstan wrote:
>
>>>
>>> It does seem a bit inconsistent that psql wouldn't connect to the
>>> specified database in order to do -l, if one is specified.
>>> Anyone want to look and see if it's easy to change?
>>>  
>>>
>>
>>
>>       options.action == ACT_LIST_DB  && options.dbname == NULL ? 
>> "postgres" : options.dbname
>>
>
> Tested, and worked fine.
>
> Committed on Head and 8.1 branches.
>
> cheers
>
> andrew
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>


Re: Optional postgres database not so optional in 8.1

From
Andrew Dunstan
Date:

Tom Lane wrote:

>Andrew Dunstan <andrew@dunslane.net> writes:
>  
>
>>I now notice that "pg_ctl -w start" fails if the postgres db is missing. 
>>I am not sure that changing pg_ctl to use this rather than template1 was 
>>a good thing, and it can't be overridden. I suggest we revert that 
>>particular change - it seems to me to confer little to no benefit, 
>>unlike the case with createdb etc.
>>    
>>
>
>pg_ctl -w is already incredibly fragile because it needs a working
>password-free login name.  Rather than worrying about whether the
>database name exists, what we ought to do is invent the long-awaited
>"ping" extension to the postmaster protocol --- something that would
>just ask "are you up and ready to accept connections" without having
>to specify a valid user *or* database name.
>
>You can sort of do this today if you are willing to examine the error
>message that comes back from the postmaster, but I think it'd be cleaner
>to have an official protocol extension.
>  
>


Actually, it looks like pg_ctl already does this:
       if ((conn = PQsetdbLogin(NULL, portstr, NULL, NULL,                                "postgres", NULL, NULL)) !=
NULL&&           (PQstatus(conn) == CONNECTION_OK ||            (strcmp(PQerrorMessage(conn),
PQnoPasswordSupplied)== 0)))       {           PQfinish(conn);           success = true;           break;       }
 


cheers

andrew



Re: Optional postgres database not so optional in 8.1

From
Tom Lane
Date:
Andrew Dunstan <andrew@dunslane.net> writes:
> I now notice that "pg_ctl -w start" fails if the postgres db is missing. 
> I am not sure that changing pg_ctl to use this rather than template1 was 
> a good thing, and it can't be overridden. I suggest we revert that 
> particular change - it seems to me to confer little to no benefit, 
> unlike the case with createdb etc.

pg_ctl -w is already incredibly fragile because it needs a working
password-free login name.  Rather than worrying about whether the
database name exists, what we ought to do is invent the long-awaited
"ping" extension to the postmaster protocol --- something that would
just ask "are you up and ready to accept connections" without having
to specify a valid user *or* database name.

You can sort of do this today if you are willing to examine the error
message that comes back from the postmaster, but I think it'd be cleaner
to have an official protocol extension.
        regards, tom lane


Re: Optional postgres database not so optional in 8.1

From
Andrew Dunstan
Date:
I never saw a followup to this. Is someone working on a ping protocol 
extension, or should we revert pg_ctl to using template1 on the ground 
that it does a poor man's ping anyway?

cheers

andrew

Andrew Dunstan wrote:

>
>
> Tom Lane wrote:
>
>> Andrew Dunstan <andrew@dunslane.net> writes:
>>  
>>
>>> I now notice that "pg_ctl -w start" fails if the postgres db is 
>>> missing. I am not sure that changing pg_ctl to use this rather than 
>>> template1 was a good thing, and it can't be overridden. I suggest we 
>>> revert that particular change - it seems to me to confer little to 
>>> no benefit, unlike the case with createdb etc.
>>>   
>>
>>
>> pg_ctl -w is already incredibly fragile because it needs a working
>> password-free login name.  Rather than worrying about whether the
>> database name exists, what we ought to do is invent the long-awaited
>> "ping" extension to the postmaster protocol --- something that would
>> just ask "are you up and ready to accept connections" without having
>> to specify a valid user *or* database name.
>>
>> You can sort of do this today if you are willing to examine the error
>> message that comes back from the postmaster, but I think it'd be cleaner
>> to have an official protocol extension.
>>  
>>
>
>
> Actually, it looks like pg_ctl already does this:
>
>        if ((conn = PQsetdbLogin(NULL, portstr, NULL, NULL,
>                                 "postgres", NULL, NULL)) != NULL &&
>            (PQstatus(conn) == CONNECTION_OK ||
>             (strcmp(PQerrorMessage(conn),
>                     PQnoPasswordSupplied) == 0)))
>        {
>            PQfinish(conn);
>            success = true;
>            break;
>        }
>
>