BEGIN;

SET LOCAL lock_timeout = '100ms';
LOCK TABLE px$hoststat IN ACCESS EXCLUSIVE MODE;

ALTER TABLE px$hoststat RENAME TO px$hoststat_tmp;
ALTER TABLE px$hoststat_ RENAME TO px$hoststat;
ALTER TABLE px$hoststat_tmp RENAME TO px$hoststat_;

COMMIT;

BEGIN;

DO $$
DECLARE
    currdt date;
    _target varchar := 'hoststat';
    target varchar := quote_ident(_target);
    section varchar;
BEGIN
    FOR currdt IN
        SELECT DISTINCT dt FROM px$hoststat_
    LOOP
        section := quote_ident(target || to_char(currdt, '_YYYYMMDD'));
        LOOP
        BEGIN
        EXECUTE 'INSERT INTO ' || section || ' as hstat (dt, host, bytes, plans, lock, lock_dl, error_fat, error_err, error_wrn, sys_anl, sys_vac, sys_chp, megaparams, megaquery, megaresult, megadiff, tmp_cnt)
        SELECT
            dt
        ,   host
        ,   coalesce(sum(bytes),0) as bytes
        ,   coalesce(sum(plans),0) as plans
        ,   coalesce(sum(lock),0) as lock
        ,   coalesce(sum(lock_dl),0) as lock_dl
        ,   coalesce(sum(error_fat),0) as error_fat
        ,   coalesce(sum(error_err),0) as error_err
        ,   coalesce(sum(error_wrn),0) as error_wrn
        ,   coalesce(sum(sys_anl),0) as sys_anl
        ,   coalesce(sum(sys_vac),0) as sys_vac
        ,   coalesce(sum(sys_chp),0) as sys_chp
        ,   coalesce(sum(megaparams),0) as megaparams
        ,   coalesce(sum(megaquery),0) as megaquery
        ,   coalesce(sum(megaresult),0) as megaresult
        ,   coalesce(sum(megadiff),0) as megadiff
        ,   coalesce(sum(tmp_cnt),0) as tmp_cnt
        FROM px$hoststat_
        WHERE dt = $1::date
        GROUP BY dt, host
        ORDER BY host
        ON CONFLICT (host)
        DO UPDATE SET
            bytes     = coalesce(hstat.bytes,0) + excluded.bytes
        ,   plans     = coalesce(hstat.plans,0) + excluded.plans
        ,   lock      = coalesce(hstat.lock,0) + excluded.lock
        ,   lock_dl   = coalesce(hstat.lock_dl,0) + excluded.lock_dl
        ,   error_fat = coalesce(hstat.error_fat,0) + excluded.error_fat
        ,   error_err = coalesce(hstat.error_err,0) + excluded.error_err
        ,   error_wrn = coalesce(hstat.error_wrn,0) + excluded.error_wrn
        ,   sys_anl   = coalesce(hstat.sys_anl,0) + excluded.sys_anl
        ,   sys_vac   = coalesce(hstat.sys_vac,0) + excluded.sys_vac
        ,   sys_chp   = coalesce(hstat.sys_chp,0) + excluded.sys_chp
        ,   megaparams = coalesce(hstat.megaparams,0) + excluded.megaparams
        ,   megaquery  = coalesce(hstat.megaquery,0) + excluded.megaquery
        ,   megaresult = coalesce(hstat.megaresult,0) + excluded.megaresult
        ,   megadiff   = coalesce(hstat.megadiff,0) + excluded.megadiff
        ,   tmp_cnt   = coalesce(hstat.tmp_cnt,0) + excluded.tmp_cnt
        ' USING currdt;
        EXIT;
        EXCEPTION
        WHEN undefined_table THEN
            EXECUTE 'CREATE TABLE IF NOT EXISTS ' || section || ' ( LIKE ' || _target || '_orig INCLUDING ALL
            , CHECK (dt = '''||currdt::text||'''::date));
            ALTER TABLE ' || _target || ' ATTACH PARTITION ' || section || ' FOR VALUES IN ('''||currdt::text||''');';
        END;
        END LOOP;
    END LOOP;
END;
$$;

TRUNCATE TABLE px$hoststat_;

COMMIT;
