Digging into this Constraint naming issue a bit more, it looks like in PostgreSQL you can look up constraints (on columns) by name from the "key_column_usage" view:
http://www.postgresql.org/docs/9.4/static/infoschema-key-column-usage.html So, it seems reasonable to change the code to simply query for the constraint name (rather than assuming the name is the default value). Something similar to this *should work* SELECT * FROM information_schema.key_column_usage WHERE table_name='[tablename]' AND column_name='[columname]'; For this specific issue with finding the name of the (old) constraint on table "metadatavalue" and column "item_id" that query would be: SELECT * FROM information_schema.key_column_usage WHERE table_name='metadatavalue' AND column_name='item_id'; Usually, that query will return "metadatavlua_item_id_fkey" (on a pre-5.0 database). But, obviously in some scenarios this constraint seems to be named "$1" (or similar) instead. (I'll add these notes to the DS-2577 ticket) - Tim On 5/14/2015 12:42 PM, Brian Freels-Stendel wrote: > Good morning, > > This is a problem from way back, see: > http://sourceforge.net/p/dspace/mailman/message/28781652/. If I remember > right, it was because In The Beginning, the constraints weren't being given > names, so they were auto-generated by the db engine. For those of us with > old databases, this will be a recurring problem. If it would help, I can > grab a schema dump (I know that's not the right terminology, but I'm hoping > the meaning will be clear) from our db to compare with a current one. We've > been up since 1.3, so we should have all of the offenders. > > B-- > > -----Original Message----- > From: Tim Donohue [mailto:tdono...@duraspace.org] > Sent: Thursday, May 14, 2015 11:17 AM > To: Peter Dietz; Wally Grotophorst > Cc: dspace-tech > Subject: Re: [Dspace-tech] flyway migration error > > Hi all, > > Just a note to say I've now logged this as a bug: > https://jira.duraspace.org/browse/DS-2577 > > In that ticket, I've tracked down the area of the code that makes the > assumption on the name of a PostgreSQL constraint (but it makes that > assumption based on the default PostgreSQL naming scheme for constraints). > So, we may just need to enhance that area of the code to no longer assume any > particular name for a PostgreSQL constraint. > > In the meantime, the "workaround" is also documented in that ticket (thanks > to you all in this thread!). > > - Tim > > On 5/14/2015 12:04 PM, Peter Dietz wrote: >> Virtual Beer for all as well. I've just ran into this, (Tim pointed me >> to it from IRC). >> >> My version of the SQL, wrapping in a transaction, to be safe, is: >> >> BEGIN; >> >> ALTER TABLE metadatavalue DROP CONSTRAINT "$1"; ALTER TABLE >> metadatavalue ADD CONSTRAINT metadatavalue_item_id_fkey FOREIGN >> KEY(item_id) REFERENCES item(item_id); >> >> COMMIT; >> >> >> >> We're not sure of what the cause is. >> 12:47 PM <tdonohue> yea, I'm not sure either. :) It's definitely a >> "glitch" that happens sometimes (since others have seen it too). But, >> according to the PostgreSQL docs, these contraints are SUPPOSED to be >> named [table]_[column]_fkey >> 12:47 PM <tdonohue> So, it seems like something has changed in >> Postgres, or Postgres doesn't always follow it's own "rules" >> >> ________________ >> Peter Dietz >> Longsight >> www.longsight.com <http://www.longsight.com> pe...@longsight.com >> <mailto:pe...@longsight.com> >> p: 740-599-5005 x809 >> >> On Wed, Apr 1, 2015 at 12:26 PM, Wally Grotophorst <wal...@gmu.edu >> <mailto:wal...@gmu.edu>> wrote: >> >> BINGO! A virtual beer for you! >> >> Thanks for the clear statement (no pun intended) of what I needed to >> do. Worked like a charm. >> >> - Wally >> >> >> >> Bill Tantzen wrote: >> > Wally, >> > Here's what I did: >> > >> > dspace=> \d metadatavalue >> > Table >> "public.metadatavalue" >> > ... >> > ... >> > ... >> > >> > Foreign-key constraints: >> > "$1" FOREIGN KEY (item_id) REFERENCES item(item_id) >> > "$2" FOREIGN KEY (metadata_field_id) REFERENCES >> > metadatafieldregistry(metadata_field_id) >> > >> > If your Foreign-key constraints look like the above, do the >> following: >> > >> > dspace=> ALTER TABLE metadatavalue DROP CONSTRAINT "$1"; >> > ALTER TABLE >> > dspace=> ALTER TABLE metadatavalue ADD CONSTRAINT >> > metadatavalue_item_id_fkey FOREIGN KEY(item_id) REFERENCES >> > item(item_id); >> > ALTER TABLE >> > >> > Then, try the conversion! >> > Cheers, >> > Bill >> > >> > On Wed, Apr 1, 2015 at 9:47 AM, Wally Grotophorst<wal...@gmu.edu >> <mailto:wal...@gmu.edu>> wrote: >> >> Trying to install 5.1 on a 4.1 database...on a new server (as a >> test) >> >> but using my 4.1 database that's imported into Postgres before I >> launch >> >> tomcat/dspace. >> >> >> >> Starts to make the schema conversion, then bombs. This part of >> the log >> >> looks like it's trying to tell me the problem: >> >> >> >> at org.flywaydb.core.Flyway.migrate(Flyway.java:811) >> >> at >> >> >> >> org.dspace.storage.rdbms.DatabaseUtils.updateDatabase(DatabaseUtils.java:419) >> >> ... 21 more >> >> Caused by: org.postgresql.util.PSQLException: ERROR: constraint >> >> "metadatavalue_item_id_fkey" of relation "metadatavalue" does >> not exist >> >> 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) >> >> >> >> >> >> I saw some comment about this issue in a few earlier posts but >> couldn't >> >> figure out how to fix this from the info in the message traffic. Is >> >> there a simple SQL fix I can run prior to trying again? Or maybe >> >> something else? >> >> >> >> Platform: OSX 10.10.1 >> >> tomcat: 7.0.59 >> >> postgres: 9.3 >> >> dspace (trying 5.1) >> >> >> >> - Wally >> >> >> >> Wally Grotophorst >> >> Associate University Librarian >> >> George Mason University >> >> Fairfax, Virginia 22030 >> >> (703) 993-9005 >> >> >> >> >> >> >> >> >> >> >> >> ------------------------------------------------------------------------------ >> >> Dive into the World of Parallel Programming The Go Parallel >> Website, sponsored >> >> by Intel and developed in partnership with Slashdot Media, is >> your hub for all >> >> things parallel software development, from weekly thought >> leadership blogs to >> >> news, videos, case studies, tutorials and more. Take a look and >> join the >> >> conversation now. http://goparallel.sourceforge.net/ >> >> _______________________________________________ >> >> DSpace-tech mailing list >> >> DSpace-tech@lists.sourceforge.net >> <mailto:DSpace-tech@lists.sourceforge.net> >> >> https://lists.sourceforge.net/lists/listinfo/dspace-tech >> >> List Etiquette: >> https://wiki.duraspace.org/display/DSPACE/Mailing+List+Etiquette >> >> >> ------------------------------------------------------------------------------ >> Dive into the World of Parallel Programming The Go Parallel Website, >> sponsored >> by Intel and developed in partnership with Slashdot Media, is your >> hub for all >> things parallel software development, from weekly thought leadership >> blogs to >> news, videos, case studies, tutorials and more. Take a look and join the >> conversation now. http://goparallel.sourceforge.net/ >> _______________________________________________ >> DSpace-tech mailing list >> DSpace-tech@lists.sourceforge.net >> <mailto:DSpace-tech@lists.sourceforge.net> >> https://lists.sourceforge.net/lists/listinfo/dspace-tech >> List Etiquette: >> https://wiki.duraspace.org/display/DSPACE/Mailing+List+Etiquette >> >> >> >> >> ---------------------------------------------------------------------- >> -------- One dashboard for servers and applications across >> Physical-Virtual-Cloud Widest out-of-the-box monitoring support with >> 50+ applications Performance metrics, stats and reports that give you >> Actionable Insights Deep dive visibility with transaction tracing >> using APM Insight. >> http://ad.doubleclick.net/ddm/clk/290420510;117567292;y >> >> >> >> _______________________________________________ >> DSpace-tech mailing list >> DSpace-tech@lists.sourceforge.net >> https://lists.sourceforge.net/lists/listinfo/dspace-tech >> List Etiquette: >> https://wiki.duraspace.org/display/DSPACE/Mailing+List+Etiquette >> > > ------------------------------------------------------------------------------ > One dashboard for servers and applications across Physical-Virtual-Cloud > Widest out-of-the-box monitoring support with 50+ applications Performance > metrics, stats and reports that give you Actionable Insights Deep dive > visibility with transaction tracing using APM Insight. > http://ad.doubleclick.net/ddm/clk/290420510;117567292;y > _______________________________________________ > DSpace-tech mailing list > DSpace-tech@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/dspace-tech > List Etiquette: > https://wiki.duraspace.org/display/DSPACE/Mailing+List+Etiquette > > ------------------------------------------------------------------------------ > One dashboard for servers and applications across Physical-Virtual-Cloud > Widest out-of-the-box monitoring support with 50+ applications > Performance metrics, stats and reports that give you Actionable Insights > Deep dive visibility with transaction tracing using APM Insight. > http://ad.doubleclick.net/ddm/clk/290420510;117567292;y > _______________________________________________ > DSpace-tech mailing list > DSpace-tech@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/dspace-tech > List Etiquette: > https://wiki.duraspace.org/display/DSPACE/Mailing+List+Etiquette > ------------------------------------------------------------------------------ One dashboard for servers and applications across Physical-Virtual-Cloud Widest out-of-the-box monitoring support with 50+ applications Performance metrics, stats and reports that give you Actionable Insights Deep dive visibility with transaction tracing using APM Insight. http://ad.doubleclick.net/ddm/clk/290420510;117567292;y _______________________________________________ DSpace-tech mailing list DSpace-tech@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/dspace-tech List Etiquette: https://wiki.duraspace.org/display/DSPACE/Mailing+List+Etiquette
