Don't add FK single column index if column is leading key of existing index

[Wes Clark]

My company, Guidewire, is attempting to move from H2 1.2 to 1.4 to take advantage of the MVStore feature that should greatly reduce deadlocks.

Is it possible to change the behavior of H2 1.4 so that when a FK constraint is created it will not also create a single column index on that column if a multicolumn index already exists with that column as a leading key?  in our case, many of our tables have a RETIRED column that is non-zero for rows that have been logically deleted.  For columns being turned into a FK, there is already a two column index on the FK column and RETIRED, so we don't need and or want or expect a separate single column index on that FK.  The behavior seems to have changed since 1.3.

[Thomas Mueller]

Hi,

Yes, I think there was a change in this area because of a bug, but I don't fully remember.

Could you please create a simple test case that shows the problem? I mean something like:

drop all objects;

create table parent(id int primary key);

create table child(id int primary key, parent_id int, x int);

create index y on child(parent_id, x);

alter table child add constraint z foreign key(parent_id) references parent(id);

script nosettings;

Regards,

Thomas

On Friday, April 17, 2015, Wes Clark <weso...@gmail.com> wrote:

My company, Guidewire, is attempting to move from H2 1.2 to 1.4 to take advantage of the MVStore feature that should greatly reduce deadlocks.

Is it possible to change the behavior of H2 1.4 so that when a FK constraint is created it will not also create a single column index on that column if a multicolumn index already exists with that column as a leading key?  in our case, many of our tables have a RETIRED column that is non-zero for rows that have been logically deleted.  For columns being turned into a FK, there is already a two column index on the FK column and RETIRED, so we don't need and or want or expect a separate single column index on that FK.  The behavior seems to have changed since 1.3.

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.
To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

[Wes Clark]

Yep, that worked.  Script attached.  If you run in the browser console, you can browse the schema and see the two indexes.

On Thursday, April 16, 2015 at 10:43:47 PM UTC-7, Thomas Mueller wrote:

Hi,

Yes, I think there was a change in this area because of a bug, but I don't fully remember.

Could you please create a simple test case that shows the problem? I mean something like:

drop all objects;

create table parent(id int primary key);

create table child(id int primary key, parent_id int, x int);

create index y on child(parent_id, x);

alter table child add constraint z foreign key(parent_id) references parent(id);

script nosettings;

Regards,

Thomas

On Friday, April 17, 2015, Wes Clark <weso...@gmail.com> wrote:

My company, Guidewire, is attempting to move from H2 1.2 to 1.4 to take advantage of the MVStore feature that should greatly reduce deadlocks.

Is it possible to change the behavior of H2 1.4 so that when a FK constraint is created it will not also create a single column index on that column if a multicolumn index already exists with that column as a leading key?  in our case, many of our tables have a RETIRED column that is non-zero for rows that have been logically deleted.  For columns being turned into a FK, there is already a two column index on the FK column and RETIRED, so we don't need and or want or expect a separate single column index on that FK.  The behavior seems to have changed since 1.3.

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.

To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscribe@googlegroups.com.

[Wes Clark]

Is this something that would be considered a bug and fixable?

[Thomas Mueller]

Hi,

Thanks a lot! Yes, I think it is a bug. I have a fix for it now. This is related to a bugfix in version 1.4.179, change log "Referential integrity constraints sometimes used the wrong index, such that updating a row in the referenced table incorrectly failed with a constraint violation." - but the bugfix was not completely correct.

Regards,

Thomas

On Tuesday, April 21, 2015, Wes Clark <weso...@gmail.com> wrote:

Is this something that would be considered a bug and fixable?

On Friday, April 17, 2015 at 10:41:55 AM UTC-7, Wes Clark wrote:

Yep, that worked.  Script attached.  If you run in the browser console, you can browse the schema and see the two indexes.

On Thursday, April 16, 2015 at 10:43:47 PM UTC-7, Thomas Mueller wrote:

Hi,

Yes, I think there was a change in this area because of a bug, but I don't fully remember.

Could you please create a simple test case that shows the problem? I mean something like:

drop all objects;

create table parent(id int primary key);

create table child(id int primary key, parent_id int, x int);

create index y on child(parent_id, x);

alter table child add constraint z foreign key(parent_id) references parent(id);

script nosettings;

Regards,

Thomas

On Friday, April 17, 2015, Wes Clark <weso...@gmail.com> wrote:

My company, Guidewire, is attempting to move from H2 1.2 to 1.4 to take advantage of the MVStore feature that should greatly reduce deadlocks.

Is it possible to change the behavior of H2 1.4 so that when a FK constraint is created it will not also create a single column index on that column if a multicolumn index already exists with that column as a leading key?  in our case, many of our tables have a RETIRED column that is non-zero for rows that have been logically deleted.  For columns being turned into a FK, there is already a two column index on the FK column and RETIRED, so we don't need and or want or expect a separate single column index on that FK.  The behavior seems to have changed since 1.3.

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.

To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.

To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

--

You received this message because you are subscribed to the Google Groups "H2 Database" group.

To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.

[Wes Clark]

Let me know when it is released.

You received this message because you are subscribed to a topic in the Google Groups "H2 Database" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/h2-database/CJQ6GHr24II/unsubscribe.
To unsubscribe from this group and all its topics, send an email to h2-database...@googlegroups.com.

[Thomas Mueller]

Hi,

It should be available in the nightly build, see "build / automated build".

The next release is announced on Twitter, on this mailing list, and on the news mailing list.

Regards,

Thomas

[Wes Clark]

Thank you, sir.

[Wes Clark]

I am using the nightly build (http://www.h2database.com/automated/h2-latest.jar), but when I run I get " java.lang.UnsupportedClassVersionError: org/h2/Driver : Unsupported major.minor version 52.0" trying to load the H2 driver.  Is there a Java compiler mismatch?  We're using Java 1.7 64-bit.

 

To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscribe@googlegroups.com.

To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.

To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscribe@googlegroups.com.

To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to a topic in the Google Groups "H2 Database" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/h2-database/CJQ6GHr24II/unsubscribe.

To unsubscribe from this group and all its topics, send an email to h2-database+unsubscribe@googlegroups.com.

To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.

To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscribe@googlegroups.com.

To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to a topic in the Google Groups "H2 Database" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/h2-database/CJQ6GHr24II/unsubscribe.

To unsubscribe from this group and all its topics, send an email to h2-database+unsubscribe@googlegroups.com.

[Thomas Mueller]

Hi,

The nightly build is currently created with Java 8 (on a Raspberry Pi by the way). I will switch to Java 7.

Regards,

Thomas

To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.

To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.

To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.

To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to a topic in the Google Groups "H2 Database" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/h2-database/CJQ6GHr24II/unsubscribe.

To unsubscribe from this group and all its topics, send an email to h2-database...@googlegroups.com.

To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.

To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.

To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to a topic in the Google Groups "H2 Database" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/h2-database/CJQ6GHr24II/unsubscribe.

To unsubscribe from this group and all its topics, send an email to h2-database...@googlegroups.com.

To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.

To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.

[weso...@gmail.com]

Last night or tonight (Tues.)?

Sent from my Cyanogen phone

[Thomas Mueller]

Hi,

Sorry it will take a few days until I can switch. Would it be possible for you to build H2 yourself, using the build script (build.sh / build.bat)?

Regards,

Thomas

[Wes Clark]

I found the source, but it appears to be of h2-1.4.187, which is last published build.  Where is the nightly/current source?

To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscribe@googlegroups.com.

To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.

To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscribe@googlegroups.com.

To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to a topic in the Google Groups "H2 Database" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/h2-database/CJQ6GHr24II/unsubscribe.

To unsubscribe from this group and all its topics, send an email to h2-database+unsubscribe@googlegroups.com.

To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.

To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscribe@googlegroups.com.

To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to a topic in the Google Groups "H2 Database" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/h2-database/CJQ6GHr24II/unsubscribe.

To unsubscribe from this group and all its topics, send an email to h2-database+unsubscribe@googlegroups.com.

To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.

To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscribe@googlegroups.com.

To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to a topic in the Google Groups "H2 Database" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/h2-database/CJQ6GHr24II/unsubscribe.

To unsubscribe from this group and all its topics, send an email to h2-database+unsubscribe@googlegroups.com.

To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.

To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscribe@googlegroups.com.

[Thomas Mueller]

Hi,

The source code is now here:

https://github.com/h2database/h2database

The nightly build is here:

http://www.h2database.com/automated/h2-latest.jar

as documented in:

http://h2database.com/html/build.html#automated

Regards,

Thomas

To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.

To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.

To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.

To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to a topic in the Google Groups "H2 Database" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/h2-database/CJQ6GHr24II/unsubscribe.

To unsubscribe from this group and all its topics, send an email to h2-database...@googlegroups.com.

To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.

To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.

To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to a topic in the Google Groups "H2 Database" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/h2-database/CJQ6GHr24II/unsubscribe.

To unsubscribe from this group and all its topics, send an email to h2-database...@googlegroups.com.

To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.

To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.

To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to a topic in the Google Groups "H2 Database" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/h2-database/CJQ6GHr24II/unsubscribe.

To unsubscribe from this group and all its topics, send an email to h2-database...@googlegroups.com.

To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.

To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.

To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.

To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.

[Wes Clark]

I was able to "git" your source code and build it, and deploy the jar, verifying that current code no longer creates a single column index to back a foreign key if a multi-column index with the same leading column already exists.  Thanks for this.  I will run with the current nightly build (compiled on Java 7) until you have another release.  I'll let you know if I find anything else.

To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscribe@googlegroups.com.

To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.

To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscribe@googlegroups.com.

To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to a topic in the Google Groups "H2 Database" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/h2-database/CJQ6GHr24II/unsubscribe.

To unsubscribe from this group and all its topics, send an email to h2-database+unsubscribe@googlegroups.com.

To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.

To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscribe@googlegroups.com.

To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to a topic in the Google Groups "H2 Database" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/h2-database/CJQ6GHr24II/unsubscribe.

To unsubscribe from this group and all its topics, send an email to h2-database+unsubscribe@googlegroups.com.

To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.

To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscribe@googlegroups.com.

To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to a topic in the Google Groups "H2 Database" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/h2-database/CJQ6GHr24II/unsubscribe.

To unsubscribe from this group and all its topics, send an email to h2-database+unsubscribe@googlegroups.com.

To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.

To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscribe@googlegroups.com.

To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.

To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscribe@googlegroups.com.