Обсуждение: 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
Re: Bug with duplicate column names via JDBC MetaData (ORDINAL_POSITION)
От
"j.random.programmer"
Дата:
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
Re: Bug with duplicate column names via JDBC MetaData (ORDINAL_POSITION)
От
"j.random.programmer"
Дата:
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