印刻数据库

外观

PostgreSQL 临时表管理 ​

临时表创建与基础使用 ​

临时表创建语法 ​

sql
-- 创建会话级临时表（会话结束后自动删除）
CREATE TEMPORARY TABLE temp_sales (
    sale_id SERIAL PRIMARY KEY,
    product_id INT NOT NULL,
    sale_date DATE NOT NULL,
    amount DECIMAL(10,2) NOT NULL
);

-- 或使用简写形式
CREATE TEMP TABLE temp_sales (
    sale_id SERIAL PRIMARY KEY,
    product_id INT NOT NULL,
    sale_date DATE NOT NULL,
    amount DECIMAL(10,2) NOT NULL
);

-- 创建事务级临时表（事务结束后自动删除）
CREATE TEMP TABLE temp_sales_trans (
    sale_id SERIAL PRIMARY KEY,
    product_id INT NOT NULL,
    sale_date DATE NOT NULL,
    amount DECIMAL(10,2) NOT NULL
) ON COMMIT DROP;

-- 事务结束后保留数据但重置表结构
CREATE TEMP TABLE temp_sales_reset (
    sale_id SERIAL PRIMARY KEY,
    product_id INT NOT NULL,
    sale_date DATE NOT NULL,
    amount DECIMAL(10,2) NOT NULL
) ON COMMIT PRESERVE ROWS;

临时表数据操作 ​

sql
-- 向临时表插入数据
INSERT INTO temp_sales (product_id, sale_date, amount)
SELECT product_id, sale_date, amount
FROM sales
WHERE sale_date BETWEEN '2023-01-01' AND '2023-01-31';

-- 从临时表查询数据
SELECT product_id, SUM(amount) as total_amount
FROM temp_sales
GROUP BY product_id
ORDER BY total_amount DESC;

-- 更新临时表数据
UPDATE temp_sales
SET amount = amount * 1.1
WHERE product_id = 123;

-- 删除临时表数据
DELETE FROM temp_sales
WHERE amount < 100;

临时表索引创建 ​

sql
-- 在临时表上创建索引
CREATE INDEX idx_temp_sales_product ON temp_sales(product_id);
CREATE INDEX idx_temp_sales_date ON temp_sales(sale_date);

-- 创建唯一索引
CREATE UNIQUE INDEX idx_temp_sales_unique ON temp_sales(sale_id);

-- 创建组合索引
CREATE INDEX idx_temp_sales_product_date ON temp_sales(product_id, sale_date);

临时表类型与作用域 ​

1. 会话级临时表 ​

  • 作用域：整个数据库会话
  • 生命周期：会话结束后自动删除
  • 使用场景：需要在多个事务中共享临时数据
sql
-- 创建会话级临时表
CREATE TEMP TABLE session_temp (
    id INT,
    data TEXT
);

-- 跨事务使用
BEGIN;
INSERT INTO session_temp VALUES (1, 'data1');
COMMIT;

BEGIN;
SELECT * FROM session_temp; -- 仍能访问到数据
COMMIT;

2. 事务级临时表 ​

  • 作用域：单个事务
  • 生命周期：事务结束后自动删除
  • 使用场景：仅在单个事务中使用的临时数据
sql
-- 创建事务级临时表
CREATE TEMP TABLE transaction_temp (
    id INT,
    data TEXT
) ON COMMIT DROP;

BEGIN;
INSERT INTO transaction_temp VALUES (1, 'data1');
SELECT * FROM transaction_temp; -- 能访问到数据
COMMIT;

SELECT * FROM transaction_temp; -- 报错：表不存在

3. 全局临时表模拟 ​

PostgreSQL不直接支持全局临时表，但可以通过普通表+会话ID模拟：

sql
-- 创建模拟全局临时表的普通表
CREATE TABLE global_temp (
    session_id TEXT NOT NULL,
    id INT NOT NULL,
    data TEXT,
    PRIMARY KEY (session_id, id)
);

-- 使用示例
DO $$
DECLARE
    my_session_id TEXT := current_setting('application_name');
BEGIN
    -- 插入数据
    INSERT INTO global_temp (session_id, id, data)
    VALUES (my_session_id, 1, 'data1');
    
    -- 查询当前会话数据
    SELECT * FROM global_temp WHERE session_id = my_session_id;
    
    -- 清理当前会话数据
    DELETE FROM global_temp WHERE session_id = my_session_id;
END;
$$;

临时表性能优化 ​

1. 临时表空间配置 ​

sql
-- 查看临时表空间配置
SHOW temp_tablespaces;

-- 设置临时表空间
ALTER SYSTEM SET temp_tablespaces = 'temp_tbs';

-- 为特定查询设置临时表空间
SET temp_tablespaces = 'temp_tbs';

2. 临时表内存配置 ​

sql
-- 查看临时表相关内存配置
SHOW temp_buffers;
SHOW work_mem;

-- 调整临时表缓冲区大小（建议为总内存的1-5%）
ALTER SYSTEM SET temp_buffers = '64MB';

-- 调整工作内存大小（影响排序、哈希等操作）
ALTER SYSTEM SET work_mem = '16MB';

3. 临时表优化最佳实践 ​

sql
-- 1. 避免在临时表上创建不必要的约束
CREATE TEMP TABLE temp_table (
    id INT,
    data TEXT
    -- 只在必要时添加约束
);

-- 2. 批量插入数据
INSERT INTO temp_table (id, data)
SELECT generate_series(1, 100000), 'test data';

-- 3. 在数据加载后创建索引
CREATE TEMP TABLE temp_data AS SELECT * FROM large_table;
CREATE INDEX idx_temp_data_id ON temp_data(id);

-- 4. 使用UNLOGGED表代替临时表（适用于需要持久化但不需要WAL日志的场景）
CREATE UNLOGGED TABLE unlogged_table (
    id INT,
    data TEXT
);

临时表监控与管理 ​

1. 查看当前会话临时表 ​

sql
-- 查看当前会话的临时表
SELECT relname, relkind, relpersistence
FROM pg_class
WHERE relpersistence IN ('t', 'u') -- t: 临时表, u: 非日志表
AND relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'pg_temp_' || pg_backend_pid()::text);

-- 查看所有临时表（需要超级用户权限）
SELECT p.pid, c.relname, c.relkind, c.relpersistence
FROM pg_class c
JOIN pg_namespace n ON c.relnamespace = n.oid
JOIN pg_stat_activity p ON n.nspname = 'pg_temp_' || p.pid::text
WHERE c.relpersistence = 't';

2. 临时表大小监控 ​

sql
-- 查看临时表大小
SELECT 
    relname AS table_name,
    pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size,
    pg_size_pretty(pg_relation_size(c.oid)) AS data_size,
    pg_size_pretty(pg_indexes_size(c.oid)) AS index_size
FROM 
    pg_class c
JOIN 
    pg_namespace n ON c.relnamespace = n.oid
WHERE 
    n.nspname LIKE 'pg_temp_%'
AND 
    c.relpersistence = 't';

3. 手动清理临时表 ​

sql
-- 手动删除临时表
DROP TABLE IF EXISTS temp_sales;

-- 删除所有临时表（谨慎使用）
DO $$
DECLARE
    rec RECORD;
BEGIN
    FOR rec IN 
        SELECT relname
        FROM pg_class c
        JOIN pg_namespace n ON c.relnamespace = n.oid
        WHERE n.nspname LIKE 'pg_temp_%'
        AND c.relpersistence = 't'
    LOOP
        EXECUTE format('DROP TABLE IF EXISTS %I', rec.relname);
    END LOOP;
END;
$$;

临时表最佳实践 ​

1. 适合使用临时表的场景 ​

  • 复杂查询结果缓存：将复杂查询结果存储在临时表中，供后续查询使用
  • 批量数据处理：在批量更新或删除操作前，先将数据加载到临时表
  • 数据转换：在ETL过程中用于中间数据存储
  • 测试和调试：在开发和测试过程中临时存储测试数据

2. 临时表使用注意事项 ​

sql
-- 1. 避免在频繁调用的函数中创建临时表
-- 2. 尽量使用较小的临时表，避免占用过多磁盘空间
-- 3. 在不再需要时手动删除临时表
-- 4. 考虑使用CTE（WITH子句）代替简单的临时表
WITH temp_cte AS (
    SELECT product_id, SUM(amount) as total
    FROM sales
    GROUP BY product_id
)
SELECT * FROM temp_cte WHERE total > 1000;

-- 5. 对于频繁使用的临时表结构，考虑使用模板表
CREATE TABLE temp_template (
    id INT,
    data TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);

-- 使用模板创建临时表
CREATE TEMP TABLE temp_usage AS SELECT * FROM temp_template WHERE 1=0;

3. 临时表与CTE的选择 ​

特性临时表CTE（WITH子句）
作用域会话或事务单个查询
可多次引用是是
支持索引是否
支持UPDATE/DELETE是否
性能适合复杂查询和大数据集适合简单查询和小数据集

临时表常见问题与解决方案 ​

1. 临时表名冲突 ​

sql
-- 问题：创建临时表时提示表已存在
-- 解决方案1：使用IF NOT EXISTS
CREATE TEMP TABLE IF NOT EXISTS temp_sales (
    sale_id SERIAL PRIMARY KEY,
    product_id INT NOT NULL,
    sale_date DATE NOT NULL,
    amount DECIMAL(10,2) NOT NULL
);

-- 解决方案2：使用唯一命名模式
CREATE TEMP TABLE temp_sales_202301 (
    sale_id SERIAL PRIMARY KEY,
    product_id INT NOT NULL,
    sale_date DATE NOT NULL,
    amount DECIMAL(10,2) NOT NULL
);

2. 临时表索引未被使用 ​

sql
-- 问题：临时表上的索引未被查询优化器使用
-- 解决方案1：分析临时表统计信息
ANALYZE temp_sales;

-- 解决方案2：强制使用索引
SELECT * FROM temp_sales
WHERE product_id = 123
ORDER BY sale_date
USING INDEX idx_temp_sales_product_date;

-- 解决方案3：调整random_page_cost参数
SET random_page_cost = 1.1;

3. 临时表占用过多磁盘空间 ​

sql
-- 问题：临时表占用过多磁盘空间导致磁盘空间不足
-- 解决方案1：定期清理不再使用的临时表
DROP TABLE IF EXISTS temp_sales_old;

-- 解决方案2：调整临时表空间位置到大容量磁盘
ALTER SYSTEM SET temp_tablespaces = 'large_disk_tbs';

-- 解决方案3：优化查询，减少临时表数据量
CREATE TEMP TABLE temp_sales AS
SELECT sale_id, product_id, sale_date, amount
FROM sales
WHERE sale_date BETWEEN '2023-01-01' AND '2023-01-31'
AND amount > 100; -- 添加过滤条件减少数据量

常见问题（FAQ） ​

Q1：临时表存储在哪里？ ​

A1：临时表存储在指定的临时表空间中，默认使用postgres数据库的默认表空间。可以通过temp_tablespaces参数配置临时表空间。

Q2：临时表会产生WAL日志吗？ ​

A2：临时表不会产生WAL日志，这意味着：

  • 临时表的操作速度更快
  • 临时表数据不会被复制到从服务器
  • 临时表数据无法通过PITR恢复

Q3：如何查看当前会话的临时表？ ​

A3：可以使用以下查询查看当前会话的临时表：

sql
SELECT relname
FROM pg_class c
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE n.nspname LIKE 'pg_temp_%'
AND c.relpersistence = 't';

Q4：临时表可以被其他会话访问吗？ ​

A4：不可以。临时表是会话隔离的，每个会话只能访问自己创建的临时表。

Q5：临时表的统计信息是如何管理的？ ​

A5：临时表的统计信息默认会被收集，但由于临时表只在会话中存在，统计信息不会持久化。建议在向临时表加载大量数据后手动运行ANALYZE命令更新统计信息。