pgtt

pig repo add pgdg -u          # 添加 PGDG 仓库并更新缓存

shared_preload_libraries = 'pgtt';

CREATE EXTENSION pgtt;

CREATE EXTENSION pgtt;

-- ON COMMIT PRESERVE ROWS：数据在会话内跨事务保持
CREATE GLOBAL TEMPORARY TABLE test_gtt (
    id integer,
    lbl text
) ON COMMIT PRESERVE ROWS;

-- ON COMMIT DELETE ROWS：数据在事务提交时删除
CREATE GLOBAL TEMPORARY TABLE session_data (
    id integer,
    value text
) ON COMMIT DELETE ROWS;

CREATE /*GLOBAL*/ TEMPORARY TABLE test_gtt (
    LIKE other_table INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES
) ON COMMIT PRESERVE ROWS;

CREATE /*GLOBAL*/ TEMPORARY TABLE gtt_copy
AS SELECT * FROM source_table WITH DATA;

INSERT INTO test_gtt VALUES (1, 'one'), (2, 'two');
SELECT * FROM test_gtt;  -- 仅在当前会话中可见

CREATE INDEX ON test_gtt (id);

CREATE GLOBAL TEMPORARY TABLE t2 (
    c1 serial PRIMARY KEY,
    c2 VARCHAR(50) UNIQUE NOT NULL,
    c3 boolean DEFAULT false
);

DROP TABLE test_gtt;  -- 即使其他会话正在使用也可以删除

SET pgtt.enabled TO off;   -- 禁用 GTT 重路由
SET pgtt.enabled TO on;    -- 重新启用 GTT 重路由