Thread: JDBC MetaData getImportedKeys query
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 JurkaIndex: 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_triggert1, pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_class ic, pg_catalog.pg_proc p1,
pg_catalog.pg_procp2, 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_indexi, 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_triggert1, pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_class ic, pg_catalog.pg_proc p1,
pg_catalog.pg_procp2, 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_indexi, 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
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_triggert1, pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_class ic, pg_catalog.pg_proc p1,
pg_catalog.pg_procp2, 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_triggert1, pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_class ic, pg_catalog.pg_proc p1,
pg_catalog.pg_procp2, 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