Re: storing TZ along timestamps

Hi,

One of our customers is interested in being able to store original
timezone along with a certain timestamp.

It is currently possible to store a TZ in a separate column, but this is
a bit wasteful and not very convenient anyway.

There are all sorts of UI issues that need to be resolved in order for
this to be a complete feature proposal, but the first thing that we
discussed was what is the storage going to look like. Of course, one
thing we don't want is to store the complete TZ name as text.

So the first thing is cataloguing timezone names, and assigning an ID to
each (maybe an OID). If we do that, then we can store the OID of the
timezone name along the int64/float8 of the actual timestamp value.

Right now we rely on the tzdata files on disk for things like
pg_timezone_names and other accesses of TZ data; so the files are the
authoritative source of TZ info. So we need to ensure that whenever the
files are updated, the catalogs are updated as well.
I think we could make this work if we "refreshed" the catalog from the
files on SIGHUP if the directory changes (say, a new timezone is
created). Note that I am currently proposing to store only the zone
names in the catalog, not the full TZ data.

Are there objections to the general idea? If not, I'll flesh a more
complete proposal.

--
Álvaro Herrera <alvherre(at)commandprompt(dot)com>

On May 27, 2011, at 1:43 PM, Alvaro Herrera wrote:

> Right now we rely on the tzdata files on disk for things like
> pg_timezone_names and other accesses of TZ data; so the files are the
> authoritative source of TZ info. So we need to ensure that whenever the
> files are updated, the catalogs are updated as well.
> I think we could make this work if we "refreshed" the catalog from the
> files on SIGHUP if the directory changes (say, a new timezone is
> created). Note that I am currently proposing to store only the zone
> names in the catalog, not the full TZ data.
>
> Are there objections to the general idea? If not, I'll flesh a more
> complete proposal.

I like it, but what do you do when a TZ has been renamed or has ceased to exist. Or, worse, existed last week, so last week's dates might still use it, but next week's must not?

Best,

David

Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> Right now we rely on the tzdata files on disk for things like
> pg_timezone_names and other accesses of TZ data; so the files are the
> authoritative source of TZ info. So we need to ensure that whenever the
> files are updated, the catalogs are updated as well.
> I think we could make this work if we "refreshed" the catalog from the
> files on SIGHUP if the directory changes (say, a new timezone is
> created).

(1) SIGHUP processing normally occurs outside any transaction.

(2) The only obvious way to ensure the "refresh" is done once, and not
once per backend, is to have the postmaster do it ... which is a
nonstarter for many reasons.

I'd suggest instead considering something like the pg_collations
approach: load up the catalog once at initdb. If the user really needs
to add to the set of accessible TZ names later, give him a tool to do
that. But it's 100% not worth either the implementation pain or the
cycles to try to auto-update the catalog, especially not as often as
once per SIGHUP.

BTW, what will you do about pg_upgrade? Ensuring the OID mapping
doesn't change seems like loads of fun.

regards, tom lane

"David E. Wheeler" <david(at)kineticode(dot)com> wrote:

> I like it, but what do you do when a TZ has been renamed or has
> ceased to exist. Or, worse, existed last week, so last week's
> dates might still use it, but next week's must not?

I think the key thing is that the timestamp portion of it would be
identical to our current TIMESTAMP WITH TIME ZONE -- always store it
in the value UTC zone. That way comparisons and math between
timestamps could remain sane. The stored time zone portion would be
what it would be the display format, if usable. In an extreme
situation like you describe above, I guess you could fall back on
what we do now for display of a timestamptz value.

Personally, I think it would be good to move a bit closer to the
standard by including a time zone in a TIMESTAMP WITH TIME ZONE
value. The biggest problem I can see is how to try to do this in a
standard conforming fashion without breaking existing code. It
would seem more than a little odd to support the standard semantics
with nonstandard syntax and vice versa.

-Kevin

"David E. Wheeler" <david(at)kineticode(dot)com> writes:
> I like it, but what do you do when a TZ has been renamed or has ceased
> to exist.

As far as that goes, I think "nothing" is a sufficient answer. There's
no requirement that an OID in the mapping table correspond to a live TZ.
It's just a more compact way of storing a string name.

regards, tom lane

On May 27, 2011, at 2:35 PM, Tom Lane wrote:

> "David E. Wheeler" <david(at)kineticode(dot)com> writes:
>> I like it, but what do you do when a TZ has been renamed or has ceased
>> to exist.
>
> As far as that goes, I think "nothing" is a sufficient answer. There's
> no requirement that an OID in the mapping table correspond to a live TZ.
> It's just a more compact way of storing a string name.

Well then you'd just want to be sure to never delete TZs.

I think the issue of trying this week to use a TZ that was removed last week might be more problematic. I mean, we could just let the user use it, but that hardly seems wise…

Best,

David

On Fri, May 27, 2011 at 2:32 PM, Kevin Grittner
<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> I think the key thing is that the timestamp portion of it would be
> identical to our current TIMESTAMP WITH TIME ZONE -- always store it
> in the value UTC zone.

Fwiw our timestamp with time zone stores seconds since the epoch. This
is a quantity which is independent of timezones entirely. Excluding
relativistic effects there have been the same number of time zones
since that point in time regardless of where you stand relative to the
sun.

My question for Alvarro is whether he really wants the text label for
the time zone at all, or just the offset which was used to enter it.
That is, if I enter "12:00pm" with my current time zone set to GMT and
later update the tzdata on the machine to start summer time on a
earlier date should the data type now show "1:00pm BST" or should it
still display "12:00pm +000" and leave it up to the reader to decide
whether why I entered it in a weird time zone for that time of year?

--
greg

On 05/27/2011 01:43 PM, Alvaro Herrera wrote:
> Hi,
>
> One of our customers is interested in being able to store original
> timezone along with a certain timestamp.
I am very interested in the use-case for this (in part as I'm working on
a PG related time talk). My experience thus far is that people who want
this do not fully understand the nature of date-time calculations and
variables in PG.
> It is currently possible to store a TZ in a separate column, but this is
> a bit wasteful and not very convenient anyway.

>
> Are there objections to the general idea? If not, I'll flesh a more
> complete proposal.
I'm not crazy about it.

Although time-stamp-with-time-zone is, perhaps, a bad name for what is
actually a "point in time", a point-in-time is what timestamptz
represents. I can enter it and allow my defaults to take over, specify
abbreviations, explicit offsets or long names none of which change the
actual point in time. Likewise, I can display said point-in-time in any
of dozens of ways according to my needs.

steve=# select '2011-05-27 12:34'::timestamptz;
timestamptz
------------------------
2011-05-27 12:34:00-07

steve=# select '2011-05-27 12:34-07'::timestamptz;
timestamptz
------------------------
2011-05-27 12:34:00-07

steve=# select '2011-05-27 12:34 PDT'::timestamptz;
timestamptz
------------------------
2011-05-27 12:34:00-07

steve=# select '2011-05-27 11:34 PST'::timestamptz;
timestamptz
------------------------
2011-05-27 12:34:00-07

steve=# select '2011-05-27 15:34 US/Eastern'::timestamptz;
timestamptz
------------------------
2011-05-27 12:34:00-07

select now() - '02:58:54.605041'::interval;
?column?
-------------------------------
2011-05-27 12:34:00.394959-07

Granted, I'm a random sample of 1, but I've never found anyone with a
real need for this feature - especially since the capability already
exists to achieve the requested result, and much more flexibly, by
either a separate column or a user-defined type.

Questions:

What would be the storage impact (tables, indexes and backups) for those
of use with tens-of-millions of pieces of timestamp data?

What type of timestamp would be stored? Abbreviated/offset (PST, -07),
full (US/Eastern) or a mix? Is there an expectation that the stored time
zone information would be used for any calculation purposes? If so, how
would rules be applied? Would there be any form of error-checking?
Currently PG accepts non-existent time zones but maps them to UTC:

steve=# select '2011-05-27 15:34'::timestamptz at time zone 'US/f00';
timezone
---------------------
2011-05-27 15:34:00

Would there be any impact to existing queries?

How would dump/restore issues be handled - especially if the time-zone
info changes in between?

More as I think of them.

Cheers,
Steve

On Fri, May 27, 2011 at 4:13 PM, Steve Crawford
<scrawford(at)pinpointresearch(dot)com> wrote:
> I am very interested in the use-case for this (in part as I'm working on a
> PG related time talk). My experience thus far is that people who want this
> do not fully understand the nature of date-time calculations and variables
> in PG.

The use cases I recall having been mentioned in the past were accurate
data retention and calendaring applications.

Accurate data retention for things like drug trials need to guarantee
they retain precisely what the user entered, not an equivalent value.
If you run a report on a drug trial you need to see that the event was
recorded as occuring at 1:00pm EST not 6:00pm GMT even if you happen
to run the report in London.

And calendaring apps want to know what timezone is attached to an
event, not only the point in time at which it occurs. If your plane
flight departs at 12:00pm GMT and lands at 2:00pm EST you need to know
that to book your taxi at 2:30pm EST -- not 7:30pm GMT.

Both of these two cases can be handled differently. The former by
storing the raw text inputs and then storing the interpreted value as
a derived column separetly, and the latter by storing the local time
zone to use for display as an additional attribute along with the
local address and other attributes of the calendar event.

--
greg

On 05/27/2011 04:29 PM, Greg Stark wrote:
> On Fri, May 27, 2011 at 4:13 PM, Steve Crawford
> <scrawford(at)pinpointresearch(dot)com> wrote:
>> I am very interested in the use-case for this (in part as I'm working on a
>> PG related time talk). My experience thus far is that people who want this
>> do not fully understand the nature of date-time calculations and variables
>> in PG.
> The use cases I recall having been mentioned in the past were accurate
> data retention and calendaring applications.
>
> Accurate data retention for things like drug trials need to guarantee
> they retain precisely what the user entered, not an equivalent value.
> If you run a report on a drug trial you need to see that the event was
> recorded as occuring at 1:00pm EST not 6:00pm GMT even if you happen
> to run the report in London.
>
> And calendaring apps want to know what timezone is attached to an
> event, not only the point in time at which it occurs. If your plane
> flight departs at 12:00pm GMT and lands at 2:00pm EST you need to know
> that to book your taxi at 2:30pm EST -- not 7:30pm GMT.
>
> Both of these two cases can be handled differently. The former by
> storing the raw text inputs and then storing the interpreted value as
> a derived column separetly, and the latter by storing the local time
> zone to use for display as an additional attribute along with the
> local address and other attributes of the calendar event.
>
So the proposed change does not handle the first case as you need to
capture the raw input.

And the second case is already well handled. In fact calendaring is a
great example. I enter the time for the teleconference and PG nicely
uses my default timezone to store the point-in-time. When you retrieve
it, it is shown in your timezone and we both pick up the phone at the
correct time. And if I know I'll be somewhere else at that time, I just
ask for the data in that zone. Altering the data type gains nothing.

Cheers,
Steve

On May 27, 2011, at 6:29 PM, Greg Stark wrote:
> Both of these two cases can be handled differently. The former by
> storing the raw text inputs and then storing the interpreted value as
> a derived column separetly, and the latter by storing the local time
> zone to use for display as an additional attribute along with the
> local address and other attributes of the calendar event.

Which means you're back to a very cumbersome method that involves another field. That's a tremendous amount of extra code.

We run multiple businesses around the globe. Each business operates in it's own timezone, and 90% of the time we want things handled in that timezone. The wheels fall off the wagon if we try and combine data from multiple locations into a single database; there's no reasonable way to say: give me the data in this field *at the timezone that was originally entered*, except for not storing timezone data at all. If we don't store timezone data at all, then it's impossible to determine an actual point in time that something happened at.
--
Jim C. Nasby, Database Architect jim(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Fri, May 27, 2011 at 04:43:28PM -0400, Alvaro Herrera wrote:
> Hi,
>
> One of our customers is interested in being able to store original
> timezone along with a certain timestamp.

I've felt that pain here and there too...

> So the first thing is cataloguing timezone names, and assigning an ID to
> each (maybe an OID). If we do that, then we can store the OID of the
> timezone name along the int64/float8 of the actual timestamp value.
>
> Right now we rely on the tzdata files on disk for things like
> pg_timezone_names and other accesses of TZ data; so the files are the
> authoritative source of TZ info. So we need to ensure that whenever the
> files are updated, the catalogs are updated as well.

Problem with this approach (mapping external time zone names to OIDs)
is: dump/restore would only be meaningful if you "carry over" the time
zone data, right?

That is: two independent systems are likely to have different mappings
(even if at some point they have the "same" TZ data?)

What would be a solution to that?

(a) A central, "official" catalog, with only additions, never deletions
(perhaps with some space carved out for "local" additions, to minimize
conflicts)?
(b) A hash of the time zone name?

Both not very good ideas, I know. Although (a) might be less bad than it
seems. Most Unixoids (including OSX) seem to have basically Olson's.
Don't know about Windows, but it might seem feasible to make some
mapping (or union). Only important rule: no backtrack :-)

Regards
- -- tomás
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFN4JWtBcgs9XrR2kYRAt+mAJ0atx3u6pll50+s4vVwCKZUjqmnSQCffWNe
gzSFgRCFUvsd8pbH1Qm/ho4=
=FVhO
-----END PGP SIGNATURE-----

On fre, 2011-05-27 at 16:57 -0700, Steve Crawford wrote:
> And the second case is already well handled. In fact calendaring is a
> great example. I enter the time for the teleconference and PG nicely
> uses my default timezone to store the point-in-time. When you
> retrieve
> it, it is shown in your timezone and we both pick up the phone at the
> correct time. And if I know I'll be somewhere else at that time, I
> just
> ask for the data in that zone. Altering the data type gains nothing.

How about a recurring appointment that happens every Tuesday whenever it
is 9:00am in California, independent of DST (in California or where ever
the participant actually is). I'm not sure how to solve that within the
SQL framework. You might need to use time with time zone with a
placeholder timezone, and then a rule that date + time with time zone
creates a timestamp with time zone that resolves the time zone for that
particular day.

On 05/28/2011 02:58 PM, Peter Eisentraut wrote:
> On fre, 2011-05-27 at 16:57 -0700, Steve Crawford wrote:
>> And the second case is already well handled. In fact calendaring is a
>> great example. I enter the time for the teleconference and PG nicely
>> uses my default timezone to store the point-in-time. When you
>> retrieve
>> it, it is shown in your timezone and we both pick up the phone at the
>> correct time. And if I know I'll be somewhere else at that time, I
>> just
>> ask for the data in that zone. Altering the data type gains nothing.
> How about a recurring appointment that happens every Tuesday whenever it
> is 9:00am in California, independent of DST (in California or where ever
> the participant actually is). I'm not sure how to solve that within the
> SQL framework. You might need to use time with time zone with a
> placeholder timezone, and then a rule that date + time with time zone
> creates a timestamp with time zone that resolves the time zone for that
> particular day.
>
>
Interval math is pretty smart about that:

select '2011-05-31 09:00'::timestamp at time zone 'PST8PDT' + ('7
days'::interval * generate_series(1,60));
------------------------
2011-06-07 09:00:00-07
2011-06-14 09:00:00-07
2011-06-21 09:00:00-07
2011-06-28 09:00:00-07
2011-07-05 09:00:00-07
2011-07-12 09:00:00-07
2011-07-19 09:00:00-07
2011-07-26 09:00:00-07
2011-08-02 09:00:00-07
2011-08-09 09:00:00-07
2011-08-16 09:00:00-07
2011-08-23 09:00:00-07
2011-08-30 09:00:00-07
2011-09-06 09:00:00-07
2011-09-13 09:00:00-07
2011-09-20 09:00:00-07
2011-09-27 09:00:00-07
2011-10-04 09:00:00-07
2011-10-11 09:00:00-07
2011-10-18 09:00:00-07
2011-10-25 09:00:00-07
2011-11-01 09:00:00-07
2011-11-08 09:00:00-08
2011-11-15 09:00:00-08
2011-11-22 09:00:00-08
2011-11-29 09:00:00-08
2011-12-06 09:00:00-08
2011-12-13 09:00:00-08
2011-12-20 09:00:00-08
2011-12-27 09:00:00-08
2012-01-03 09:00:00-08
2012-01-10 09:00:00-08
2012-01-17 09:00:00-08
2012-01-24 09:00:00-08
2012-01-31 09:00:00-08
2012-02-07 09:00:00-08
2012-02-14 09:00:00-08
2012-02-21 09:00:00-08
2012-02-28 09:00:00-08
2012-03-06 09:00:00-08
2012-03-13 09:00:00-07
2012-03-20 09:00:00-07
2012-03-27 09:00:00-07
2012-04-03 09:00:00-07
2012-04-10 09:00:00-07
2012-04-17 09:00:00-07
2012-04-24 09:00:00-07
2012-05-01 09:00:00-07
2012-05-08 09:00:00-07
2012-05-15 09:00:00-07
2012-05-22 09:00:00-07
2012-05-29 09:00:00-07
...

Or if you have to call in from London (notice the blips between 4pm and
5pm due to London and California switching to/from DST on different dates):

select ('2011-05-31 09:00'::timestamp at time zone 'PST8PDT' + ('7
days'::interval * generate_series(1,60))) at time zone 'Europe/London';
---------------------
2011-06-07 17:00:00
2011-06-14 17:00:00
2011-06-21 17:00:00
2011-06-28 17:00:00
2011-07-05 17:00:00
2011-07-12 17:00:00
2011-07-19 17:00:00
2011-07-26 17:00:00
2011-08-02 17:00:00
2011-08-09 17:00:00
2011-08-16 17:00:00
2011-08-23 17:00:00
2011-08-30 17:00:00
2011-09-06 17:00:00
2011-09-13 17:00:00
2011-09-20 17:00:00
2011-09-27 17:00:00
2011-10-04 17:00:00
2011-10-11 17:00:00
2011-10-18 17:00:00
2011-10-25 17:00:00
2011-11-01 16:00:00
2011-11-08 17:00:00
2011-11-15 17:00:00
2011-11-22 17:00:00
2011-11-29 17:00:00
2011-12-06 17:00:00
2011-12-13 17:00:00
2011-12-20 17:00:00
2011-12-27 17:00:00
2012-01-03 17:00:00
2012-01-10 17:00:00
2012-01-17 17:00:00
2012-01-24 17:00:00
2012-01-31 17:00:00
2012-02-07 17:00:00
2012-02-14 17:00:00
2012-02-21 17:00:00
2012-02-28 17:00:00
2012-03-06 17:00:00
2012-03-13 16:00:00
2012-03-20 16:00:00
2012-03-27 17:00:00
2012-04-03 17:00:00
2012-04-10 17:00:00
2012-04-17 17:00:00
2012-04-24 17:00:00
2012-05-01 17:00:00
2012-05-08 17:00:00
...

Cheers,
Steve

On Fri, 2011-05-27 at 16:43 -0400, Alvaro Herrera wrote:
> Hi,
>
> One of our customers is interested in being able to store original
> timezone along with a certain timestamp.

I assume that you're talking about a new data type, not augmenting the
current types, correct?

Regards,
Jeff Davis

Excerpts from Jeff Davis's message of mié jun 01 19:57:40 -0400 2011:
> On Fri, 2011-05-27 at 16:43 -0400, Alvaro Herrera wrote:
> > Hi,
> >
> > One of our customers is interested in being able to store original
> > timezone along with a certain timestamp.
>
> I assume that you're talking about a new data type, not augmenting the
> current types, correct?

Yes

--
Álvaro Herrera <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On Wed, Jun 1, 2011 at 8:18 PM, Alvaro Herrera
<alvherre(at)commandprompt(dot)com> wrote:
> Excerpts from Jeff Davis's message of mié jun 01 19:57:40 -0400 2011:
>> On Fri, 2011-05-27 at 16:43 -0400, Alvaro Herrera wrote:
>> > Hi,
>> >
>> > One of our customers is interested in being able to store original
>> > timezone along with a certain timestamp.
>>
>> I assume that you're talking about a new data type, not augmenting the
>> current types, correct?
>
> Yes

why not use a composite type for that? performance maybe?

merlin

Excerpts from Merlin Moncure's message of mié jun 01 21:36:32 -0400 2011:
> On Wed, Jun 1, 2011 at 8:18 PM, Alvaro Herrera
> <alvherre(at)commandprompt(dot)com> wrote:
> > Excerpts from Jeff Davis's message of mié jun 01 19:57:40 -0400 2011:
> >> On Fri, 2011-05-27 at 16:43 -0400, Alvaro Herrera wrote:
> >> > Hi,
> >> >
> >> > One of our customers is interested in being able to store original
> >> > timezone along with a certain timestamp.
> >>
> >> I assume that you're talking about a new data type, not augmenting the
> >> current types, correct?
> >
> > Yes
>
> why not use a composite type for that? performance maybe?

To avoid having to implement all the operators and lookup tables (of
timezones) in userland, mainly. Probably performance would be affected
too, not sure, but that's not the main point.

--
Álvaro Herrera <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On 06/01/2011 05:18 PM, Alvaro Herrera wrote:
> Excerpts from Jeff Davis's message of mié jun 01 19:57:40 -0400 2011:
>> On Fri, 2011-05-27 at 16:43 -0400, Alvaro Herrera wrote:
>>> Hi,
>>>
>>> One of our customers is interested in being able to store original
>>> timezone along with a certain timestamp.
>> I assume that you're talking about a new data type, not augmenting the
>> current types, correct?
> Yes
>
That eliminates many of my issues - I just didn't want the type changed
underneath me. But some considerations remain - including some new that
have crossed my mind:

1. How would the time-zone be defined in this composite? Offset from
GMT? Timezone (well, link thereto) with all DST rules intact? Would
"extract" need to be modified to include the ability to grab the timezone?

2. What would be the precedence for defining originating timezone?
Default? Set timezone to? ...at time zone...? Based on the timestamp
(2011-06-02 12:34:56-07)?

3. Would indexing/sorting include the originating zone? If so, how would
time zones collate (base offset, actual offset based on the timestamp,
name)?

4. What would be the corresponding type when used with
Perl/PHP/Python/... applications - would they require special
non-standard handling?

Since this isn't going to alter my current beloved timestamptz and I
don't have a use-case I leave the decisions on the above to others. But
in my imagined use-cases I still see the originating zone as a separate
piece of information better handled as a different column - for example
sorting by timestamp plus priority or selecting everything for a
specific time zone.

Cheers,
Steve

On Thu, Jun 2, 2011 at 12:55 PM, Alvaro Herrera
<alvherre(at)commandprompt(dot)com> wrote:
>> >> > One of our customers is interested in being able to store original
>> >> > timezone along with a certain timestamp.
>> >>
>> >> I assume that you're talking about a new data type, not augmenting the
>> >> current types, correct?
>> >
>> > Yes
>>
>> why not use a composite type for that?  performance maybe?
>
> To avoid having to implement all the operators and lookup tables (of
> timezones) in userland, mainly.  Probably performance would be affected
> too, not sure, but that's not the main point.

right -- I see where you are going with this. ok, some random questions:
*) what about making a 'timezone' type in addition to (or even instead
of) the timezonetz_inputtz? Then you could in theory treat the your
proposed type as a composite of timezonetz and timezone, just as
timestamptz is a 'composite' of date and timetz. (note I'm not
necessarily arguing against the creation of a specific unified type --
performance is important for time types).

*) in/out formats...what would be the wire formats of your type -- in
particular, the binary format?

*) do you see your type interacting with various datetime function
(like extract) or will a cast be required? Interval math?

*) how does ordering and uniqueness apply to same timestamps with
unique input time zones?

merlin

On Fri, 2011-05-27 at 16:43 -0400, Alvaro Herrera wrote:
> One of our customers is interested in being able to store original
> timezone along with a certain timestamp.

Another thing to consider is that this will eliminate any useful total
order.

You could define an arbitrary total order, of course, just to allow
BTrees for equality searches. However, I don't think you should define
">" (and other non-equality comparator operators) according to that
total order -- they should be more hidden like "~>~". ">" should not
exist as an operator over this type at all.

I also do not like the idea of having "=" mean "equivalent after
timezone adjustment". If we're making a distinction between "2000-01-01
10:00:00 +03" and "2000-01-01 9:00:00 +02", then "=" should not obscure
that distinction.

Regards,
Jeff Davis

On Thu, Jun 2, 2011 at 6:06 PM, Steve Crawford
<scrawford(at)pinpointresearch(dot)com> wrote:
> On 06/01/2011 05:18 PM, Alvaro Herrera wrote:
>>
>> Excerpts from Jeff Davis's message of mié jun 01 19:57:40 -0400 2011:
>>>
>>> On Fri, 2011-05-27 at 16:43 -0400, Alvaro Herrera wrote:
>>>>
>>>> Hi,
>>>>
>>>> One of our customers is interested in being able to store original
>>>> timezone along with a certain timestamp.
>>>
>>> I assume that you're talking about a new data type, not augmenting the
>>> current types, correct?
>>
>> Yes
>>
> That eliminates many of my issues - I just didn't want the type changed
> underneath me. But some considerations remain - including some new that have
> crossed my mind:
>
> 1. How would the time-zone be defined in this composite? Offset from GMT?
> Timezone (well, link thereto) with all DST rules intact? Would "extract"
> need to be modified to include the ability to grab the timezone?

That doesn't seem appropriate, because timezones are not always
represented by strict offsets from GMT. Some frequently-used
timezones represent variable offsets. ("EDT/EST", I'm looking at
you!)

> 2. What would be the precedence for defining originating timezone? Default?
> Set timezone to? ...at time zone...? Based on the timestamp (2011-06-02
> 12:34:56-07)?
>
> 3. Would indexing/sorting include the originating zone? If so, how would
> time zones collate (base offset, actual offset based on the timestamp,
> name)?

Some timezones contain discontinuities, so that the notion of sorting
them seems implausible, as there isn't properly an "ordering."

> 4. What would be the corresponding type when used with Perl/PHP/Python/...
> applications - would they require special non-standard handling?
>
> Since this isn't going to alter my current beloved timestamptz and I don't
> have a use-case I leave the decisions on the above to others. But in my
> imagined use-cases I still see the originating zone as a separate piece of
> information better handled as a different column - for example sorting by
> timestamp plus priority or selecting everything for a specific time zone.

I'd tend to think that this is best captured by having two pieces of
information:
a) The timestamp in UTC terms, so that it's a totally stable value,
which is amenable to comparison against other timestamps (irrespective
of timezone)
b) A symbolic representation of the timezone, perhaps its name.

It's not at all obvious that these ought to be treated as a singular data type.
--
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"

On Thu, 2011-06-02 at 18:46 +0000, Christopher Browne wrote:
> > 1. How would the time-zone be defined in this composite? Offset from GMT?
> > Timezone (well, link thereto) with all DST rules intact? Would "extract"
> > need to be modified to include the ability to grab the timezone?
>
> That doesn't seem appropriate, because timezones are not always
> represented by strict offsets from GMT. Some frequently-used
> timezones represent variable offsets. ("EDT/EST", I'm looking at
> you!)

In conjunction with a specific timestamp, a timezone does strictly map
to a single offset.

That is, unless it's a timestamp in the future, and someone decides to
adjust a timezone before the timestamp actually occurs. But that's a
problem with the current timestamptz implementation anyway...

> > Since this isn't going to alter my current beloved timestamptz and I don't
> > have a use-case I leave the decisions on the above to others. But in my
> > imagined use-cases I still see the originating zone as a separate piece of
> > information better handled as a different column - for example sorting by
> > timestamp plus priority or selecting everything for a specific time zone.

I have a similar inclination. ">" seems like the fundamental operation
you'd want to perform on any timestamp (perhaps more so than equality),
and that's not well-defined if there is no total order (but several
meaningful partial orders).

However, I do see some nice benefits, too. The main one is that you can
easily get either local time or GMT out of it. So you can answer queries
such as "which of these activities occurred outside of normal business
hours" as well as "which of these events happened first". It would take
a little care to use properly, however.

Regards,
Jeff Davis

On Thu, Jun 2, 2011 at 3:02 PM, Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
> On Thu, 2011-06-02 at 18:46 +0000, Christopher Browne wrote:
>> > 1. How would the time-zone be defined in this composite? Offset from GMT?
>> > Timezone (well, link thereto) with all DST rules intact? Would "extract"
>> > need to be modified to include the ability to grab the timezone?
>>
>> That doesn't seem appropriate, because timezones are not always
>> represented by strict offsets from GMT.  Some frequently-used
>> timezones represent variable offsets.  ("EDT/EST", I'm looking at
>> you!)
>
> In conjunction with a specific timestamp, a timezone does strictly map
> to a single offset.

But that doesn't seem like enough, because if someone adds '1 day',
knowing the offset isn't sufficient to figure out the answer. You
have to know where the DST boundary is.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Thu, 2011-06-02 at 20:28 -0400, Robert Haas wrote:
> But that doesn't seem like enough, because if someone adds '1 day',
> knowing the offset isn't sufficient to figure out the answer. You
> have to know where the DST boundary is.

Good point, I guess the timezone itself needs to be stored. That's a
little unfortunate, because timezones are somewhat of a moving target
(which I think was Tom's point).

That means that we'd need an entire history (and future?) of timezone
definitions, and apply the timezone definition as of the associated
timestamp to get the offset. Or, should we apply the timezone definition
as of the "real" time the value was entered?

Regards,
Jeff Davis

On Jun 2, 2011, at 7:48 PM, Jeff Davis wrote:
> On Thu, 2011-06-02 at 20:28 -0400, Robert Haas wrote:
>> But that doesn't seem like enough, because if someone adds '1 day',
>> knowing the offset isn't sufficient to figure out the answer. You
>> have to know where the DST boundary is.
>
> Good point, I guess the timezone itself needs to be stored. That's a
> little unfortunate, because timezones are somewhat of a moving target
> (which I think was Tom's point).
>
> That means that we'd need an entire history (and future?) of timezone
> definitions, and apply the timezone definition as of the associated
> timestamp to get the offset. Or, should we apply the timezone definition
> as of the "real" time the value was entered?

As someone else mentioned, timestamptz suffers the exact same problems.

I'm torn between whether the type should store the original time or the original time converted to GMT. I believe you would have the most accuracy if you stored the original time... but then indexing becomes problematic. I don't know if this data quality issue can be solved by anything short of somehow storing the actual timezone rule that was in place when the data was set.

Speaking of input; someone asked what timezone should be used as the "original" timezone. We should use whatever timezone was passed in with the value, and if one wasn't passed in we should use whatever the timezone GUC is set to (I'm assuming that's what timestamptz does).
--
Jim C. Nasby, Database Architect jim(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net

On tor, 2011-06-02 at 22:58 -0500, Jim Nasby wrote:
> I'm torn between whether the type should store the original time or
> the original time converted to GMT. I believe you would have the most
> accuracy if you stored the original time... but then indexing becomes
> problematic. I don't know if this data quality issue can be solved by
> anything short of somehow storing the actual timezone rule that was in
> place when the data was set.
>
> Speaking of input; someone asked what timezone should be used as the
> "original" timezone. We should use whatever timezone was passed in
> with the value, and if one wasn't passed in we should use whatever the
> timezone GUC is set to (I'm assuming that's what timestamptz does).

I think all of that comes down to business rules. Train and airline
companies etc. have probably figured this out for themselves, not
necessarily consistent with each other. So it's doubtful whether a
single solution that we can hash out here is going to work well in
practice. I think making it easier to implement particular business
rules in this area in userspace (composite types, etc.) might go a
longer way.

On Thu, Jun 2, 2011 at 8:58 PM, Jim Nasby <jim(at)nasby(dot)net> wrote:
>
> I'm torn between whether the type should store the original time or the original time converted to GMT.

This is the wrong way to think about it. We *never* store time
"converted to GMT". When we want to represent a point in time we
represent it as seconds since the epoch. This is a quantity that is
the same everywhere in the world (modulo relativistic effects...) ie.
it's a concept which is completely independent of timezones.

The question here is how to represent more complex concepts than
simply points in time. I think the two concepts under discussion are
a) a composite type representing a point in time and a timezone it
should be interpreted in for operations and display and b) the
original input provided which is a text string with the constraint
that it's a valid input which can be interpreted as a point in time.

--
greg

On Jun 4, 2011, at 3:56 AM, Greg Stark wrote:
> On Thu, Jun 2, 2011 at 8:58 PM, Jim Nasby <jim(at)nasby(dot)net> wrote:
>>
>> I'm torn between whether the type should store the original time or the original time converted to GMT.
>
> This is the wrong way to think about it. We *never* store time
> "converted to GMT". When we want to represent a point in time we
> represent it as seconds since the epoch.
Right. Sorry, my bad.

> The question here is how to represent more complex concepts than
> simply points in time. I think the two concepts under discussion are
> a) a composite type representing a point in time and a timezone it
> should be interpreted in for operations and display and b) the
> original input provided which is a text string with the constraint
> that it's a valid input which can be interpreted as a point in time.

My fear with A is that something could change that would make it impossible to actually get back to the time that was originally entered. For example, a new version of the timezone database could change something. Though, that problem also exists for timestamptz today, so presumably if it was much of an issue we'd have gotten complaints by now.
--
Jim C. Nasby, Database Architect jim(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net

Hi,

On May 27, 2011, at 11:43 PM, Alvaro Herrera wrote:
>
> One of our customers is interested in being able to store original
> timezone along with a certain timestamp.
>
> It is currently possible to store a TZ in a separate column, but this is
> a bit wasteful and not very convenient anyway.
>
> There are all sorts of UI issues that need to be resolved in order for
> this to be a complete feature proposal, but the first thing that we
> discussed was what is the storage going to look like. Of course, one
> thing we don't want is to store the complete TZ name as text.
>
> So the first thing is cataloguing timezone names, and assigning an ID to
> each (maybe an OID). If we do that, then we can store the OID of the
> timezone name along the int64/float8 of the actual timestamp value.

So, I'd think there are 2 reasonable approaches to storing the
timezone part:

1. Store the timezone abbreviation (i.e. 'EST' along w/ the timestamp
data).
2. Assign OID to each of the timezones and store it w/ the timestamp.

The first option seem to avoid the necessity of creating a new system
catalog for timezone information and the burden of updating it,
because current implementation is already capable of translating
abbreviations to useful timezone information. The question is, whether
just a TZ abbreviation is sufficient to uniquely identify the timezone
and get the offset and DST rules. If it's not sufficient, how
conflicting TZ short names are handled in the current code (i.e. 'AT
TIME ZONE ...')?

The second choice doesn't avoids the issue of ambiguous names,
although it requires moving TZ information inside the database and
providing some means to update it. There were mentions of potential
problems w/ pg_upgrade and pg_dump, if we add a massive amount of
oids for the timezones. What are these problems specifically?

I'd thing that storing TZ abbreviations is more straightforward and
easier to implement, unless there are too ambiguous to identify the
timezone correctly.

> Note that I am currently proposing to store only the zone
> names in the catalog, not the full TZ data.

Where would we store other bits of timezone information? Wouldn't it
be inconvenient to update names in system catalogs and DST rules
elsewhere?

Alexey.

--
Command Prompt, Inc. http://www.CommandPrompt.com
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On Mon, Jun 6, 2011 at 7:50 AM, Jim Nasby <jim(at)nasby(dot)net> wrote:
> On Jun 4, 2011, at 3:56 AM, Greg Stark wrote:
>> On Thu, Jun 2, 2011 at 8:58 PM, Jim Nasby <jim(at)nasby(dot)net> wrote:
>>>
>>> I'm torn between whether the type should store the original time or the original time converted to GMT.
>>
>> This is the wrong way to think about it. We *never* store time
>> "converted to GMT".  When we want to represent a point in time we
>> represent it as seconds since the epoch.
> Right. Sorry, my bad.
>
>> The question here is how to represent more complex concepts than
>> simply points in time. I think the two concepts under discussion are
>> a) a composite type representing a point in time and a timezone it
>> should be interpreted in for operations and display and b) the
>> original input provided which is a text string with the constraint
>> that it's a valid input which can be interpreted as a point in time.
>
> My fear with A is that something could change that would make it impossible to actually get back to the time that was originally entered. For example, a new version of the timezone database could change something. Though, that problem also exists for timestamptz today, so presumably if it was much of an issue we'd have gotten complaints by now.

The common problem is daylight savings time being declared or
cancelled. This happens numerous times throughout the year, often with
short notice.

If you want to store '6pm July 3rd 2014 Pacific/Fiji', and want that
to keep meaning 6pm Fiji time no matter what decisions the Fijian
government makes over the next two years, you need to store the
wallclock (local) time and the timezone. The wallclock time remains
fixed, but the conversion to UTC may float.

If you are storing an point in time that remains stable no matter
future political decisions, you store UTC time and an offset. The
conversion to wallclock time may float, and your 6pm Fiji time meeting
might change to 5pm or 7pm depending on the policical edicts.

If you are only storing past events, its not normally an issue but
timezone information does occasionally get changed retroactively if
errors are discovered.

--
Stuart Bishop <stuart(at)stuartbishop(dot)net>
http://www.stuartbishop.net/

On Jul 6, 2011, at 9:24 AM, Alexey Klyukin wrote:
> So, I'd think there are 2 reasonable approaches to storing the
> timezone part:
>
> 1. Store the timezone abbreviation (i.e. 'EST' along w/ the timestamp
> data).
> 2. Assign OID to each of the timezones and store it w/ the timestamp.
>
> The first option seem to avoid the necessity of creating a new system
> catalog for timezone information and the burden of updating it,
> because current implementation is already capable of translating
> abbreviations to useful timezone information. The question is, whether
> just a TZ abbreviation is sufficient to uniquely identify the timezone
> and get the offset and DST rules. If it's not sufficient, how
> conflicting TZ short names are handled in the current code (i.e. 'AT
> TIME ZONE ...')?

It's not enough. See the timezone_abbreviations setting.
--
Jim C. Nasby, Database Architect jim(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net

On Fri, Jul 8, 2011 at 11:13 AM, Stuart Bishop <stuart(at)stuartbishop(dot)net> wrote:
> On Mon, Jun 6, 2011 at 7:50 AM, Jim Nasby <jim(at)nasby(dot)net> wrote:
>> On Jun 4, 2011, at 3:56 AM, Greg Stark wrote:
>>> On Thu, Jun 2, 2011 at 8:58 PM, Jim Nasby <jim(at)nasby(dot)net> wrote:
>>>>
>>>> I'm torn between whether the type should store the original time or the original time converted to GMT.
>>>
>>> This is the wrong way to think about it. We *never* store time
>>> "converted to GMT".  When we want to represent a point in time we
>>> represent it as seconds since the epoch.
>> Right. Sorry, my bad.
>>
>>> The question here is how to represent more complex concepts than
>>> simply points in time. I think the two concepts under discussion are
>>> a) a composite type representing a point in time and a timezone it
>>> should be interpreted in for operations and display and b) the
>>> original input provided which is a text string with the constraint
>>> that it's a valid input which can be interpreted as a point in time.
>>
>> My fear with A is that something could change that would make it impossible to actually get back to the time that was originally entered. For example, a new version of the timezone database could change something. Though, that problem also exists for timestamptz today, so presumably if it was much of an issue we'd have gotten complaints by now.
>
> The common problem is daylight savings time being declared or
> cancelled. This happens numerous times throughout the year, often with
> short notice.
>
> If you want to store '6pm July 3rd 2014 Pacific/Fiji', and want that
> to keep meaning 6pm Fiji time no matter what decisions the Fijian
> government makes over the next two years, you need to store the
> wallclock (local) time and the timezone. The wallclock time remains
> fixed, but the conversion to UTC may float.
>
> If you are storing an point in time that remains stable no matter
> future political decisions, you store UTC time and an offset. The
> conversion to wallclock time may float, and your 6pm Fiji time meeting
> might change to 5pm or 7pm depending on the policical edicts.

This is a pretty good point. You would want the first of these
(probably) for the time a meeting is scheduled to start, and the
second for the time of a meteor shower (or some other natural event
that doesn't care what the politicians decide).

I feel like the second of these is pretty well handled by our existing
timestamptz data type. Granted, you can't store the intended display
time zone, but putting it in a separate column is not really a
problem: at least, it has the right semantics. So maybe the first is
the one we should be aiming at. If so, storing a counter and a time
zone is the wrong approach: you need to record something like <year,
month, day, hour, minute, second, tzname>.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Jul 18, 2011, at 12:29 AM, Robert Haas wrote:
> On Fri, Jul 8, 2011 at 11:13 AM, Stuart Bishop <stuart(at)stuartbishop(dot)net> wrote:
>> On Mon, Jun 6, 2011 at 7:50 AM, Jim Nasby <jim(at)nasby(dot)net> wrote:
>>> On Jun 4, 2011, at 3:56 AM, Greg Stark wrote:
>>>> On Thu, Jun 2, 2011 at 8:58 PM, Jim Nasby <jim(at)nasby(dot)net> wrote:
>>>>>
>>>>> I'm torn between whether the type should store the original time or the original time converted to GMT.
>>>>
>>>> This is the wrong way to think about it. We *never* store time
>>>> "converted to GMT". When we want to represent a point in time we
>>>> represent it as seconds since the epoch.
>>> Right. Sorry, my bad.
>>>
>>>> The question here is how to represent more complex concepts than
>>>> simply points in time. I think the two concepts under discussion are
>>>> a) a composite type representing a point in time and a timezone it
>>>> should be interpreted in for operations and display and b) the
>>>> original input provided which is a text string with the constraint
>>>> that it's a valid input which can be interpreted as a point in time.
>>>
>>> My fear with A is that something could change that would make it impossible to actually get back to the time that was originally entered. For example, a new version of the timezone database could change something. Though, that problem also exists for timestamptz today, so presumably if it was much of an issue we'd have gotten complaints by now.
>>
>> The common problem is daylight savings time being declared or
>> cancelled. This happens numerous times throughout the year, often with
>> short notice.
>>
>> If you want to store '6pm July 3rd 2014 Pacific/Fiji', and want that
>> to keep meaning 6pm Fiji time no matter what decisions the Fijian
>> government makes over the next two years, you need to store the
>> wallclock (local) time and the timezone. The wallclock time remains
>> fixed, but the conversion to UTC may float.
>>
>> If you are storing an point in time that remains stable no matter
>> future political decisions, you store UTC time and an offset. The
>> conversion to wallclock time may float, and your 6pm Fiji time meeting
>> might change to 5pm or 7pm depending on the policical edicts.
>
> This is a pretty good point. You would want the first of these
> (probably) for the time a meeting is scheduled to start, and the
> second for the time of a meteor shower (or some other natural event
> that doesn't care what the politicians decide).
>
> I feel like the second of these is pretty well handled by our existing
> timestamptz data type. Granted, you can't store the intended display
> time zone, but putting it in a separate column is not really a
> problem: at least, it has the right semantics. So maybe the first is
> the one we should be aiming at. If so, storing a counter and a time
> zone is the wrong approach: you need to record something like <year,
> month, day, hour, minute, second, tzname>.

Right; you need a timestamp and you need to know what timezone that timestamp was entered in. That means you can always convert that time to whatever timezone you'd like (like timestamptz), but you also know what time was originally entered, and what timezone it was entered in. Technically you can do that with a separate field, but that seems really ugly to me.

So what we're proposing is a new data type that stores a timestamp as well as the timezone that that time was originally entered in. We can't just store a 3 letter timezone abbreviation, because the mapping from 3 letter TZs to actual TZs is not fixed (see the timezone_abbreviations GUC). I believe the best way to handle this is a system table that stores the name of every timezone that the database has ever loaded from the timezone data files, along with an OID. That means that the storage for this is just a timestamp and an OID.
--
Jim C. Nasby, Database Architect jim(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net

Jim,

> Right; you need a timestamp and you need to know what timezone that timestamp was entered in. That means you can always convert that time to whatever timezone you'd like (like timestamptz), but you also know what time was originally entered, and what timezone it was entered in. Technically you can do that with a separate field, but that seems really ugly to me.

I disagree. It's a good mapping of the actual data.

The timestamp and the timezone in which that timestamp was entered are
two separate pieces of data and *ought* to be in two separate fields.
For one thing, the question of "what timezone was this entered in" is an
application-specific question, since you have three different potential
timezones:

* the actual client timezone
* the actual server timezone
* the application timezone if the application has configurable timezones

In a builtin data type, which of those three would you pick? Only the
application knows.

Additionally, if you have your timestamp-with-original-timezone data
type, then you're going to need to recode every single
timestamp-handling function and operator to handle the new type.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

Excerpts from Josh Berkus's message of lun jul 18 18:37:15 -0400 2011:

> The timestamp and the timezone in which that timestamp was entered are
> two separate pieces of data and *ought* to be in two separate fields.
> For one thing, the question of "what timezone was this entered in" is an
> application-specific question, since you have three different potential
> timezones:
>
> * the actual client timezone
> * the actual server timezone
> * the application timezone if the application has configurable timezones
>
> In a builtin data type, which of those three would you pick? Only the
> application knows.

I think this whole discussion is built on the assumption that the client
timezone and the application timezone are one thing and the same; and
the server timezone is not relevant at all. If the app TZ is not the
client TZ, then the app will need fixed.

> Additionally, if you have your timestamp-with-original-timezone data
> type, then you're going to need to recode every single
> timestamp-handling function and operator to handle the new type.

I have my doubts about that, and I hope not. These details haven't been
discussed at all; I only started this thread to get community approval
on cataloguing the TZs.

--
Álvaro Herrera <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Josh Berkus <josh(at)agliodbs(dot)com> wrote:

> The timestamp and the timezone in which that timestamp was entered
> are two separate pieces of data and *ought* to be in two separate
> fields.

So, if you're grabbing a timestamp and the time zone for it, how do
you ensure you've done that atomically if you're at the boundary of
a DST change? The difficulty of grabbing both such that they are
guaranteed to correspond suggests to me that they really form a
single logical value.

> For one thing, the question of "what timezone was this entered in"
> is an application-specific question, since you have three
> different potential timezones:
>
> * the actual client timezone
> * the actual server timezone
> * the application timezone if the application has configurable
> timezones
>
> In a builtin data type, which of those three would you pick?

Well clearly the only one *PostgreSQL* would "pick" is one assigned
within the database server; otherwise, for a data type like this the
value coming over the wire should specify it.

If I want the client side value (in Java) it's easy enough to get
such a value. "new GregorianCalendar()" is described thusly:

| Constructs a default GregorianCalendar using the current time in
| the default time zone with the default locale.

How does Java assign those defaults? Why should PostgreSQL care?
It's got the means to do so for itself. The point is, people can
easily establish such a value on the client side; why not on the
server side?

> Only the application knows [whether it should pick the value or
> let the database pick it].

When are things otherwise? Obviously the application will assign it
or choose to let the server assign it (if that's the right thing).

> Additionally, if you have your timestamp-with-original-timezone
> data type, then you're going to need to recode every single
> timestamp-handling function and operator to handle the new type.

Why? I think you'd want to add some *new* casts and operators for
the new data type; I don't see why any existing ones would need to
be modified.

-Kevin

"Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
> Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>> The timestamp and the timezone in which that timestamp was entered
>> are two separate pieces of data and *ought* to be in two separate
>> fields.

> So, if you're grabbing a timestamp and the time zone for it, how do
> you ensure you've done that atomically if you're at the boundary of
> a DST change?

In my view of the world, the timezone that you are in is not an object
that changes across a DST boundary. So the above is a red herring.
It is only a problem if you insist on a broken concept of what a
timezone is.

regards, tom lane

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
>> Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>>> The timestamp and the timezone in which that timestamp was
>>> entered are two separate pieces of data and *ought* to be in two
>>> separate fields.
>
>> So, if you're grabbing a timestamp and the time zone for it, how
>> do you ensure you've done that atomically if you're at the
>> boundary of a DST change?
>
> In my view of the world, the timezone that you are in is not an
> object that changes across a DST boundary.

You're right -- the moment in time should be fixed like in the
current PostgreSQL "timestamp with time zone", and the time zone
doesn't change with DST. Not an intentional read herring, but
definitely some muddy thinking there.

That weakens the argument for such a data type. Even with that, I
suspect that its value as a convenience for application programmers
would be sufficient that an extension to provide such functionality
would get used. Essentially the current timestamptz bundled with a
time zone and which is, by default, displayed "at time zone" of the
attached time zone on output.

-Kevin

On 19 July 2011 17:11, Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
>>> Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>>>> The timestamp and the timezone in which that timestamp was
>>>> entered are two separate pieces of data and *ought* to be in two
>>>> separate fields.
>>
>>> So, if you're grabbing a timestamp and the time zone for it, how
>>> do you ensure you've done that atomically if you're at the
>>> boundary of a DST change?
>>
>> In my view of the world, the timezone that you are in is not an
>> object that changes across a DST boundary.
>
> You're right -- the moment in time should be fixed like in the
> current PostgreSQL "timestamp with time zone", and the time zone
> doesn't change with DST.  Not an intentional read herring, but
> definitely some muddy thinking there.

There was an earlier point made that if someone puts eg 5pm local time
two years in the future into the database, and then the DST boundary
gets moved subsequently, some applications would like the value to
still say 5pm local time, even though that means it now refers to a
different point in absolute time - this potentially seems like a
useful feature. Retroactive timezone changes wouldn't make a lot of
sense in this case though...

I guess there are three concepts of time here - an absolute fixed time
with no reference to a timezone, a time with a timezone that is still
set as a fixed point in time, or a local time in a specific timezone
that would move if the timezone definition changed.

Ian

Alvaro, Kevin,

>> In a builtin data type, which of those three would you pick? Only the
>> application knows.
>
> I think this whole discussion is built on the assumption that the client
> timezone and the application timezone are one thing and the same; and
> the server timezone is not relevant at all. If the app TZ is not the
> client TZ, then the app will need fixed.

Not at all. Consider a hosted webapp where the user is allowed to set
their own timezone, but you use pooled connections. In that case, the
app is going to be handling user timezones with an AT TIME ZONE, not
with a SET TIMEZONE=""

> I have my doubts about that, and I hope not. These details haven't been
> discussed at all; I only started this thread to get community approval
> on cataloguing the TZs.

I am strongly in favor of having a *timezone* data type and some system
whereby we can uniquely identify timezones in the Zic database. That
would be tremendously useful for all sorts of things. I'm just
asserting that those who want a composite timestamp+saved-time-zone data
type have not thought about all of the complications involved.

> So, if you're grabbing a timestamp and the time zone for it, how do
> you ensure you've done that atomically if you're at the boundary of
> a DST change? The difficulty of grabbing both such that they are
> guaranteed to correspond suggests to me that they really form a
> single logical value.

Not relevant, given that (hopefully) the conception of a time zone
should exist independantly of whether it's currently in DST or not.
That is, the time zone is NOT "-07". The time zone is "US/Pacific".

> Why? I think you'd want to add some *new* casts and operators for
> the new data type; I don't see why any existing ones would need to
> be modified.

That would work too. What I'm pointing out is that we can't implement
the new type using just one-line modifications to the old operators and
functions.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

On Jul 19, 2011, at 2:06 PM, Josh Berkus wrote:

> I am strongly in favor of having a *timezone* data type and some system
> whereby we can uniquely identify timezones in the Zic database.

CREATE OR REPLACE FUNCTION is_timezone(
tz CITEXT
) RETURNS BOOLEAN LANGUAGE plpgsql STABLE AS $$
BEGIN
PERFORM NOW() AT TIME ZONE tz;
RETURN TRUE;
EXCEPTION WHEN invalid_parameter_value THEN
RETURN FALSE;
END;
$$;

CREATE DOMAIN timezone AS CITEXT CHECK ( is_timezone( VALUE ) );

Best,

David

On Jul 19, 2011, at 4:20 PM, David E. Wheeler wrote:
> On Jul 19, 2011, at 2:06 PM, Josh Berkus wrote:
>
>> I am strongly in favor of having a *timezone* data type and some system
>> whereby we can uniquely identify timezones in the Zic database.
>
> CREATE OR REPLACE FUNCTION is_timezone(
> tz CITEXT
> ) RETURNS BOOLEAN LANGUAGE plpgsql STABLE AS $$
...
> CREATE DOMAIN timezone AS CITEXT CHECK ( is_timezone( VALUE ) );

Storing giant globs of text with every timestamp field is really ugly.
--
Jim C. Nasby, Database Architect jim(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net

On Jul 19, 2011, at 11:22 AM, Ian Caulfield wrote:
> On 19 July 2011 17:11, Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
>> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
>>>> Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>>>>> The timestamp and the timezone in which that timestamp was
>>>>> entered are two separate pieces of data and *ought* to be in two
>>>>> separate fields.
>>>
>>>> So, if you're grabbing a timestamp and the time zone for it, how
>>>> do you ensure you've done that atomically if you're at the
>>>> boundary of a DST change?
>>>
>>> In my view of the world, the timezone that you are in is not an
>>> object that changes across a DST boundary.
>>
>> You're right -- the moment in time should be fixed like in the
>> current PostgreSQL "timestamp with time zone", and the time zone
>> doesn't change with DST. Not an intentional read herring, but
>> definitely some muddy thinking there.
>
> There was an earlier point made that if someone puts eg 5pm local time
> two years in the future into the database, and then the DST boundary
> gets moved subsequently, some applications would like the value to
> still say 5pm local time, even though that means it now refers to a
> different point in absolute time - this potentially seems like a
> useful feature. Retroactive timezone changes wouldn't make a lot of
> sense in this case though...

Right; and timezone's aren't supposed to change retroactively. The ZIC database is specifically setup so that it knows the history of TZ changes and deals with the past correctly.

> I guess there are three concepts of time here - an absolute fixed time
> with no reference to a timezone, a time with a timezone that is still
> set as a fixed point in time, or a local time in a specific timezone
> that would move if the timezone definition changed.

Or, another way to put the third class: a timestamp that remembers what it's original timezone was so that you can refer to it a common timezone (such as UTC), OR you can refer to it at it's original, local time. That's our exact need for this: we have different businesses that operate in different timezones. Generally, we only care about things in local time, but there are cases (such as event logging) where we could care about local *OR* unified time.
--
Jim C. Nasby, Database Architect jim(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net

On Jul 21, 2011, at 2:39 PM, Jim Nasby wrote:

>> CREATE OR REPLACE FUNCTION is_timezone(
>> tz CITEXT
>> ) RETURNS BOOLEAN LANGUAGE plpgsql STABLE AS $$
> ...
>> CREATE DOMAIN timezone AS CITEXT CHECK ( is_timezone( VALUE ) );
>
> Storing giant globs of text with every timestamp field is really ugly.

You work with what you've got.

David

On Jul 19, 2011, at 4:06 PM, Josh Berkus wrote:
>> I have my doubts about that, and I hope not. These details haven't been
>> discussed at all; I only started this thread to get community approval
>> on cataloguing the TZs.
>
> I am strongly in favor of having a *timezone* data type and some system
> whereby we can uniquely identify timezones in the Zic database. That
> would be tremendously useful for all sorts of things. I'm just
> asserting that those who want a composite timestamp+saved-time-zone data
> type have not thought about all of the complications involved.

Having to deal with timezone's completely separate from their timestamps is a huge PITA. That said, if we had a timezone datatype there's at least the possibility of using a composite type to deal with all of this. Or at least we can just create a custom datatype using existing tools... the only part of this that I see that actually requires closer core support is the timezone data itself.

So if the community is OK with adding a timezone datatype then we can focus on that and leave the timestamptztz data type as an add-on (at least assuming we don't run into any gotchas).

Alvaro, please speak up if there's any technical issues here that I've missed?
--
Jim C. Nasby, Database Architect jim(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net

Jim Nasby <jim(at)nasby(dot)net> writes:
> On Jul 19, 2011, at 11:22 AM, Ian Caulfield wrote:
>> There was an earlier point made that if someone puts eg 5pm local time
>> two years in the future into the database, and then the DST boundary
>> gets moved subsequently, some applications would like the value to
>> still say 5pm local time, even though that means it now refers to a
>> different point in absolute time - this potentially seems like a
>> useful feature. Retroactive timezone changes wouldn't make a lot of
>> sense in this case though...

> Right; and timezone's aren't supposed to change retroactively. The ZIC database is specifically setup so that it knows the history of TZ changes and deals with the past correctly.

You haven't noticed that at least two or three times a year, there are
"historical corrections" in the ZIC database? The mapping between local
time and UTC might be less likely to change for a time instant in the
past than one in the future, but it would be folly to assume that it's
immutable in either direction.

regards, tom lane

On Thu, Jul 21, 2011 at 5:48 PM, Jim Nasby <jim(at)nasby(dot)net> wrote:
> On Jul 19, 2011, at 4:06 PM, Josh Berkus wrote:
>>> I have my doubts about that, and I hope not.  These details haven't been
>>> discussed at all; I only started this thread to get community approval
>>> on cataloguing the TZs.
>>
>> I am strongly in favor of having a *timezone* data type and some system
>> whereby we can uniquely identify timezones in the Zic database.  That
>> would be tremendously useful for all sorts of things.  I'm just
>> asserting that those who want a composite timestamp+saved-time-zone data
>> type have not thought about all of the complications involved.
>
> Having to deal with timezone's completely separate from their timestamps is a huge PITA. That said, if we had a timezone datatype there's at least the possibility of using a composite type to deal with all of this. Or at least we can just create a custom datatype using existing tools... the only part of this that I see that actually requires closer core support is the timezone data itself.
>
> So if the community is OK with adding a timezone datatype then we can focus on that and leave the timestamptztz data type as an add-on (at least assuming we don't run into any gotchas).

As I have been watching this whole thread, my inclination has been to
look at this from a "Prolog" perspective, where we think about the
database as indicating a series of assertions about facts, from which
we then try to reason.

I suspect that determining what *really* needs to get recorded depends
on this. And it seems to me that trying to head down the path of
defining oid-based lookups of timezone names may be putting the cart
before the horse.

There are a number of facts about a timestamp:

1. What time did the database server think it was?

SELECT NOW();

captures the database's concept of what time it was, complete with:
a) The time, based, I think, on UT1. With the caveat that there's no
certainty that the DB server's time is necessarily correct.
b) An encoding of the timezone offset based on the value of the
TimeZone GUC for this connection.

If one is running an NTP daemon, pointing to a decently-connected
network of NTP servers, then it's likely that this time is pretty
accurate. And most of the time, I'd be inclined to treat this as
authoritative, and contend that anything else is likely to be less
correct and less easy to work with.

The goal of this discussion thread is to record another timestamp with
a different basis. It's not entirely clear what is its basis. I'll
suggest one, which mostly underlines my contention that it's likely
"less correct" and "less easy to work with" than having a column
defined as...
some_timestamp timestamp with timezone default NOW()

2. Client-based timestamp, comprising two things:

a) A time, ascertained by the client.
b) A timezone, ascertained by the client.

Note that timezones are pretty open-ended. There is an authoritative
encoding defined in the tz database, but there are other values used
out there. We had to patch Slony-I to have it use 'ISO' timestamps,
and recommend running in GMT/UTC, because there are values that blow
things up.

For instance, on AIX, there is a habit for boxes to set TZ=CUT0, out
of the box, which isn't on what PostgreSQL considers to be the
"official list."

On the more whimsical side of things, Joey Hess, a Debian developer
noted for such things as ikiwiki, etckeeper, git-annex, decided to
create his very own custom timezone, "JEST", because he was irritated
about DST.

http://kitenet.net/~joey/blog/entry/howto_create_your_own_time_zone/
http://kitenet.net/~joey/blog/entry/JEST_results/

That "whimsical" entry won't be going into tzdata, and while we could
discount this case as whimsy, it's not "out there" for organizations
such as nation states to decide to legislate their own things, that we
can't be certain will necessarily get into tzdata.

There are enough aliases and possibilities of local national decisions
to make it at least somewhat troublesome to treat this as something
that can be considered fixed down to the OID level.

My conclusion would be that if someone is really, really, really keen
on capturing their own notion of timezone, then this fits with the
notion that, if they want to have something that could be treated as
remotely authoritative, they should capture a multiplicity of pieces
of datestamp information, and actively accept that this will be pretty
duplicative.

- I'd commend capturing NOW() in a timestamptz field. That gives you:
1. What time the DB server thought it was, in terms of UT1
2. What timezone it thought was tied to that connection.
- Also, I'd be inclined to capture, in plain text form:
3. A client-recorded timestamp. I'm agnostic as to whether this has
*any* validation done on it; I'd think it plausible that this is
simply a text field, that might require a human to interpret it.
4. A client-recorded timezone. This would be a plain text field, and
I'm not certain it's of any particular value to try to validate it
against any would-be authoritative list. Why shouldn't Joey be able
to use JEST?
--
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"

On Jul 21, 2011, at 5:30 PM, Christopher Browne wrote:
> - I'd commend capturing NOW() in a timestamptz field. That gives you:
> 1. What time the DB server thought it was, in terms of UT1
> 2. What timezone it thought was tied to that connection.

Except that it doesn't, and that's exactly the problem I'm trying to solve here. I want to know what timezone we were using when we put a value into timestamptz, which then got converted to UT1. Without a reliable way to store what the timezone *was* at that time, we have no way to go back to it.

Now, we can debate whether it makes more sense to store the original time without conversion to UT1, or whether we should store the time after converting it to UT1 (or whether we should offer both options), but that debate is pointless without a good way to remember what timezone it started out in.

Arguably, we could just create an add-on data type for storing that timezone information, but that seems pretty daft to me: you're stuck either storing raw text which takes what should be a 12 byte datatype up to a 20-30 byte type (8 byte timestamp + varlena + text of timezone name), or you end up with major problems trying to keep an enum in sync with what the database has available in it's ZIC database.
--
Jim C. Nasby, Database Architect jim(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net

On Thu, Jul 21, 2011 at 7:05 PM, Jim Nasby <jim(at)nasby(dot)net> wrote:
> On Jul 21, 2011, at 5:30 PM, Christopher Browne wrote:
>> - I'd commend capturing NOW() in a timestamptz field.  That gives you:
>> 1.  What time the DB server thought it was, in terms of UT1
>> 2.  What timezone it thought was tied to that connection.
>
> Except that it doesn't, and that's exactly the problem I'm trying to solve here. I want to know what timezone we were using when we put a value into timestamptz, which then got converted to UT1. Without a reliable way to store what the timezone *was* at that time, we have no way to go back to it.
>
> Now, we can debate whether it makes more sense to store the original time without conversion to UT1, or whether we should store the time after converting it to UT1 (or whether we should offer both options), but that debate is pointless without a good way to remember what timezone it started out in.
>
> Arguably, we could just create an add-on data type for storing that timezone information, but that seems pretty daft to me: you're stuck either storing raw text which takes what should be a 12 byte datatype up to a 20-30 byte type (8 byte timestamp + varlena + text of timezone name), or you end up with major problems trying to keep an enum in sync with what the database has available in it's ZIC database.

You have those same problems trying to include the time zone
information in some new timestampreallyhasthetz data type, though.

This problem reminds me a great deal of the problems associated with
managing security labels for SE-Linux. There aren't that many
distinct values, so ideally it would be nice to store an OID -> string
mapping somewhere and just store the OIDs in the main table. But a
new security label can appear at any time, and it doesn't work to have
the transaction that discovers it do the insert into the mapping
table. Time zones have the same problem, more or less. Now, maybe if
we had non-transactional tables like Alvaro keeps muttering about...

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Excerpts from Robert Haas's message of vie jul 22 11:33:09 -0400 2011:

> This problem reminds me a great deal of the problems associated with
> managing security labels for SE-Linux. There aren't that many
> distinct values, so ideally it would be nice to store an OID -> string
> mapping somewhere and just store the OIDs in the main table. But a
> new security label can appear at any time, and it doesn't work to have
> the transaction that discovers it do the insert into the mapping
> table. Time zones have the same problem, more or less. Now, maybe if
> we had non-transactional tables like Alvaro keeps muttering about...

Oh, I wasn't relating these problems to non transactional tables ...
Hmm.

--
Álvaro Herrera <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Jim,

> Arguably, we could just create an add-on data type for storing that timezone information, but that seems pretty daft to me: you're stuck either storing raw text which takes what should be a 12 byte datatype up to a 20-30 byte type (8 byte timestamp + varlena + text of timezone name), or you end up with major problems trying to keep an enum in sync with what the database has available in it's ZIC database.

Sure, although there's no getting around the portability issues. The
moment you move that data between servers, you risk having specific
timezones not be available on the second server. Or worse, be available
but have a different definition -- if, for example, you're running a
more/less updated PostgreSQL on the second server.

I'm not saying that this isn't worth solving. I could really use a
timezone datatype which was synched with ZIC in some way, and so could a
lot of other users, whether or not a timestamp + original timezone type
is available as well. But don't underestimate the scope of the problem.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

On Jul 22, 2011, at 10:33 AM, Robert Haas wrote:
> On Thu, Jul 21, 2011 at 7:05 PM, Jim Nasby <jim(at)nasby(dot)net> wrote:
>> On Jul 21, 2011, at 5:30 PM, Christopher Browne wrote:
>>> - I'd commend capturing NOW() in a timestamptz field. That gives you:
>>> 1. What time the DB server thought it was, in terms of UT1
>>> 2. What timezone it thought was tied to that connection.
>>
>> Except that it doesn't, and that's exactly the problem I'm trying to solve here. I want to know what timezone we were using when we put a value into timestamptz, which then got converted to UT1. Without a reliable way to store what the timezone *was* at that time, we have no way to go back to it.
>>
>> Now, we can debate whether it makes more sense to store the original time without conversion to UT1, or whether we should store the time after converting it to UT1 (or whether we should offer both options), but that debate is pointless without a good way to remember what timezone it started out in.
>>
>> Arguably, we could just create an add-on data type for storing that timezone information, but that seems pretty daft to me: you're stuck either storing raw text which takes what should be a 12 byte datatype up to a 20-30 byte type (8 byte timestamp + varlena + text of timezone name), or you end up with major problems trying to keep an enum in sync with what the database has available in it's ZIC database.
>
> You have those same problems trying to include the time zone
> information in some new timestampreallyhasthetz data type, though.
>
> This problem reminds me a great deal of the problems associated with
> managing security labels for SE-Linux. There aren't that many
> distinct values, so ideally it would be nice to store an OID -> string
> mapping somewhere and just store the OIDs in the main table. But a
> new security label can appear at any time, and it doesn't work to have
> the transaction that discovers it do the insert into the mapping
> table. Time zones have the same problem, more or less. Now, maybe if
> we had non-transactional tables like Alvaro keeps muttering about...

Hrm, don't we only pull in ZIC info on a reload? Or do we actually refer to it dynamically all the time? Perhaps we can enforce that we'll only recognize new TZ info as part of a config reload?

Josh Berkus also made a good point that this does introduce the risk that you could end up moving data to a different server, that has a different ZIC database (perhaps via replication); at which point the fit could hit the shan (or the excrement could impact the cooling device...). So perhaps the only reasonable way to handle this is to actually load ZIC data into the database itself.
--
Jim C. Nasby, Database Architect jim(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net

On Mon, Jul 25, 2011 at 6:26 PM, Jim Nasby <jim(at)nasby(dot)net> wrote:
> Hrm, don't we only pull in ZIC info on a reload? Or do we actually refer to it dynamically all the time? Perhaps we can enforce that we'll only recognize new TZ info as part of a config reload?

Hmm. That might work in theory, but I don't see any good way to
update every database's tz table on each reload.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Jul 25, 2011, at 6:22 PM, Robert Haas wrote:
> On Mon, Jul 25, 2011 at 6:26 PM, Jim Nasby <jim(at)nasby(dot)net> wrote:
>> Hrm, don't we only pull in ZIC info on a reload? Or do we actually refer to it dynamically all the time? Perhaps we can enforce that we'll only recognize new TZ info as part of a config reload?
>
> Hmm. That might work in theory, but I don't see any good way to
> update every database's tz table on each reload.

I'm assuming that the issue here is that multiple backends could be connected to the same database, and we don't want all of them to try to actually do the updates, only the first one that discovers the change. If that's the problem you foresee then perhaps it's a non-issue... if each backend only updates things that have actually changed, and they do that with race-free 'merge' logic, then only the first backend to attempt the update would end up finding actual work to do.

Or are you seeing a problem I'm missing?
--
Jim C. Nasby, Database Architect jim(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net

On Tue, Jul 26, 2011 at 6:45 PM, Jim Nasby <jim(at)nasby(dot)net> wrote:
> On Jul 25, 2011, at 6:22 PM, Robert Haas wrote:
>> On Mon, Jul 25, 2011 at 6:26 PM, Jim Nasby <jim(at)nasby(dot)net> wrote:
>>> Hrm, don't we only pull in ZIC info on a reload? Or do we actually refer to it dynamically all the time? Perhaps we can enforce that we'll only recognize new TZ info as part of a config reload?
>>
>> Hmm.  That might work in theory, but I don't see any good way to
>> update every database's tz table on each reload.
>
> I'm assuming that the issue here is that multiple backends could be connected to the same database, and we don't want all of them to try to actually do the updates, only the first one that discovers the change. If that's the problem you foresee then perhaps it's a non-issue... if each backend only updates things that have actually changed, and they do that with race-free 'merge' logic, then only the first backend to attempt the update would end up finding actual work to do.
>
> Or are you seeing a problem I'm missing?

What if 4 backends concurrently are the "first ones" to try to
simultaneously add "South America/Ruritania", and...

1. Connection #1 came in 'first', but rolls back its transaction.
2. Connection #2 came in 'second', and also winds up rolling back its
transaction because the connection fails due to a network problem.
3. Connection #3 actually completes. But doesn't commit until after #4.
4. Connection #4 started last, but turns out to COMMIT first.

The "merge" is a pretty bad one. They all have to try to succeed, and
in some way that doesn't block things.

Perhaps the TZ values need to not be UNIQUE, but some process can come
in afterwards and rewrite to drop out non-unique values. That's not
very nice either; that means you can't use a FK reference against the
TZ table.

Or you need to have something that comes in afterwards and repoints
tuples to the *real* TZ entry, which seems likely to be troublesome.

This just gets so ugly so fast; the attempt to save space by storing a
pointer to the TZ value is just filled with trouble (and potential
#fail).
--
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"

On Jul 26, 2011, at 5:56 PM, Christopher Browne wrote:
>> I'm assuming that the issue here is that multiple backends could be connected to the same database, and we don't want all of them to try to actually do the updates, only the first one that discovers the change. If that's the problem you foresee then perhaps it's a non-issue... if each backend only updates things that have actually changed, and they do that with race-free 'merge' logic, then only the first backend to attempt the update would end up finding actual work to do.
>>
>> Or are you seeing a problem I'm missing?
>
> What if 4 backends concurrently are the "first ones" to try to
> simultaneously add "South America/Ruritania", and...
>
> 1. Connection #1 came in 'first', but rolls back its transaction.
> 2. Connection #2 came in 'second', and also winds up rolling back its
> transaction because the connection fails due to a network problem.
> 3. Connection #3 actually completes. But doesn't commit until after #4.
> 4. Connection #4 started last, but turns out to COMMIT first.

Ugh, I didn't realize that a reload would take effect in the middle of a transaction. That certainly kills what I proposed.

Though, now I'm wondering why this info would need to be in every database anyway... certainly this should be treated as global data, and if that's the case then only one process needs to update it. Though I'm not sure if it's possible for global data to be ACID.

Anyway, rather than continue this on-list, I'm going to get Alvaro to think about it in more detail and see what he comes up with.
--
Jim C. Nasby, Database Architect jim(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net