\pset pager off
DO $$
DECLARE
    r RECORD;
    completed_size BIGINT := 0;
    total_size BIGINT;
    progress_text TEXT;
    start_time TIMESTAMP := clock_timestamp()::timestamp(3);
    last_time TIMESTAMP := start_time;
    table_size BIGINT;
    table_seconds FLOAT;
BEGIN
    BEGIN
        -- Get the total size
        SELECT sum(pg_relation_size(c.oid)) INTO total_size
        FROM pg_class c
        JOIN pg_namespace n ON n.oid = c.relnamespace
        ;
        -- Loop and pre_warm, outputting progress
        FOR r IN
            SELECT c.oid, c.relname, n.nspname
            FROM pg_class c
            JOIN pg_namespace n ON n.oid = c.relnamespace
            ORDER BY pg_relation_size(c.oid)
        LOOP
            RAISE NOTICE '% - Prewarming: %.% (%)',
                clock_timestamp()::timestamp(3),
                r.nspname,
                r.relname,
                pg_size_pretty(pg_relation_size(r.oid))
                ;
            BEGIN
                PERFORM pg_prewarm(r.oid);
            EXCEPTION WHEN OTHERS THEN
                RAISE WARNING 'Failed to prewarm %: %', r.relname, SQLERRM;
            END;
            completed_size := completed_size + pg_relation_size(r.oid);
            table_size := pg_relation_size(r.oid);
            table_seconds := EXTRACT(EPOCH FROM (clock_timestamp() - last_time));
            last_time := clock_timestamp();
            progress_text := format('(%s of %s (%s%%) completed)',
                                pg_size_pretty(completed_size),
                                pg_size_pretty(total_size),
                                round((completed_size::float / total_size::float * 100)::numeric, 3))
                                ;
            RAISE NOTICE '% - Done: %.% % (ETA: %)',
                clock_timestamp()::timestamp(3),
                r.nspname,
                r.relname,
                progress_text,
                CASE 
                    WHEN table_seconds > 0 AND table_size > 0 THEN
                        to_char(
                            '1 second'::interval * ((total_size - completed_size)::float / (table_size::float / table_seconds))::int,
                            'HH24:MI:SS'
                        )
                    ELSE
                        'calculating...'
                END
                ;
        END LOOP;
    EXCEPTION 
        WHEN QUERY_CANCELED THEN
            RAISE NOTICE 'Script cancelled by user at %', clock_timestamp()::timestamp(3);
        WHEN OTHERS THEN
            RAISE WARNING '%', SQLERRM;
    END;
    RAISE NOTICE 'Total elapsed time: %', age(clock_timestamp()::timestamp(3), start_time);
END $$;