Task #9013 (closed)
Opened 12 years ago
Closed 12 years ago
Detector update query exception
Reported by: | cxallan | Owned by: | jamoore |
---|---|---|---|
Priority: | blocker | Milestone: | OMERO-4.4 |
Component: | Model | Version: | n.a. |
Keywords: | n.a. | Cc: | cblackburn, jburel, sylittlewood |
Resources: | n.a. | Referenced By: | n.a. |
References: | n.a. | Remaining Time: | 0.0d |
Sprint: | 2012-06-05 (16) |
Description
When updating a Detector or related objects linking to a Detector we get the following exception in the Blitz-0.log:
Caused by: org.postgresql.util.PSQLException: ERROR: column detectors0_.group_id does not exist Position: 1008 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2103) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1836) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:512) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:388) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:273) at sun.reflect.GeneratedMethodAccessor38.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:597) at bitronix.tm.resource.jdbc.BaseProxyHandlerClass.invoke(BaseProxyHandlerClass.java:63) at $Proxy60.executeQuery(Unknown Source) at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208) at org.hibernate.loader.Loader.getResultSet(Loader.java:1869) at org.hibernate.loader.Loader.doQuery(Loader.java:718) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:270) at org.hibernate.loader.Loader.loadCollection(Loader.java:2082) ... 112 more
This appears to be due to database schema issues:
omero44=# \d detector Table "public.detector" Column | Type | Modifiers ---------------------+------------------+----------- amplificationgain | double precision | gain | double precision | offset | double precision | voltage | double precision | zoom | double precision | manufacturerspec_id | bigint | not null instrument | bigint | not null type | bigint | Indexes: "detector_pkey" PRIMARY KEY, btree (manufacturerspec_id) "i_detector_instrument" btree (instrument) "i_detector_type" btree (type) Foreign-key constraints: "fkdetector_instrument_instrument" FOREIGN KEY (instrument) REFERENCES instrument(id) "fkdetector_manufacturerspec_id_manufacturerspec" FOREIGN KEY (manufacturerspec_id) REFERENCES manufacturerspec(id) "fkdetector_type_detectortype" FOREIGN KEY (type) REFERENCES detectortype(id) Referenced by: TABLE "detectorsettings" CONSTRAINT "fkdetectorsettings_detector_detector" FOREIGN KEY (detector) REFERENCES detector(manufacturerspec_id)
Change History (6)
comment:1 Changed 12 years ago by cxallan
- Cc cblackburn jburel sylittlewood added
- Component changed from General to Model
- Priority changed from minor to blocker
comment:2 Changed 12 years ago by jmoore
- Remaining Time set to 1.0
- Status changed from new to accepted
comment:3 Changed 12 years ago by jmoore
comment:4 Changed 12 years ago by jmoore
- Remaining Time changed from 1.0 to 0.25
Doesn't look there's a "correct" way to fix this issue in the Hibernate world, so queries on joined-subclasses (as opposed to on the superclass) will continue to fail in this manner. But, that problem existed before "settings" and "reference" were added, so I'm assuming we can get away with that for 4.4.0.
Instead, I'm going to add a workaround in object.vm so that if the superclass in question is "...Settings" or "...Reference" that a TABLE_PER_CLASS strategy will be used, so that each of the tables will have a group_id, owner_id, etc.
The trade-offs for the TABLE_PER_CLASS strategy are the what columns can be present on the superclass and which identity strategy we can use (see: https://docs.jboss.org/hibernate/annotations/3.5/reference/en/html_single/) which shouldn't be an issue. It also makes sense because "Settings" and "Reference" are essentially dummy classes, and we do not plan on adding columns to them.
comment:5 Changed 12 years ago by jmoore
- Remaining Time changed from 0.25 to 0.5
After fixing the Reference/Settings/DectorSettings... hierarchy, I found issues with all of the ManufacturerSpec/Microscope... hierarchy.
Since ManufacturerSpec has fields itself, this may be a more complicated issue.
In either case, we are likely going to need tests that try to retrieve all settings/manu.specs or specific subclasses to see if any exceptions are thrown.
comment:6 Changed 12 years ago by jmoore
- Remaining Time changed from 0.5 to 0
- Resolution set to fixed
- Status changed from accepted to closed
Fixed and pushed to schema-2012-06. Again, lookout for weird queries involving ManufacturerSpec subclasses (especially LightSource).
See: https://hibernate.onjira.com/browse/HHH-1901 (Opened 2006; unresolved)