如何解决PostgreSQL因未输入WHERE条件导致整张表被误更新的问题？

本文共计795个文字，预计阅读时间需要4分钟。

为了避免出现这个错误，可以在PostgreSQL数据库中通过触发器来解决问题。以下是使用PL/pgSQL编写的触发器示例，以及如何修改`postgresql.conf`配置和添加`cus`。

1. 编写PL/pgSQL触发器：

sqlCREATE OR REPLACE FUNCTION prevent_error()RETURNS TRIGGER AS $$BEGIN -- 在这里添加你的逻辑来处理可能引发错误的情况 -- 例如，检查某些条件并抛出错误 IF NEW.column_name='error_condition' THEN RAISE EXCEPTION 'Error condition detected'; END IF; RETURN NEW;END;$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_nameBEFORE INSERT OR UPDATE ON your_tableFOR EACH ROW EXECUTE FUNCTION prevent_error();

2. 修改`postgresql.conf`配置：

打开`postgresql.conf`文件，通常位于PostgreSQL数据目录中。添加或修改以下配置项：

shared_preload_libraries='your_custom_library'

这里`your_custom_library`是你自定义的库文件名，它包含了触发器或其他需要预加载的函数。

3. 添加`cus`目录：

确保你的自定义库文件位于PostgreSQL的共享库目录中。如果不存在，可以创建一个名为`cus`的目录，并将你的库文件放在其中。例如：

sudo mkdir -p /usr/local/pgsql/cussudo cp your_custom_library.so /usr/local/pgsql/cus/

确保你有足够的权限来修改这些文件和目录。

通过以上步骤，你可以通过触发器来避免错误，并通过配置文件和目录设置来确保自定义库能够被正确加载和使用。

虽然出现这个错误很挫，但有时候还是会被你或者你的同事碰到。为了避免这个错误，PostgreSQL数据库中可以通过触发器来解决，这里用的是plpgsql 。

1、修改postgresql.conf配置

增加：custom_variable_classes = 'limits'
重新加载：pg_ctl reload

2、创建触发器函数

CREATE OR REPLACE FUNCTION zero_counter()
RETURNS TRIGGER
LANGUAGE plpgsql
AS
$BODY$
DECLARE
BEGIN
PERFORM set_config('limits.test', '0', true);
IF TG_OP = 'UPDATE' THEN
RETURN NEW;
END IF;
RETURN OLD;
END;
$BODY$;

CREATE OR REPLACE FUNCTION limit_modifications()
RETURNS TRIGGER
LANGUAGE plpgsql
AS
$BODY$
DECLARE
i INT4;
BEGIN
i := current_setting('limits.test')::INT4 + 1;
PERFORM set_config('limits.test', i::TEXT, true);
IF i > 5000 THEN
RAISE EXCEPTION '% of more than 5000 row is forbidden.', TG_OP;
END IF;
IF TG_OP = 'UPDATE' THEN
RETURN NEW;
END IF;
RETURN OLD;
END;
$BODY$;

3、测试

forummon=# create table test as select i as id, 'password for: ' || i as pass from generate_series(1,10000) i;
forummon=# CREATE TRIGGER zero_counter BEFORE UPDATE OR DELETE ON test FOR EACH STATEMENT EXECUTE PROCEDURE zero_counter();
CREATE TRIGGER
forummon=# CREATE TRIGGER limit_modifications BEFORE UPDATE OR DELETE ON test FOR EACH ROW EXECUTE PROCEDURE limit_modifications();
CREATE TRIGGER
forummon=# \d+ test
Table "public.test"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------+-----------+----------+--------------+-------------
id | integer | | plain | |
pass | text | | extended | |
Triggers:
limit_modifications BEFORE DELETE OR UPDATE ON test FOR EACH ROW EXECUTE PROCEDURE limit_modifications()
zero_counter BEFORE DELETE OR UPDATE ON test FOR EACH STATEMENT EXECUTE PROCEDURE zero_counter()
Has OIDs: no

forummon=# update test set pass = 'qq' where id<5003 ;
ERROR: UPDATE of more than 5000 row is forbidden.
forummon=# update test set pass = 'qq' where id<5000 ;
UPDATE 4999
forummon=# drop table test ;
DROP TABLE

总结

批量操作的数量限制可以在limit_modifications中修改，切记在update不确定时先select再update。

参考连接：www.depesz.com/2007/07/27/update-account-set-password-new_password-oops/