Re: information_schema.columns changes needed for OLEDB

Number 4 is actually numeric_precision (I typed incorrectly). My
recollection is that numeric_precision sometimes expressed in radix 2 and it
caused issues for Windows apps.

I agree on other issues. I was curious if database can help OLEDB driver (to
make it simpler). Anyway it can emulate values for specific Windows apps on
the fly. Thank you!

On Sat, May 23, 2009 at 2:57 PM, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:

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