I resolved this with following process.
1) Wrote a postgres function to grab all the relevant FKs (constraint name, schema, table, col, etc) from the info schema table and dump into a "temp" table.
2) In that same function, looped over the results from 1 to dump the FK values into another "temp" table. I say temp because its not flagged as such, but I'll drop it later.
3) Wrote a second function to remove the FKs from 1.
1,2, and 3 occur in release scripts that our process executes before the LabKey compilation.
4) Increment my module's version to 17.3. Write a 17.2-17.3.sql script that includes a trigger function that fires after insert or update on core.qcstate. This trigger grabs the data from 1 and 2 to reconstruct the FKs.
The trigger was necessary since my module's name is alphabetically before study and the upgrade script would fire before the qcstate migration one. I didn't want to hack around with naming schemes (e.g. Module: zMyUpdates), so a trigger made the most sense.
5) Remove the trigger function.
This was necessary because we register datasets with LabKey and adhere to the FK constraint definitions. The study.qcstate --> core.qcstate migration didn't account for this and exploded on the DROP table study.qcstate; query.
If I missed something, I'd still be interested in hearing about it, but this seems to have done the trick. |