Обсуждение: Bug in metadata.getColumns()/ORDINAL_POSITION
Hi:
This is using postgres 8.2.x and the latest 8.2-504
JDBC driver.
I have a table, defined like so:
test=# \d queue_stat;
Table "public.queue_stat"
Column | Type |
Modifiers
-------------------------------------------------------
queue_stat_id | bigint | not
null default nextval
('queue_stat_queue_stat_id_seq'::regclass)
start_time | timestamp without time zone |
finish_time | timestamp without time zone |
processing_error | text |
status | character varying(24) |
fullpath_scan_dir | character varying(255) |
========================================
Note, there are 6 columns defined, total in the table.
This snippet of code:
-------------------------------------------------------
String columnpattern = "%"; //all columns
ResultSet rs = md.getColumns(
catalogname, schemaname, tablename, columnpattern);
QueryUtil.ensureScrollable(rs);
rs.beforeFirst();
while (rs.next())
{
String colname = rs.getString ("COLUMN_NAME");
int datatype = rs.getInt ("DATA_TYPE");
String typename = rs.getString ("TYPE_NAME");
int colsize = rs.getInt ("COLUMN_SIZE");
int nullable = rs.getInt ("NULLABLE");
String remarks = rs.getString ("REMARKS");
int colnum = rs.getInt ("ORDINAL_POSITION");
--------------------------------------------------
gives me information about each column. I need the
ordinal
position because I have written a O/R tool that uses
the ordinal
position returned by the database meta data to
generate
methods like:
result_set.getString(1)
etc.
Here's the bug.
For the above table (queue_stat), I get (via my o/r
tool):
INFO 1 >>>> Processing table: queue_stat
[colname=queue_stat_id, colnum=1,
typename=bigserial]
[colname=start_time, colnum=2,
typename=timestamp]
[colname=finish_time, colnum=3,
typename=timestamp]
[colname=processing_error, colnum=5, typename=text]
[colname=status, colnum=6,
typename=varchar]
[colname=fullpath_scan_dir, colnum=7,
typename=varchar]
NOTE, THE COLNUM (via ("ORDINAL_POSITION")) returns:
1, 2, 3, 5, 6, 7
This is WRONG. It should be:
1, 2, 3, 4, 5, 6
Of course, this results in all sorts of problems when
the tool
tries to say:
result_set.getString(7)
So in a nutshell: there seems to be a bug in:
ResultSet rs = md.getColumns(
catalogname, schemaname, tablename, columnpattern);
as far as the:
"ORDINAL_POSITION" column is concerned.
This is a pretty serious bug for any O/R tool. I have
not
encountered this problem before, so something must
have
changed, either in the database or in the driver. Very
weird...
Best regards,
--j
____________________________________________________________________________________
Do you Yahoo!?
Everyone is raving about the all-new Yahoo! Mail beta.
http://new.mail.yahoo.com
On Fri, 16 Feb 2007, j.random.programmer wrote:
> This is using postgres 8.2.x and the latest 8.2-504
> JDBC driver.
>
> [DatabaseMetaData.getColumns returns wrong ordinal_position like so:]
>
> NOTE, THE COLNUM (via ("ORDINAL_POSITION")) returns:
> 1, 2, 3, 5, 6, 7
>
> This is WRONG. It should be:
> 1, 2, 3, 4, 5, 6
>
Turns out this is a problem with dropped columns:
CREATE TABLE test (a int, b int, c int);
ALTER TABLE test DROP b;
SELECT attname, attnum FROM pg_attribute
WHERE attnum > 0 AND attrelid = 'test'::regclass;
attname | attnum
------------------------------+--------
a | 1
........pg.dropped.2........ | 2
c | 3
(3 rows)
So we need to do our own counting in getColumns instead of relying on
attnum. I'll look at a fix, but for the moment a workaround would be to
recreate your table without any dropped columns.
Kris Jurka
This is a quick followup to my earlier post. Upon further testing, this bug reliably and reproducably happens when an "alter table" command is used on the database. For for example: test=# create table foo(x int, y varchar(5)); At this point, the jdbc metadata (md) method: md.getColumns( catalogname, schemaname, tablename, "%"); returns the correct ORDINAL_POSITION for x and y. x -- >1 y ---> 2 Now, let's do this: test=# alter table foo add z text; ALTER TABLE test=# alter table foo drop z; ALTER TABLE test=# alter table foo drop y; ALTER TABLE test=# alter table foo add a varchar(10); md.getColumns( catalogname, schemaname, tablename, "%"); now returns x --> 1 a --> 4 and that's the bug right there. Should be a ---> 2 (NOT 4) since there are only 2 columsn in the database at this point. Maybe the database is not updating the system tables properly when columns are altered/dropped ? Or is this something in the driver ? Either way, this totally breaks any O/R mapping tool. Best regards, -j ____________________________________________________________________________________ TV dinner still cooling? Check out "Tonight's Picks" on Yahoo! TV. http://tv.yahoo.com/
"j.random.programmer" <javadesigner@yahoo.com> writes:
> This is a quick followup to my earlier post. Upon
> further
> testing, this bug reliably and reproducably happens
> when an "alter table" command is used on the database.
So the problem is that it's returning pg_attribute.attnum without any
consideration for earlier dropped columns. Not sure how expensive it'd
be to get the current logical column number, though --- at a minimum one
would have to select all the table's pg_attribute rows :-(
regards, tom lane
On Sat, 17 Feb 2007, Tom Lane wrote: > "j.random.programmer" <javadesigner@yahoo.com> writes: >> This is a quick followup to my earlier post. Upon >> further >> testing, this bug reliably and reproducably happens >> when an "alter table" command is used on the database. > > So the problem is that it's returning pg_attribute.attnum without any > consideration for earlier dropped columns. Not sure how expensive it'd > be to get the current logical column number, though --- at a minimum one > would have to select all the table's pg_attribute rows :-( > I've fixed this in CVS when running against 8.4 and later servers by using the row_number window function as was suggested elsewhere: http://archives.postgresql.org/pgsql-general/2009-11/msg01067.php Kris Jurka