Hi Elliot, I just upgraded the Balsam Consortium from 2.0.9 up to 2.3.5. I can tell you that there are half a dozen places where that kind of thing happens. I suspect the moral is that we need to stay on top of our upgrades but, that said, my solution was to take snapshots after each major upgrade and hack out the offending duplicates when I encountered them. It took several restores and restarts to get thorugh all of the scripts but frequent snapshots made it much less painful.
I am not enough of a SQL wonk to know how one writes a script that checks update log first and conditionally executes the patch. In later scripts the devs have mercifully added notices that say, "this upgrade may fail since it is intended to provide a patch that may already exist" before they do the patch in a Begin transaction block. That is not consistant as you walk through the upgrade scripts however so don't count on it being "okay" if a block fails. Ultimately I found that, as painful as it was, walking through each upgrade script in sequence, restarts not withstanding, yields the best schema at the end of the day. In sum, if misery loves company, you are well loved. :) Steve Wills -----Original Message----- From: Elliot Voris [mailto:[email protected]] Sent: Wednesday, April 17, 2013 07:50 PM To: [email protected], [email protected] Subject: [Evergreen-admin] Evergreen 2.2.0-2.2.1 Upgrade Script - Actions Already Logged in config.upgrade_log Hey, All I?m working on upgrading my (currently 2.1.1 in production) Evergreen server up to the latest version 2.3.5, as of this morning when I started. I?m having a bit of trouble with one of the version-upgrade scripts (Open-ILS/src/sql/Pg/version-upgrade/2.2.0-2.2.1-upgrade-db.sql). All the previous scripts worked (2.1.1-2.1.2, 2.1.2-2.1.3, 2.1-2.2) without anything of concern. When I try to run this script, I get the following output: postgres@evergreen-test:~/scripts/version-upgrade$ postgres@evergreen-test:~/scripts/version-upgrade$ psql -U evergreen -h localhost -f ./2.2.0-2.2.1-upgrade-db.sql old_evergreen Password for user evergreen: BEGIN INSERT 0 1 psql:./2.2.0-2.2.1-upgrade-db.sql:10: ERROR: duplicate key value violates unique constraint "upgrade_log_pkey" DETAIL: Key (version)=(0722) already exists. CONTEXT: SQL statement "INSERT INTO config.upgrade_log (version, applied_to) VALUES (my_db_patch, my_applied_to)" PL/pgSQL function "upgrade_deps_block_check" line 17 at SQL statement psql:./2.2.0-2.2.1-upgrade-db.sql:13: ERROR: current transaction is aborted, commands ignored until end of transaction block psql:./2.2.0-2.2.1-upgrade-db.sql:20: ERROR: current transaction is aborted, commands ignored until end of transaction block psql:./2.2.0-2.2.1-upgrade-db.sql:58: ERROR: current transaction is aborted, commands ignored until end of transaction block psql:./2.2.0-2.2.1-upgrade-db.sql:61: ERROR: current transaction is aborted, commands ignored until end of transaction block psql:./2.2.0-2.2.1-upgrade-db.sql:161: ERROR: current transaction is aborted, commands ignored until end of transaction block psql:./2.2.0-2.2.1-upgrade-db.sql:164: ERROR: current transaction is aborted, commands ignored until end of transaction block psql:./2.2.0-2.2.1-upgrade-db.sql:264: ERROR: current transaction is aborted, commands ignored until end of transaction block psql:./2.2.0-2.2.1-upgrade-db.sql:267: ERROR: current transaction is aborted, commands ignored until end of transaction block psql:./2.2.0-2.2.1-upgrade-db.sql:365: ERROR: current transaction is aborted, commands ignored until end of transaction block ROLLBACK postgres@evergreen-test:~/scripts/version-upgrade$ I looked into it, and sure enough 0722 already does exist in config.upgrade_log. It is not the most recent entry in that table, and it was ?applied_to? NULL. As I?m looking through the rest of the script, It looks as though upgrade operations 0725 and 0726 would likely throw the same error. What can I do to resolve the trouble with this particular script? My intention is to run through every one of the version-upgrade scripts between 2.1.1 and 2.3.5 (2.3.6 afterwards, as well), is that not the correct way to go about it? Thanks! Elliot J. Voris IT Director St. Louis Christian College [email protected] [email protected] 314.837.6777 x1250
