insane sentry postgres toast corruption destroyer.sql

-- assumptions (many!)
--   you have a table called "nodestore_node"
--   you know how many rows it has and will stop this function manually
--     when its offset grows too large
--   the "id" column is a varchar
--   the "data" column is TOASTed
--   some of the TOAST storage is corrupt(!)
--   length(data) fails, revealing the corruption
--   you don't care about "data" and are happy to TRUNCATE any corrupted values
--   no one else is using the database (there is NO LOCKING)

create or replace function truncate_bad(lim bigint, off bigint) returns void as $$
  declare
    badId varchar;
  begin
    raise notice 'limit %, offset %', truncate_bad.lim, truncate_bad.off;
    begin
      -- length(data) should fail if there is a problem like
      --   missing chunk number 0 for toast value 1869121 in pg_toast_16499
      perform length(data) from nodestore_node where id in (
          select id from nodestore_node order by id limit truncate_bad.lim offset truncate_bad.off
      );
    exception
    when others then
      -- yikes! this is a catch-all exception handler
      raise notice '%: %', SQLSTATE, SQLERRM;
      if truncate_bad.lim = 1 then
        -- we only asked for a single row and it failed, TRUNCATE its "data" column!
        select id into badId from nodestore_node order by id limit 1 offset truncate_bad.off;
        raise notice 'truncating "data" for bad id: %', badId;
        update nodestore_node set data = '' where id = badId;
        return;
      end if;
      -- NB: integer division
      perform truncate_bad((truncate_bad.lim + 1) / 2, truncate_bad.off);
      return;
    end;
    -- no error here; try the next batch
    perform truncate_bad(truncate_bad.lim, truncate_bad.off + truncate_bad.lim);
  end;
$$ language plpgsql;