Обсуждение: Bug with duplicate column names via JDBC MetaData (ORDINAL_POSITION)

While using JDBC MetaData for my O/R tool, I've come across this
bug recently;

---------------- Background -------------------
Postgres version: 8.3.3
JDBC driver version: 8.3-604 JDBC 3

test=# \d pg_ts_dict;
   Table "pg_catalog.pg_ts_dict"
     Column     | Type | Modifiers
----------------+------+-----------
 dictname       | name | not null
 dictnamespace  | oid  | not null
 dictowner      | oid  | not null
 dicttemplate   | oid  | not null
 dictinitoption | text |
Indexes:
    "pg_ts_dict_dictname_index" UNIQUE, btree (dictname, dictnamespace)
    "pg_ts_dict_oid_index" UNIQUE, btree (oid)

---------------------JDBC MetaData bug -------------------
When getting JDBC Meta data, the column numbers for the
following 2 columns, via ('ORDINAL_POSITION') of the 2
columns are IDENTICAL:

[1]
colname=dictname, colnum=1, sqltype=12, typename=name, colsize=2147483647, nullable=false, isAutoIncrement=false,
remarks=null,hasRemarks=false, isPK=false, fkdata=null] 
  --and--
[2]
colname=dict_name, colnum=1, sqltype=12, typename=text, colsize=2147483647, nullable=false, isAutoIncrement=false,
remarks=null,hasRemarks=false, isPK=false, fkdata=null] 
-------------------------------------------------------------

Note, "dict_name" does not show up via the \d pg_ts_dict
output above but this, possibly extraneous column, does
show up in the JDBC MetaData.

Of course, it's possible that JDBC is not supposed to work
with pg_* tables (system space?) but as of now, this is
severely breaking my O/R tool.

--j

On Mon, 20 Apr 2009, j.random.programmer wrote:

> While using JDBC MetaData for my O/R tool, I've come across this
> bug recently;
>
> When getting JDBC Meta data, the column numbers for the
> following 2 columns, via ('ORDINAL_POSITION') of the 2
> columns are IDENTICAL:
>
> [1]
> colname=dictname, colnum=1, sqltype=12, typename=name, colsize=2147483647, nullable=false, isAutoIncrement=false,
remarks=null,hasRemarks=false, isPK=false, fkdata=null] 
>  --and--
> [2]
> colname=dict_name, colnum=1, sqltype=12, typename=text, colsize=2147483647, nullable=false, isAutoIncrement=false,
remarks=null,hasRemarks=false, isPK=false, fkdata=null] 
> -------------------------------------------------------------
>
> Note, "dict_name" does not show up via the \d pg_ts_dict
> output above but this, possibly extraneous column, does
> show up in the JDBC MetaData.

Without showing the calls you're making it's not completely clear what
your ORM tool is doing.  The attached test case of just getColumns has no
problems.

One thing to be careful of when using DatabaseMetaData is that the table
names are really table name patterns, so you may in fact have a pgftsedict
table that is also matching the pg_ts_dict pattern.

Kris Jurka

Kris Jurka wrote:
>
>
> On Mon, 20 Apr 2009, j.random.programmer wrote:
>
>> While using JDBC MetaData for my O/R tool, I've come across this
>> bug recently;
>>
>> When getting JDBC Meta data, the column numbers for the
>> following 2 columns, via ('ORDINAL_POSITION') of the 2
>> columns are IDENTICAL:
>>
>> [1]
>> colname=dictname, colnum=1, sqltype=12, typename=name,
>> colsize=2147483647, nullable=false, isAutoIncrement=false,
>> remarks=null, hasRemarks=false, isPK=false, fkdata=null]
>>  --and--
>> [2]
>> colname=dict_name, colnum=1, sqltype=12, typename=text,
>> colsize=2147483647, nullable=false, isAutoIncrement=false,
>> remarks=null, hasRemarks=false, isPK=false, fkdata=null]
>> -------------------------------------------------------------
>>
>> Note, "dict_name" does not show up via the \d pg_ts_dict
>> output above but this, possibly extraneous column, does
>> show up in the JDBC MetaData.
>
> Without showing the calls you're making it's not completely clear what
> your ORM tool is doing.  The attached test case of just getColumns has
> no problems.

I wonder if this is a problem with dropped columns again.

(mr. random programmer, please tell us your JDBC driver version and
postgresql server version at least!)

-O

On Wed, 1 Apr 2009, Oliver Jowett wrote:

> I wonder if this is a problem with dropped columns again.
>

I doubt it, dropped columns never have a duplicate pg_attribute.attnum
column.  If he somehow managed to get two columns with the same name but
different number that might be a possibility, but he's got two columns
with the same number but different names.

> (mr. random programmer, please tell us your JDBC driver version and
> postgresql server version at least!)
>

He did mention these in his original email, I just trimmed it out.

Kris Jurka

Kris, Oliver:

Thanks for the quick replies.

Just to recap, this is on postgres 8.3.3 and JDBC3 8.3-604.

Using your exact program Kris, I get duplicate entries for ALL columns, when I run it on my machine.

Here is the output
-----------------------------------------------------------
root@turing:/tmp# java MDts

TABLE_CAT, TABLE_SCHEM, TABLE_NAME, COLUMN_NAME, DATA_TYPE, TYPE_NAME, COLUMN_SIZE, BUFFER_LENGTH, DECIMAL_DIGITS,
NUM_PREC_RADIX,NULLABLE, REMARKS, COLUMN_DEF, SQL_DATA_TYPE, SQL_DATETIME_SUB, CHAR_OCTET_LENGTH, ORDINAL_POSITION,
IS_NULLABLE,SCOPE_CATLOG, SCOPE_SCHEMA, SCOPE_TABLE, SOURCE_DATA_TYPE 
null, pg_catalog, pg_ts_dict, dictname, 12, name, 2147483647, null, 0, 10, 0, null, null, null, null, 2147483647, 1,
NO,null, null, null, null 
null, pg_catalog, pg_ts_dict, dictnamespace, -5, oid, 10, null, 0, 10, 0, null, null, null, null, 10, 2, NO, null,
null,null, null 
null, pg_catalog, pg_ts_dict, dictowner, -5, oid, 10, null, 0, 10, 0, null, null, null, null, 10, 3, NO, null, null,
null,null 
null, pg_catalog, pg_ts_dict, dicttemplate, -5, oid, 10, null, 0, 10, 0, null, null, null, null, 10, 4, NO, null, null,
null,null 
null, pg_catalog, pg_ts_dict, dictinitoption, 12, text, 2147483647, null, 0, 10, 1, null, null, null, null, 2147483647,
5,YES, null, null, null, null 
null, public, pg_ts_dict, dict_name, 12, text, 2147483647, null, 0, 10, 0, null, null, null, null, 2147483647, 1, NO,
null,null, null, null 
null, public, pg_ts_dict, dict_init, 1111, regprocedure, 2147483647, null, 0, 10, 1, null, null, null, null,
2147483647,2, YES, null, null, null, null 
null, public, pg_ts_dict, dict_initoption, 12, text, 2147483647, null, 0, 10, 1, null, null, null, null, 2147483647, 3,
YES,null, null, null, null 
null, public, pg_ts_dict, dict_lexize, 1111, regprocedure, 2147483647, null, 0, 10, 0, null, null, null, null,
2147483647,4, NO, null, null, null, null 
null, public, pg_ts_dict, dict_comment, 12, text, 2147483647, null, 0, 10, 1, null, null, null, null, 2147483647, 5,
YES,null, null, null, null 
-------------------------------------------------------

Note, the ORDINAL_POSITION, 6th column from the end, is
repeated for each column. For example, see dictname and
dict_name. This is exactly the problem my O/R tool is
also facing.

Also, on my machine, there only seems to be one pg_ts_dict
table and no other table with that pattern.

test=# \d pg_ts_dict;
   Table "pg_catalog.pg_ts_dict"
     Column     | Type | Modifiers
----------------+------+-----------
 dictname       | name | not null
 dictnamespace  | oid  | not null
 dictowner      | oid  | not null
 dicttemplate   | oid  | not null
 dictinitoption | text |
Indexes:
    "pg_ts_dict_dictname_index" UNIQUE, btree (dictname, dictnamespace)
    "pg_ts_dict_oid_index" UNIQUE, btree (oid)

test=# \d pg*ts*dict;
   Table "pg_catalog.pg_ts_dict"
     Column     | Type | Modifiers
----------------+------+-----------
 dictname       | name | not null
 dictnamespace  | oid  | not null
 dictowner      | oid  | not null
 dicttemplate   | oid  | not null
 dictinitoption | text |
Indexes:
    "pg_ts_dict_dictname_index" UNIQUE, btree (dictname, dictnamespace)
    "pg_ts_dict_oid_index" UNIQUE, btree (oid)

test=# \d pgtsdict;
Did not find any relation named "pgtsdict".
test=# \d pg_tsdict;
Did not find any relation named "pg_tsdict".
test=# \d pgts_dict;
Did not find any relation named "pgts_dict".

Dunno what is causing this, especially since you said
you didn't see these duplicates on your machine. Maybe
you are running something later than postgres 8.3.3 ?

Best regards,
--j

On Tue, 21 Apr 2009, j.random.programmer wrote:

> Just to recap, this is on postgres 8.3.3 and JDBC3 8.3-604.
>
> null, pg_catalog, pg_ts_dict, dictname, 12, name, 2147483647, null, 0, 10, 0, null, null, null, null, 2147483647, 1,
NO,null, null, null, null 
> null, public, pg_ts_dict, dict_name, 12, text, 2147483647, null, 0, 10, 0, null, null, null, null, 2147483647, 1, NO,
null,null, null, null 

What you have is a pre-8.3 install that had the tsearch2 contrib module
installed that was upgraded to 8.3 with the core tsearch functionality.
If you look at the schemas in the above output or do \d *.pg_ts_dict you
will see two tables.

Kris Jurka

Hi:

> > null, pg_catalog, pg_ts_dict, dictname, 12, name,
> 2147483647, null, 0, 10, 0, null, null, null, null,
> 2147483647, 1, NO, null, null, null, null
> > null, public, pg_ts_dict, dict_name, 12, text,
> 2147483647, null, 0, 10, 0, null, null, null, null,
> 2147483647, 1, NO, null, null, null, null
>
> What you have is a pre-8.3 install that had the tsearch2
> contrib module installed that was upgraded to 8.3 with the
> core tsearch functionality. If you look at the schemas in
> the above output or do \d *.pg_ts_dict you will see two
> tables.

Yes, that was the setup of my postgres...you diagnosed it *exactly*. Are you sure you are not a doctor in real life ?
:-)

Best regards,
--j