Fix table_version revision disorder

gistfile1.txt

--
-- 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;