Обсуждение: Column size BUG with text/bytea with 7.4 JDBC Driver (build 213)
Hi: 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. (The mysql jdbc driver correctly returns 65535 for these binary/text column types) postgres _should_ return the max col size which for bytea is about 1 GB (or is it 4 GB theoretically) ? Either way, it certainly isn't -1 Please fix this ASAP, I am writing an advanced db abstraction layer which automatically generated validators based on min/max column sizes. Best regards, --j __________________________________ Do you Yahoo!? Yahoo! Small Business $15K Web Design Giveaway http://promotions.yahoo.com/design_giveaway/
j.random.programmer wrote: > Hi: > > 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. (The mysql jdbc driver correctly returns > 65535 for these binary/text column types) > > postgres _should_ return the max col size which > for bytea is about 1 GB (or is it 4 GB theoretically) > ? > > Either way, it certainly isn't -1 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. There are already special cases in getColumns for bpchar, varchar, numeric, decimal, bit, and varbit. It wouldn't be hard to add a new case for text and bytea, assuming there's a way to find an appropriate maximum size from the backend in these cases. I'd be reluctant to hardcode a maximum size (although I could see an argument for returning Integer.MAX_VALUE instead of -1). > Please fix this ASAP, I am writing an advanced > db abstraction layer which automatically generated > validators based on min/max column sizes. Again, the fastest way to get this fixed is to provide a patch. The code in question is in org/postgresql/jdbc1/AbstractJdbc1DatabaseMetaData.java. -O
On Wed, 7 Apr 2004, j.random.programmer 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) > 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? Kris Jurka
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