-- View table type
SELECT 
    schemaname,
    tablename,
    CASE 
        WHEN relpersistence = 'u' THEN 'UNLOGGED'
        WHEN relpersistence = 'p' THEN 'PERMANENT' 
        ELSE 'OTHER'
    END as table_type
FROM pg_tables t
JOIN pg_class c ON c.relname = t.tablename
WHERE schemaname = 'public';

-- Convert UNLOGGED tables to regular tables, where unlogged_table_name is the table name
ALTER TABLE unlogged_table_name SET LOGGED;

-- Lock status monitoring: the mode field indicates the lock type, AccessExclusiveLock is the most restrictive lock, and unlogged_table_name is the table name
SELECT 
    l.locktype,
    l.mode,
    l.granted,
    l.pid,
    a.state,
    a.query_start,
    now() - a.query_start as duration,
    CASE 
        WHEN l.granted THEN 'GRANTED'
        ELSE 'WAITING'
    END as lock_status,
    substring(a.query, 1, 80) as query_snippet
FROM pg_locks l
LEFT JOIN pg_stat_activity a ON l.pid = a.pid
WHERE l.relation IS NOT NULL 
    AND EXISTS (
        SELECT 1 FROM pg_class c 
        WHERE c.oid = l.relation 
        AND c.relname = 'unlogged_table_name'
    )
ORDER BY l.granted, a.query_start;

-- For large tables, it is recommended to convert them in batches
-- 1. Create a new LOGGED table
CREATE TABLE user_cache_new (LIKE user_cache);

-- 2. Migrate data in batches
INSERT INTO user_cache_new 
SELECT * FROM user_cache 
WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02';

-- 3. Rename the table
BEGIN;
DROP TABLE user_cache;
ALTER TABLE user_cache_new RENAME TO user_cache;
COMMIT;