Query for DatabaseMetaData.getImportedKey

From: snpe <snpe(at)snpe(dot)co(dot)yu>
To: pgsql-hackers(at)postgresql(dot)org
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Query for DatabaseMetaData.getImportedKey
Date: 2002-11-16 19:57:29
Message-ID: 200211161957.30073.snpe@snpe.co.yu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-jdbc

Hello
I want change DatabaseMetaData.getImportedKeys that return name of constraint
in place of pg_trigger.tgargs (for FK_NAME)
Query for getImportedKey is like :

Query 1:

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
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
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'
ORDER BY prelname,keyseq

I set like this :

Query 2:

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 2 is very slow (sometime 10-20 minutes)

I call vacuumdb --all --full --analyze

I have 282 rows in pg_class, 1900 in pg_attribute, 141 in pg_constraint
and pg_trigger.
What is wrong ?

regards
Haris Peco

Browse pgsql-hackers by date

  From Date Subject
Next Message Marc G. Fournier 2002-11-16 20:04:10 RC1 packaged and available ...
Previous Message My Deja 2002-11-16 16:47:58 Where can I find a list of the new features appearing in Postgresql 7.3?

Browse pgsql-jdbc by date

  From Date Subject
Next Message snpe 2002-11-16 20:45:47 Re: streaming result sets: progress
Previous Message Harald Krake 2002-11-16 18:23:01 Re: why not type casting by default in prepared statements?