Quantcast

A renewed plea for inclusion of zone.tab

classic Classic list List threaded Threaded
19 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

A renewed plea for inclusion of zone.tab

Andrew Gierth
Back before 8.2 came out, I posted:

> Any view over the full timezone names should also include the
> corresponding data from zone.tab in the timezone library source.

I got no meaningful response to this (Tom responded with an erroneous
statement and ignored my explanation of his mistake).

Back before 8.3 came out, Naz Gassiep posted on the same subject:

http://archives.postgresql.org/pgsql-hackers/2008-01/msg00217.php

and got no response at all.

He posted again:

http://archives.postgresql.org//pgsql-hackers/2008-02/msg00552.php

and got no meaningful response.

About every third month, I end up explaining on IRC to some
unfortunate user that the information he needs _is_ available, but
only by extracting zone.tab from the source and loading it up into a
table. At the VERY LEAST, can we PLEASE have the zone.tab file
INSTALLED WHERE IT BELONGS rather than simply ignored, so that even if
further requests to include the information in a system view go
unheard by -hackers, I can at least provide a pgfoundry module or
something to do the job.

To answer the inevitable responses preemptively:

1) The data in zone.tab is needed any time you want to offer a user a
sensible way of choosing his timezone; it associates timezones with
countries and provides additional detail for certain annoying edge
cases.

2) zone.tab does not include every alias name that shows up in
pg_timezone_names. IT'S NOT SUPPOSED TO INCLUDE THEM.

3) zone.tab _IS_ maintained just as carefully as the rest of the data.

I don't know exactly what needs to be changed on the Windows side
(where it is arguably even _more_ important to do this), but for
everyone else, see the attached patch.

--
Andrew (irc:RhodiumToad)



--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

zone-tab.patch (627 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: A renewed plea for inclusion of zone.tab

Tom Lane-2
Andrew Gierth <[hidden email]> writes:
> At the VERY LEAST, can we PLEASE have the zone.tab file
> INSTALLED WHERE IT BELONGS rather than simply ignored, so that even if
> further requests to include the information in a system view go
> unheard by -hackers, I can at least provide a pgfoundry module or
> something to do the job.

I still see no point in this unless we expose the information in
pg_timezone_names, which requires rather more than a one-line patch.
If you want to prepare such a patch and put it in the queue for 8.5,
go right ahead.

                        regards, tom lane

--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: A renewed plea for inclusion of zone.tab

David E. Wheeler
On Apr 6, 2009, at 5:48 PM, Tom Lane wrote:

> Andrew Gierth <[hidden email]> writes:
>> At the VERY LEAST, can we PLEASE have the zone.tab file
>> INSTALLED WHERE IT BELONGS rather than simply ignored, so that even  
>> if
>> further requests to include the information in a system view go
>> unheard by -hackers, I can at least provide a pgfoundry module or
>> something to do the job.
>
> I still see no point in this unless we expose the information in
> pg_timezone_names, which requires rather more than a one-line patch.

Wouldn't it make it easier for Andrew or someone else to create a  
pgFoundry project for 8.4 at the same time as an 8.5 patch? That way I  
can get the functionality in 8.4 by installing the (yes, thus far  
theoretical) pgFoundry module.

What's the harm in installing zone.tab?

Best,

David

--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: A renewed plea for inclusion of zone.tab

David Fetter
In reply to this post by Tom Lane-2
On Mon, Apr 06, 2009 at 08:48:55PM -0400, Tom Lane wrote:

> Andrew Gierth <[hidden email]> writes:
> > At the VERY LEAST, can we PLEASE have the zone.tab file INSTALLED
> > WHERE IT BELONGS rather than simply ignored, so that even if
> > further requests to include the information in a system view go
> > unheard by -hackers, I can at least provide a pgfoundry module or
> > something to do the job.
>
> I still see no point in this unless we expose the information in
> pg_timezone_names, which requires rather more than a one-line patch.
> If you want to prepare such a patch and put it in the queue for 8.5,
> go right ahead.

There's really no point, and a lot of good stuff lost, in leaving this
thing out.  It's not like the few kilobytes it takes up could possibly
matter in the grand scheme of things.

Cheers,
David.
--
David Fetter <[hidden email]> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: [hidden email]

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: A renewed plea for inclusion of zone.tab

Alvaro Herrera-7
David Fetter wrote:

> On Mon, Apr 06, 2009 at 08:48:55PM -0400, Tom Lane wrote:
> > Andrew Gierth <[hidden email]> writes:
> > > At the VERY LEAST, can we PLEASE have the zone.tab file INSTALLED
> > > WHERE IT BELONGS rather than simply ignored, so that even if
> > > further requests to include the information in a system view go
> > > unheard by -hackers, I can at least provide a pgfoundry module or
> > > something to do the job.
> >
> > I still see no point in this unless we expose the information in
> > pg_timezone_names, which requires rather more than a one-line patch.
> > If you want to prepare such a patch and put it in the queue for 8.5,
> > go right ahead.
>
> There's really no point, and a lot of good stuff lost, in leaving this
> thing out.  It's not like the few kilobytes it takes up could possibly
> matter in the grand scheme of things.

Agreed, it seems to me that a patch to install zone.tab during "make
install" could be applied at this time (before beta so that packagers
don't complain that we didn't give them time to fix their file lists).
A more complete patch can be discussed for 8.5.

... though there's not much point in arguing over a patch that no one
has even submitted ...

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: A renewed plea for inclusion of zone.tab

Tom Lane-2
In reply to this post by David Fetter
David Fetter <[hidden email]> writes:
> On Mon, Apr 06, 2009 at 08:48:55PM -0400, Tom Lane wrote:
>> I still see no point in this unless we expose the information in
>> pg_timezone_names, which requires rather more than a one-line patch.

> There's really no point, and a lot of good stuff lost,

Like what?  I do not actually believe that anyone needs an interactive
geographical timezone selector based on pg_timezone_names.  Any such
application is far more likely to be looking at the system tzdata files.
In any case it would need a lot more data that we aren't supplying
(like, say, a world map...)

My fundamental objection here is that we'd be permanently widening the
API we have to support for timezones, which could come back to haunt us.
I will agree that it's not too likely upstream would stop supporting
zone.tab entirely, but it's quite likely they could change the format or
further complicate its relationship to the actual zone list.  I don't
wish to buy into that without a more credible argument that there are
actual database applications that need this data.

                        regards, tom lane

--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: A renewed plea for inclusion of zone.tab

David E. Wheeler
In reply to this post by Alvaro Herrera-7
On Apr 7, 2009, at 3:26 PM, Alvaro Herrera wrote:

> Agreed, it seems to me that a patch to install zone.tab during "make
> install" could be applied at this time (before beta so that packagers
> don't complain that we didn't give them time to fix their file lists).
> A more complete patch can be discussed for 8.5.
>
> ... though there's not much point in arguing over a patch that no one
> has even submitted ...

Andrew did, in fact, submit the patch to install zone.tab.

Best,

David

--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: A renewed plea for inclusion of zone.tab

Alvaro Herrera-7
David E. Wheeler wrote:

> On Apr 7, 2009, at 3:26 PM, Alvaro Herrera wrote:
>
>> Agreed, it seems to me that a patch to install zone.tab during "make
>> install" could be applied at this time (before beta so that packagers
>> don't complain that we didn't give them time to fix their file lists).
>> A more complete patch can be discussed for 8.5.
>>
>> ... though there's not much point in arguing over a patch that no one
>> has even submitted ...
>
> Andrew did, in fact, submit the patch to install zone.tab.

Hmm, yeah, that he did.  (Seems to be missing "make uninstall" support
though.)

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: A renewed plea for inclusion of zone.tab

Andrew Gierth
>>>>> "Alvaro" == Alvaro Herrera <[hidden email]> writes:

 >> Andrew did, in fact, submit the patch to install zone.tab.

 Alvaro> Hmm, yeah, that he did.  (Seems to be missing "make
 Alvaro> uninstall" support though.)

The rm -rf in the uninstall rule seems to be sufficient for that.

(What _is_ missing, as I said, is Windows support.)

--
Andrew (irc:RhodiumToad)

--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: A renewed plea for inclusion of zone.tab

Andrew Gierth
In reply to this post by Tom Lane-2
>>>>> "Tom" == Tom Lane <[hidden email]> writes:

 > David Fetter <[hidden email]> writes:
 >> On Mon, Apr 06, 2009 at 08:48:55PM -0400, Tom Lane wrote:
 >>> I still see no point in this unless we expose the information in
 >>> pg_timezone_names, which requires rather more than a one-line patch.

 >> There's really no point, and a lot of good stuff lost,

 Tom> Like what?  I do not actually believe that anyone needs an
 Tom> interactive geographical timezone selector based on
 Tom> pg_timezone_names.

As the bard said, "There are more things in heaven and earth, Horatio,
than are dreamed of in your philosophy."

Having been involved in writing an app that would have used exactly
such a thing had it been available, I find your disbelief somewhat ...
unpersuasive. The frequency with which the topic comes up on IRC is
also significant (it's not a multiple-times-per-week thing like window
functions or LATERAL, but it's often enough to be noticed).

The usual conversation goes something like this (generally following
on from some discussion of how to do timezone conversions):

 Q: how do I get the list of available zone names?

 A: see pg_timezone_names

 Q: but there's 1650/1400/560/452 [delete as applicable] entries in
    there!  how do I know which one to use for any given user? Can I
    work it out from the user's location?

 A: Some locations have timezones that vary by county level, so it's
    hard to automate unless you have a street address and detailed
    maps/database of Indiana and other awkward places. Best bet is to
    ask the user themselves, once you know what country they're in.

 Q: How do you know what zones are in what countries?

 A: that info is in zone.tab, which you can find either from your OS's
    timezone directory or from the postgres source for your postgres
    version. Put that data in a table or something and use it to prompt
    the user; it has text to help disambiguate the obscure cases.

 Q: ... wtf? why is that not installed anywhere?

 Tom> Any such application is far more likely to be looking at the
 Tom> system tzdata files.

Only if it's using the system TZ functions to do conversions rather
than doing them inside pg, which certainly isn't how _I'd_ recommend
an app writer do it.

 Tom> In any case it would need a lot more data that we aren't
 Tom> supplying (like, say, a world map...)

Nope. While I'm sure someone has a use for the lat/long stuff, the
more likely approach is just to select the zone names and explanatory
text for the country of interest, and let the user select a zone. No
extra data needed.

 Tom> My fundamental objection here is that we'd be permanently
 Tom> widening the API we have to support for timezones, which could
 Tom> come back to haunt us.  I will agree that it's not too likely
 Tom> upstream would stop supporting zone.tab entirely, but it's quite
 Tom> likely they could change the format or further complicate its
 Tom> relationship to the actual zone list.  I don't wish to buy into
 Tom> that without a more credible argument that there are actual
 Tom> database applications that need this data.

zone.tab doesn't become part of the API any more than the actual
content of timezone/America/New_York is part of the API. It is up to
whoever is providing an interface to access that data to cope with
any changes in format.

--
Andrew (irc:RhodiumToad)

--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: A renewed plea for inclusion of zone.tab

Chris Browne
[hidden email] (Andrew Gierth) writes:

> The usual conversation goes something like this (generally following
> on from some discussion of how to do timezone conversions):
>
>  Q: how do I get the list of available zone names?
>
>  A: see pg_timezone_names
>
>  Q: but there's 1650/1400/560/452 [delete as applicable] entries in
>     there!  how do I know which one to use for any given user? Can I
>     work it out from the user's location?
>
>  A: Some locations have timezones that vary by county level, so it's
>     hard to automate unless you have a street address and detailed
>     maps/database of Indiana and other awkward places. Best bet is to
>     ask the user themselves, once you know what country they're in.
>
>  Q: How do you know what zones are in what countries?
>
>  A: that info is in zone.tab, which you can find either from your OS's
>     timezone directory or from the postgres source for your postgres
>     version. Put that data in a table or something and use it to prompt
>     the user; it has text to help disambiguate the obscure cases.
>
>  Q: ... wtf? why is that not installed anywhere?

I can confirm having recently hit something rather like this...

We wanted to indicate which timezone people were in, but in the
absence of having something like zone.tab conveniently available,
people were starting to talk about designing their own ad-hoc, buggy
version of something looking like about half of zone.tab.

>  Tom> Any such application is far more likely to be looking at the
>  Tom> system tzdata files.
>
> Only if it's using the system TZ functions to do conversions rather
> than doing them inside pg, which certainly isn't how _I'd_ recommend
> an app writer do it.

Right.  Having zone.tab data available in the DB would be *way* more
convenient, especially in that it is not at all obvious that it's
always available on systems, let alone in a consistent place.

This isn't an argument for "it must be considered standard,
yesterday."  But it's plenty valuable to look to having a way to parse
zone.tab and turn it into a table or two.
--
let name="cbbrowne" and tld="cbbrowne.com" in name ^ "@" ^ tld;;
http://linuxdatabases.info/info/linuxxian.html
"The only thing  better than TV with the  sound off is  Radio with the
sound off." -- Dave Moon

--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: A renewed plea for inclusion of zone.tab

Josh Berkus
In reply to this post by Andrew Gierth
Tom,

>  Like what?  I do not actually believe that anyone needs an
>  interactive geographical timezone selector based on
>  pg_timezone_names.

Actually, considering that PostgreSQL is the leading open source GIS
database, I expect that a *lot* of people want this.  Or, at least,
enough data to ad-hoc something themselves.

--
Josh Berkus
PostgreSQL Experts Inc.
www.pgexperts.com

--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: A renewed plea for inclusion of zone.tab

Tom Lane-2
Josh Berkus <[hidden email]> writes:
> Tom,
>> Like what?  I do not actually believe that anyone needs an
>> interactive geographical timezone selector based on
>> pg_timezone_names.

> Actually, considering that PostgreSQL is the leading open source GIS
> database, I expect that a *lot* of people want this.

Surely we'd have seen more complaints, then.

                        regards, tom lane

--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: A renewed plea for inclusion of zone.tab

David Fetter
On Fri, Apr 10, 2009 at 03:01:05PM -0400, Tom Lane wrote:

> Josh Berkus <[hidden email]> writes:
> > Tom,
> >> Like what?  I do not actually believe that anyone needs an
> >> interactive geographical timezone selector based on
> >> pg_timezone_names.
>
> > Actually, considering that PostgreSQL is the leading open source
> > GIS database, I expect that a *lot* of people want this.
>
> Surely we'd have seen more complaints, then.

You're setting a pretty high bar here for a pretty small change which
will cause a pretty large increase in convenience.  What is the actual
problem here?

Cheers,
David.
--
David Fetter <[hidden email]> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: [hidden email]

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: A renewed plea for inclusion of zone.tab

Tom Lane-2
David Fetter <[hidden email]> writes:
> You're setting a pretty high bar here for a pretty small change which
> will cause a pretty large increase in convenience.  What is the actual
> problem here?

I gave my reasoning before: widening this API has possibly nontrivial
future maintenance costs, and the actual use-case for the data is
unconvincing.

                        regards, tom lane

--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: A renewed plea for inclusion of zone.tab

Robert Haas
In reply to this post by Tom Lane-2
On Fri, Apr 10, 2009 at 3:01 PM, Tom Lane <[hidden email]> wrote:

> Josh Berkus <[hidden email]> writes:
>> Tom,
>>> Like what?  I do not actually believe that anyone needs an
>>> interactive geographical timezone selector based on
>>> pg_timezone_names.
>
>> Actually, considering that PostgreSQL is the leading open source GIS
>> database, I expect that a *lot* of people want this.
>
> Surely we'd have seen more complaints, then.

This idea is at least +5 just on this thread; more significant changes
have been made with less support.

...Robert

--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: A renewed plea for inclusion of zone.tab

Richard Rowell-2
In reply to this post by Andrew Gierth
>Surely we'd have seen more complaints, then.
> regards, tom lane

This gets a definite +1 here as we are using "SET TIMEZONE" at the
beginning of each transaction so that each user sees/records dates
automatically in whatever timezone they have associated with them.
Works beautifully with very little help from the application side.
The only downside is finding a way to give the user an appropriate
list of timezones to choose from.

--
"An eye for eye only ends up making the whole world blind." -- Mohandas Gandhi

--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: A renewed plea for inclusion of zone.tab

David E. Wheeler
In reply to this post by Tom Lane-2
On Apr 10, 2009, at 12:15 PM, Tom Lane wrote:

> I gave my reasoning before: widening this API has possibly nontrivial
> future maintenance costs, and the actual use-case for the data is
> unconvincing.

It seems to me that the immediate patch to simply copy zone.tab has no  
effect on the API. That's a debate worth having, but I don't think  
it's relevant to this particular patch, is it?

Best,

David

--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: A renewed plea for inclusion of zone.tab

Tom Lane-2
"David E. Wheeler" <[hidden email]> writes:
> On Apr 10, 2009, at 12:15 PM, Tom Lane wrote:
>> I gave my reasoning before: widening this API has possibly nontrivial
>> future maintenance costs, and the actual use-case for the data is
>> unconvincing.

> It seems to me that the immediate patch to simply copy zone.tab has no  
> effect on the API. That's a debate worth having, but I don't think  
> it's relevant to this particular patch, is it?

Well, as far as I can see the immediate patch is pretty useless without
an API change in front of it.  Andrew was threatening to write a
pgfoundry module that read from the file directly, but considering that
such a thing would require superuser privileges it doesn't seem like a
particularly attractive answer.

The immediate patch has other bogosities too: why not iso3166.tab too,
if we are going to start exposing this data?  Without an agreed-on API
change we don't really know what we need or why.

                        regards, tom lane

--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Loading...