`execution-20260911/database-rehearsal.sql`

15 lines
BEGIN;
LOCK TABLE carnegie_catalog IN SHARE ROW EXCLUSIVE MODE;
CREATE TEMP TABLE repair_expected(id int,sku text,before text,after text) ON COMMIT DROP;
INSERT INTO repair_expected VALUES (4494,'DWAG-379296','63001','63001-upholstery'),(4495,'DWAG-379297','630010','630010-upholstery'),(4496,'DWAG-379298','630011','630011-upholstery'),(4497,'DWAG-379299','630012','630012-upholstery'),(4498,'DWAG-379300','630013','630013-upholstery'),(4499,'DWAG-379301','630014','630014-upholstery'),(4500,'DWAG-379302','630015','630015-upholstery'),(4501,'DWAG-379303','630016','630016-upholstery'),(4502,'DWAG-379304','630017','630017-upholstery'),(4503,'DWAG-379305','630018','630018-upholstery'),(4504,'DWAG-379306','630019','630019-upholstery'),(4505,'DWAG-379307','63002','63002-upholstery'),(4506,'DWAG-379308','630020','630020-upholstery'),(4507,'DWAG-379309','630021','630021-upholstery'),(4508,'DWAG-379310','630022','630022-upholstery'),(4509,'DWAG-379311','630023','630023-upholstery'),(4510,'DWAG-379312','630024','630024-upholstery'),(4511,'DWAG-379313','630025','630025-upholstery'),(4512,'DWAG-379314','630026','630026-upholstery'),(4513,'DWAG-379315','63003','63003-upholstery'),(4514,'DWAG-379316','63004','63004-upholstery'),(4515,'DWAG-379317','63005','63005-upholstery'),(4516,'DWAG-379318','63006','63006-upholstery'),(4517,'DWAG-379319','63007','63007-upholstery'),(4518,'DWAG-379320','63008','63008-upholstery'),(4519,'DWAG-379321','63009','63009-upholstery');
DO $$ BEGIN
IF (SELECT count(*) FROM carnegie_catalog c JOIN repair_expected e ON c.id=e.id AND c.dw_sku=e.sku AND c.mfr_sku=e.before)<>26 THEN RAISE EXCEPTION 'prestate mismatch'; END IF;
END $$;
UPDATE carnegie_catalog c SET mfr_sku=e.after FROM repair_expected e WHERE c.id=e.id AND c.dw_sku=e.sku AND c.mfr_sku=e.before;
DO $$ BEGIN
IF (SELECT count(*) FROM carnegie_catalog c JOIN repair_expected e ON c.id=e.id AND c.dw_sku=e.sku AND c.mfr_sku=e.after)<>26 THEN RAISE EXCEPTION 'poststate mismatch'; END IF;
IF (SELECT count(*)=count(DISTINCT mfr_sku) FROM carnegie_catalog) IS NOT TRUE THEN RAISE EXCEPTION 'identity collision'; END IF;
END $$;
UPDATE carnegie_catalog c SET mfr_sku=e.before FROM repair_expected e WHERE c.id=e.id AND c.dw_sku=e.sku AND c.mfr_sku=e.after;
SELECT 'verified26-and-reversed';
ROLLBACK;