
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)

   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 ?

   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.

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 ..)

===============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/>


Mon, 2 Dec 2002 20:50:42 +0100 (CET)
Henner Zeller <henner@freiheit.com>
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
>


Mon, 2 Dec 2002 15:29:46 -0500 (EST)
Kris Jurka <books@ejurka.com>
Re: PG 7.3: Query Meta Data with the JDBC-driver
Query in getImportedKey work bad in postgresql.
I ask on PostgreSQLlists for query like :
SELECT DISTINCT n.nspname as pnspname,n2.nspname as fnspname, c.relname as prelname,
	c2.relname as frelname, t.tgconstrname, a.attnum as keyseq, ic.relname as fkeyname,
	t.tgdeferrable, t.tginitdeferred, t.tgnargs,t.tgargs, p1.proname as updaterule,
	p2.proname as deleterule,con.conname as conname 
FROM pg_catalog.pg_namespace n, pg_catalog.pg_namespace n2,
	pg_catalog.pg_trigger t, pg_catalog.pg_trigger t1,
	pg_catalog.pg_class c, pg_catalog.pg_class c2,
	pg_catalog.pg_class ic, pg_catalog.pg_proc p1,
	pg_catalog.pg_proc p2, pg_catalog.pg_index i,
	pg_catalog.pg_attribute a,pg_catalog.pg_constraint con
WHERE (t.tgrelid=c.oid AND t.tgisconstraint
	AND t.tgconstrrelid=c2.oid AND t.tgfoid=p1.oid AND p1.proname like 'RI\\_FKey\\_%\\_upd')
	AND (t1.tgrelid=c.oid AND t1.tgisconstraint AND t1.tgconstrrelid=c2.oid
	AND t1.tgfoid=p2.oid AND p2.proname like 'RI\\_FKey\\_%\\_del') AND i.indrelid=c.oid
	AND i.indexrelid=ic.oid AND ic.oid=a.attrelid AND i.indisprimary  AND c.relnamespace = n.oid
	AND c2.relnamespace=n2.oid AND c2.relname='fin_nk'
	AND (c2.oid =con.conrelid AND n.oid=con.connamespace AND con.contype='f' AND c.oid=con.confrelid)
ORDER BY prelname,keyseq

Query behave ugly - sometime work in 2 sec,  sometime 170 sec or
 return tuplestore error.
Query can be better with contrib/array, but it isn't in base PostgreSQL

We need contrib/array in 7.4 and unique name in pg_constraint

regards
Haris Peco

On Monday 02 December 2002 19:50, 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)
>
>    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 ?
>
>    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.
>
> 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 ..)
>
> ===============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


Mon, 2 Dec 2002 22:08:56 +0000
snpe <snpe@snpe.co.yu>
Re: PG 7.3: Query Meta Data with the JDBC-driver
> We need contrib/array in 7.4 and unique name in pg_constraint

Whelp. I'd like to make the constraint name unique too, but how do you
coax everyone into renaming their existing constraints -- especially
when there isn't an ALTER CONSTRAINT ... RENAME type statement?

-- 
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc
02 Dec 2002 17:32:38 -0500
Rod Taylor <rbt@rbt.ca>
Re: PG 7.3: Query Meta Data with the JDBC-driver