如何让DuckDB批量脚本遇OOM等错误时立即退出？

我有一个设置了自定义memory_limit的DuckDB批量脚本，当其中一条语句出现OOM（内存不足）错误时，脚本并未退出，而是继续执行剩余语句。这些后续语句因前置依赖失败要么报错要么无意义，导致日志信息混乱；如果失败的是UPDATE类命令，还可能引发数据不一致问题。

示例脚本：

SET memory_limit = '100GB';
SET threads TO 64;

CREATE TABLE summary AS
  SELECT
    point_id,
    COUNT(pred_occ) AS ensemble_support,
    AVG(pred_range) AS pat_mean,
    QUANTILE_CONT(pred_occ, [0.1, 0.5, 0.9]) AS occurrence_quantiles,
    QUANTILE_CONT(pred_count, [0.1, 0.5, 0.9]) AS count_quantiles,
    QUANTILE_CONT(pred_occ * pred_count, [0.1, 0.5, 0.9]) AS abundance_quantiles
  FROM predictions
  GROUP BY point_id;

CREATE TABLE erd AS
  SELECT
    CAST(SPLIT_PART(point_id, '-', 2) AS BIGINT) AS checklist_id,
    SPLIT_PART(point_id, '-', 1) AS type,
    ensemble_support, pat_mean,
    occurrence_quantiles[2] AS occurrence_median,
    occurrence_quantiles[1] AS occurrence_lower,
    occurrence_quantiles[3] AS occurrence_upper,
    count_quantiles[2] AS count_median,
    count_quantiles[1] AS count_lower,
    count_quantiles[3] AS count_upper,
    abundance_quantiles[2] AS abundance_median,
    abundance_quantiles[1] AS abundance_lower,
    abundance_quantiles[3] AS abundance_upper
  FROM summary
  WHERE point_id LIKE 'test-%';

COPY (
  SELECT
    s.*,
    e.latitude, e.longitude, e.year, e.day_of_year, e.observer_id
  FROM erd as s
  INNER JOIN '{input_erd_pq}' as e
    ON s.checklist_id = e.checklist_id
) TO '{predictions_erd_pq}' (FORMAT 'parquet');

COPY (
  SELECT
    CAST(SPLIT_PART(point_id, '-', 2) AS BIGINT) AS srd_id,
    CAST(NULLIF(SPLIT_PART(point_id, '-', 3), '') AS INTEGER) AS day_of_year,
    ensemble_support, pat_mean,
    occurrence_quantiles[2] AS occurrence_median,
    occurrence_quantiles[1] AS occurrence_lower,
    occurrence_quantiles[3] AS occurrence_upper,
    count_quantiles[2] AS count_median,
    count_quantiles[1] AS count_lower,
    count_quantiles[3] AS count_upper,
    abundance_quantiles[2] AS abundance_median,
    abundance_quantiles[1] AS abundance_lower,
    abundance_quantiles[3] AS abundance_upper
  FROM summary
  WHERE point_id LIKE 'srd-%') TO '{predictions_srd_pq}' (FORMAT 'parquet');

错误日志：

Out of Memory Error: Failed to allocate block of 2048 bytes (bad allocation)
Catalog Error: Table with name summary does not exist!
Did you mean "temp.information_schema.schemata"?
LINE 15:   FROM summary
                ^
Catalog Error: Table with name erd does not exist!
Did you mean "temp.information_schema.tables"?
LINE 5:   FROM erd as s
               ^
Catalog Error: Table with name summary does not exist!
Did you mean "temp.information_schema.schemata"?
LINE 15:   FROM summary
                ^

从日志可见，即便前置命令失败，脚本仍尝试执行所有后续语句。以下是几种让DuckDB在语句执行失败时立即退出的方法：

方法1：使用DuckDB命令行的--exit-on-error参数 #

这是最简单直接的方案，在执行脚本时添加该参数，只要有任意一条SQL语句执行失败，DuckDB就会立即退出，不再执行后续语句。

执行命令示例：

duckdb --exit-on-error your_script.sql

该参数适用于DuckDB 0.7.0及以上版本，能覆盖包括OOM在内的各类执行错误场景。

方法2：在SQL脚本中使用事务与异常处理（DuckDB 0.9.0+） #

如果需要在SQL脚本内部实现失败即退出的逻辑，可以利用DuckDB的事务和异常处理机制。将所有逻辑包裹在事务块中，当捕获到异常时回滚并退出。

修改后的脚本示例：

BEGIN TRANSACTION;

SET memory_limit = '100GB';
SET threads TO 64;

CREATE TABLE summary AS
  SELECT
    point_id,
    COUNT(pred_occ) AS ensemble_support,
    AVG(pred_range) AS pat_mean,
    QUANTILE_CONT(pred_occ, [0.1, 0.5, 0.9]) AS occurrence_quantiles,
    QUANTILE_CONT(pred_count, [0.1, 0.5, 0.9]) AS count_quantiles,
    QUANTILE_CONT(pred_occ * pred_count, [0.1, 0.5, 0.9]) AS abundance_quantiles
  FROM predictions
  GROUP BY point_id;

CREATE TABLE erd AS
  SELECT
    CAST(SPLIT_PART(point_id, '-', 2) AS BIGINT) AS checklist_id,
    SPLIT_PART(point_id, '-', 1) AS type,
    ensemble_support, pat_mean,
    occurrence_quantiles[2] AS occurrence_median,
    occurrence_quantiles[1] AS occurrence_lower,
    occurrence_quantiles[3] AS occurrence_upper,
    count_quantiles[2] AS count_median,
    count_quantiles[1] AS count_lower,
    count_quantiles[3] AS count_upper,
    abundance_quantiles[2] AS abundance_median,
    abundance_quantiles[1] AS abundance_lower,
    abundance_quantiles[3] AS abundance_upper
  FROM summary
  WHERE point_id LIKE 'test-%';

COPY (
  SELECT
    s.*,
    e.latitude, e.longitude, e.year, e.day_of_year, e.observer_id
  FROM erd as s
  INNER JOIN '{input_erd_pq}' as e
    ON s.checklist_id = e.checklist_id
) TO '{predictions_erd_pq}' (FORMAT 'parquet');

COPY (
  SELECT
    CAST(SPLIT_PART(point_id, '-', 2) AS BIGINT) AS srd_id,
    CAST(NULLIF(SPLIT_PART(point_id, '-', 3), '') AS INTEGER) AS day_of_year,
    ensemble_support, pat_mean,
    occurrence_quantiles[2] AS occurrence_median,
    occurrence_quantiles[1] AS occurrence_lower,
    occurrence_quantiles[3] AS occurrence_upper,
    count_quantiles[2] AS count_median,
    count_quantiles[1] AS count_lower,
    count_quantiles[3] AS count_upper,
    abundance_quantiles[2] AS abundance_median,
    abundance_quantiles[1] AS abundance_lower,
    abundance_quantiles[3] AS abundance_upper
  FROM summary
  WHERE point_id LIKE 'srd-%') TO '{predictions_srd_pq}' (FORMAT 'parquet');

COMMIT;

EXCEPTION WHEN OTHERS THEN
  ROLLBACK;
  SELECT 'Execution failed, exiting' AS error_message;
  EXIT;

注意：DuckDB的事务默认会自动提交单条语句，因此必须显式使用BEGIN TRANSACTION将所有语句纳入同一个事务中，异常处理才能生效。

方法3：通过Shell脚本逐句控制执行 #

如果需要更灵活的控制逻辑，可以用Shell脚本逐行执行SQL语句，每次执行后检查退出码，若非0则立即终止脚本。

示例bash脚本：

#!/bin/bash
DB_PATH="your_database.duckdb"

# 定义执行SQL的函数，失败则退出
run_sql() {
  local sql="$1"
  echo "Executing: $sql"
  duckdb "$DB_PATH" -c "$sql"
  if [ $? -ne 0 ]; then
    echo "SQL execution failed, exiting"
    exit 1
  fi
}

# 依次执行每条SQL语句
run_sql "SET memory_limit = '100GB'; SET threads TO 64;"

run_sql "CREATE TABLE summary AS
  SELECT
    point_id,
    COUNT(pred_occ) AS ensemble_support,
    AVG(pred_range) AS pat_mean,
    QUANTILE_CONT(pred_occ, [0.1, 0.5, 0.9]) AS occurrence_quantiles,
    QUANTILE_CONT(pred_count, [0.1, 0.5, 0.9]) AS count_quantiles,
    QUANTILE_CONT(pred_occ * pred_count, [0.1, 0.5, 0.9]) AS abundance_quantiles
  FROM predictions
  GROUP BY point_id;"

run_sql "CREATE TABLE erd AS
  SELECT
    CAST(SPLIT_PART(point_id, '-', 2) AS BIGINT) AS checklist_id,
    SPLIT_PART(point_id, '-', 1) AS type,
    ensemble_support, pat_mean,
    occurrence_quantiles[2] AS occurrence_median,
    occurrence_quantiles[1] AS occurrence_lower,
    occurrence_quantiles[3] AS occurrence_upper,
    count_quantiles[2] AS count_median,
    count_quantiles[1] AS count_lower,
    count_quantiles[3] AS count_upper,
    abundance_quantiles[2] AS abundance_median,
    abundance_quantiles[1] AS abundance_lower,
    abundance_quantiles[3] AS abundance_upper
  FROM summary
  WHERE point_id LIKE 'test-%';"

run_sql "COPY (
  SELECT
    s.*,
    e.latitude, e.longitude, e.year, e.day_of_year, e.observer_id
  FROM erd as s
  INNER JOIN '{input_erd_pq}' as e
    ON s.checklist_id = e.checklist_id
) TO '{predictions_erd_pq}' (FORMAT 'parquet');"

run_sql "COPY (
  SELECT
    CAST(SPLIT_PART(point_id, '-', 2) AS BIGINT) AS srd_id,
    CAST(NULLIF(SPLIT_PART(point_id, '-', 3), '') AS INTEGER) AS day_of_year,
    ensemble_support, pat_mean,
    occurrence_quantiles[2] AS occurrence_median,
    occurrence_quantiles[1] AS occurrence_lower,
    occurrence_quantiles[3] AS occurrence_upper,
    count_quantiles[2] AS count_median,
    count_quantiles[1] AS count_lower,
    count_quantiles[3] AS count_upper,
    abundance_quantiles[2] AS abundance_median,
    abundance_quantiles[1] AS abundance_lower,
    abundance_quantiles[3] AS abundance_upper
  FROM summary
  WHERE point_id LIKE 'srd-%') TO '{predictions_srd_pq}' (FORMAT 'parquet');"

这种方法适合需要在执行前后添加额外逻辑（如日志记录、资源清理）的场景。

内容的提问来源于stack exchange，提问作者sligocki