Thread: Problem with createdb

Hi,

have you done any modification to your pg_hba.conf ?


Durgaprasad Pawar a écrit :
> Hi,
>     I am experiencing a strange problem with createdb. It gives
> following error message.
>
> ***********
> bin # ./createdb -U dpgres -p 9999 -h 127.0.0.1 -q mydb
> createdb: could not connect to database postgres: could not connect to
> server: No route to host
> Is the server running on host "127.0.0.1" and accepting
> TCP/IP connections on port 9999?
> ************
>
> However, when I remove the -h option, it works smoothly.
>
> ************
> bin # ./createdb -U dpgres -p 9999 mydb
> CREATE DATABASE
> ************
>
> The changes in postgresql.conf file are:
>
> shared_buffers = 300MB
> work_mem = 150MB
>
> My system has 2 GB of RAM.
>
> The parameters in /etc/system are as follows.
>
> set msgsys:msginfo_msgmnb=65536
> set msgsys:msginfo_msgtql=1024
> set shmsys:shminfo_shmmax=0x15E00000
> set shmsys:shminfo_shmmin=1
> set shmsys:shminfo_shmmni=256
> set shmsys:shminfo_shmseg=256
> set semsys:seminfo_semmap=256
> set semsys:seminfo_semmni=512
> set semsys:seminfo_semmns=512
> set semsys:seminfo_semmsl=32
>
>
> Can anybody help me understand what the problem might be?
>
>
> Regards,
> DP
>
> ------------------------------------------------------------------------
> Forgot the famous last words? Access your message archive online.
> Click here.
> <http://in.rd.yahoo.com/tagline_webmessenger_4/*http://in.messenger.yahoo.com/webmessengerpromo.php>


--
-- Jean Arnaud
-- PhD student, SARDES project
-- INRIA Rhône-Alpes / LIG
-- http://sardes.inrialpes.fr/~jarnaud

Durgaprasad Pawar <durgaprasad.pawar@yahoo.co.in> writes:
> bin # ./createdb -U dpgres -p 9999 -h 127.0.0.1 -q mydb
> createdb: could not connect to database postgres: could not
> connect to server: No route to host

"No route to host" for 127.0.0.1 ???

There's something seriously hosed about your networking configuration.
This isn't a Postgres-specific problem --- no doubt you'll find that any
TCP-based connection fails similarly, eg telnet.  Since you didn't
mention what platform this is, there's not much help we can give, but
in any case you should find an OS-specific help list for it.

            regards, tom lane

On Oct 31, 2007, at 9:07 , Wright, George wrote:

> The OS is Suse 10.2, timezone files have been updated with the
> files in tzdata2007h.tar.gz.

What version of PostgreSQL? I believe in recent versions PostgreSQL
uses its own time zone library, not the system time zone files.
> The settings on the box are Brazil East. The hour of 2007-10-14
> 00:00:00 to 2007-10-14 00:59:59 does not exist. In that part of
> Brazil they move the clock from midnight to 1am as they step
> forward into DST.

Time zones and DST are tricky. I believe PostgreSQL takes the input and

> myhost=> select isfinite(timestamp '2007-10-14 00:00:00 BRST');
>
>  isfinite
>
> ----------
>
>  t
>
> (1 row)

Out of curiosity, what would you expect it to return? AIUI, isfinite
just returns true or false if the timestamp is finite or not, not
whether or not it's a valid time.

test=# select isfinite('infinity'::timestamp with time zone);
isfinite
----------
f
(1 row)

test=# select isfinite('-infinity'::timestamp with time zone);
isfinite
----------
f
(1 row)

test=# select isfinite('2007-10-14 00:00:00'::timestamp with time zone);
isfinite
----------
t
(1 row)

Timestamps with time zones are stored internally at UTC: display with
time zone is determined by client settings (which default to the
server time zone setting). I don't think PostgreSQL will reject a
timestamp string just because it's an hour that's skipped due to a
DST change: it'll convert it to UTC and display it in the appropriate
time zone for the client. For example, there was a DST shift at
2006-04-02 02:00:00 in US/Central:

test=# show time zone;
   TimeZone
------------
US/Central
(1 row)

test=# select '2006-04-02 01:00:00'::timestamp with time zone;
       timestamptz
------------------------
2006-04-02 01:00:00-06
(1 row)

test=# select '2006-04-02 02:00:00'::timestamp with time zone;
       timestamptz
------------------------
2006-04-02 03:00:00-05
(1 row)

test=# select '2006-04-02 02:30:00'::timestamp with time zone;
       timestamptz
------------------------
2006-04-02 03:30:00-05
(1 row)

While officially the hour between 02:00 and 03:00 was officially
skipped, PostgreSQL takes the input and converts it appropriately.
Note it displays the timestamp correctly, taking into account the DST
shift.
> badger=> select isfinite(date '2007-10-14 24:00:01');
>
> ERROR:  date/time field value out of range: "2007-10-14 24:00:01"

What time is 24:00:01? I believe that's your problem. It's not a
valid input format for a timestamp.

test=# select '2007-10-14 24:00:00'::date;
     date
------------
2007-10-14
(1 row)

test=# select '2007-10-14 24:00:01'::date;
ERROR:  date/time field value out of range: "2007-10-14 24:00:01"

You've got three different, unconnected issues here: 1) I doubt
PostgreSQL is using the time zone files you think it is; 2)
PostgreSQL doesn't store time zone information; 3) isfinite doesn't
have anything to do with the issue you're seeing.

Michael Glaesemann
grzm seespotcode net

The version is 8.1.5

I would expect:
myhost=> select isfinite(timestamp '2007-10-14 00:00:00 BRST');

to error out just like:

myhost=> select isfinite(date '2007-02-29 00:00:01');
ERROR:  date/time field value out of range: "2007-02-29 00:00:01"

as there really shouldn't be an epoch representation of a non-existent
time. The next epoch second after 2007-10-13 23:59:59 is 2007-10-14
01:00:00, not 2007-10-14 00:00:00 in this time zone.

As for PostGreSQL using the correct files, I copied the timezone files
to both the OS and the Postgres locations: (and rebooted of course)

/usr/share/zoneinfo
/usr/share/postgresql/timezone  (I assume these are used by PG)

Running the following queries shows the time zone change appropriately
to reflect DST:

myhost=> select timestamp with time zone '2007-10-13 23:59:59';
      timestamptz
------------------------
 2007-10-13 23:59:59-03
(1 row)

myhost=> select timestamp with time zone '2007-10-14 01:00:01';
      timestamptz
------------------------
 2007-10-14 01:00:01-02
(1 row)

myhost=> select timestamp with time zone '2007-10-13 23:59:59 BRT';
      timestamptz
------------------------
 2007-10-13 23:59:59-03
(1 row)

myhost=> select timestamp with time zone '2007-10-14 01:00:01 BRST';
      timestamptz
------------------------
 2007-10-14 01:00:01-02
(1 row)

This was not the case before I installed the time zone files.
I believe this shows that the time zone files are installed correctly
for PostGreSQL.

I guess I assumed that since '2007-10-14 00:00:00 BRST' or '2007-10-14
00:00:00 BRT' are invalid, that the conversion to internal epoch seconds
would fail. The name of the function isfinite is a bit vague. Maybe as
you suggest, its purpose is not really to do a calendar validation.



-----Original Message-----
From: Michael Glaesemann [mailto:grzm@seespotcode.net]
Sent: Wednesday, October 31, 2007 11:11 AM
To: Wright, George
Cc: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] timestamp problem


On Oct 31, 2007, at 9:07 , Wright, George wrote:

> The OS is Suse 10.2, timezone files have been updated with the
> files in tzdata2007h.tar.gz.

What version of PostgreSQL? I believe in recent versions PostgreSQL
uses its own time zone library, not the system time zone files.
> The settings on the box are Brazil East. The hour of 2007-10-14
> 00:00:00 to 2007-10-14 00:59:59 does not exist. In that part of
> Brazil they move the clock from midnight to 1am as they step
> forward into DST.

Time zones and DST are tricky. I believe PostgreSQL takes the input and

> myhost=> select isfinite(timestamp '2007-10-14 00:00:00 BRST');
>
>  isfinite
>
> ----------
>
>  t
>
> (1 row)

Out of curiosity, what would you expect it to return? AIUI, isfinite
just returns true or false if the timestamp is finite or not, not
whether or not it's a valid time.

test=# select isfinite('infinity'::timestamp with time zone);
isfinite
----------
f
(1 row)

test=# select isfinite('-infinity'::timestamp with time zone);
isfinite
----------
f
(1 row)

test=# select isfinite('2007-10-14 00:00:00'::timestamp with time zone);
isfinite
----------
t
(1 row)

Timestamps with time zones are stored internally at UTC: display with
time zone is determined by client settings (which default to the
server time zone setting). I don't think PostgreSQL will reject a
timestamp string just because it's an hour that's skipped due to a
DST change: it'll convert it to UTC and display it in the appropriate
time zone for the client. For example, there was a DST shift at
2006-04-02 02:00:00 in US/Central:

test=# show time zone;
   TimeZone
------------
US/Central
(1 row)

test=# select '2006-04-02 01:00:00'::timestamp with time zone;
       timestamptz
------------------------
2006-04-02 01:00:00-06
(1 row)

test=# select '2006-04-02 02:00:00'::timestamp with time zone;
       timestamptz
------------------------
2006-04-02 03:00:00-05
(1 row)

test=# select '2006-04-02 02:30:00'::timestamp with time zone;
       timestamptz
------------------------
2006-04-02 03:30:00-05
(1 row)

While officially the hour between 02:00 and 03:00 was officially
skipped, PostgreSQL takes the input and converts it appropriately.
Note it displays the timestamp correctly, taking into account the DST
shift.
> badger=> select isfinite(date '2007-10-14 24:00:01');
>
> ERROR:  date/time field value out of range: "2007-10-14 24:00:01"

What time is 24:00:01? I believe that's your problem. It's not a
valid input format for a timestamp.

test=# select '2007-10-14 24:00:00'::date;
     date
------------
2007-10-14
(1 row)

test=# select '2007-10-14 24:00:01'::date;
ERROR:  date/time field value out of range: "2007-10-14 24:00:01"

You've got three different, unconnected issues here: 1) I doubt
PostgreSQL is using the time zone files you think it is; 2)
PostgreSQL doesn't store time zone information; 3) isfinite doesn't
have anything to do with the issue you're seeing.

Michael Glaesemann
grzm seespotcode net

On Oct 31, 2007, at 10:39 , Wright, George wrote:

> myhost=> select isfinite(date '2007-02-29 00:00:01');
> ERROR:  date/time field value out of range: "2007-02-29 00:00:01"

I can see where you're coming from, but I don't necessarily agree.
2007-02-29 is an invalid date, regardless of time zone. 24:00:01 is
an invalid time regardless of time zone. Perhaps this is a question
of not being strict enough on input, but given how often DST and time
zone rules change, I don't think I'd want a situation where
PostgreSQL might accept a particular string as a valid timestamp one
day and reject it the next due to an update of time zone data files.

> as there really shouldn't be an epoch representation of a non-existent
> time. The next epoch second after 2007-10-13 23:59:59 is 2007-10-14
> 01:00:00, not 2007-10-14 00:00:00 in this time zone.

There's no such thing as an epoch second with respect to a time zone.

> This was not the case before I installed the time zone files.
> I believe this shows that the time zone files are installed correctly
> for PostGreSQL.

Well, that would make it appear that what you did properly updated
the time zone data for PostgreSQL. But as you noted, the PostgreSQL
time zone files are different from the systems.

> The name of the function isfinite is a bit vague. Maybe as
> you suggest, its purpose is not really to do a calendar validation.

My question is why did you even think it did? The docs (and the
function name) are pretty clear what it does:

isfinite(timestamp)    boolean    Test for finite time stamp (not equal to
infinity)    isfinite(timestamp '2001-02-16 21:28:30')    true

http://www.postgresql.org/docs/8.2/interactive/functions-
datetime.html#FUNCTIONS-DATETIME-TABLE

Michael Glaesemann
grzm seespotcode net

Fair enough.

In the Bruce Momjian PostGreSQL Introduction and Concepts book:

In the temporal section it mentions

Function            Returns
isfinite(col)        BOOLEAN indicating whether col is a valid date.

That seems to be where I went wrong.

Is there a calendar validation routine in PostGreSQL? I didn't see one
in the \df listing.



-----Original Message-----
From: Michael Glaesemann [mailto:grzm@seespotcode.net]
Sent: Wednesday, October 31, 2007 12:05 PM
To: Wright, George
Cc: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] timestamp problem


On Oct 31, 2007, at 10:39 , Wright, George wrote:

> myhost=> select isfinite(date '2007-02-29 00:00:01');
> ERROR:  date/time field value out of range: "2007-02-29 00:00:01"

I can see where you're coming from, but I don't necessarily agree.
2007-02-29 is an invalid date, regardless of time zone. 24:00:01 is
an invalid time regardless of time zone. Perhaps this is a question
of not being strict enough on input, but given how often DST and time
zone rules change, I don't think I'd want a situation where
PostgreSQL might accept a particular string as a valid timestamp one
day and reject it the next due to an update of time zone data files.

> as there really shouldn't be an epoch representation of a non-existent
> time. The next epoch second after 2007-10-13 23:59:59 is 2007-10-14
> 01:00:00, not 2007-10-14 00:00:00 in this time zone.

There's no such thing as an epoch second with respect to a time zone.

> This was not the case before I installed the time zone files.
> I believe this shows that the time zone files are installed correctly
> for PostGreSQL.

Well, that would make it appear that what you did properly updated
the time zone data for PostgreSQL. But as you noted, the PostgreSQL
time zone files are different from the systems.

> The name of the function isfinite is a bit vague. Maybe as
> you suggest, its purpose is not really to do a calendar validation.

My question is why did you even think it did? The docs (and the
function name) are pretty clear what it does:

isfinite(timestamp)    boolean    Test for finite time stamp (not equal to

infinity)    isfinite(timestamp '2001-02-16 21:28:30')    true

http://www.postgresql.org/docs/8.2/interactive/functions-
datetime.html#FUNCTIONS-DATETIME-TABLE

Michael Glaesemann
grzm seespotcode net

[Please don't top post as it makes the discussion more difficult to
follow.]

On Oct 31, 2007, at 12:40 , Wright, George wrote:

> In the Bruce Momjian PostGreSQL Introduction and Concepts book:

Yeah, that's getting pretty long in the tooth. (And by the way, it's
PostgreSQL. No cap G)

>
> In the temporal section it mentions
>
> Function            Returns
> isfinite(col)        BOOLEAN indicating whether col is a valid date.

I'd argue that this has always been wrong, but my PostgreSQL
knowledge doesn't extend quite that far back.

> Is there a calendar validation routine in PostGreSQL? I didn't see one
> in the \df listing.

Depends on what you mean by calendar validation. For the specific
case you've been interested in, I doubt it, though you could probably
cobble one together using a procedural language. Maybe PL/perlu and
some Perl libraries would help you out.


Michael Glaesemann
grzm seespotcode net

"Wright, George" <George.Wright@infimatic.com> writes:
> I would expect:
> myhost=> select isfinite(timestamp '2007-10-14 00:00:00 BRST');
> to error out just like:

No, this is intentional behavior.  Note the comment in
DetermineTimeZoneOffset:

    /*
     * It's an invalid or ambiguous time due to timezone transition. Prefer
     * the standard-time interpretation.
     */

BTW, isfinite() has got nothing whatever to do with this.  If an error
were to be thrown, it would have to be during timestamp value input.

            regards, tom lane

Michael Glaesemann <grzm@seespotcode.net> writes:
> On Oct 31, 2007, at 12:40 , Wright, George wrote:
>> In the temporal section it mentions
>> Function            Returns
>> isfinite(col)        BOOLEAN indicating whether col is a valid date.

> I'd argue that this has always been wrong, but my PostgreSQL
> knowledge doesn't extend quite that far back.

Yeah, the definition of the function has always been the same as it is
now: it just checks for the special timestamp values "+infinity" and
"-infinity".  If you cross your eyes suitably maybe you could make an
argument that the above description is not incorrect, but I'd agree that
it's not very good.

>> Is there a calendar validation routine in PostGreSQL? I didn't see one
>> in the \df listing.

> Depends on what you mean by calendar validation. For the specific
> case you've been interested in, I doubt it, though you could probably
> cobble one together using a procedural language. Maybe PL/perlu and
> some Perl libraries would help you out.

In the Postgres design, the sort of validation George is interested
in is done by the timestamp datatype's input routine: anything that
has managed to get stored in the database is a valid value, by
definition.  So a "validation test" would consist of something like
trying to cast a string to timestamp and seeing if it throws an error.
Unfortunately, there's no provision for "I disagree with this datatype's
validation rules", other than writing your own datatype or hacking the
source code...

            regards, tom lane