Last active
February 15, 2018 17:31
-
-
Save strk/aac9149e43bc4a4ec9667efe23b35679 to your computer and use it in GitHub Desktop.
Fix table_version revision disorder
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
| -- | |
| -- Change ID of any revision having revision_time at a later time than | |
| -- any other revision with higher ID | |
| -- | |
| -- New IDs for revisions to be moved will be assigned in | |
| -- revision_time order and start after the highest existing | |
| -- revision ID. | |
| -- | |
| -- The number of moved revisions is returned. | |
| -- | |
| -- { | |
| CREATE OR REPLACE FUNCTION ver_fix_revision_disorder() | |
| RETURNS bigint AS | |
| $FIX$ | |
| DECLARE | |
| rec RECORD; | |
| newid bigint; | |
| rec2 RECORD; | |
| totaldisordered bigint; | |
| numdisordered bigint; | |
| BEGIN | |
| -- 1. Make sure sequence is set to stop filling gaps | |
| PERFORM setval('table_version.revision_id_seq', | |
| greatest( | |
| (select max(id) FROM table_version.revision), | |
| (select nextval('table_version.revision_id_seq') ) | |
| ), true); | |
| -- 2. For each misplaced revision, move to correct place | |
| totaldisordered :=0; | |
| LOOP | |
| numdisordered := 0; | |
| FOR rec IN | |
| WITH revs AS ( | |
| SELECT | |
| row_number() OVER (ORDER BY id) seq, | |
| id, | |
| revision_time | |
| FROM | |
| table_version.revision | |
| ) | |
| SELECT | |
| a.id, | |
| a.revision_time | |
| FROM revs a | |
| WHERE EXISTS ( | |
| SELECT b.id FROM revs b WHERE b.seq > a.seq | |
| AND a.revision_time > b.revision_time | |
| ) order by a.revision_time | |
| LOOP | |
| -- Revision rec.id has to be moved to nextval('table_version.revision_id_seq'); | |
| numdisordered := numdisordered + 1; | |
| -- Create new revision record | |
| INSERT INTO table_version.revision | |
| (id, revision_time, start_time, user_name, schema_change, comment) | |
| SELECT | |
| nextval('table_version.revision_id_seq'::regclass), | |
| revision_time, start_time, user_name, schema_change, | |
| comment | |
| FROM table_version.revision | |
| WHERE id = rec.id | |
| RETURNING id | |
| INTO newid; | |
| RAISE WARNING 'Revisions higher than % have earlier time, renaming to %', rec.id, newid; | |
| -- Update table_version.tables_changed | |
| UPDATE table_version.tables_changed | |
| SET revision = newid | |
| WHERE revision = rec.id; | |
| -- Update all revisions of all revisioned tables | |
| FOR rec2 IN SELECT schema_name, table_name | |
| FROM table_version.versioned_tables | |
| LOOP | |
| EXECUTE format('UPDATE table_version.%s_%s_revision SET | |
| _revision_created = $1 WHERE _revision_created = $2', | |
| rec2.schema_name, rec2.table_name) | |
| USING newid, rec.id; | |
| EXECUTE format('UPDATE table_version.%s_%s_revision SET | |
| _revision_expired = $1 WHERE _revision_expired = $2', | |
| rec2.schema_name, rec2.table_name) | |
| USING newid, rec.id; | |
| END LOOP; | |
| -- Delete now hopefully unreferenced old revision | |
| DELETE FROM table_version.revision WHERE id = rec.id; | |
| END LOOP; | |
| totaldisordered := totaldisordered + numdisordered; | |
| IF numdisordered = 0 THEN | |
| EXIT; | |
| END IF; | |
| END LOOP; | |
| RETURN totaldisordered; | |
| END; | |
| $FIX$ | |
| LANGUAGE 'plpgsql' VOLATILE; | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment