Best practices: Handling Daylight-saving time

On Fri, Mar 11, 2005 at 03:25:28PM +0100, Együd Csaba wrote:

Hi All,
I'd like to ask your opininon about how to handle DST on an 7/24 system.
Where should it be handled: on the server side or on the client side? And
how could I (at all could I???) make it transparent?

Or we must bow to the fact that twice a year there are two unusable hours?
If it cannot be solved technically, than it is acceptable, but if there is a
chance to do it, I'd like to try it.

I guess the same way computers have been handling it for years: store
time as "seconds since epoch" and let the user interface handle the
translation. Timezone files for anywhere in the world are available as
well as routines for converting seconds since epoch to localtime and
vice-versa.

Hope this helps,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

On Fri, Mar 11, 2005 at 15:25:28 +0100,
Egy�d Csaba <csegyud@vnet.hu> wrote:

Hi All,
I'd like to ask your opininon about how to handle DST on an 7/24 system.

My advice would be to use GMT and not have to worry about DST while
collecting data. When displaying data you might convert the timestamps
to localtime if that is useful for people using the data.

csegyud@vnet.hu (Egy�d Csaba) wrote:

I'd like to ask your opininon about how to handle DST on an 7/24 system.
Where should it be handled: on the server side or on the client side? And
how could I (at all could I???) make it transparent?

Don't use DST.

Use GMT/UTC.

That makes the issue go away.
--
(reverse (concatenate 'string "gro.mca" "@" "enworbbc"))
http://cbbrowne.com/info/slony.html
Signs of a Klingon Programmer #2: "You question the worthiness of my
code? I should kill you where you stand!"

On Friday 11 March 2005 6:25 am, Együd Csaba wrote:

Hi All,
I'd like to ask your opininon about how to handle DST on an 7/24
system. Where should it be handled: on the server side or on the
client side? And how could I (at all could I???) make it
transparent?

Or we must bow to the fact that twice a year there are two unusable
hours? If it cannot be solved technically, than it is acceptable,
but if there is a chance to do it, I'd like to try it.

Our system stores 200-1000 measured data per minute, comming from
substations. The substations clock is synchronized periodically as
well. When the DST is switched there is 1 hour of data missing (or
overlapped). Certainly the client machines are autmatically
adjusted for the DST.

First, at point in time is a point in time. You might call it
2005-03-11 01:02:03.12345 GMT or you might represent it in any other
time zone with or without Daylight Saving Time (or European Summer
Time or...) but it is still the same point in time.

Your assertion about unusable hours is incorrect. Rather, if you have
this problem then you aren't sending/storing the time stamp data
completely/correctly.

Here on the West Coast of the US in the spring the time simply moves
seamlessly and continuously from 01:59:59 PST to 03:00:00 PDT.
Similarly in the fall it goes from 01:59:59 PDT to 01:00:00 PST. If
you throw away the time zone the you will naturally have problems.

The localization in both *nix and PostgreSQL will display the times
just fine based on whether or not they are in standard or daylight
time. (Try "select now();" and "select now() + '1 month'::interval;"

Whether you choose to fix things by specifying complete timestamps,
standardize on GMT, use unix timestamps, etc. is up to you. It will
all work fine if both ends use the same convention and you don't drop
critical parts of the timezone information.

Side note: if you are using cron/at to schedule your data collection
then you need to investigate the behaviour of your versions of those
programs and compare it to your desired outcome.

Cheers,
Steve

On Fri, Mar 11, 2005 at 03:25:28PM +0100, Egy?d Csaba wrote:

Hi All,
I'd like to ask your opininon about how to handle DST on an 7/24 system.
Where should it be handled: on the server side or on the client side? And
how could I (at all could I???) make it transparent?

As others have mentioned, store timestamps on the server in UTC, and
translate to/from local time on the client side if desired. Postgres
can do this for you in the query; just look in the docs to see how.

I have personally encountered situations where that is not quite
adequate, however, because the data elements may originate in
different time zones, and it may be necessary to display in the
original time zone instead of (or in addition to) the local time zone
of the client. (Weather data, for example, should generally be
displayed using the time zone of the source of the data.) In this
case, you must store some representation of the source time zone in a
separate field, in addition to the UTC timestamp. You can then use
both fields together to retrieve the correct original local time.
(Make sure to note in that time zone field whether or not DST is
observed in that locale.) Exactly how to do this depends on your
application.

Or we must bow to the fact that twice a year there are two unusable hours?
If it cannot be solved technically, than it is acceptable, but if there is a
chance to do it, I'd like to try it.

When timestamps are stored in UTC, the missing (when skipping forward)
and duplicate (when setting back) hours are only an issue in the user
interface, for entry and display of the times. The missing hours are
generally easier to deal with, since the only thing affected is the
calculation of durations. If your interface displays elapsed time,
make sure you take this into account. (The easy way is to do the
duration calculations in the database, using UTC.) Duplicate times
are more difficult: when displaying, you need to indicate whether DST
was in effect or not (i.e., was it the first 02:30 or the second?).
If times are to be entered manually in local time, the interface needs
to notice when an ambiguous time has been entered and ask the user to
disambiguate somehow. This is a hell of a lot of trouble to go to for
something that will only come up very rarely or never in most
applications, but you have to do it if you want to get it right.

Our system stores 200-1000 measured data per minute, comming from
substations. The substations clock is synchronized periodically as well.
When the DST is switched there is 1 hour of data missing (or overlapped).
Certainly the client machines are autmatically adjusted for the DST.

If you have control over the production of data on these client
machines, just make sure it is produced in UTC, and the issue goes
away. Otherwise, you can convert their local time back to UTC for
storage in the database, but then you have the duplicate hour
ambiguity to deal with. If you know the data will be coming in
sequentially and/or in near real-time, you can probably figure it out
with a little extra logic in the app that loads the data into the DB.

Randall

Not exactly...

Here is a scenario I ran in to with collecting bandwidth usage and
displaying it back in graph form to customers.

You can store the timestamps in GMT, but the customer wants to see when
spikes happen in his localtime, which most likely has DST. So twice a
year, you are either compressing two hours of bandwidth usage into one,
or the opposite, stretching one hour in to two, which of course produces
somewhat odd looking graphs during that time.

Besides making note of DST on the graph so the customer can see it, I
haven't found a elegant solution to this problem.

On Fri, 2005-03-11 at 12:19 -0500, Christopher Browne wrote:

csegyud@vnet.hu (Együd Csaba) wrote:

I'd like to ask your opininon about how to handle DST on an 7/24 system.
Where should it be handled: on the server side or on the client side? And
how could I (at all could I???) make it transparent?

Don't use DST.

Use GMT/UTC.

That makes the issue go away.

--
Mike Benoit <ipso@snappymail.ca>

On Fri, 2005-03-11 at 13:47, Mike Benoit wrote:

Not exactly...

Here is a scenario I ran in to with collecting bandwidth usage and
displaying it back in graph form to customers.

You can store the timestamps in GMT, but the customer wants to see when
spikes happen in his localtime, which most likely has DST. So twice a
year, you are either compressing two hours of bandwidth usage into one,
or the opposite, stretching one hour in to two, which of course produces
somewhat odd looking graphs during that time.

Besides making note of DST on the graph so the customer can see it, I
haven't found a elegant solution to this problem.

I would think that if you stored them with the local timezone, and used
AT TIME ZONE to convert them to GMT for sorting, then they should show
up in the right order. Just a guess.

On 2005-03-11, Mike Benoit <ipso@snappymail.ca> wrote:

Here is a scenario I ran in to with collecting bandwidth usage and
displaying it back in graph form to customers.

You can store the timestamps in GMT, but the customer wants to see when
spikes happen in his localtime, which most likely has DST. So twice a
year, you are either compressing two hours of bandwidth usage into one,
or the opposite, stretching one hour in to two, which of course produces
somewhat odd looking graphs during that time.

That seems an odd way to handle it. If you graph the data by days according
to the customer's time, then on one day in the year your graph is one hour
smaller, and on another day it is one hour larger. The point to notice is
that the customer's local time should affect only the _labels_ on the graph,
and possibly your choice of start and end times, and not the _data_ being
plotted.

For example, suppose I have a table:

create table tztst (ts timestamptz primary key, value float8 not null);

and I want to plot individual days from it in the customer's timezone:

test=> set timezone to 'America/Denver'; -- or wherever he is
SET

test=> select ts::time,value from tztst
where ts between '2005-04-02 00:00:00' and '2005-04-02 23:59:59' order by ts;
ts | value
----------+------------------
00:00:00 | 286.764410064167
01:00:00 | 291.294525072763
02:00:00 | 294.912455364789
03:00:00 | 297.582051776698
04:00:00 | 299.276640583591
05:00:00 | 299.979290014267
06:00:00 | 299.68297942788
07:00:00 | 298.390669461862
08:00:00 | 296.115272450212
09:00:00 | 292.879523407724
10:00:00 | 288.715752869235
11:00:00 | 283.665563853606
12:00:00 | 277.779416180109
13:00:00 | 271.116122290598
14:00:00 | 263.742259615024
15:00:00 | 255.731505351766
16:00:00 | 247.16390030942
17:00:00 | 238.125049165494
18:00:00 | 228.705265132773
19:00:00 | 218.998667579544
20:00:00 | 209.102241619985
21:00:00 | 199.11486907096
22:00:00 | 189.136340457592
23:00:00 | 179.266357939324
(24 rows)

test=> select ts::time,value from tztst
where ts between '2005-04-03 00:00:00' and '2005-04-03 23:59:59' order by ts;
ts | value
----------+------------------
00:00:00 | 169.603539118895
01:00:00 | 160.244431687857
03:00:00 | 151.282548753949
04:00:00 | 142.807434489044
05:00:00 | 134.903769433375
06:00:00 | 127.650524395576
07:00:00 | 121.120171402458
08:00:00 | 115.377959582483
09:00:00 | 110.481263218032
10:00:00 | 106.479008480546
11:00:00 | 103.411184576393
12:00:00 | 101.308444187935
13:00:00 | 100.19179720206
14:00:00 | 100.072400786337
15:00:00 | 100.951447910284
16:00:00 | 102.820155425614
17:00:00 | 105.659851824544
18:00:00 | 109.442163799338
19:00:00 | 114.129299739007
20:00:00 | 119.674427330605
21:00:00 | 126.022141492211
22:00:00 | 133.109017962198
23:00:00 | 140.864247013488
(23 rows)

test=> select ts::time,value from tztst
where ts between '2005-10-30 00:00:00' and '2005-10-30 23:59:59' order by ts;
ts | value
----------+------------------
00:00:00 | 110.349122831853
01:00:00 | 114.741289638094
01:00:00 | 119.837588745288
02:00:00 | 125.595930978012
03:00:00 | 131.968759497219
04:00:00 | 138.903442561358
05:00:00 | 146.342708199957
06:00:00 | 154.225117209803
07:00:00 | 162.485570567354
08:00:00 | 171.055847066766
09:00:00 | 179.865166743321
10:00:00 | 188.840775429059
11:00:00 | 197.908545612907
12:00:00 | 206.99358864294
13:00:00 | 216.020873214721
14:00:00 | 224.915845037786
15:00:00 | 233.605042562575
16:00:00 | 242.016703682664
17:00:00 | 250.081358401684
18:00:00 | 257.732402570221
19:00:00 | 264.906647954345
20:00:00 | 271.544844092858
21:00:00 | 277.592167633387
22:00:00 | 282.998675105977
23:00:00 | 287.71971539486
(25 rows)

All of these can be converted to meaningful (and un-distorted) graphs.

--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services

Instead of spikes or dips, with your method customers will just be
confused as to why the labels skip an hour, or have two of the same
hour. It would make for a more accurate graph though, your right.

6 of 1, half a dozen of another I guess.

On Fri, 2005-03-11 at 23:33 +0000, Andrew - Supernews wrote:

On 2005-03-11, Mike Benoit <ipso@snappymail.ca> wrote:

Here is a scenario I ran in to with collecting bandwidth usage and
displaying it back in graph form to customers.

You can store the timestamps in GMT, but the customer wants to see when
spikes happen in his localtime, which most likely has DST. So twice a
year, you are either compressing two hours of bandwidth usage into one,
or the opposite, stretching one hour in to two, which of course produces
somewhat odd looking graphs during that time.

That seems an odd way to handle it. If you graph the data by days according
to the customer's time, then on one day in the year your graph is one hour
smaller, and on another day it is one hour larger. The point to notice is
that the customer's local time should affect only the _labels_ on the graph,
and possibly your choice of start and end times, and not the _data_ being
plotted.

For example, suppose I have a table:

create table tztst (ts timestamptz primary key, value float8 not null);

and I want to plot individual days from it in the customer's timezone:

test=> set timezone to 'America/Denver'; -- or wherever he is
SET

test=> select ts::time,value from tztst
where ts between '2005-04-02 00:00:00' and '2005-04-02 23:59:59' order by ts;
ts | value
----------+------------------
00:00:00 | 286.764410064167
01:00:00 | 291.294525072763
02:00:00 | 294.912455364789
03:00:00 | 297.582051776698
04:00:00 | 299.276640583591
05:00:00 | 299.979290014267
06:00:00 | 299.68297942788
07:00:00 | 298.390669461862
08:00:00 | 296.115272450212
09:00:00 | 292.879523407724
10:00:00 | 288.715752869235
11:00:00 | 283.665563853606
12:00:00 | 277.779416180109
13:00:00 | 271.116122290598
14:00:00 | 263.742259615024
15:00:00 | 255.731505351766
16:00:00 | 247.16390030942
17:00:00 | 238.125049165494
18:00:00 | 228.705265132773
19:00:00 | 218.998667579544
20:00:00 | 209.102241619985
21:00:00 | 199.11486907096
22:00:00 | 189.136340457592
23:00:00 | 179.266357939324
(24 rows)

test=> select ts::time,value from tztst
where ts between '2005-04-03 00:00:00' and '2005-04-03 23:59:59' order by ts;
ts | value
----------+------------------
00:00:00 | 169.603539118895
01:00:00 | 160.244431687857
03:00:00 | 151.282548753949
04:00:00 | 142.807434489044
05:00:00 | 134.903769433375
06:00:00 | 127.650524395576
07:00:00 | 121.120171402458
08:00:00 | 115.377959582483
09:00:00 | 110.481263218032
10:00:00 | 106.479008480546
11:00:00 | 103.411184576393
12:00:00 | 101.308444187935
13:00:00 | 100.19179720206
14:00:00 | 100.072400786337
15:00:00 | 100.951447910284
16:00:00 | 102.820155425614
17:00:00 | 105.659851824544
18:00:00 | 109.442163799338
19:00:00 | 114.129299739007
20:00:00 | 119.674427330605
21:00:00 | 126.022141492211
22:00:00 | 133.109017962198
23:00:00 | 140.864247013488
(23 rows)

test=> select ts::time,value from tztst
where ts between '2005-10-30 00:00:00' and '2005-10-30 23:59:59' order by ts;
ts | value
----------+------------------
00:00:00 | 110.349122831853
01:00:00 | 114.741289638094
01:00:00 | 119.837588745288
02:00:00 | 125.595930978012
03:00:00 | 131.968759497219
04:00:00 | 138.903442561358
05:00:00 | 146.342708199957
06:00:00 | 154.225117209803
07:00:00 | 162.485570567354
08:00:00 | 171.055847066766
09:00:00 | 179.865166743321
10:00:00 | 188.840775429059
11:00:00 | 197.908545612907
12:00:00 | 206.99358864294
13:00:00 | 216.020873214721
14:00:00 | 224.915845037786
15:00:00 | 233.605042562575
16:00:00 | 242.016703682664
17:00:00 | 250.081358401684
18:00:00 | 257.732402570221
19:00:00 | 264.906647954345
20:00:00 | 271.544844092858
21:00:00 | 277.592167633387
22:00:00 | 282.998675105977
23:00:00 | 287.71971539486
(25 rows)

All of these can be converted to meaningful (and un-distorted) graphs.

--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

--
Mike Benoit <ipso@snappymail.ca>

On Fri, Mar 11, 2005 at 01:43:21PM -0500, Randall Nortman wrote:

As others have mentioned, store timestamps on the server in UTC,

1) As long as I store them as <timestamp with time zone> I should
not need to care what they are stored as on the backend as
long as I provide the proper timezone for the client location.
Correct ?

2) If I then retrieve them as "... at time zone <...>" I will get
the equivalent time in the time zone of the retrieving client.
The same could be be achieved with "set timezone" per session.
Correct ?

3) If I retrieve them without "at time zone" I will get them with
the time zone that was stored in the first place, right ?

4) I could be wrong on 3, it might be that I then get
times at the time zone the machine running PostgreSQL is set
to - still the correct point in time but not the *source* time
zone.

GnuMed operates on the assumptions that 1 and 2 hold true. It
does not bet itself on 3. Are we safe ?

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

On Sat, Mar 12, 2005 at 05:44:52PM +0100, Karsten Hilbert wrote:

On Fri, Mar 11, 2005 at 01:43:21PM -0500, Randall Nortman wrote:

As others have mentioned, store timestamps on the server in UTC,

1) As long as I store them as <timestamp with time zone> I should
not need to care what they are stored as on the backend as
long as I provide the proper timezone for the client location.
Correct ?

2) If I then retrieve them as "... at time zone <...>" I will get
the equivalent time in the time zone of the retrieving client.
The same could be be achieved with "set timezone" per session.
Correct ?

Yes and Yes

3) If I retrieve them without "at time zone" I will get them with
the time zone that was stored in the first place, right ?

[...]

This would be news to me. I don't think it's possible to *not* have a
timezone set on a session. The server will have a default timezone
based either on the local (server) system time or the setting of the
timezone variable in postgresql.conf. Additionally, libpq
applications will, I believe, issue a "set timezone" during initial
connection setup. The manual (section 8.5.3) seems to indicate that
libpq will only do that if PGTZ is set, but I seem to recall it
happening without PGTZ. (But I'm not entirely sure; feel free to
experiment.)

Anyway, afaik, Postgres does not store the "original" timezone
anywhere, and so could not possibly retrieve it. I think the only
physical difference between the "timestamp" and "timestamp with time
zone" types is in the system catalog; the manual states that both of
them store 8 bytes and have the same range. If "timestamp with time
zone" were storing anything extra, I would think the storage size
would be greater or else the range smaller.

Randall

Randall Nortman <postgreslists@wonderclown.com> writes:

Anyway, afaik, Postgres does not store the "original" timezone
anywhere, and so could not possibly retrieve it. I think the only
physical difference between the "timestamp" and "timestamp with time
zone" types is in the system catalog; the manual states that both of
them store 8 bytes and have the same range.

There is no physical difference between the types: they are both 8-byte
quantities measuring seconds since the Epoch. I think we use midnight
1/1/2000 as the Epoch rather than the traditional Unix 1/1/1970 Epoch,
but otherwise it's exactly the same idea.

The logical difference between the two is that timestamp with tz assumes
that the Epoch is midnight UTC (which means that any particular stored
value represents a very definite real-world instant), while timestamp
without tz ignores the entire concept of time zones; its Epoch is
midnight in an unspecified time zone. Thus, timestamp with tz can and
does convert back and forth between UTC (for the stored values) and your
current TimeZone setting (for display). In timestamp without tz, what
you see is all there is.

Personally I would always use timestamp with tz for representing actual
time instants. Timestamp without tz has uses in some calendar
applications, but it is inherently ambiguous as a representation of a
specific instant. In particular, for the data recording application
that started this thread, it'd be a horrid idea to even think of using
timestamp without tz, specifically because it's incapable of dealing
with things like DST jumps.

regards, tom lane

On Sat, Mar 12, 2005 at 12:22:38PM -0500, Randall Nortman wrote:

Anyway, afaik, Postgres does not store the "original" timezone
anywhere, and so could not possibly retrieve it. I think the only
physical difference between the "timestamp" and "timestamp with time
zone" types is in the system catalog; the manual states that both of
them store 8 bytes and have the same range. If "timestamp with time
zone" were storing anything extra, I would think the storage size
would be greater or else the range smaller.

Am I correct to assume that this could be solved with a user
defined composite data type ? From the docs it so seems.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

For the record, if people search the archives for solutions.

Problem:

You need the time zone in use for <timestamp with time zone>
when data was previously inserted/updated.

Discussion:

As others have mentioned, store timestamps on the server in UTC,

1) As long as I store them as <timestamp with time zone> I should
not need to care what they are stored as on the backend as
long as I provide the proper timezone for the client location.
Correct ?

2) If I then retrieve them as "... at time zone <...>" I will get
the equivalent time in the time zone of the retrieving client.
The same could be be achieved with "set timezone" per session.
Correct ?

Yes and Yes

3) If I retrieve them without "at time zone" I will get them with
the time zone that was stored in the first place, right ?

[...]

This would be news to me.

...

Anyway, afaik, Postgres does not store the "original" timezone
anywhere, and so could not possibly retrieve it.

Solution:

GnuMed now uses a trigger to store the time zone at the time
of data insertion. This was close enough for our needs at the
time (yes, we are old, triggers still need to return opaque on
some of our installations...).

Code:

\unset ON_ERROR_STOP
drop trigger tr_set_encounter_timezone on clin_encounter;
drop function f_set_encounter_timezone();
\set ON_ERROR_STOP 1

create function f_set_encounter_timezone() returns opaque as '
begin
if TG_OP = ''INSERT'' then
NEW.source_time_zone := (select (extract(timezone from (select now()))::text || ''seconds'')::interval);
else
NEW.source_time_zone := OLD.source_time_zone;
end if;
return NEW;
end;
' language 'plpgsql';

create trigger tr_set_encounter_timezone
before insert or update on clin_encounter
for each row execute procedure f_set_encounter_timezone()
;

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

Hi,

I really have a problem with a production environment (RH 9, Postgresql
7.4).

When executing a stored procedure on my computer (development
environment: 7.4 under cygwin. ) everything is oki

When I deploy on the production env the same stored procedure with the
same data (different OS and postgresql instance) the stored procedure
crash. I get this error:
java.io.IOException: Unable to get row: ERROR: cannot EXECUTE a null
querystring when executing the

I looked with pgadminIII and found that a charater used as a string
separator (i.e.: ÿ) is shown as � on the production database. It look
just oki in my dev env. (I included part of both stored procedure below).

Both stored procedure have been added to postgresql via JDBC. When I
update the stored procedure via Pgadmin III the stored procedure look oki.

Any Idea what can be the error. Is there any JDBC/Postgresql 7.4 version
that can cause the behavior. Do I have to set a flag somewhere?! Is
there a way I can work around this problem?

Thanks for your help .. it's really appreciated

/David

CREATE OR REPLACE FUNCTION uk_webos_parseitemprice(int4, _int4)
RETURNS int4 AS
'
DECLARE
commandId ALIAS FOR $1;
arrayProp ALIAS FOR $2;
rawData RECORD;
oneRow text[];
i INTEGER;
idValue VARCHAR;
typeValue VARCHAR;

....

OFFSET 1
LOOP
select into oneRow (string_to_array(rawData.VDDATA,\'ÿ\'));
action:=oneRow[1];
FOR i IN array_lower(oneRow, 1)..array_upper(oneRow, 1) LOOP
column:=oneRow[i];
IF (column = \'þ\')
THEN
....

END;
'
LANGUAGE 'plpgsql' VOLATILE;

CREATE OR REPLACE FUNCTION uk_webos_parseitemprice(int4, _int4)
RETURNS int4 AS
'
DECLARE

....

-- RAISE NOTICE \'test \' ;
FOR rawData IN
SELECT VDNUM, VDVSSRC, VDVSNUM, VDKEY, VDDATA, ts
FROM VD
WHERE VDVSNUM = commandId
AND VDKEY = \'IL\'
AND VDVSSRC = 1
ORDER BY VDNUM
OFFSET 1
LOOP
select into oneRow (string_to_array(rawData.VDDATA,\'�\'));
action:=oneRow[1];
FOR i IN array_lower(oneRow, 1)..array_upper(oneRow, 1) LOOP
column:=oneRow[i];
IF (column = \'�\')
THEN
column:= null ;
END IF;
IF (i = arrayProp[1])
THEN
idValue:= column;
ELSIF (i = arrayProp[2])
THEN
typeValue:= column;
ELSIF (i = arrayProp[3])
THEN
itemIdValue:= column;
ELSIF (i = arrayProp[4])
THEN
resourceIdValue:= column;
ELSIF (i = arrayProp[5])
THEN
minimalQuantityValue:= column;
ELSIF (i = arrayProp[6])
THEN
unitPriceValue:= column;
END IF;
END LOOP;
IF ((action = \'UPDATE\') or (action = \'GUESS\'))
THEN
EXECUTE \'DELETE FROM IL WHERE ILNUM =\' || idValue;
END IF;
-- process the insert statement
insertStatement:= \'INSERT INTO IL ( ILNUM, ILTYPE, ILICNUM, ILRRNUM,
ILQTE, ILPRIX, ts ) VALUES ( \' || idValue ||\', \'|| typeValue ||\',\';
IF (itemIdValue is null)
THEN
insertStatement:= insertStatement || \' null,\';
ELSE
insertStatement:= insertStatement || quote_literal(itemIdValue)|| \',\';
END IF;
IF (resourceIdValue is null)
THEN
insertStatement:= insertStatement || \' null,\';
ELSE
insertStatement:= insertStatement || quote_literal(resourceIdValue)||
\',\';
END IF;
insertStatement:= insertStatement ||
minimalQuantityValue||\',\'||unitPriceValue||\',CURRENT_TIMESTAMP ) \';
-- RAISE NOTICE \'insertStatement %\', insertStatement ;
EXECUTE insertStatement;
END LOOP;
return -1;
END;
'
LANGUAGE 'plpgsql' VOLATILE;

On Mon, 2005-03-14 at 16:32 -0500, David Gagnon wrote:

Hi,

I really have a problem with a production environment (RH 9, Postgresql
7.4).

When I deploy on the production env the same stored procedure with the
same data (different OS and postgresql instance) the stored procedure
crash. I get this error:
java.io.IOException: Unable to get row: ERROR: cannot EXECUTE a null
querystring when executing the

I looked with pgadminIII and found that a charater used as a string
separator (i.e.: ÿ) is shown as � on the production database. It look
just oki in my dev env. (I included part of both stored procedure below).

were the 2 clusters initialized with the same locale
settings ?

gnari

Thanks for your answer.

The ISP created the db fom me .. So I don't have this information. I
search the web to know how to get this info via PgadminIII and I haven't
found :-( Is there a way to get this information once the database have
been created ? I looked via psql .. I haven`t found either

Thanks
/David

Ragnar Hafstað wrote:

David Gagnon wrote:

Thanks for your answer.

The ISP created the db fom me .. So I don't have this information. I
search the web to know how to get this info via PgadminIII and I
haven't found :-( Is there a way to get this information once the
database have been created ? I looked via psql .. I haven`t found either

David, you can try 'SHOW ALL' command.

Thanks
/David

Miroslav

Hi

Thanks for the tips. Locale are the same ... unless I don`t look at
the right thing...

Production ENV
-----------------
lc_collate | C
lc_ctype | C
lc_messages | C
lc_monetary | C
lc_numeric | C
lc_time | C

Dev ENV
----------
"lc_collate";"C"
"lc_ctype";"C"
"lc_messages";"C"
"lc_monetary";"C"
"lc_numeric";"C"
"lc_time";"C"

Thanks for your help
/David

PROD ALL VARIABLES
---------------------------
"add_missing_from";"on"
"australian_timezones";"off"
"authentication_timeout";"60"
"check_function_bodies";"on"
"checkpoint_segments";"3"
"checkpoint_timeout";"300"
"checkpoint_warning";"30"
"client_encoding";"UNICODE"
"client_min_messages";"notice"
"commit_delay";"0"
"commit_siblings";"5"
"cpu_index_tuple_cost";"0.001"
"cpu_operator_cost";"0.0025"
"cpu_tuple_cost";"0.01"
"DateStyle";"ISO, MDY"
"db_user_namespace";"off"
"deadlock_timeout";"1000"
"debug_pretty_print";"off"
"debug_print_parse";"off"
"debug_print_plan";"off"
"debug_print_rewritten";"off"
"default_statistics_target";"10"
"default_transaction_isolation";"read committed"
"default_transaction_read_only";"off"
"dynamic_library_path";"$libdir"
"effective_cache_size";"1000"
"enable_hashagg";"on"
"enable_hashjoin";"on"
"enable_indexscan";"on"
"enable_mergejoin";"on"
"enable_nestloop";"on"
"enable_seqscan";"on"
"enable_sort";"on"
"enable_tidscan";"on"
"explain_pretty_print";"on"
"extra_float_digits";"0"
"from_collapse_limit";"8"
"fsync";"on"
"geqo";"on"
"geqo_effort";"1"
"geqo_generations";"0"
"geqo_pool_size";"0"
"geqo_selection_bias";"2"
"geqo_threshold";"11"
"join_collapse_limit";"8"
"krb_server_keyfile";"FILE:/etc/sysconfig/pgsql/krb5.keytab"
"lc_collate";"C"
"lc_ctype";"C"
"lc_messages";"C"
"lc_monetary";"C"
"lc_numeric";"C"
"lc_time";"C"
"log_connections";"off"
"log_duration";"off"
"log_error_verbosity";"default"
"log_executor_stats";"off"
"log_hostname";"off"
"log_min_duration_statement";"-1"
"log_min_error_statement";"panic"
"log_min_messages";"notice"
"log_parser_stats";"off"
"log_pid";"off"
"log_planner_stats";"off"
"log_source_port";"off"
"log_statement";"off"
"log_statement_stats";"off"
"log_timestamp";"off"
"max_connections";"100"
"max_expr_depth";"10000"
"max_files_per_process";"1000"
"max_fsm_pages";"20000"
"max_fsm_relations";"1000"
"max_locks_per_transaction";"64"
"password_encryption";"on"
"port";"5432"
"pre_auth_delay";"0"
"preload_libraries";"unset"
"random_page_cost";"4"
"regex_flavor";"advanced"
"rendezvous_name";"unset"
"search_path";"$user,public"
"server_encoding";"UNICODE"
"server_version";"7.4.7"
"shared_buffers";"1000"
"silent_mode";"off"
"sort_mem";"1024"
"sql_inheritance";"on"
"ssl";"off"
"statement_timeout";"0"
"stats_block_level";"off"
"stats_command_string";"off"
"stats_reset_on_server_start";"on"
"stats_row_level";"off"
"stats_start_collector";"on"
"superuser_reserved_connections";"2"
"syslog";"0"
"syslog_facility";"LOCAL0"
"syslog_ident";"postgres"
"tcpip_socket";"on"
"TimeZone";"unknown"
"trace_notify";"off"
"transaction_isolation";"read committed"
"transaction_read_only";"off"
"transform_null_equals";"off"
"unix_socket_directory";"unset"
"unix_socket_group";"unset"
"unix_socket_permissions";"511"
"vacuum_mem";"8192"
"virtual_host";"unset"
"wal_buffers";"8"
"wal_debug";"0"
"wal_sync_method";"fdatasync"
"zero_damaged_pages";"off"

DEV ENV FULL VARIABLE
-------------------------------
"add_missing_from";"on"
"australian_timezones";"off"
"authentication_timeout";"60"
"check_function_bodies";"on"
"checkpoint_segments";"3"
"checkpoint_timeout";"300"
"checkpoint_warning";"30"
"client_encoding";"UNICODE"
"client_min_messages";"notice"
"commit_delay";"0"
"commit_siblings";"5"
"cpu_index_tuple_cost";"0.001"
"cpu_operator_cost";"0.0025"
"cpu_tuple_cost";"0.01"
"DateStyle";"ISO, MDY"
"db_user_namespace";"off"
"deadlock_timeout";"1000"
"debug_pretty_print";"off"
"debug_print_parse";"off"
"debug_print_plan";"off"
"debug_print_rewritten";"off"
"default_statistics_target";"10"
"default_transaction_isolation";"read committed"
"default_transaction_read_only";"off"
"dynamic_library_path";"$libdir"
"effective_cache_size";"1000"
"enable_hashagg";"on"
"enable_hashjoin";"on"
"enable_indexscan";"on"
"enable_mergejoin";"on"
"enable_nestloop";"on"
"enable_seqscan";"on"
"enable_sort";"on"
"enable_tidscan";"on"
"explain_pretty_print";"on"
"extra_float_digits";"0"
"from_collapse_limit";"8"
"fsync";"on"
"geqo";"on"
"geqo_effort";"1"
"geqo_generations";"0"
"geqo_pool_size";"0"
"geqo_selection_bias";"2"
"geqo_threshold";"11"
"join_collapse_limit";"8"
"krb_server_keyfile";"unset"
"lc_collate";"C"
"lc_ctype";"C"
"lc_messages";"C"
"lc_monetary";"C"
"lc_numeric";"C"
"lc_time";"C"
"log_connections";"off"
"log_duration";"off"
"log_error_verbosity";"default"
"log_executor_stats";"off"
"log_hostname";"off"
"log_min_duration_statement";"-1"
"log_min_error_statement";"panic"
"log_min_messages";"notice"
"log_parser_stats";"off"
"log_pid";"off"
"log_planner_stats";"off"
"log_source_port";"off"
"log_statement";"off"
"log_statement_stats";"off"
"log_timestamp";"off"
"max_connections";"40"
"max_expr_depth";"10000"
"max_files_per_process";"1000"
"max_fsm_pages";"20000"
"max_fsm_relations";"1000"
"max_locks_per_transaction";"64"
"password_encryption";"on"
"port";"5432"
"pre_auth_delay";"0"
"preload_libraries";"unset"
"random_page_cost";"4"
"regex_flavor";"advanced"
"rendezvous_name";"unset"
"search_path";"$user,public"
"server_encoding";"UNICODE"
"server_version";"7.4.5"
"shared_buffers";"1000"
"silent_mode";"off"
"sort_mem";"1024"
"sql_inheritance";"on"
"ssl";"off"
"statement_timeout";"0"
"stats_block_level";"off"
"stats_command_string";"off"
"stats_reset_on_server_start";"on"
"stats_row_level";"off"
"stats_start_collector";"on"
"superuser_reserved_connections";"2"
"syslog";"0"
"syslog_facility";"LOCAL0"
"syslog_ident";"postgres"
"tcpip_socket";"on"
"TimeZone";" 5 4,M4.1.0/2,M10.5.0/2"
"trace_notify";"off"
"transaction_isolation";"read committed"
"transaction_read_only";"off"
"transform_null_equals";"off"
"unix_socket_directory";"unset"
"unix_socket_group";"unset"
"unix_socket_permissions";"511"
"vacuum_mem";"8192"
"virtual_host";"unset"
"wal_buffers";"8"
"wal_debug";"0"
"wal_sync_method";"fsync"
"zero_damaged_pages";"off"

Miroslav Šulc wrote: