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
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]; }
*************** *** 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
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
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?
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>
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];> }>
***************> *** 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>
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>
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>
If you would like to report an abuse of our service, such as a spam message, please . Если Вы хотите пожаловаться на содержимое этой страницы, пожалуйста .
