Re: PG 7.3: Query Meta Data with the JDBC-driver - Mailing list pgsql-hackers

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.pronameas 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_classc, pg_catalog.pg_class c2,pg_catalog.pg_class ic, pg_catalog.pg_proc p1,pg_catalog.pg_proc p2,
pg_catalog.pg_indexi,pg_catalog.pg_attribute a,pg_catalog.pg_constraint con
 
WHERE (t.tgrelid=c.oid AND t.tgisconstraintAND 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.oidAND t1.tgfoid=p2.oid AND
p2.pronamelike 'RI\\_FKey\\_%\\_del') AND i.indrelid=c.oidAND i.indexrelid=ic.oid AND ic.oid=a.attrelid AND
i.indisprimary AND c.relnamespace = n.oidAND c2.relnamespace=n2.oid AND c2.relname='fin_nk'AND (c2.oid =con.conrelid
ANDn.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 orreturn 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