Re: PG 7.3: Query Meta Data with the JDBC-driver

On Mon, 2 Dec 2002, Henner Zeller wrote:

>
> Hi,
> Just compiled the 7.3 branch from source and made some tests using the
> JDBC driver coming with it. I did some tests with the henplus
> JDBC-shell and noticed some problems quering the database meta data:
>
> o the foreign key name is 'wierd'
> ---
> DatabaseMetaData meta = conn.getMetaData();
> ResultSet rset = meta.getImportedKeys(null, null, 'bar');
> rset.next();
> String foreignKeyName=rset.getString(12);
> ---
> results in names that seemingly contains the internal representation:
> fk_foo_id\000bar\000foo\000UNSPECIFIED\000fooref\000id\000
> (see below for an example)

In September, I proposed a patch to change this to the foreign key name.
This was rejected because <= 7.2 servers don't enforce unique constraint
names per table, so it was decided to keep the above behavior to
guaranteee a unique name. I think this should be changed. See the
original discussion at...

http://archives.postgresql.org/pgsql-patches/2002-09/msg00150.php

>
> o It takes _ages_ to retrieve the meta data. While doing a 'describe',
> the postmaster process runs on 100% CPU. And: it takes extremly
> different amounts of time. Executing the describe-command below, it
> took (56.285 sec, 12.799 sec, 5 min 13.468, 12.203 sec) to execute
> the same command. This look very like a missing or
> random break-condition somewhere in a loop ?

The query to generate the ResultSet is a monster and has enough tables
involved to enable the genetic query optimizer which is neither consistent
nor particularly good. I was able to solve this using an ANALYZE, but the
long term solution is to state the desired join order explicitly in the
query using JOIN statements. I will submit a patch to this effect later
this week.

> o this might be a minor point, but annoying as well: the columns are
> not ordered in the sequence the're created in the table.

I have already submitted a patch to fix this because of a previous
complaint.

http://fts.postgresql.org/db/mw/msg.html?mid=1359758

> If this cannot be reproduced, I'll try to track this down, but probably
> this seems simple to you (BTW: doing this with the current 7.4development
> CVS on my machine, this results in a segmentation fault on the postmaster
> side - this indicates, that there indeed is a problem ..)

Will investigate as well.

> ===============8<==============
> pg> create table foo (id int4 constraint pk_foo primary key);
> pg> create table bar ( id int4 constraint pk_bar primary key,
> fooref int4 constraint fk_foo_id references foo(id)
> );
> pg> describe bar
> catalog: postgres
> '->' : referencing
> --------+---------+------+---------+--------+------------------------------------------------------------+
> column | type | null | default | pk | fk |
> --------+---------+------+---------+--------+------------------------------------------------------------+
> fooref | int4(4) | YES | [NULL] | | fk_foo_id\000bar\000foo\000UNSPECIFIED\000fooref\000id\000 |
> | | | | | -> foo(id) |
> id | int4(4) | NO | [NULL] | pk_bar | |
> --------+---------+------+---------+--------+------------------------------------------------------------+
> 56.285 sec
> ===============================
>
>
> ciao,
> -hen
>
> BTW:
> henplus JDBC-Shell can be found
> <http://henplus.sourceforge.net/>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>