Supabase视图调用含临时表的PL/pgSQL函数遇405错误求助

安全阿岳

2026-6-23

问题：PL/pgSQL函数使用临时表导致PostgREST GET请求返回405错误

背景

在PL/pgSQL函数中使用临时表优化性能，该函数被用于视图中。Supabase表查看器内访问正常，但前端通过GET请求访问视图时出现405错误。改用不含临时表的函数版本后，视图可正常访问，但性能明显下降。

技术环境

PL/pgSQL语言
所有函数均标记为VOLATILE
PostgREST版本12.0.2
PostgreSQL版本15.1.1.9

含临时表的函数版本（性能优但访问报错）

CREATE OR REPLACE FUNCTION calculate_working_minutes(
    p_user_id UUID,
    p_start_date DATE,
    p_end_date DATE
) RETURNS INTEGER AS $$
DECLARE
    v_working_day_count INT := 0;
    v_date RECORD;
    v_model_id BIGINT;
    v_is_working_day INT;
BEGIN
    -- Step 1: 获取用户所有相关工时模型分配记录
    CREATE TEMP TABLE temp_model_assignments AS
    SELECT working_time_model_id, year, month
    FROM types_working_time_model_assignment_records
    WHERE user_id = p_user_id
      AND (year < EXTRACT(YEAR FROM p_end_date) 
          OR (year = EXTRACT(YEAR FROM p_end_date) AND month <= EXTRACT(MONTH FROM p_end_date)))
    ORDER BY year DESC, month DESC;

    -- Step 2: 获取相关工时模型详情
    CREATE TEMP TABLE temp_working_time_models AS
    SELECT id, 
           sunday_working_day, 
           monday_working_day, 
           tuesday_working_day, 
           wednesday_working_day, 
           thursday_working_day, 
           friday_working_day, 
           saturday_working_day
    FROM types_working_time_models
    WHERE id IN (SELECT DISTINCT working_time_model_id FROM temp_model_assignments);

    -- Step 3: 遍历日期计算工作日数
    FOR v_date IN (SELECT generate_series(p_start_date, p_end_date, '1 day'::INTERVAL)::DATE AS g_date)
    LOOP
        IF EXISTS (SELECT 1 FROM temp_model_assignments) THEN
            -- 获取当前日期对应的工时模型ID
            SELECT working_time_model_id
            INTO v_model_id
            FROM temp_model_assignments
            WHERE (year < EXTRACT(YEAR FROM v_date.g_date) 
                  OR (year = EXTRACT(YEAR FROM v_date.g_date) AND month <= EXTRACT(MONTH FROM v_date.g_date)))
            ORDER BY year DESC, month DESC
            LIMIT 1;

            -- 判断当前日期是否为工作日
            SELECT (
                CASE 
                    WHEN EXTRACT(DOW FROM v_date.g_date::DATE) = 0 AND sunday_working_day THEN 1
                    WHEN EXTRACT(DOW FROM v_date.g_date::DATE) = 1 AND monday_working_day THEN 1
                    WHEN EXTRACT(DOW FROM v_date.g_date::DATE) = 2 AND tuesday_working_day THEN 1
                    WHEN EXTRACT(DOW FROM v_date.g_date::DATE) = 3 AND wednesday_working_day THEN 1
                    WHEN EXTRACT(DOW FROM v_date.g_date::DATE) = 4 AND thursday_working_day THEN 1
                    WHEN EXTRACT(DOW FROM v_date.g_date::DATE) = 5 AND friday_working_day THEN 1
                    WHEN EXTRACT(DOW FROM v_date.g_date::DATE) = 6 AND saturday_working_day THEN 1
                    ELSE 0
                END
            ) INTO v_is_working_day
            FROM temp_working_time_models
            WHERE id = v_model_id;

            v_working_day_count := v_working_day_count + v_is_working_day;
        ELSE
            v_working_day_count := v_working_day_count + 0;
        END IF;
    END LOOP;

    -- 清理临时表
    DROP TABLE temp_model_assignments;
    DROP TABLE temp_working_time_models;

    RETURN v_working_day_count;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

不含临时表的函数版本（性能差但访问正常）

CREATE OR REPLACE FUNCTION calculate_working_minutes(
    p_user_id UUID,
    p_start_date DATE,
    p_end_date DATE
) RETURNS INTEGER AS $$
DECLARE
    v_working_day_count INT := 0;
    v_date RECORD;
    v_model_id BIGINT;
    v_is_working_day INT;
BEGIN
    -- 遍历日期计算工作日数
    FOR v_date IN (SELECT generate_series(p_start_date, p_end_date, '1 day'::INTERVAL)::DATE AS g_date)
    LOOP
        IF EXISTS (
            SELECT 1
            FROM types_working_time_model_assignment_records
            WHERE user_id = p_user_id
              AND (year < EXTRACT(YEAR FROM v_date.g_date) 
                  OR (year = EXTRACT(YEAR FROM v_date.g_date) AND month <= EXTRACT(MONTH FROM v_date.g_date)))
        ) THEN
            -- 获取当前日期对应的工时模型ID
            SELECT working_time_model_id
            INTO v_model_id
            FROM types_working_time_model_assignment_records
            WHERE user_id = p_user_id
              AND (year < EXTRACT(YEAR FROM v_date.g_date) 
                  OR (year = EXTRACT(YEAR FROM v_date.g_date) AND month <= EXTRACT(MONTH FROM v_date.g_date)))
            ORDER BY year DESC, month DESC
            LIMIT 1;

            -- 判断当前日期是否为工作日
            SELECT (
                CASE 
                    WHEN EXTRACT(DOW FROM v_date.g_date::DATE) = 0 AND sunday_working_day THEN 1
                    WHEN EXTRACT(DOW FROM v_date.g_date::DATE) = 1 AND monday_working_day THEN 1
                    WHEN EXTRACT(DOW FROM v_date.g_date::DATE) = 2 AND tuesday_working_day THEN 1
                    WHEN EXTRACT(DOW FROM v_date.g_date::DATE) = 3 AND wednesday_working_day THEN 1
                    WHEN EXTRACT(DOW FROM v_date.g_date::DATE) = 4 AND thursday_working_day THEN 1
                    WHEN EXTRACT(DOW FROM v_date.g_date::DATE) = 5 AND friday_working_day THEN 1
                    WHEN EXTRACT(DOW FROM v_date.g_date::DATE) = 6 AND saturday_working_day THEN 1
                    ELSE 0
                END
            ) INTO v_is_working_day
            FROM types_working_time_models
            WHERE id = v_model_id;

            v_working_day_count := v_working_day_count + v_is_working_day;
        ELSE
            v_working_day_count := v_working_day_count + 0;
        END IF;
    END LOOP;

    RETURN v_working_day_count;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

错误日志

Log Event Message
cannot execute CREATE TABLE AS in a read-only transaction

日志元数据：

[
  {
    "file": null,
    "host": "###",
    "metadata": [],
    "parsed": [
      {
        "application_name": "postgrest",
        "backend_type": "client backend",
        "command_tag": "SELECT",
        "connection_from": "###",
        "context": "SQL statement \"CREATE TEMP TABLE temp_model_assignments AS\r\n    SELECT working_time_model_id, year, month\r\n    FROM types_working_time_model_assignment_records\r\n    WHERE user_id = p_user_id\r\n      AND (year < EXTRACT(YEAR FROM p_end_date) \r\n          OR (year = EXTRACT(YEAR FROM p_end_date) AND month <= EXTRACT(MONTH FROM p_end_date)))\r\n    ORDER BY year DESC, month DESC\"\nPL/pgSQL function calculate_working_days(uuid,date,date) line 8 at SQL statement\nPL/pgSQL function calculate_working_minutes(uuid,date,date) line 10 at assignment",
        "database_name": "postgres",
        "detail": null,
        "error_severity": "ERROR",
        "hint": null,
        "internal_query": null,
        "internal_query_pos": null,
        "leader_pid": null,
        "location": null,
        "process_id": ###,
        "query": "WITH pgrst_source AS ( SELECT \"public\".\"view_timetracking_weekly\".* FROM \"public\".\"view_timetracking_weekly\"     LIMIT $1 OFFSET $2 )  SELECT null::bigint AS total_result_set, pg_catalog.count(_postgrest_t) AS page_total, coalesce(json_agg(_postgrest_t), '[]') AS body, nullif(current_setting('response.headers', true), '') AS response_headers, nullif(current_setting('response.status', true), '') AS response_status, '' AS response_inserted FROM ( SELECT * FROM pgrst_source ) _postgrest_t",
        "query_id": ###,
        "query_pos": null,
        "session_id": "###",
        "session_line_num": 3,
        "session_start_time": "2024-05-28 20:36:48 UTC",
        "sql_state_code": "25006",
        "timestamp": "2024-05-28 20:36:53.743 UTC",
        "transaction_id": 0,
        "user_name": "authenticator",
        "virtual_transaction_id": "###"
      }
    ],
    "parsed_from": null,
    "project": null,
    "source_type": null
  }
]

解决方案

原因分析

PostgREST默认将GET请求放在只读事务中执行，而创建临时表属于写操作，违反了只读事务的限制，触发25006错误，最终表现为前端收到405错误。

解决办法

方法1：用CTE替代临时表

将临时表替换为公共表表达式（CTE），既保留预查询的性能优势，又能在只读事务中运行。修改后的函数如下：

CREATE OR REPLACE FUNCTION calculate_working_minutes(
    p_user_id UUID,
    p_start_date DATE,
    p_end_date DATE
) RETURNS INTEGER AS $$
DECLARE
    v_working_day_count INT := 0;
    v_date RECORD;
    v_model_id BIGINT;
    v_is_working_day INT;
BEGIN
    -- 用CTE替代临时表，预加载所需数据
    WITH temp_model_assignments AS (
        SELECT working_time_model_id, year, month
        FROM types_working_time_model_assignment_records
        WHERE user_id = p_user_id
          AND (year < EXTRACT(YEAR FROM p_end_date) 
              OR (year = EXTRACT(YEAR FROM p_end_date) AND month <= EXTRACT(MONTH FROM p_end_date)))
        ORDER BY year DESC, month DESC
    ),
    temp_working_time_models AS (
        SELECT id, 
               sunday_working_day, 
               monday_working_day, 
               tuesday_working_day, 
               wednesday_working_day, 
               thursday_working_day, 
               friday_working_day, 
               saturday_working_day
        FROM types_working_time_models
        WHERE id IN (SELECT DISTINCT working_time_model_id FROM temp_model_assignments)
    )
    -- 遍历日期计算工作日数
    FOR v_date IN (SELECT generate_series(p_start_date, p_end_date, '1 day'::INTERVAL)::DATE AS g_date)
    LOOP
        IF EXISTS (SELECT 1 FROM temp_model_assignments) THEN
            SELECT working_time_model_id
            INTO v_model_id
            FROM temp_model_assignments
            WHERE (year < EXTRACT(YEAR FROM v_date.g_date) 
                  OR (year = EXTRACT(YEAR FROM v_date.g_date) AND month <= EXTRACT(MONTH FROM v_date.g_date)))
            ORDER BY year DESC, month DESC
            LIMIT 1;

            SELECT (
                CASE 
                    WHEN EXTRACT(DOW FROM v_date.g_date::DATE) = 0 AND sunday_working_day THEN 1
                    WHEN EXTRACT(DOW FROM v_date.g_date::DATE) = 1 AND monday_working_day THEN 1
                    WHEN EXTRACT(DOW FROM v_date.g_date::DATE) = 2 AND tuesday_working_day THEN 1
                    WHEN EXTRACT(DOW FROM v_date.g_date::DATE) = 3 AND wednesday_working_day THEN 1
                    WHEN EXTRACT(DOW FROM v_date.g_date::DATE) = 4 AND thursday_working_day THEN 1
                    WHEN EXTRACT(DOW FROM v_date.g_date::DATE) = 5 AND friday_working_day THEN 1
                    WHEN EXTRACT(DOW FROM v_date.g_date::DATE) = 6 AND saturday_working_day THEN 1
                    ELSE 0
                END
            ) INTO v_is_working_day
            FROM temp_working_time_models
            WHERE id = v_model_id;

            v_working_day_count := v_working_day_count + v_is_working_day;
        ELSE
            v_working_day_count := v_working_day_count + 0;
        END IF;
    END LOOP;

    RETURN v_working_day_count;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

方法2：修改PostgREST配置关闭只读事务

在Supabase控制台中进入Settings -> Database -> PostgREST Configuration，添加db-read-only = false配置项，重启PostgREST服务。此方法会让所有GET请求在可写事务中执行，存在一定安全风险，需谨慎使用。

方法3：改用POST请求调用函数

将函数改为返回表类型，前端通过POST请求调用PostgREST的函数接口，此时PostgREST会使用可写事务执行函数。但需要修改前端请求逻辑，从GET改为POST。

Supabase视图调用含临时表的PL/pgSQL函数遇405错误求助

背景 #

技术环境 #

含临时表的函数版本（性能优但访问报错） #

不含临时表的函数版本（性能差但访问正常） #

错误日志 #

解决方案 #

原因分析 #

解决办法 #