Re: JDBC MetaData getImportedKeys query

Patch applied.

I applied the basic one to the 7.3 branch and the one with the new
functionality for FK_NAME to head.

thanks,
--Barry

Kris Jurka wrote:
> For the DatabaseMetaData methods getImportedKeys, getExportedKeys, and
> getCrossReference a large query is used that triggered the genetic query
> optimizer which occasionally produced bad plans which made it look like
> the driver "hung" while it executed. I've add some explicit JOIN
> statements so that it no longer enables the genetic optimizer and
> generates reasonable and consistent plans.
>
> I've attached two versions of the patch. One which has an additional
> change to the value of the FK_NAME column. Currently the returned value
> is the triggers arguments which look like
>
> "<unnamed>\000t2\000t1\000UNSPECIFIED\000a\000a\000"
>
> This was required for server versions < 7.3 when a user did not supply
> constraint names. Every constraint was named "<unnamed>"
> . 7.3 has enforced unique constraint names per table so unnamed foreign
> keys will have different names "$1", "$2" and so on. I've used logic
> along the lines of the following to preserve the unique names in the
> original scheme, but allow people who go to the trouble of naming their
> constraints to see them:
>
> if (triggerargs.startsWith("<unnamed>")) {
> fkname = [the whole ugly trigger args name originally used];
> } else {
> fkname = [the actual fk name];
> }
>
> Kris Jurka
>
>
> ------------------------------------------------------------------------
>
> Index: src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1DatabaseMetaData.java
> ===================================================================
> RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1DatabaseMetaData.java,v
> retrieving revision 1.14
> diff -c -r1.14 AbstractJdbc1DatabaseMetaData.java
> *** src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1DatabaseMetaData.java 2002/12/20 13:15:53 1.14
> --- src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1DatabaseMetaData.java 2003/01/13 17:56:35
> ***************
> *** 2912,2973 ****
> return connection.createStatement().executeQuery(sql);
> }
>
> - /*
> - SELECT
> - c.relname as primary,
> - c2.relname as foreign,
> - t.tgconstrname,
> - ic.relname as fkeyname,
> - af.attnum as fkeyseq,
> - ipc.relname as pkeyname,
> - ap.attnum as pkeyseq,
> - t.tgdeferrable,
> - t.tginitdeferred,
> - t.tgnargs,t.tgargs,
> - p1.proname as updaterule,
> - p2.proname as deleterule
> - FROM
> - pg_trigger t,
> - pg_trigger t1,
> - pg_class c,
> - pg_class c2,
> - pg_class ic,
> - pg_class ipc,
> - pg_proc p1,
> - pg_proc p2,
> - pg_index if,
> - pg_index ip,
> - pg_attribute af,
> - pg_attribute ap
> - WHERE
> - (t.tgrelid=c.oid
> - AND t.tgisconstraint
> - AND t.tgconstrrelid=c2.oid
> - AND t.tgfoid=p1.oid
> - and p1.proname like '%%upd')
> -
> - and
> - (t1.tgrelid=c.oid
> - and t1.tgisconstraint
> - and t1.tgconstrrelid=c2.oid
> - AND t1.tgfoid=p2.oid
> - and p2.proname like '%%del')
> -
> - AND c2.relname='users'
> -
> - AND
> - (if.indrelid=c.oid
> - AND if.indexrelid=ic.oid
> - and ic.oid=af.attrelid
> - AND if.indisprimary)
> -
> - and
> - (ip.indrelid=c2.oid
> - and ip.indexrelid=ipc.oid
> - and ipc.oid=ap.attrelid
> - and ip.indisprimary)
> -
> - */
> /**
> *
> * @param catalog
> --- 2912,2917 ----
> ***************
> *** 3014,3068 ****
> */
>
> if (connection.haveMinimumServerVersion("7.3")) {
> ! select = "SELECT DISTINCT n.nspname as pnspname,n2.nspname as fnspname, ";
> ! from = " 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 = " AND c.relnamespace = n.oid AND c2.relnamespace=n2.oid ";
> if (primarySchema != null && !"".equals(primarySchema)) {
> ! where += " AND n.nspname = '"+escapeQuotes(primarySchema)+"' ";
> }
> if (foreignSchema != null && !"".equals(foreignSchema)) {
> where += " AND n2.nspname = '"+escapeQuotes(foreignSchema)+"' ";
> }
> } else {
> ! select = "SELECT DISTINCT NULL::text as pnspname, NULL::text as fnspname, ";
> ! from = " FROM pg_trigger t, pg_trigger t1, pg_class c, pg_class c2, pg_class ic, pg_proc p1, pg_proc p2, pg_index i, pg_attribute a ";
> }
>
> String sql = select
> ! + "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
> + "WHERE "
> // isolate the update rule
> ! + "(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 "
> // isolate the delete rule
> ! + "(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 "
> + where;
>
> if (primaryTable != null) {
> ! sql += "AND c.relname='" + escapeQuotes(primaryTable) + "' ";
> }
> if (foreignTable != null) {
> sql += "AND c2.relname='" + escapeQuotes(foreignTable) + "' ";
> --- 2958,3025 ----
> */
>
> if (connection.haveMinimumServerVersion("7.3")) {
> ! select = "SELECT n1.nspname as pnspname,n2.nspname as fnspname, ";
> ! from = " FROM pg_catalog.pg_namespace n1 "+
> ! " JOIN pg_catalog.pg_class c1 ON (c1.relnamespace = n1.oid) "+
> ! " JOIN pg_catalog.pg_index i ON (c1.oid=i.indrelid) "+
> ! " JOIN pg_catalog.pg_class ic ON (i.indexrelid=ic.oid) "+
> ! " JOIN pg_catalog.pg_attribute a ON (ic.oid=a.attrelid), "+
> ! " pg_catalog.pg_namespace n2 "+
> ! " JOIN pg_catalog.pg_class c2 ON (c2.relnamespace=n2.oid), "+
> ! " pg_catalog.pg_trigger t1 "+
> ! " JOIN pg_catalog.pg_proc p1 ON (t1.tgfoid=p1.oid), "+
> ! " pg_catalog.pg_trigger t2 "+
> ! " JOIN pg_catalog.pg_proc p2 ON (t2.tgfoid=p2.oid) ";
> if (primarySchema != null && !"".equals(primarySchema)) {
> ! where += " AND n1.nspname = '"+escapeQuotes(primarySchema)+"' ";
> }
> if (foreignSchema != null && !"".equals(foreignSchema)) {
> where += " AND n2.nspname = '"+escapeQuotes(foreignSchema)+"' ";
> }
> } else {
> ! select = "SELECT NULL::text as pnspname, NULL::text as fnspname, ";
> ! from = " FROM pg_class c1 "+
> ! " JOIN pg_index i ON (c1.oid=i.indrelid) "+
> ! " JOIN pg_class ic ON (i.indexrelid=ic.oid) "+
> ! " JOIN pg_attribute a ON (ic.oid=a.attrelid), "+
> ! " pg_class c2, "+
> ! " pg_trigger t1 "+
> ! " JOIN pg_proc p1 ON (t1.tgfoid=p1.oid), "+
> ! " pg_trigger t2 "+
> ! " JOIN pg_proc p2 ON (t2.tgfoid=p2.oid) ";
> }
>
> String sql = select
> ! + "c1.relname as prelname, "
> + "c2.relname as frelname, "
> ! + "t1.tgconstrname, "
> + "a.attnum as keyseq, "
> + "ic.relname as fkeyname, "
> ! + "t1.tgdeferrable, "
> ! + "t1.tginitdeferred, "
> ! + "t1.tgnargs,t1.tgargs, "
> + "p1.proname as updaterule, "
> + "p2.proname as deleterule "
> + from
> + "WHERE "
> // isolate the update rule
> ! + "(t1.tgrelid=c1.oid "
> ! + "AND t1.tgisconstraint "
> ! + "AND t1.tgconstrrelid=c2.oid "
> ! + "AND p1.proname LIKE 'RI\\\\_FKey\\\\_%\\\\_upd') "
>
> ! + "AND "
> // isolate the delete rule
> ! + "(t2.tgrelid=c1.oid "
> ! + "AND t2.tgisconstraint "
> ! + "AND t2.tgconstrrelid=c2.oid "
> ! + "AND p2.proname LIKE 'RI\\\\_FKey\\\\_%\\\\_del') "
> !
> + "AND i.indisprimary "
> + where;
>
> if (primaryTable != null) {
> ! sql += "AND c1.relname='" + escapeQuotes(primaryTable) + "' ";
> }
> if (foreignTable != null) {
> sql += "AND c2.relname='" + escapeQuotes(foreignTable) + "' ";
> ***************
> *** 3076,3083 ****
> --- 3033,3046 ----
> // since when getting crossreference, primaryTable will be defined
>
> if (primaryTable != null) {
> + if (connection.haveMinimumServerVersion("7.3")) {
> + sql += "fnspname,";
> + }
> sql += "frelname";
> } else {
> + if (connection.haveMinimumServerVersion("7.3")) {
> + sql += "pnspname,";
> + }
> sql += "prelname";
> }
>
>
>
> ------------------------------------------------------------------------
>
> Index: src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1DatabaseMetaData.java
> ===================================================================
> RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1DatabaseMetaData.java,v
> retrieving revision 1.14
> diff -c -r1.14 AbstractJdbc1DatabaseMetaData.java
> *** src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1DatabaseMetaData.java 2002/12/20 13:15:53 1.14
> --- src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1DatabaseMetaData.java 2003/01/13 18:55:23
> ***************
> *** 2912,2973 ****
> return connection.createStatement().executeQuery(sql);
> }
>
> - /*
> - SELECT
> - c.relname as primary,
> - c2.relname as foreign,
> - t.tgconstrname,
> - ic.relname as fkeyname,
> - af.attnum as fkeyseq,
> - ipc.relname as pkeyname,
> - ap.attnum as pkeyseq,
> - t.tgdeferrable,
> - t.tginitdeferred,
> - t.tgnargs,t.tgargs,
> - p1.proname as updaterule,
> - p2.proname as deleterule
> - FROM
> - pg_trigger t,
> - pg_trigger t1,
> - pg_class c,
> - pg_class c2,
> - pg_class ic,
> - pg_class ipc,
> - pg_proc p1,
> - pg_proc p2,
> - pg_index if,
> - pg_index ip,
> - pg_attribute af,
> - pg_attribute ap
> - WHERE
> - (t.tgrelid=c.oid
> - AND t.tgisconstraint
> - AND t.tgconstrrelid=c2.oid
> - AND t.tgfoid=p1.oid
> - and p1.proname like '%%upd')
> -
> - and
> - (t1.tgrelid=c.oid
> - and t1.tgisconstraint
> - and t1.tgconstrrelid=c2.oid
> - AND t1.tgfoid=p2.oid
> - and p2.proname like '%%del')
> -
> - AND c2.relname='users'
> -
> - AND
> - (if.indrelid=c.oid
> - AND if.indexrelid=ic.oid
> - and ic.oid=af.attrelid
> - AND if.indisprimary)
> -
> - and
> - (ip.indrelid=c2.oid
> - and ip.indexrelid=ipc.oid
> - and ipc.oid=ap.attrelid
> - and ip.indisprimary)
> -
> - */
> /**
> *
> * @param catalog
> --- 2912,2917 ----
> ***************
> *** 3014,3068 ****
> */
>
> if (connection.haveMinimumServerVersion("7.3")) {
> ! select = "SELECT DISTINCT n.nspname as pnspname,n2.nspname as fnspname, ";
> ! from = " 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 = " AND c.relnamespace = n.oid AND c2.relnamespace=n2.oid ";
> if (primarySchema != null && !"".equals(primarySchema)) {
> ! where += " AND n.nspname = '"+escapeQuotes(primarySchema)+"' ";
> }
> if (foreignSchema != null && !"".equals(foreignSchema)) {
> where += " AND n2.nspname = '"+escapeQuotes(foreignSchema)+"' ";
> }
> } else {
> ! select = "SELECT DISTINCT NULL::text as pnspname, NULL::text as fnspname, ";
> ! from = " FROM pg_trigger t, pg_trigger t1, pg_class c, pg_class c2, pg_class ic, pg_proc p1, pg_proc p2, pg_index i, pg_attribute a ";
> }
>
> String sql = select
> ! + "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
> + "WHERE "
> // isolate the update rule
> ! + "(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 "
> // isolate the delete rule
> ! + "(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 "
> + where;
>
> if (primaryTable != null) {
> ! sql += "AND c.relname='" + escapeQuotes(primaryTable) + "' ";
> }
> if (foreignTable != null) {
> sql += "AND c2.relname='" + escapeQuotes(foreignTable) + "' ";
> --- 2958,3025 ----
> */
>
> if (connection.haveMinimumServerVersion("7.3")) {
> ! select = "SELECT n1.nspname as pnspname,n2.nspname as fnspname, ";
> ! from = " FROM pg_catalog.pg_namespace n1 "+
> ! " JOIN pg_catalog.pg_class c1 ON (c1.relnamespace = n1.oid) "+
> ! " JOIN pg_catalog.pg_index i ON (c1.oid=i.indrelid) "+
> ! " JOIN pg_catalog.pg_class ic ON (i.indexrelid=ic.oid) "+
> ! " JOIN pg_catalog.pg_attribute a ON (ic.oid=a.attrelid), "+
> ! " pg_catalog.pg_namespace n2 "+
> ! " JOIN pg_catalog.pg_class c2 ON (c2.relnamespace=n2.oid), "+
> ! " pg_catalog.pg_trigger t1 "+
> ! " JOIN pg_catalog.pg_proc p1 ON (t1.tgfoid=p1.oid), "+
> ! " pg_catalog.pg_trigger t2 "+
> ! " JOIN pg_catalog.pg_proc p2 ON (t2.tgfoid=p2.oid) ";
> if (primarySchema != null && !"".equals(primarySchema)) {
> ! where += " AND n1.nspname = '"+escapeQuotes(primarySchema)+"' ";
> }
> if (foreignSchema != null && !"".equals(foreignSchema)) {
> where += " AND n2.nspname = '"+escapeQuotes(foreignSchema)+"' ";
> }
> } else {
> ! select = "SELECT NULL::text as pnspname, NULL::text as fnspname, ";
> ! from = " FROM pg_class c1 "+
> ! " JOIN pg_index i ON (c1.oid=i.indrelid) "+
> ! " JOIN pg_class ic ON (i.indexrelid=ic.oid) "+
> ! " JOIN pg_attribute a ON (ic.oid=a.attrelid), "+
> ! " pg_class c2, "+
> ! " pg_trigger t1 "+
> ! " JOIN pg_proc p1 ON (t1.tgfoid=p1.oid), "+
> ! " pg_trigger t2 "+
> ! " JOIN pg_proc p2 ON (t2.tgfoid=p2.oid) ";
> }
>
> String sql = select
> ! + "c1.relname as prelname, "
> + "c2.relname as frelname, "
> ! + "t1.tgconstrname, "
> + "a.attnum as keyseq, "
> + "ic.relname as fkeyname, "
> ! + "t1.tgdeferrable, "
> ! + "t1.tginitdeferred, "
> ! + "t1.tgnargs,t1.tgargs, "
> + "p1.proname as updaterule, "
> + "p2.proname as deleterule "
> + from
> + "WHERE "
> // isolate the update rule
> ! + "(t1.tgrelid=c1.oid "
> ! + "AND t1.tgisconstraint "
> ! + "AND t1.tgconstrrelid=c2.oid "
> ! + "AND p1.proname LIKE 'RI\\\\_FKey\\\\_%\\\\_upd') "
>
> ! + "AND "
> // isolate the delete rule
> ! + "(t2.tgrelid=c1.oid "
> ! + "AND t2.tgisconstraint "
> ! + "AND t2.tgconstrrelid=c2.oid "
> ! + "AND p2.proname LIKE 'RI\\\\_FKey\\\\_%\\\\_del') "
> !
> + "AND i.indisprimary "
> + where;
>
> if (primaryTable != null) {
> ! sql += "AND c1.relname='" + escapeQuotes(primaryTable) + "' ";
> }
> if (foreignTable != null) {
> sql += "AND c2.relname='" + escapeQuotes(foreignTable) + "' ";
> ***************
> *** 3076,3083 ****
> --- 3033,3046 ----
> // since when getting crossreference, primaryTable will be defined
>
> if (primaryTable != null) {
> + if (connection.haveMinimumServerVersion("7.3")) {
> + sql += "fnspname,";
> + }
> sql += "frelname";
> } else {
> + if (connection.haveMinimumServerVersion("7.3")) {
> + sql += "pnspname,";
> + }
> sql += "prelname";
> }
>
> ***************
> *** 3160,3165 ****
> --- 3123,3129 ----
> // Parse the tgargs data
> String fkeyColumn = "";
> String pkeyColumn = "";
> + String fkName = "";
> // Note, I am guessing at most of this, but it should be close
> // if not, please correct
> // the keys are in pairs and start after the first four arguments
> ***************
> *** 3172,3180 ****
> // we are primarily interested in the column names which are the last items in the string
>
> StringTokenizer st = new StringTokenizer(targs, "\\000");
>
> int advance = 4 + (keySequence - 1) * 2;
> ! for ( int i = 0; st.hasMoreTokens() && i < advance ; i++ )
> st.nextToken(); // advance to the key column of interest
>
> if ( st.hasMoreTokens() )
> --- 3136,3151 ----
> // we are primarily interested in the column names which are the last items in the string
>
> StringTokenizer st = new StringTokenizer(targs, "\\000");
> + if (st.hasMoreTokens()) {
> + fkName = st.nextToken();
> + }
> +
> + if (fkName.startsWith("<unnamed>")) {
> + fkName = targs;
> + }
>
> int advance = 4 + (keySequence - 1) * 2;
> ! for ( int i = 1; st.hasMoreTokens() && i < advance ; i++ )
> st.nextToken(); // advance to the key column of interest
>
> if ( st.hasMoreTokens() )
> ***************
> *** 3190,3196 ****
> tuple[7] = fkeyColumn.getBytes(); //FKCOLUMN_NAME
>
> tuple[8] = rs.getBytes(6); //KEY_SEQ
> ! tuple[11] = targs.getBytes(); //FK_NAME this will give us a unique name for the foreign key
> tuple[12] = rs.getBytes(7); //PK_NAME
>
> // DEFERRABILITY
> --- 3161,3167 ----
> tuple[7] = fkeyColumn.getBytes(); //FKCOLUMN_NAME
>
> tuple[8] = rs.getBytes(6); //KEY_SEQ
> ! tuple[11] = fkName.getBytes(); //FK_NAME this will give us a unique name for the foreign key
> tuple[12] = rs.getBytes(7); //PK_NAME
>
> // DEFERRABILITY
>
>
> ------------------------------------------------------------------------
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html