
CREATE OR REPLACE FUNCTION __clear_table_tail(arg_relname text, arg_relnamespace text, arg_field text, arg_start_page integer, arg_pages_to_clean integer) RETURNS integer
    LANGUAGE plpgsql
    AS $_$

DECLARE

    _tuples_per_page    integer;

    _current_tid_list tid[];
    _next_tid_list    tid[];
    _new_tid          tid;

    _last_page        integer;
    _min_tid          tid;
    _max_tid          tid;

    _timestamp        timestamp;

    _update_loops         integer := 0;

BEGIN
    --created by Maxim Boguk (maxim.boguk@gmail.com) November 2010

    --clever workaround against firing triggers during updates
    --probably would be good idea check existance of tgenabled IN ('A', 'R') before
    SET LOCAL session_replication_role TO replica;

    SELECT clock_timestamp() INTO STRICT _timestamp;

    --calculating maximum tuples per page for table
    SELECT current_setting('block_size')::int4 / sum(attlen) INTO STRICT _tuples_per_page FROM pg_attribute WHERE attrelid=quote_ident(arg_relname)::regclass AND attnum < 0;

    --some bulletproofing and validation
    IF arg_start_page<arg_pages_to_clean OR arg_pages_to_clean<1 OR arg_pages_to_clean IS NULL OR arg_start_page IS NULL OR arg_start_page<1 THEN
        RAISE WARNING 'cannot empty last % pages starting from % page, wrong call', arg_pages_to_clean, arg_start_page;
        RETURN NULL;
    END IF;

    --final page in range
    _last_page := arg_start_page-arg_pages_to_clean+1;

    --getting mininal and maximal tid in range
    SELECT (_last_page,1)::text::tid INTO STRICT _min_tid;
    SELECT (arg_start_page,_tuples_per_page)::text::tid INTO STRICT _max_tid;

    --generating list of all possible tuples(tids) in requested range of the table
    SELECT array_agg((pi,ti)::text::tid) INTO STRICT _current_tid_list
    FROM generate_series(1, _tuples_per_page) AS tuple(ti)
    JOIN generate_series(_last_page, arg_start_page) AS page(pi) ON TRUE;

    WHILE _update_loops<_tuples_per_page LOOP
        _update_loops := _update_loops+1;
        --trying update all tuples in current page range
        FOR _new_tid IN EXECUTE 'UPDATE ONLY ' || quote_ident(arg_relnamespace) || '.' || quote_ident(arg_relname) || ' SET ' || arg_field || ' = ' || arg_field || ' WHERE ctid=ANY($1) RETURNING ctid' USING _current_tid_list LOOP
            --look like no more free space in the table left...
            IF _new_tid>_max_tid THEN
                RAISE NOTICE 'No more free space in table';
                RETURN 0;
            --tuple still in working range, therefore need more update loops
            ELSIF _new_tid>=_min_tid THEN
                _next_tid_list := _next_tid_list||_new_tid;
            END IF;
        END LOOP;

        --no more tuples to work with in requested range left... finishing
        IF coalesce(array_length(_next_tid_list, 1),0) = 0 THEN
            RAISE NOTICE 'finished clearing % pages after % ms, loops: %', arg_pages_to_clean, 1000*EXTRACT(EPOCH FROM (clock_timestamp()-_timestamp)), _update_loops;
            RETURN _last_page-1;
        END IF;

        _current_tid_list := _next_tid_list;
        _next_tid_list := '{}';
    END LOOP;

    RAISE WARNING 'Max loops (%) reached but not finished... something wrong (total updated % tuples)', _update_loops, _total_updated_tuples;
    RETURN NULL;
END;
$_$;
