PostgreSQL 版本 | 支持版本 |
PostgreSQL 17 | v17.10_r1.19及以上 |
PostgreSQL 18 | v18.4_r1.10及以上 |
SHOW tencentdb_version; 查看当前实例的内核版本。shared_preload_libraries 参数，在其参数运行值中勾选 pg_savior，保存后重启实例。pg_savior.enabled 默认为 on），无需额外操作。CREATE TABLE 中带 PRIMARY KEY 或 UNIQUE 约束时，同样按“非并发建索引”防护拦截（临时表除外），处理方式见 常见问题。ANALYZE 更新统计信息。user 级（可在会话中直接 SET），也可在控制台参数设置中修改。参数 | 默认值 | 说明 |
pg_savior.enabled | on | 防护总开关，off 时关闭全部防护 |
pg_savior.bypass | off | 会话级放行开关，on 时当前会话跳过全部防护 |
pg_savior.max_rows_affected | 0 | UPDATE/DELETE 预估影响行数上限，超过即拦截；0表示不限制 |
pg_savior.large_table_threshold_rows | 1000000 | 大表判定阈值（行数），超过该行数的表受 DDL 防护 |
pg_savior.max_query_cost | 0 | 语句预估执行代价（total_cost）上限，超过即拦截；0表示不限制 |
pg_savior.max_result_rows | 0 | 语句返回行数上限，超过即报错中止；0表示不限制 |
root（tencentdb_superuser 成员）账号执行，在本节创建的示例表，您可直接复现。shared_preload_libraries，在其参数运行值中勾选 pg_savior，保存并重启实例。postgres=> SHOW shared_preload_libraries;shared_preload_libraries--------------------------------------------------------------------------------------------------------pg_textsearch, pg_cron, pg_squeeze, timescaledb, pg_jieba, pg_bigm, tencentdb_pwdcheck, pg_savior(1 row)
pg_savior 即已加载。查看防护参数状态：postgres=> SELECT name, setting, context FROM pg_settings WHERE name LIKE 'pg_savior%' ORDER BY name;name | setting | context--------------------------------------+---------+---------pg_savior.bypass | off | userpg_savior.enabled | on | userpg_savior.large_table_threshold_rows | 1000000 | userpg_savior.max_rows_affected | 0 | user(4 rows)
context 为 user 表示所有参数均可在会话中通过 SET 直接调整，无需重启实例。postgres=> CREATE TABLE orders (id int,customer text,amount numeric);CREATE TABLEpostgres=> INSERT INTO orders VALUES (1, 'alice', 100.00), (2, 'bob', 250.50), (3, 'carol', 78.90);INSERT 0 3postgres=> ANALYZE orders;ANALYZE
postgres=> DELETE FROM orders;ERROR: pg_savior: DELETE without WHERE clause is blockedHINT: Add a WHERE clause, or set pg_savior.bypass = on for this session.postgres=> UPDATE orders SET amount = 0;ERROR: pg_savior: UPDATE without WHERE clause is blockedHINT: Add a WHERE clause, or set pg_savior.bypass = on for this session.
postgres=> WITH moved AS (DELETE FROM orders RETURNING *) SELECT count(*) FROM moved;ERROR: pg_savior: DELETE without WHERE clause is blockedHINT: Add a WHERE clause, or set pg_savior.bypass = on for this session.
postgres=> DELETE FROM orders WHERE id = 0;DELETE 0
pg_savior.max_rows_affected 后，优化器预估影响行数超过该值的 UPDATE/DELETE 会被拦截。以下将上限设为2，示例表 orders 中有3行数据：root=> SET pg_savior.max_rows_affected = 2; DELETE FROM orders WHERE id > 0;SETERROR: pg_savior: DELETE estimated to affect 3 rows, exceeds pg_savior.max_rows_affected (2)HINT: Refine the WHERE clause, raise pg_savior.max_rows_affected, or set pg_savior.bypass = on. Run ANALYZE if the estimate looks wrong.
root=> SET pg_savior.max_rows_affected = 2; UPDATE orders SET amount = 0 WHERE id <= 2;SETUPDATE 2root=> SET pg_savior.max_rows_affected = 0;SET
postgres=> CREATE INDEX idx_orders_customer ON orders (customer);ERROR: pg_savior: CREATE INDEX without CONCURRENTLY is blockedHINT: Use CREATE INDEX CONCURRENTLY (it cannot run in a transaction block), or set pg_savior.bypass = on for this session.
CREATE INDEX CONCURRENTLY 在线创建即可：postgres=> CREATE INDEX CONCURRENTLY idx_orders_customer ON orders (customer);CREATE INDEX
CREATE INDEX CONCURRENTLY 不能在事务块中执行。pg_savior.large_table_threshold_rows（默认 1000000）的表视为“大表”，对其执行 TRUNCATE、DROP TABLE 及可能触发整表重写的 ALTER TABLE 会被拦截。以下创建一张100万行以上的示例表：postgres=> CREATE TABLE big_orders AS SELECT g AS id, 'customer_' || g AS customer, g::numeric AS amount FROM generate_series(1, 1000100) g;SELECT 1000100postgres=> ANALYZE big_orders;ANALYZE
postgres=> TRUNCATE big_orders;ERROR: pg_savior: TRUNCATE on a large table "big_orders" (1000100 rows) is blockedHINT: Verify the target, raise pg_savior.large_table_threshold_rows, or set pg_savior.bypass = on. Run ANALYZE if the row estimate looks wrong.postgres=> DROP TABLE big_orders;ERROR: pg_savior: DROP TABLE on a large table "big_orders" (1000100 rows) is blockedHINT: Verify the target, raise pg_savior.large_table_threshold_rows, or set pg_savior.bypass = on. Run ANALYZE if the row estimate looks wrong.postgres=> ALTER TABLE big_orders ADD COLUMN memo text DEFAULT 'N';ERROR: pg_savior: ALTER TABLE ADD COLUMN with DEFAULT on a large table (1000100 rows) is blockedHINT: This operation may rewrite the whole table (it is blocked conservatively). Plan a batched migration; raise pg_savior.large_table_threshold_rows; or set pg_savior.bypass = on. Run ANALYZE if the row estimate looks wrong.postgres=> ALTER TABLE big_orders ALTER COLUMN amount TYPE numeric(12,2);ERROR: pg_savior: ALTER TABLE ALTER COLUMN TYPE on a large table (1000100 rows) is blockedHINT: This operation may rewrite the whole table (it is blocked conservatively). Plan a batched migration; raise pg_savior.large_table_threshold_rows; or set pg_savior.bypass = on. Run ANALYZE if the row estimate looks wrong.
ALTER TABLE 不受影响：postgres=> ALTER TABLE big_orders ADD COLUMN memo text;ALTER TABLE
postgres=> CREATE TABLE junk (id int);CREATE TABLEpostgres=> DROP TABLE junk;DROP TABLE
ALTER TABLE 操作包括：带 DEFAULT 的 ADD COLUMN 与 ALTER COLUMN TYPE，这两类操作可能重写整表。TRUNCATE ... CASCADE 关联的外键引用表也会纳入检查。pg_savior.max_query_cost 后，规划器预估执行代价（total_cost）超过该值的语句在执行前被拦截。以下示例将上限设为100，示例表 big_orders（100万行以上）上的一次全表统计查询将被拦截：root=> SET pg_savior.max_query_cost = 100; SELECT count(*) FROM big_orders;SETERROR: pg_savior: statement estimated cost 13569 exceeds pg_savior.max_query_cost (100)HINT: Refine the query, raise pg_savior.max_query_cost, or set pg_savior.bypass = on. Check the estimate with EXPLAIN.
EXPLAIN 只生成执行计划、不实际执行语句，不受该防护限制，可用来查看预估代价：root=> SET pg_savior.max_query_cost = 100; EXPLAIN SELECT count(*) FROM big_orders;SETQUERY PLAN----------------------------------------------------------------------------------------------Finalize Aggregate (cost=13569.07..13569.08 rows=1 width=8)-> Gather (cost=13568.85..13569.06 rows=2 width=8)Workers Planned: 2-> Partial Aggregate (cost=12568.85..12568.86 rows=1 width=8)-> Parallel Seq Scan on big_orders (cost=0.00..11527.08 rows=416708 width=0)(5 rows)root=> SET pg_savior.max_query_cost = 0;SET
EXPLAIN 查看。EXPLAIN ANALYZE 会实际执行语句，同样受该防护检查。pg_savior.max_result_rows 后，语句输出行数超过该值即报错中止。以下示例将上限设为2，示例表 orders 中有3行数据：root=> SET pg_savior.max_result_rows = 2; SELECT id FROM orders;SETERROR: pg_savior: statement returned more than 2 rowsHINT: Add a LIMIT, raise pg_savior.max_result_rows, or set pg_savior.bypass = on.
LIMIT 后正常返回：root=> SET pg_savior.max_result_rows = 2; SELECT id FROM orders LIMIT 2;SETid----12(2 rows)root=> SET pg_savior.max_result_rows = 0;SET
LIMIT 或调整上限后重试。postgres=> DROP DATABASE postgres;ERROR: pg_savior: DROP DATABASE "postgres" is blockedHINT: Set pg_savior.bypass = on for this session if you really mean it.
DROP DATABASE IF EXISTS 属于幂等清理脚本用法，正常跳过：postgres=> DROP DATABASE IF EXISTS savior_demo_never;NOTICE: database "savior_demo_never" does not exist, skippingDROP DATABASE
root=> SET pg_savior.enabled = off; DELETE FROM orders;SETDELETE 3root=> INSERT INTO orders VALUES (1, 'alice', 100.00), (2, 'bob', 250.50), (3, 'carol', 78.90);INSERT 0 3root=> SET pg_savior.enabled = on;SET
root=> SET pg_savior.bypass = on; DELETE FROM orders;SETDELETE 3root=> SET pg_savior.bypass = off;SET
pg_savior.enabled 的参数运行值修改为 off。bypass 与 enabled 的会话级修改不影响其他会话。CREATE TABLE 带 PRIMARY KEY 或 UNIQUE 约束时报错「CREATE INDEX without CONCURRENTLY is blocked」如何处理？postgres=> CREATE TABLE t3 (id int PRIMARY KEY, v text);ERROR: pg_savior: CREATE INDEX without CONCURRENTLY is blockedHINT: Use CREATE INDEX CONCURRENTLY (it cannot run in a transaction block), or set pg_savior.bypass = on for this session.
postgres=> CREATE TABLE t3 (id int, v text);CREATE TABLEpostgres=> CREATE UNIQUE INDEX CONCURRENTLY t3_id_uidx ON t3(id);CREATE INDEXpostgres=> ALTER TABLE t3 ADD CONSTRAINT t3_pkey PRIMARY KEY USING INDEX t3_id_uidx;NOTICE: ALTER TABLE / ADD CONSTRAINT USING INDEX will rename index "t3_id_uidx" to "t3_pkey"ALTER TABLEpostgres=> DROP TABLE t3;DROP TABLE
ANALYZE 更新统计信息，再重试。pg_savior.large_table_threshold_rows 的设置，确认阈值是否被调整为小于表行数的值。SET pg_savior.bypass = on; 放行，执行完成后建议改回 off。放行仅对当前会话有效。pg_savior.enabled 在会话中临时设为 off，或在控制台参数设置中修改为 off。变更完成后建议重新开启防护。文档反馈