Duplicating this data on every record of the builds table cost approximately 4G of duplication.
Note that the database migration included took about 4h45m on an untuned server which uses very slow rotational disks in a RAID5 setup, with not a lot of RAM. I imagine in production it might take an hour or two, but not 4. If this should become a chunked migration, I can do that.
Note: Because of the question about chunked migrations, I have NOT YET tested this migration thoroughly enough for merge.
MB5INTIQWM5VXEEWFNX46SWVNTORXWZGKZDOYZEORQ5T5ZM7XI2QC
UROSQNYPEQJWILVG7JFJYT673C5IL4RXCKZKBUKGXXZND7JK6DLAC
BQHDSG6OTHRD3WP25GX2N46ZUFRDH4E6L7URATJWXN5TKIKAPQFAC
SVDYZSZCLZX77OM4W4XK5ODTVZQ6VTRTMHXOFXO65WZNFZPQAG2AC
JM3DPYOMVNMCL5GMEYC3Y4NDRGTNIFBBFTPGPVT66GPENVPU7EVQC
J5UVLXOK6EDIL5I7VKWH4V2QDS4DPD7FHRK6XBWSXFRQS4JKXFZQC
SB2V735VJ2CDHGCXRUA5FOYHDRXQFVOZ3KXC3YKXWRNW6DIX7RXQC
MOX7XJ2E3XISXA7V7T4W6GEAGECGWBZ4PYSLTYBVVR4VAKOI33CQC
QNDXPVCIRJRPFTYNRDCQC5KORSYB5FPX7IAM6UGACWCU6HWYT6CAC
S6OISBQ3HPFHAAQ5ENG7N3MNGOPNEJPIFKSSA5N4G6KJQTQBSSLQC
L2E6EVE2RVFVDCUNRJ4CZYSQNS2DZUA5DTBETHBDUQUV2KQQRAOQC
NZXX6PLDWEDQHXFF5JCZWGB22VA3WSDYLX65K6BQ5GNWPOE43N7QC
FTPCV25MOLQUNR5CAR453W7T7QTUZRLPLEOSDZ5HSDFAXQZVHOYQC
AZCCANUBA5NBQCU5EHKCNDLEUS5VPXSSVZVUKGKGJLUNYIGSJAYQC
5SHCWE7XPQORSOLY7HGAIK2ODKBFPY4KVXRL2W7X6D4WALU544HQC
KPZNJ33UUF6TK5OPB6K5KLM3ZEK7YV3IF32HTLJFURCO6ICDMGYAC
X27GNHDV5KPZ5GSH6DCAJMNCEMZLCP7M43JWF2X3O5QWXMOX273AC
SJPEC375K7K4PJRGHLFKAOXX5FFAJCGNVIARROGOR3RISTZBYBDAC
Y6AHH4THYQA43V77L43YM42DYRPCMDSWLUV4NKWAQYMPL4NTUIPQC
7ECJWNVXNO3BKM7B7FIFIRBE77QET5PK2C3XKVQUXCYKHDP3V4UQC
JIJDYWPYMZZNFBCWYSYR72RNEW5MSI26MJ5UXXIU7DDX25IBR3CQC
3LEGFJFPEUE7RLBS2GYULCKIOPGI3UNGC6FYGAF7F2HZ2TDHKVUAC
K22TMPH534XFKE6MWYRZSOCRWB7MCBKTMUBIBDF36FJR5DF5UE3AC
2GUAKGTBTNFFER343SQWSLFYIXXHJLDSGH5JHF7QMC3AVZB7Q3TQC
7X2VKCAMPOVATS4EJ2N2V6WGFMYPA5ZFWJQBMXQISYPNTBCUN3FAC
HZWUT4YNGQE3RSTS7XAZK4UQCUHZ5KHA7CHL2JNSXQIPPB6UGKTQC
VU2OLHD246DFWL2WPFSKWPLXTN3WRL25TSRU4PJMJBNWDF35OFRAC
N22GPKYTOLZLBGTGDATQDVZ4R5APZEAOIA7L32X4UXBH4XNI7MWAC
SW7STLQ76HI564FLEJTVUGFMZ2DNMHPJFYFRWY5KYP2OQC42VIVAC
RFE6T5LGBFFNEPHZOPF4UNMFC2L4CGD5TPAMOXDLRPH3TZJ43UBAC
VCG6KNUXD2O4AFRCHIHI4HYKKDNMUGUFFXI72HOTTOOLJMOV7BCAC
444WMU3QUJ5C56CZTNOOM7VMVAL4LR5QZT63FDMQCJILWXVXBNJAC
MWXDVTS3V6SLJ27SJUVN3LT34H3ZMLHPYQCDV7C6KPLHV2QTEF7QC
D5QIOJGPKQJIYBUCSC3MFJ3TXLPNZ2XMI37GXMFRVRFWWR2VMTFAC
# Created by DBIx::Class::Schema::Loader v0.07049 @ 2020-05-27 17:40:41
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:RIKKFfcKXFWIUeM8ma++iw
# Created by DBIx::Class::Schema::Loader v0.07049 @ 2021-01-22 07:11:57
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:Df5N0EByYJqoSUqA0dld/A
# Created by DBIx::Class::Schema::Loader v0.07049 @ 2021-01-21 11:13:38
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:zDBtAFc4HiFUcL/TpkuCcg
# Created by DBIx::Class::Schema::Loader v0.07049 @ 2021-01-22 07:11:57
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:hdu+0WWo2363dVvImMKxdA
# Created by DBIx::Class::Schema::Loader v0.07049 @ 2020-05-27 17:40:41
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:aDW78MCelU/ma953aTcHvA
# Created by DBIx::Class::Schema::Loader v0.07049 @ 2021-01-22 07:11:57
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:6P1qlC5oVSPRSgRBp6nmrw
# Created by DBIx::Class::Schema::Loader v0.07049 @ 2020-05-27 17:40:41
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:iBGJjFWiI9Wy9zwT7xGOEA
# Created by DBIx::Class::Schema::Loader v0.07049 @ 2021-01-22 07:11:57
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:Ff5gJejFu+02b0lInobOoQ
-- Copy of the nixExprInput/nixExprPath fields of the jobset that
-- instantiated this build. Needed if we want to reproduce this
-- build. FIXME: this should be stored in JobsetEvals, storing it
-- here is denormal.
nixExprInput text,
nixExprPath text,
ALTER TABLE JobsetEvals
ADD COLUMN nixExprInput text,
ADD COLUMN nixExprPath text;
-- This migration took 4.5 hours on a server
-- with 5400RPM drives, against a copy of hydra's
-- production dataset. It might take a significantly
-- less amount of time there, and not justify a
-- batched migration.
UPDATE jobsetevals
SET (nixexprinput, nixexprpath) = (
SELECT builds.nixexprinput, builds.nixexprpath
FROM builds
LEFT JOIN jobsetevalmembers
ON jobsetevalmembers.build = builds.id
WHERE jobsetevalmembers.eval = jobsetevals.id
LIMIT 1
)
WHERE jobsetevals.id in (
SELECT jobsetevalsprime.id
FROM jobsetevals as jobsetevalsprime
WHERE jobsetevalsprime.nixexprinput IS NULL
-- AND jobsetevalsprime.id > ? --------- These are in case of a batched migration
ORDER BY jobsetevalsprime.id ASC -- /
-- LIMIT ? -- ----------------------
);
ALTER TABLE builds
DROP COLUMN nixexprinput,
DROP COLUMN nixexprpath;