Skip site navigation (1) Skip section navigation (2)

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

From: "j(dot)random(dot)programmer" <javadesigner(at)yahoo(dot)com>
To: Kris Jurka <books(at)ejurka(dot)com>
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: Column size BUG with text/bytea with 7.4 JDBC Driver (build 213)
Date: 2004-04-17 02:23:12
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-jdbc
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

"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

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,


Do you Yahoo!?
Yahoo! Tax Center - File online by April 15th

In response to

pgsql-jdbc by date

Next:From: j.random.programmerDate: 2004-04-17 02:27:17
Subject: Re: Boolean/Bit BUG with 7.4 JDBC Driver (build 213)
Previous:From: Edoardo CeccarelliDate: 2004-04-16 22:58:42
Subject: slow seqscan

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group