Day 6: Pillar 2 - Stored Procedures & Transaction Control (กลไกการสร้าง Stored Procedures, การควบคุม Transaction ภายในด้วย COMMIT และ ROLLBACK, และการทำ Chunked Batch Processing)

-- ❌ ฟังก์ชันไม่สามารถใส่ COMMIT ได้ เมื่อรัน 5 ล้านแถว
-- เกิด Memory Leak, WAL Bloat 200GB และ Rollback ทั้งหมดถ้าพัง
CREATE OR REPLACE FUNCTION fn_archive_old_logs_flawed()
RETURNS INT 
LANGUAGE plpgsql 
AS $$
DECLARE
    v_count INT;
BEGIN
    -- 🚨 อัปเดตทีเดียว 5,000,000 แถว ถือ Lock ค้าง 2 ชม. และบวม WAL
    UPDATE logs 
    SET status = 'ARCHIVED' 
    WHERE log_date < '2025-01-01';

    GET DIAGNOSTICS v_count = ROW_COUNT;

    -- 🚨 หากเขียน COMMIT; ตรงนี้ จะเกิด Error:
    -- ERROR: invalid transaction termination!
    RETURN v_count;
END $$;

-- ❌ เรียกใช้ใน Query:
-- SELECT fn_archive_old_logs_flawed();

-- [Statement 1] สร้าง Stored Procedure สำหรับ Batch Processing ปริมาณมหาศาล
CREATE OR REPLACE PROCEDURE pr_archive_old_logs_production(
    p_batch_size INT DEFAULT 10000 -- กำหนดขนาด Chunk ละ 10,000 แถว
)
LANGUAGE plpgsql
AS $$
DECLARE
    -- [บรรทัดที่ 1-3] ประกาศตัวแปรติดตามจำนวนแถวที่ประมวลผล
    v_chunk_updated INT := 0;
    v_total_updated INT := 0;
BEGIN
    LOOP
        -- [บรรทัดที่ 4-13] ประมวลผลเฉพาะกลุ่มย่อย 10,000 แถวโดยใช้ CTE + LIMIT
        WITH target_chunk AS (
            SELECT id FROM logs
            WHERE status = 'PENDING' AND log_date < '2025-01-01'
            ORDER BY id ASC -- 🎯 จัดลำดับ ID แน่นอน ป้องกัน Deadlock
            LIMIT p_batch_size
            FOR UPDATE SKIP LOCKED -- 🎯 ข้ามแถวติดล็อก ไม่ขวางคนอื่น
        ),
        updated_rows AS (
            UPDATE logs
            SET status = 'ARCHIVED',
                archived_at = CURRENT_TIMESTAMP
            FROM target_chunk
            WHERE logs.id = target_chunk.id
            RETURNING logs.id
        )
        SELECT COUNT(*) INTO v_chunk_updated FROM updated_rows;

        -- [บรรทัดที่ 14-16] เช็คถ้าจบงานแล้ว ให้หลุดออกจากลูป
        IF v_chunk_updated = 0 THEN
            EXIT;
        END IF;

        v_total_updated := v_total_updated + v_chunk_updated;

        -- [บรรทัดที่ 17] แจ้ง Log ความคืบหน้า
        RAISE NOTICE 'Batch Success: % rows archived. Total: %', v_chunk_updated, v_total_updated;

        -- 🎯 [บรรทัดที่ 18] สั่ง COMMIT ปิด Transaction ของ Chunk นี้!
        -- ยืนยันข้อมูลลงดิสก์, ปลด Row Locks ทั้งหมด และระบาย WAL ทันที!
        COMMIT;

    END LOOP;

    RAISE NOTICE '== เสร็จสิ้นกระบวนการ ARCHIVE ข้อมูลทั้งหมด % รายการ ==', v_total_updated;
END $$;

-- [Statement 2] เรียกใช้งานผ่านคำสั่ง CALL
CALL pr_archive_old_logs_production(10000);