Re: information_schema.columns changes needed for OLEDB

On Friday 22 May 2009 18:27:01 Konstantin Izmailov wrote:
> As we discussed at pgcon2009 there are some changes/fixes necessary in
> information_schema.columns to allow correct work of applications and
> services via OLEDB on Windows. Here are some:
>
> 1. data_type field contains types names that are not recognized by MS apps.
>
> Code around: rename types on the fly, e.g.
>
> integer -> int
>
> character varying -> varchar
>
> character -> char

The spelling of these types in the information schema is fixed by the SQL
standard. We can't change that.

> timestamp without time zone -> datetime

And that would certainly be wrong for other applications, because PostgreSQL
doesn't have a datetime type.

> bytea -> image

And that we certainly can't do either.

> 2. character_maximum_length field
>
> Code around: change value for text abd bytea types
>
> [text] 1073741823

(see next item)

> [bytea] 2147483647

But bytea is not a character type in the first place, so this value is
meaningless.

> 3. character_octet_length should always be double of
> character_maximum_length (due to Unicode character size on Windows which is
> 2).

We could do something like that if we exposed the maximum octet length of a
character per encoding. But what I wonder is whether this should reflect the
server or the client encoding. How do your applications use this value?

> 4. datetime_precision field is not always correct
>
> Code around: change value of the fly, e.g. if value is not null then
>
> [numeric] keep the value (ok)
>
> [bigint] set value to 19
>
> all other set to 10

Why would numeric and bigint affect *datetime*_precision at all?

> 5. numeric_precision_radix field should always be equal to 10

Why?

> 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.

> [datetime] set value to 3

Well, it really depends on what you set it to when you declared the column,
no?