Bug #1056 (closed)
Opened 16 years ago
Closed 16 years ago
Database upgrade OMERO3A__9 breaks IAdmin.setDefaultGroup, et al.
| Reported by: | jamoore | Owned by: | jamoore |
|---|---|---|---|
| Priority: | blocker | Cc: | atarkowska, cxallan |
| Sprint: | n.a. | ||
| Total Remaining Time: | n.a. |
Description (last modified by jmoore)
Hibernate does not properly handle re-ordering of lists like Pixels->Channels or Experimenter->GroupExperimenterMap when there is a unique constraint on (parent, parent_index) as OMERO3A__9 added for performance reasons (#1047).
See HHH-3160 for more information. Currently looking for a workaround.
Change History (7)
comment:2 Changed 16 years ago by jmoore
- Status changed from new to assigned
I've found a workaround via Postgres triggers (necessary since Postgres doesn't yet support deferred unique constraints, the suggested workaround from HHH-3160):
CREATE OR REPLACE FUNCTION groupexperimentermap_child_index_move() RETURNS "trigger" AS '
DECLARE
duplicate INT8;
BEGIN
-- Avoids a query if the new and old values of x are the same.
IF new.child = old.child AND new.child_index = old.child_index THEN
RETURN new;
END IF;
-- At most, there should be one duplicate
SELECT id INTO duplicate
FROM groupexperimentermap
WHERE child = new.child AND child_index = new.child_index
OFFSET 0
LIMIT 1;
IF duplicate IS NOT NULL THEN
RAISE NOTICE ''Remapping groupexperimentermap % via (-1 - oldvalue )'', duplicate;
UPDATE groupexperimentermap SET child_index = -1 - child_index WHERE id = duplicate;
END IF;
RETURN new;
END;' LANGUAGE plpgsql;
CREATE TRIGGER groupexperimentermap_child_index_trigger
BEFORE UPDATE ON groupexperimentermap
FOR EACH ROW EXECUTE PROCEDURE groupexperimentermap_child_index_move ();
This can become OMERO3A__11 but JDBC can't handle this syntax, and so our current "ant setup-db" routine is no longer viable.
Currently looking into replacing with python.
comment:3 Changed 16 years ago by jmoore
r2783 fixes this. Apparently, the Postgres JDBC driver separates all statements on semicolons and passes them individually to database. The solution for #1047 includes plpgsql stored procedures which themselves have ";"'s leading to syntax exceptions.
Though this is something of a hack, I've changed ant's setup-db target to use ";;" as a delimiter which allows views.sql to be loaded. This means that each file is being loaded as one statement: schema.sql, data.sql, and views.sql. This may cause issues on some Postgres versions, but on 8.3 it is now working.
Testing needed on other versions.
comment:4 Changed 16 years ago by atarkowska
sql_run] Executing resource: /Users/Ola/J2EP/OMERO/dist/target/views.sql [sql_run] Failed to execute: BEGIN; DROP TABLE (...)
BUILD FAILED
org.postgresql.util.PSQLException: ERROR: language "plpgsql" does not exist
comment:5 Changed 16 years ago by jmoore
If this is related to the problem in your email, Ola:
Ola:~/J2EP/OMERO/dist Ola$ dropdb omero3 && createdb omero3 DROP DATABASE CREATE DATABASE Ola:~/J2EP/OMERO/dist Ola$ createlang plpgsql omero3 Ola:~/J2EP/OMERO/dist Ola$ psql omero3 < sql/psql/OMERO3A__11/views.sql BEGIN ERROR: table "count_plate_screenlinks_by_owner" does not exist ERROR: current transaction is aborted, commands ignored until end of transaction block (...) ROLLBACK
then the issue is that you didn't run schema.sql and data.sql, when doing this manually.
But with r2783, you can return to using ant setup-db just that createlang is now necessary:
dropdb omero3 createdb omero3 createlang plpgsql omero3 ant setup-db
comment:6 Changed 16 years ago by atarkowska
ok, it works, my mistake
comment:7 Changed 16 years ago by jmoore
- Resolution set to fixed
- Status changed from assigned to closed
Now with several of us using this, I'll consider it fixed. Let's open up new tickets for any particular performance or regression issues.
