Re: information_schema.columns changes needed for OLEDB

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> On Friday 22 May 2009 18:27:01 Konstantin Izmailov wrote:
>> [ discussion of some details of information_schema results ]

Are we going to do anything about any of these issues?
In particular,

>> 6. datetime_precision field, minor changes
>>
>> Code around: change value on the fly, e.g.
>>
>> [date] set value to zero

> Makes sense. I think this is not correct at the moment.

AFAICS, the SQL standard demands that precision and scale fields be
non-null all the time for those data types where they make sense
(this is encoded in the CHECK CONSTRAINTs that are declared for the
various information-schema tables, see particularly 21.15
DATA_TYPE_DESCRIPTOR base table in SQL99). DATE is clearly wrong
per spec, but it's not the only problem.

Our interpretation has been to set these values to null if the typmod
is defaulted, which is reasonable in the abstract but it's still a
violation of spec. I wonder whether we should be inserting some large
limit value instead.

For the datetime types we actually have a perfectly good default
precision value, namely six digits, if the DB is using integer datetimes
--- and I don't think there's a strong argument not to use this value
for float timestamps too.

I'd prefer to avoid a catversion bump at this stage of the cycle,
but it looks like any changes here would merely involve the bodies of
some functions in information_schema.sql. I think we could just change
them without a catversion bump. Any beta testers who actually care
could easily insert the new definitions without an initdb.

regards, tom lane