SEO

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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings