Molotilka

gistfile1.txt

CREATE or REPLACE FUNCTION molotilka(tbl regclass, start_page bigint, progress bool) RETURNS text AS $$

    result = plpy.execute("select pg_relation_size('{0}') / 8192 as n_pages".format(tbl))
    n_pages = int(result[0]['n_pages'])

    plpy.notice("N_PAGES: {0}".format(n_pages))

    for page in range(start_page, n_pages):
    
        if progress or page % 10000 == 0:
            plpy.notice("progress: PAGE {0}".format(page))

        try:
            lps_in_page = plpy.execute("select lp from heap_page_items(get_raw_page('{0}', {1}))".format(tbl, page))
        except plpy.SPIError, e:
            if e.sqlstate == '57014':
                return "Canceled"
            plpy.notice("Page corruption: {0}. SPIERROR: {1}".format(page, e.sqlstate))
            continue;

        for lp_in_page in lps_in_page:

            try:
                plpy.execute("select * from {0} where ctid = '({1},{2})'".format(tbl, page, lp_in_page['lp']))
            except plpy.SPIError, e:
                if e.sqlstate == '57014':
                    return "Canceled"
                plpy.notice("TUPLE SPIERROR: {0}".format(e.sqlstate))
                plpy.notice("Tuple corruption, ctid: ({0},{1})".format(page, lp_in_page['lp']))

    return "End"
$$ LANGUAGE plpython2u;