Thread: Where does meta-data come from?

We are running into a strange problem -- one of our developers is
extracting meta-data from our data base, and is seeing a difference in
the data that is being returned.  Columns typed as int or int4 are
returning smallint from the meta-data information through jdbc, but if
we go in and look at the tables through \d in the command line client,
it is correctly typed as int.  This started happening after we upgraded
to 8.1.3 and after issuing alter table commands to create primary keys.
(one data base was fine after the upgrade -- yesterday, but today, after
creation of primary keys, is now giving us the wrong int type).

I'd like to know where jdbc is getting this information, so I can try to
track down the cause.

TIA.

On Wed, 29 Mar 2006, Tom Arthurs wrote:

> We are running into a strange problem -- one of our developers is extracting
> meta-data from our data base, and is seeing a difference in the data that is
> being returned.  Columns typed as int or int4 are returning smallint from the
> meta-data information through jdbc, but if we go in and look at the tables
> through \d in the command line client, it is correctly typed as int.  This
> started happening after we upgraded to 8.1.3 and after issuing alter table
> commands to create primary keys. (one data base was fine after the upgrade --
> yesterday, but today, after creation of primary keys, is now giving us the
> wrong int type).
>
> I'd like to know where jdbc is getting this information, so I can try to
> track down the cause.
>

The JDBC driver issues queries against the system catalogs.  The best
thing you can do is send us a reproducible example of returning the wrong
type information.  My attempt (attached) works fine here.

Kris Jurka

Thanks, Kris

I'm trying to get that information from the developer (he's in Toronto, and I'm in San Francisco) but he's using some package to accomplish to extract the data, so does not have the queries to hand.  It may take a while to get that information -- what I was hoping for was some information on which system catalogs jdbc queries -- if you could even point me to the part of the source that emits these queries we could take it from there, I think.  In the meantime I'll go download the source for the driver and see if I can winkle out the queries on my own.

I'm on the systems/dba team so I'm sort of relaying information back and forth. :)

Kris Jurka wrote:

On Wed, 29 Mar 2006, Tom Arthurs wrote:

We are running into a strange problem -- one of our developers is extracting meta-data from our data base, and is seeing a difference in the data that is being returned.  Columns typed as int or int4 are returning smallint from the meta-data information through jdbc, but if we go in and look at the tables through \d in the command line client, it is correctly typed as int.  This started happening after we upgraded to 8.1.3 and after issuing alter table commands to create primary keys. (one data base was fine after the upgrade -- yesterday, but today, after creation of primary keys, is now giving us the wrong int type).

I'd like to know where jdbc is getting this information, so I can try to track down the cause.

The JDBC driver issues queries against the system catalogs.  The best thing you can do is send us a reproducible example of returning the wrong type information.  My attempt (attached) works fine here.

Kris Jurka

import java.sql.*;

public class IntType {
public static void main(String args[]) throws Exception {	Class.forName("org.postgresql.Driver");	Connection conn = DriverManager.getConnection("jdbc:postgresql://localhost:5434/jurka","jurka","");
	Statement stmt = conn.createStatement();	stmt.execute("CREATE TEMP TABLE inttest (a int4)");
	ResultSet rs = conn.getMetaData().getColumns(null, "", "inttest", "");	while (rs.next()) {		System.out.println(rs.getString("COLUMN_NAME") + " - " + rs.getString("TYPE_NAME"));	}
	stmt.execute("ALTER TABLE inttest ADD CONSTRAINT it_pk PRIMARY KEY (a)");	rs = conn.getMetaData().getColumns(null, "", "inttest", "");	while (rs.next()) {		System.out.println(rs.getString("COLUMN_NAME") + " - " + rs.getString("TYPE_NAME"));	}}

} 

On Wed, 29 Mar 2006, Tom Arthurs wrote:

> I'm trying to get that information from the developer (he's in Toronto, and
> I'm in San Francisco) but he's using some package to accomplish to extract
> the data, so does not have the queries to hand.  It may take a while to get
> that information -- what I was hoping for was some information on which
> system catalogs jdbc queries -- if you could even point me to the part of the
> source that emits these queries we could take it from there, I think.  In the
> meantime I'll go download the source for the driver and see if I can winkle
> out the queries on my own.
>

Well, you haven't said what metadata your getting, so it's tough to say
which system catalogs will be involved.  There are two main interfaces for
retrieving metadata.  That from query results, ResultSetMetaData and that
from just inspecting tables and views, DatabaseMetaData.  These are mostly
implemented in org.postgresql.jdbc2.AbstractJdbc2ResultSetMetaData and
org.postgresql.jdbc2.AbstractJdbc2DatabaseMetaData respectively.

Kris Jurka

oops -- resent to reply to all, sorry about that.

ah.

It's from inspecting tables, views, etc.  He is extracting this to build
some objects that he's rolling into classes for persistent objects
that's he's rolling into classes.  He rebuilds this ever time we make db
changes.

Thanks for the information below, we can probably get the queries from
there and see if we can spot exactly what is changing and why.  In late
breaking news, the one table that I know for sure is exhibiting this
behavior has not changed between yesterday and today, so we are all back
to scratching our heads.

Kris Jurka wrote:
>
>
> On Wed, 29 Mar 2006, Tom Arthurs wrote:
>
>> I'm trying to get that information from the developer (he's in
>> Toronto, and I'm in San Francisco) but he's using some package to
>> accomplish to extract the data, so does not have the queries to
>> hand.  It may take a while to get that information -- what I was
>> hoping for was some information on which system catalogs jdbc queries
>> -- if you could even point me to the part of the source that emits
>> these queries we could take it from there, I think.  In the meantime
>> I'll go download the source for the driver and see if I can winkle
>> out the queries on my own.
>>
>
> Well, you haven't said what metadata your getting, so it's tough to
> say which system catalogs will be involved.  There are two main
> interfaces for retrieving metadata.  That from query results,
> ResultSetMetaData and that from just inspecting tables and views,
> DatabaseMetaData.  These are mostly
> implemented in org.postgresql.jdbc2.AbstractJdbc2ResultSetMetaData and
> org.postgresql.jdbc2.AbstractJdbc2DatabaseMetaData respectively.
>
> Kris Jurka
>

Thanks again -- it *was* a problem in our code, (actually one of our
third-party jar files didn't build) but getting the source of the
meta-data help to eliminate that as a source of the problem.

Kris Jurka wrote:
>
>
> On Wed, 29 Mar 2006, Tom Arthurs wrote:
>
>> I'm trying to get that information from the developer (he's in
>> Toronto, and I'm in San Francisco) but he's using some package to
>> accomplish to extract the data, so does not have the queries to
>> hand.  It may take a while to get that information -- what I was
>> hoping for was some information on which system catalogs jdbc queries
>> -- if you could even point me to the part of the source that emits
>> these queries we could take it from there, I think.  In the meantime
>> I'll go download the source for the driver and see if I can winkle
>> out the queries on my own.
>>
>
> Well, you haven't said what metadata your getting, so it's tough to
> say which system catalogs will be involved.  There are two main
> interfaces for retrieving metadata.  That from query results,
> ResultSetMetaData and that from just inspecting tables and views,
> DatabaseMetaData.  These are mostly
> implemented in org.postgresql.jdbc2.AbstractJdbc2ResultSetMetaData and
> org.postgresql.jdbc2.AbstractJdbc2DatabaseMetaData respectively.
>
> Kris Jurka
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>               http://archives.postgresql.org