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@postgresql.org so that your
> message can get through to the mailing list cleanly
>