Re: storing TZ along timestamps

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?"