Backfill vulnerability findings' `initial_pipeline_id` column

What does this MR do and why?

Context

As part of the epic to drop the vulnerability_occurrence_pipelines table, we need to ensure that all existing vulnerability findings have their initial_finding_pipeline and latest_finding_pipeline columns backfilled.

We will focus on backfilling the initial_pipeline_id column first, as the latest_pipeline_id column will be populated "naturally" over time by the vulnerability ingestion process.

SQL

• explain for the cte: https://console.postgres.ai/gitlab/gitlab-production-main/sessions/28081/commands/87447

raw sql

EXPLAIN
SELECT
    occurrence_id,
    pipeline_id,
    ROW_NUMBER() OVER (PARTITION BY occurrence_id ORDER BY created_at ASC) AS rn
FROM
    vulnerability_occurrence_pipelines
WHERE
    occurrence_id IN (413002, 2912815, 2912816, 383352, 327598, 327599, 327600, 383351, 395639, 395640, 395641, 422470, 422471, 418664, 419299, 2912976, 2912977, 2912978, 418333, 418334, 422493, 422494, 433950, 433951, 436534, 326867, 352809, 892860, 327483, 357557, 357558, 357559, 357560, 357561, 357562, 357564, 357565, 357566, 891672, 891673, 891674, 891675, 891676, 2912730, 2912729, 2912731, 2912732, 2912733, 2912734, 2912735, 2912736, 2912737, 2912738, 2912739, 2912740, 2912741, 2912742, 2912743, 2912744, 2912745, 327301, 357563, 357554, 357555, 357556, 327289, 327290, 327291, 327292, 327293, 327294, 327295, 327296, 327297, 327298, 327299, 327300, 349133, 349134, 371395, 371396, 371397, 371398, 371399, 371400, 371401, 371402, 371403, 371404, 371405, 371406, 371407, 371408, 371409, 371410, 371411, 371412, 371413, 371414, 371415, 395570, 395571, 395572, 395573, 395574, 395575, 395576, 395577, 395578, 395579, 395580, 395581, 395582, 395583, 395584, 395585, 395586, 395587, 395588, 395589, 395590, 396956, 396957, 396958, 396959, 396960, 396961, 396962, 396963, 396964, 396965, 396966, 396967, 396968, 396969, 396970, 396971, 396972, 396973, 396974, 396975, 396976, 430685, 326871, 349128, 327261, 327262, 2912860, 349230, 385876, 406534, 406535, 2913753, 326838, 328855, 383358, 384248, 393597, 398314, 891681, 2912891, 2912890, 327792, 327793, 327794, 418665, 441306, 326768, 418642, 418687, 2912746, 381705, 385716, 395591, 402470, 2913012, 443647, 422487, 407365, 2912834, 327664, 327665, 327666, 327667, 383353, 395646, 349131, 403629, 451832, 445818, 445819, 690463, 327098, 2912712, 690457, 452178, 378826, 326932, 451591, 451592, 451593, 451594, 451595, 451596, 451597, 690473, 327834, 327835, 344558, 344559, 344560, 2912955, 433119, 327141, 384715, 412908, 419885, 326827, 2913030, 327473, 327474, 327475, 327476, 327477, 327478, 342588, 342589, 342590, 342591, 342592, 395619, 395620, 395621, 395622, 395623, 395624, 326852, 327605, 690479, 327503, 327504, 327505, 327506, 327507, 327508, 327513, 335259, 371289, 377904, 403624, 327722, 2913055, 2912858, 2912859, 2913036, 327064, 2912905, 2912903, 2912904, 2912899, 2912900, 2912901, 2912902, 395659, 327804, 327805, 327806, 327807, 327808, 361231, 361232, 361233, 361234, 382185, 382186, 382187, 382994, 382995, 382996, 382997, 382998, 384626, 384627, 384628, 386074, 386075, 386076, 386077, 395660, 395661, 395662, 400744, 400745, 400746, 400747, 400748, 403862, 403863, 403864, 403865, 403866, 409849, 409850, 409851, 409852, 409853, 409854, 411108, 411109, 411110, 413398, 413399, 413400, 413401, 416618, 416619, 416620, 416621, 417963, 417964, 417965, 417966, 417967, 417968, 417969, 417970, 418832, 418833, 418834, 418835, 418836, 418837, 418838, 418839, 418840, 418841, 418854, 418855, 418856, 418857, 418858, 418859, 418860, 418861, 418862, 418863, 419705, 419706, 419707, 419708, 419709, 419710, 419711, 419712, 419713, 419714, 419715, 419716, 421154, 421155, 421156, 421157, 421158, 421159, 421160, 421161, 422476, 422477, 422478, 422479, 422480, 422481, 422482, 422483, 422484, 422485, 430741, 430742, 430743, 430744, 430745, 430746, 430747, 430748, 430749, 430750, 433577, 433578, 433579, 433580, 433581, 433582, 433583, 433584, 433585, 449083, 449084, 449085, 449086, 449087, 449088, 449671, 449672, 449673, 449674, 449675, 449676, 456600, 456601, 456602, 456603, 456604, 456605, 456606, 456607, 456608, 456609, 456610, 418645, 326937, 326824, 421341, 418685, 327615, 327188, 327189, 327190, 395541, 395542, 395543, 2913049, 327007, 404459, 326931, 405246, 418667, 407355, 410368, 690460, 891685, 2913034, 326797, 2912970, 367072, 326807, 327839, 419117, 430794, 413004, 407353, 407354, 326868, 352810, 361235, 373244, 452990, 326873, 349130, 690484, 690474, 2912965, 413041, 392578, 379775, 412134, 350414, 392045, 326785, 403617, 2912957, 418689, 413008, 326889, 358937, 326804, 418502, 406191, 415314, 417186, 417959, 433112, 436088, 327640, 327634, 349144, 422472, 2912830, 2912831, 327647, 327648, 327191, 402200, 422501, 435774, 418668, 418675, 892852, 422498, 422500, 327493, 327494, 2912940, 2912941, 327899, 327900, 327901, 327902, 327903, 327904, 327905, 327906, 349145, 349146, 349147, 349148, 349149, 349150, 349151, 375739, 375740, 375741, 375742, 375743, 375744, 375745, 375746, 377009, 377010, 377011, 377012, 377013, 377014, 377015, 384245, 384246, 384247, 385292, 385293, 385294, 430776, 430777, 430778, 327145, 326911, 326912, 2913066, 326961, 384249, 2913042, 2912939, 326811, 327012, 421342, 690468, 418683, 326872, 326904, 430807, 2913035, 327484, 327485, 327891, 327892, 327841, 430763, 690454, 327237, 367230, 430669, 413024, 413001, 326899, 430810, 327810, 327811, 382189, 382190, 383000, 383001, 386079, 386080, 430751, 430752, 422502, 2912991, 406391, 407920, 430734, 440646, 442278, 690450, 690451, 2912918, 2912919, 327855, 327856, 327857, 342594, 430766, 430767, 430768, 418318, 418319, 326857, 402370, 418631, 2912974, 327061, 327195, 418309, 418310, 418311, 418312, 413007, 405255, 2912774, 2912775, 2912776, 327495, 327496, 327497, 383315, 413402, 412990, 326774, 326818, 454192, 418676, 2913068, 327158, 418638, 326856, 384242, 392376, 396696, 327227, 327228, 327229, 327230, 327231, 395555, 2912950, 2912999, 413038, 327093, 413045, 413051, 326803, 406970, 406971, 326906, 326907, 327062, 430796, 2912946, 421343, 418649, 413005, 2913062, 327063, 327032, 418643, 690491, 418338, 2912894, 443964, 418623, 326886, 358934, 418621, 326929, 430798, 336424, 433114, 2912773, 326888, 358936, 2912713, 2912714, 2912715, 396159, 327263, 327264, 327265, 327266, 395556, 395557, 395558, 395559, 396160, 396161, 410376, 410377, 410378, 410379, 413071, 435775, 433113, 327033, 327094, 327375, 327376, 327159, 2913067, 327636, 327637, 363561, 363562, 408910, 2912982, 327128, 892865, 380249, 327907, 405245, 413010, 418639, 327625, 327626, 327627, 433120, 326790, 2912914, 2912747, 2912748, 407912, 411560, 415147, 421336, 2912975, 326789, 326813, 385291, 403628, 327187, 2912959, 327574, 412999, 2913056, 437118, 418693, 892849, 326770, 380247, 327690, 327691, 327692, 358719, 358720, 358721, 358722, 358723, 397079, 397080, 2912930, 327882, 364917, 405188, 405851, 326909, 358940, 327083, 329640, 419209, 2912928, 2912929, 410188, 410189, 410190, 327155, 327156, 327157, 329325, 329326, 329327, 365649, 2913024, 326815, 381710, 326933, 407804, 414397, 430790, 418613, 326859, 398315, 402369, 404463, 408035, 430730, 430731, 446080, 418633, 892857, 326831, 892853, 2913004, 442194, 326783, 454361, 413039, 385717, 327255, 329642, 690458, 327540, 377487, 326884, 358933, 377006, 2912754, 327330, 327331, 327714, 327715, 326766, 2912998, 382028, 384329, 441305, 413012, 326997, 327638, 326861, 326862, 352838, 403507, 403508, 404290, 404291, 327428, 349129, 386899, 392044, 690445, 2912817, 327606, 327607, 376692, 376771, 405042, 405043, 413407, 414235, 414236, 440043, 377655, 413075, 690476, 327480, 327342, 2912710, 2912711, 352184, 383925, 383926, 327214, 327215, 327216, 327217, 327218, 327219, 327220, 327221, 327222, 327223, 329703, 329704, 329705, 329706, 329707, 329708, 352180, 352181, 352182, 352183, 380090, 383924, 383927, 395552, 395553, 395554, 412251, 412252, 412253, 412254, 412255, 412256, 415402, 415403, 415404, 415405, 415406, 415407, 415967, 415968, 415969, 415970, 415971, 415972, 415973, 418829, 418830, 418831, 449670, 456593, 456594, 456595, 456596, 456597, 456598, 456599, 2912820, 430708, 442331, 430827, 334968, 327040, 326946, 892864, 326828, 361628, 386491, 413073, 327586, 327587, 327588, 422499, 376297, 690475, 327112, 418658, 418706, 413026, 326914, 327895, 413000, 2913005, 440592, 326786, 433116, 410192, 410193, 413031, 2913029, 418652, 327616, 327617, 327341, 326826, 418679, 327820, 430757, 375747, 375748, 375749, 377488, 377489, 377490, 443965, 327704, 383356, 395655, 327160, 326772, 382478, 452989, 327619, 408777, 408778, 2913053, 430819, 388086, 690471, 327853, 327854, 327051, 2912846, 2912847, 2912848, 2912849, 2912850, 2912851, 2912852, 2912853, 2912854, 2912855, 418641, 2913064, 2912839, 2912840, 2912841, 2912842, 2912843, 2912844, 2912845, 327693, 327694, 327695, 327696)

MR acceptance checklist

Please evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

---

resolves: #451180 (closed)
Related to #422383 (closed)