Thread: Re: [COMMITTERS] pgsql/src/include/catalog (pg_type.h)

> Hmm, numeric array type was missing too.  Added.
> Of the standard types, only 'timestamp' seems not to have an array 
> type; should it be added, or are we going to remove that type for 7.0 
> anyway?

Will be removed/replaced.
                 - Thomas

-- 
Thomas Lockhart                lockhart@alumni.caltech.edu
South Pasadena, California

> > Hmm, numeric array type was missing too.  Added.
> > Of the standard types, only 'timestamp' seems not to have an array 
> > type; should it be added, or are we going to remove that type for 7.0 
> > anyway?
> 
> Will be removed/replaced.

We are going to internally move everything to the more standard ANSI
names, right, or do we give preference to the older types?

--  Bruce Momjian                        |  http://www.op.net/~candle pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 

> We are going to internally move everything to the more standard ANSI
> names, right, or do we give preference to the older types?

Well, that could be up for discussion. The "internal" abstime/reltime
types are direct copies of Unix system time, which most systems
support at a fundamental level. Moving to timestamp/interval will
double the storage size of those fields, with no increase in
functionality afaik.

Peter brought up changing one field to timestamp; that would have the
benefit of being able to specify times past y2038.
                  - Thomas

-- 
Thomas Lockhart                lockhart@alumni.caltech.edu
South Pasadena, California

On 2000-01-18, Thomas Lockhart mentioned:

> > We are going to internally move everything to the more standard ANSI
> > names, right, or do we give preference to the older types?
> 
> Well, that could be up for discussion. The "internal" abstime/reltime

We might as well make that change now rather than dragging the old baggage
(8 different types after all!) around for another major release. I don't
mean dropping them but putting forth a clear preference.

Preferred set: timestamp, interval, date, time

timespan: alias to interval, for compatibility
datetime: alias to timestamp, for compatibility

abstime, reltime: deprecated, used only for internal catalogs

I mean that would make sense to me as a user. I have long been confused
about that.

> types are direct copies of Unix system time, which most systems
> support at a fundamental level. Moving to timestamp/interval will

The problem also seems to be that on some systems they seem to be 8 byte
types (see original TODO item). So either you move it to proper int32
types, thus losing the exact correspondence, or you make them aliases to
timespan and interval as well and lose them sometime.

> double the storage size of those fields, with no increase in
> functionality afaik.

Isn't storage size in multiples of 8192 anyway? So this probably makes
zero difference in practice.

> Peter brought up changing one field to timestamp; that would have the
> benefit of being able to specify times past y2038.

The Y2038 problem is next. We could be the first ones to comply. :)

-- 
Peter Eisentraut                  Sernanders väg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden

> We might as well make that change now rather than dragging the old baggage
> (8 different types after all!) around for another major release. I don't
> mean dropping them but putting forth a clear preference.
> Preferred set: timestamp, interval, date, time
> timespan: alias to interval, for compatibility
> datetime: alias to timestamp, for compatibility
> abstime, reltime: deprecated, used only for internal catalogs
> I mean that would make sense to me as a user. I have long been confused
> about that.

Hmm. I *think* I state a clear preference in the User's Guide. Is
there another place to mention this? Should we be more explicit?? If
we're going to fix it up, we need some suggestions ;)

> The problem also seems to be that on some systems they seem to be 8 byte
> types (see original TODO item). So either you move it to proper int32
> types, thus losing the exact correspondence, or you make them aliases to
> timespan and interval as well and lose them sometime.

That's a detail on 64 bit systems like Alpha/Unix, but afaik one can
force the field into 4 bytes and you get the Right Thing, at least
until 2038. I'd prefer moving to an 8 byte integer, but we don't have
those on enough of our supported platforms, so the 8 byte float is the
next best thing to get past 2038.

> > double the storage size of those fields, with no increase in
> > functionality afaik.
> Isn't storage size in multiples of 8192 anyway? So this probably makes
> zero difference in practice.

It actually makes a big difference on the simplest tests, which have a
single small column. Then, the tuple overhead is most obvious, and
(I'm not sure of the actual numbers) going from 40 bytes to 60 bytes
is significant.

> > Peter brought up changing one field to timestamp; that would have the
> > benefit of being able to specify times past y2038.
> The Y2038 problem is next. We could be the first ones to comply. :)

Since we are currently mapping to Unix system time, I'd rather go slow
and wait for a good OS solution. Or we could go to 8 byte integers
with 100ns ticks a la Corba Time (hmm, maybe we can get an
implementation from somewhere which would work on all of our
platforms??). The double we currently have for user time isn't likely
to be what OSes end up using, though with our license they could ;)
                    - Thomas

-- 
Thomas Lockhart                lockhart@alumni.caltech.edu
South Pasadena, California

On 2000-01-19, Thomas Lockhart mentioned:

> Hmm. I *think* I state a clear preference in the User's Guide. Is

Yeah, they say datetime is the "best general date and time" type.

> there another place to mention this? Should we be more explicit?? If
> we're going to fix it up, we need some suggestions ;)

The users still look at 8 different types and which gets mapped to what in
"some future release". I was thinking along the lines of

DATE/TIME TYPES

We have these (SQL compat.) types: timestamp, date, time, interval
[ these four types have clearly distinct functionality, so there is no
need for "preferences" ]

<body of description here>

Appendix/Note:
To ensure compatibility to earlier versions of PostgreSQL we also continue
to provide datetime (equivalent to timestamp), timespan (equivalent to
interval). The types abstime and reltime are lower precision types which
are used internally. You are discouraged from using any of these types in
new applications and move your old ones over where appropriate. Any or all
of these type might disappear in a future release. [ 7.1 or 7.2 I guess ]


If you want me to help writing something like this up, tell me.

I'd also envision a similar change to the documentation of the numerical
types. The way it currently looks is "Okay, this is what those standard
guys say and this is what _we_ say. You can use the standard stuff but our
stuff gets is implemented natively, so it's your pick."

This is by no means to bash the documentation writers, I just like the
idea of supporting standard SQL over Postgres'isms where both are
equivalent. See also CAST vs ::, etc.

-- 
Peter Eisentraut                  Sernanders väg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden

> ... I was thinking along the lines of
> If you want me to help writing something like this up, tell me.

Well, looks like you just did. If you want to plop it into sgml and
commit it, that would be great. Otherwise, I'll steal it and do it
sometime soon ;)

> This is by no means to bash the documentation writers, I just like the
> idea of supporting standard SQL over Postgres'isms where both are
> equivalent. See also CAST vs ::, etc.

Right, I'm happy going through the docs and emphasizing SQL92 vs older
"Pig-isms" for equivalent features. For 7.0, I'd also like to go
through and reorganize the User's Guide, but I'm not sure if I'll get
time...
                   - Thomas

-- 
Thomas Lockhart                lockhart@alumni.caltech.edu
South Pasadena, California

On Thu, Jan 20, 2000 at 06:55:18PM +0100, Peter Eisentraut wrote:
> 
> Appendix/Note:
> To ensure compatibility to earlier versions of PostgreSQL we also continue
> to provide datetime (equivalent to timestamp), timespan (equivalent to
> interval).

BTW, it seems Insight's PostgreSQL ODBC 6.40.0006 driver converts

Date/Time -> datetime (rather than timestamp)

Cheers,

Patrick

On Thu, Jan 20, 2000 at 06:55:18PM +0100, Peter Eisentraut wrote:
>
> Appendix/Note:
> To ensure compatibility to earlier versions of PostgreSQL we also continue
> to provide datetime (equivalent to timestamp), timespan (equivalent to
> interval).

BTW, it seems Insight's PostgreSQL ODBC 6.40.0006 driver converts

Date/Time -> datetime (rather than timestamp)

Cheers,

Patrick