Re: Column size BUG with text/bytea with 7.4 JDBC Driver (build 213)

Hi all:

I am continually impressed with the thoughtful
responses on pgsql-jdbc ! Please see my reply at
the bottom of this message.

I originally wrote:
> > The latest JDBC driver (build 213) has a serious
> > bug for the "COLUMN_SIZE" value, obtained via
> > DBMetaData, getColumns() and then "COLUMN_SIZE.
> >
> > The column size returned for both bytea and text
> > types is -1.
> >
> > postgres _should_ return the max col size which
> > for bytea is about 1 GB (or is it 4 GB
> theoretically)

"Oliver Jowett" then wrote:

This column actually comes directly from
pg_attribute.attlen on the
backend for most types; for bytea/text this appears to
be always -1.

I note that JDBC only defines COLUMN_SIZE for char,
date, numeric, and
decimal types, so it's not particularly portable to
assume a useful
value for LONGVARBINARY columns (i.e. bytea). text
columns are a bit
harder since they're not really varchar at all, but
they map to
Types.VARCHAR as the closest equivalent JDBC type.

Nevertheless it'd be nice if the driver handled this
better.

"Kris Jurka" then wrote:

> For bytea 1GB would be a reasonable limit, but for
> text it is more
> difficult to say what the maximum size is because
> the maximum size for
> varchar fields is measured in the number of
> characters, not the number of
> bytes. The 1GB limit is a byte count limit, not a
> character count limit,
> so what would you recommend returning in the
> presence of a non single byte
> encoding?

Firstly, Oliver and Kris, after having thought more
about this, I now think we should let the driver
to behave as it does right now. This is because the
"-1" value clearly implies that bytea (varbinary)
text limits are not defined by the JDBC spec and
any attempt to return a hacked value of say 1 GB will
break at some point because of encoding and other
issues. It's better to fail-fast upfront and let the
driver warn us that this value cannot be know with
certainty.

I ended up creating a database abstraction layer
with a abstract class called "DBspecific" with
abstract database specific methods like:

abstract boolean isAutoIncrement(...) etc.

[since we need to do this and other things in
a database specific manner anyway ]. I can add
database specific methods that return validators
based on column lengths and restrictions (null,
not-null) etc.

So in a nutshell I now feel the postgres drivers'
behavior is OK and should not be changed since it
can be worked around quite easily and if it _was_
changed, it would lead to mysterious runtime failures
in validation code.

Best regards,

--j

__________________________________
Do you Yahoo!?
Yahoo! Tax Center - File online by April 15th
http://taxes.yahoo.com/filing.html