Created
February 17, 2018 21:21
-
-
Save rlburkes/ab7f0f47c14bb0ea630289d1a502c200 to your computer and use it in GitHub Desktop.
Crazy Migration
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE TRIGGER migrate_products AFTER UPDATE OF migrated ON products FOR EACH ROW WHEN (OLD.migrated IS NULL AND NEW.migrated IS true) EXECUTE PROCEDURE populate_referenced_entities(); | |
CREATE OR REPLACE FUNCTION batch_set_migrated() RETURNS INTEGER LANGUAGE plpgsql AS $$ | |
DECLARE batched_count INTEGER = 1; | |
BEGIN | |
WITH unmigrated_products AS ( | |
SELECT id | |
FROM products | |
WHERE migrated IS NULL | |
LIMIT 50 | |
FOR UPDATE NOWAIT | |
), | |
migrated_products AS ( | |
UPDATE products | |
SET migrated = true | |
FROM unmigrated_products | |
WHERE unmigrated_products.id = products.id | |
RETURNING products.id | |
) SELECT COUNT(1) INTO batched_count FROM migrated_products; | |
RETURN batched_count; | |
END$$; | |
DO LANGUAGE plpgsql $$ | |
DECLARE counter INTEGER = 1; | |
BEGIN | |
WHILE counter > 0 LOOP | |
SELECT INTO counter batch_set_migrated(); | |
END LOOP; | |
END$$; | |
DROP TRIGGER migrate_products ON products; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment