Re: information_schema.columns changes needed for OLEDB

On Monday 08 June 2009 07:12:33 Tom Lane wrote:
> Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> > On Sunday 31 May 2009 18:41:55 Tom Lane wrote:
> >> 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.
> >
> > The DATE change is the only thing I'd be prepared to make right now.
>
> At this point I think the clear decision is "we're not changing anything
> for 8.4". I've put the issue on the TODO list for future development
> cycles.

After gathering that there will probably be some other changes before
release that will require an initdb (even without catversion bump), and
after reexamining the issue, I think it's trivial and uncontroversial to
fix the datetime issues:

diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql
index 9c5672f..cb0296a 100644
--- a/src/backend/catalog/information_schema.sql
+++ b/src/backend/catalog/information_schema.sql
@@ -160,12 +160,12 @@ CREATE FUNCTION _pg_datetime_precision(typid oid, typmod int4) RETURNS integer
RETURNS NULL ON NULL INPUT
AS
$$SELECT
- CASE WHEN $2 = -1 /* default typmod */
- THEN null
+ CASE WHEN $1 IN (1082) /* date */
+ THEN 0
WHEN $1 IN (1083, 1114, 1184, 1266) /* time, timestamp, same + tz */
- THEN $2
+ THEN CASE WHEN $2 = -1 THEN 6 ELSE $2 END
WHEN $1 IN (1186) /* interval */
- THEN $2 & 65535
+ THEN CASE WHEN $2 = -1 THEN 6 ELSE $2 & 65535 END
ELSE null
END$$;

I have also prepared a patch that creates more realistic values for
character_octet_length based on encoding information, which I will propose
for 8.5. The issue of whether to report null or some large value for
"unlimited" length data types needs some more thought.