aonestar.public

Parameters

Name	Type	Mode
p_dry_run	boolean	IN (DEFAULT true)
p_min_age_hours	integer	IN (DEFAULT 24)
p_categories	text[]	IN (DEFAULT ARRAY['guest_document')
p_max_delete	integer	IN (DEFAULT 5000)

Definition

DECLARE
    v_prop      text := lower(fn_prop_code());
    v_prefixes  text[] := '{}';
    v_prefix    text;
    v_category  text;
    v_scanned   int   := 0;
    v_orphans   jsonb := '[]'::jsonb;
    v_count     int;
    v_paths     text[];
    v_deleted   int   := 0;
    v_aborted   boolean := false;
    r           record;
BEGIN
    -- prefix ต่อ category — แถม UPPERCASE twin เมื่อไม่เท่ากัน (legacy: property.code เก็บตัวใหญ่)
    FOREACH v_category IN ARRAY p_categories LOOP
        v_prefixes := v_prefixes || (v_prop || '/' || v_category || '/');
        IF upper(v_prop) <> v_prop THEN
            v_prefixes := v_prefixes || (upper(v_prop) || '/' || v_category || '/');
        END IF;
    END LOOP;

-- materialize referenced paths ครั้งเดียว (exact match — path ใน DB เก็บ case ตามจริง)
    DROP TABLE IF EXISTS _s3_referenced;
    CREATE TEMP TABLE _s3_referenced ON COMMIT DROP AS
        SELECT p AS file_path FROM fn_s3_referenced_paths() p;
    CREATE INDEX ON _s3_referenced (file_path);
    ANALYZE _s3_referenced;

FOREACH v_prefix IN ARRAY v_prefixes LOOP
        FOR r IN
            SELECT v_prefix || d.file_name AS full_path,
                   d.file_size, d.last_modified
            FROM doc.s3_list_file_details(v_prefix) d
        LOOP
            v_scanned := v_scanned + 1;

IF r.last_modified < now() - make_interval(hours => p_min_age_hours)
               AND NOT EXISTS (SELECT 1 FROM _s3_referenced t WHERE t.file_path = r.full_path) THEN
                v_orphans := v_orphans || jsonb_build_object(
                    'file_path',     r.full_path,
                    'file_size',     r.file_size,
                    'last_modified', r.last_modified);
            END IF;
        END LOOP;
    END LOOP;

v_count := jsonb_array_length(v_orphans);

IF v_count > p_max_delete THEN
        v_aborted := true;
    ELSIF NOT p_dry_run AND v_count > 0 THEN
        SELECT array_agg(o->>'file_path') INTO v_paths
        FROM jsonb_array_elements(v_orphans) o;
        v_deleted := doc.s3_delete_files(v_paths);
    END IF;

RETURN jsonb_build_object(
        'dry_run',  p_dry_run,
        'prefixes', to_jsonb(v_prefixes),
        'scanned',  v_scanned,
        'orphans',  v_count,
        'deleted',  v_deleted,
        'aborted',  v_aborted,
        'files',    v_orphans);
END

sp_s3_cleanup_orphans

Parameters

Definition