Re: Comment on timezone and interval types

On Wed, Oct 27, 2004 at 09:21:39AM +0200, Thomas Hallgren wrote:
> Martijn,
> >I agree. One issue I can think of is that if you store each timestamp
> >as a (seconds,timezone) pair, the storage requirements will balloon,
> >since timezone can be something like "Australia/Sydney" and this will
> >be repeated for every value in the table. I don't know how to deal
> >easily with this since there is no unique identifier to timezones and
> >no implicit order.
> >
> >The only solution I can think of is have initdb create a pg_timezones
> >table which assigns an OID to each timezone it finds. Then the type can
> >use that.
> >
> >I think this is a good solution actually, any thoughts?
>
> Using OID's is a good idea, but I think a canonical list of known
> timezone to OID mappings must be maintained and shipped with the
> PostgreSQL core.

How can there be a "canonical list of known timezones" if every
operating system has it's own list. Maybe you can provide a base list,
but you have to allow for people to make their own.

> If OID's are generated at initdb time, there's a great risk that the
> OID's will differ between databases using different versions of
> PostgreSQL. That in turn might have some negative implications for data
> exchange.

I doubt it, the OIDs would never be output. Types, triggers, functions
etc all have OIDs that never appear in any output anywhere, so why
should these. Since PostgreSQL doesn't support you to copying any part
of the raw data files between different installations, let alone
different versions, I think the issues with data exchange are not a
problem.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.