To push this to production, a one-time migration script for each database was needed since there were schema changes involved. The alternative was to re-create the databases from scratch. Since we will do such re-creation in the future, it may be helpful for future reference to consult the migration script that was used during implementation of this bugfix:
-- Drop the foreign key constraints referencing the identifier column that needs to be altered
ALTER table feature_specifier
DROP CONSTRAINT feature_specifier_feature_specification_fkey ;
ALTER table quantitative_feature_value
DROP CONSTRAINT quantitative_feature_value_feature_fkey ;
ALTER table two_cohort_feature_association_test
DROP CONSTRAINT two_cohort_feature_association_test_feature_tested_fkey ;
ALTER table quantitative_feature_value_queue
DROP CONSTRAINT quantitative_feature_value_queue_feature_fkey ;
-- Alter the data type for all those formerly foreign keys
ALTER TABLE feature_specifier
ALTER COLUMN feature_specification TYPE INTEGER USING feature_specification::integer;
ALTER TABLE quantitative_feature_value
ALTER COLUMN feature TYPE INTEGER USING feature::integer;
ALTER TABLE two_cohort_feature_association_test
ALTER COLUMN feature_tested TYPE INTEGER USING feature_tested::integer;
ALTER TABLE quantitative_feature_value_queue
ALTER COLUMN feature TYPE INTEGER USING feature::integer;
-- Alter the data type of the actual identifier (as opposed to references to it)
ALTER TABLE feature_specification
ALTER COLUMN identifier TYPE INTEGER USING identifier::integer ;
-- Simulate the SERIAL modifier, since the table isn't being recreated
CREATE SEQUENCE feature_specification_identifier_seq ;
ALTER TABLE feature_specification ALTER COLUMN identifier SET NOT NULL ;
ALTER TABLE feature_specification ALTER COLUMN identifier SET DEFAULT nextval('feature_specification_identifier_seq') ;
ALTER SEQUENCE feature_specification_identifier_seq OWNED BY feature_specification.identifier ;
-- Add the constraints back, the foreign keys into the identifier with newly-numeric-auto-incrementing type
ALTER TABLE feature_specifier
ADD CONSTRAINT fk_identifier_feature_specifier FOREIGN KEY (feature_specification)
REFERENCES feature_specification(identifier) ;
ALTER TABLE quantitative_feature_value
ADD CONSTRAINT fk_identifier_quantitative_feature_value FOREIGN KEY (feature)
REFERENCES feature_specification(identifier) ;
ALTER TABLE two_cohort_feature_association_test
ADD CONSTRAINT fk_identifier_two_cohort_feature_association_test FOREIGN KEY (feature_tested)
REFERENCES feature_specification(identifier) ;
ALTER TABLE quantitative_feature_value_queue
ADD CONSTRAINT fk_identifier_quantitative_feature_value_queue FOREIGN KEY (feature)
REFERENCES feature_specification(identifier) ;
-- Miscellaneous update to additional table, no constraint changes necessary
ALTER TABLE cell_set_cache
ALTER COLUMN feature TYPE INTEGER USING feature::integer ;
All tests pass, and the application seems to be functioning normally with the new build.
Basically, what is done is to convert the column feature_specification.identifier from the default string type used automatically by all the identifiers (VARCHAR(512)) into an auto-incrementing integer.
Addresses #330.
To push this to production, a one-time migration script for each database was needed since there were schema changes involved. The alternative was to re-create the databases from scratch. Since we will do such re-creation in the future, it may be helpful for future reference to consult the migration script that was used during implementation of this bugfix:
All tests pass, and the application seems to be functioning normally with the new build.