本期问答如下：
- PG存储过程里exception能不能捕获违返外键约束的错误
- 使用psql客户端登录数据库时如何禁止在.psql_history文件记录操作命令
- PG普通用户禁用分区表触发器失败问题
- PG的约束是否可以延迟生效
Q1. PG存储过程里exception能不能捕获违返外键约束的错误
问题描述
PG存储过程里exception能不能捕获违返外键约束的错误
问题解答
PG存储过程里exception不能捕获违返外键约束的报错，可以捕获主键约束的报错
主键约束是存过执行sql期间报错的，所以可以捕获到报错
存过中涉及外键约束时，调用存过执行期间没问题 ，外键约束在是在存过执行后通过内部触发器来实现的，所以产生的报错在存过执行期间捕获不到
示例如下：
test_db=# \d+ tab
Table "public.tab"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+-------------------+-----------+----------+---------+----------+--------------+-------------
id | integer | | not null | | plain | |
info | character varying | | | | extended | |
Indexes:
"tab_pkey" PRIMARY KEY, btree (id)
Referenced by:
TABLE "tab_fk" CONSTRAINT "fk1" FOREIGN KEY (id) REFERENCES tab(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
Access method: heap
test_db=# \d+ tab_fk
Table "public.tab_fk"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
id | integer | | not null | | plain | |
num | integer | | | | plain | |
Indexes:
"tab_fk_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
"fk1" FOREIGN KEY (id) REFERENCES tab(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
Access method: heap
主键约束可以捕获
test_db=# \sf prc_test_get_error
CREATE OR REPLACE PROCEDURE public.prc_test_get_error()
LANGUAGE plpgsql
AS $procedure$
DECLARE
BEGIN
insert into tab(id,info) values(100,'abcdef') ;
--insert into tab_fk values(200,100);
EXCEPTION
WHEN others then
raise notice E'Got exception:
SQLSTATE: %
SQLERRM: %', SQLSTATE, SQLERRM;
END;
$procedure$
test_db=# call prc_test_get_error();
NOTICE: Got exception:
SQLSTATE: 23505
SQLERRM: duplicate key value violates unique constraint "tab_pkey"
CALL
test_db=#
外键约束捕获不到
test_db=# \sf prc_test_get_error
CREATE OR REPLACE PROCEDURE public.prc_test_get_error()
LANGUAGE plpgsql
AS $procedure$
DECLARE
BEGIN
--insert into tab(id,info) values(100,'abcdef') ;
insert into tab_fk values(200,100);
EXCEPTION
WHEN others then
raise notice E'Got exception:
SQLSTATE: %
SQLERRM: %', SQLSTATE, SQLERRM;
END;
$procedure$
test_db=# call prc_test_get_error();
ERROR: insert or update on table "tab_fk" violates foreign key constraint "fk1"
DETAIL: Key (id)=(200) is not present in table "tab".
test_db=#
Q2. 使用psql客户端登录数据库时如何禁止在.psql_history文件记录操作命令
问题描述
使用psql客户端登录数据库时如何禁止在.psql_history文件记录操作命令
问题解答
HISTSIZE 存储在命令历史中的最大命令数（默认值是500）。如果被设置为一个负值，则不会应用限制。
所以在.psqlrc文件中设置HISTSIZE为0即可不记录操作命令
示例如下：
当设置HISTSIZE为0时，psql登录数据库后并进行操作后可以看到.psql_history文件大小为0，不记录操作命令
[postgres@mydb1a ~]$ cat .psqlrc
\set HISTSIZE 0
[postgres@mydb1a ~]$ psql
psql (12.3)
Type "help" for help.
postgres=# \echo :HISTSIZE
0
postgres=# select now();
now
-------------------------------
2023-04-09 02:50:33.906512+08
(1 row)
postgres=# \q
[postgres@mydb1a ~]$ ls -ltr .psql_history
-rw-------. 1 postgres postgres 0 4月 9 02:39 .psql_history
[postgres@mydb1a ~]$ cat .psql_history
[postgres@mydb1a ~]$
当设置HISTSIZE为-1时，可以记录操作记录，且记录行数不受限制
[postgres@mydb1a ~]$ cat .psqlrc
\set HISTSIZE -1
[postgres@mydb1a ~]$ psql
psql (12.3)
Type "help" for help.
postgres=# \echo :HISTSIZE
-1
postgres=# select now();
now
-------------------------------
2023-04-09 03:12:09.415315+08
(1 row)
postgres=# \q
[postgres@mydb1a ~]$ tail -5 .psql_history
select now();
\q
\echo :HISTSIZE
select now();
\q
[postgres@mydb1a ~]$ wc -l .psql_history
609 .psql_history
[postgres@mydb1a ~]$
注释以上设置时，HISTSIZE默认值为500，且记录500行操作记录
[postgres@mydb1a ~]$ cat .psqlrc
--\set HISTSIZE -1
[postgres@mydb1a ~]$ psql
psql (12.3)
Type "help" for help.
postgres=# \echo :HISTSIZE
500
postgres=# select timestamp();
ERROR: syntax error at or near ")"
LINE 1: select timestamp();
^
postgres=# select current_timestamp();
ERROR: syntax error at or near ")"
LINE 1: select current_timestamp();
^
postgres=# select current_timestamp;
current_timestamp
-----------------------------
2023-04-09 03:14:03.3642+08
(1 row)
postgres=# \q
[postgres@mydb1a ~]$ tail -5 .psql_history
\echo :HISTSIZE
select timestamp();
select current_timestamp();
select current_timestamp;
\q
[postgres@mydb1a ~]$ wc -l .psql_history
500 .psql_history
[postgres@mydb1a ~]$
Q3. PG普通用户禁用分区表触发器失败问题
问题描述
禁用分区表触发器时报错无权限，普通用户权限一致时，在pg12和pg14测试都存在该问题，pg15可以禁用成功
问题解答
在分区表增加触发器时，会为每个分区都创建相同的触发器。在pg15以前的版本中，分区上的触发器创建时被认为是系统触发器，通过pg_trigger表可以发现分区上触发器的tgisinternal属性为true，此时去禁用触发器又会判断执行操作的用户是否为superuser，如果不是superuser则报错无权限，导致禁用分区表触发器失败。pg15中分区上的触发器创建时则不是系统触发器，所以普通用户可以禁用成功。
示例如下：
pg14.7
postgres=> select * from pg_trigger ;
oid | tgrelid | tgparentid | tgname | tgfoid | tgtype | tgenabled | tgisinternal | tgconstrrelid | tgconstrindid | tgconstraint | tgdeferrable | tginitdef
erred | tgnargs | tgattr | tgargs | tgqual | tgoldtable | tgnewtable
-------+---------+------------+----------+--------+--------+-----------+--------------+---------------+---------------+--------------+--------------+----------
------+---------+--------+--------+--------+------------+------------
16399 | 16385 | 0 | test_ptt | 16398 | 17 | O | f | 0 | 0 | 0 | f | f
| 0 | | \x | | |
16400 | 16388 | 16399 | test_ptt | 16398 | 17 | O | t | 0 | 0 | 0 | f | f
| 0 | | \x | | |
16401 | 16391 | 16399 | test_ptt | 16398 | 17 | O | t | 0 | 0 | 0 | f | f
| 0 | | \x | | |
16402 | 16394 | 16399 | test_ptt | 16398 | 17 | O | t | 0 | 0 | 0 | f | f
| 0 | | \x | | |
(4 rows)
postgres=> alter table test_p_t disable trigger test_ptt ;
ERROR: permission denied: "test_ptt" is a system trigger
pg15.1
postgres=> select * from pg_trigger ;
oid | tgrelid | tgname | tgfoid | tgtype | tgenabled | tgisinternal | tgconstrrelid | tgconstrindid | tgconstraint | tgdeferrable | tginitdeferred | tgnar
gs | tgattr | tgargs | tgqual | tgoldtable | tgnewtable
-------+---------+----------+--------+--------+-----------+--------------+---------------+---------------+--------------+--------------+----------------+------
---+--------+--------+--------+------------+------------
16410 | 16397 | test_ptt | 16409 | 17 | O | f | 0 | 0 | 0 | f | f |
0 | | \x | | |
16411 | 16400 | test_ptt | 16409 | 17 | O | f | 0 | 0 | 0 | f | f |
0 | | \x | | |
16412 | 16403 | test_ptt | 16409 | 17 | O | f | 0 | 0 | 0 | f | f |
0 | | \x | | |
16413 | 16406 | test_ptt | 16409 | 17 | O | f | 0 | 0 | 0 | f | f |
0 | | \x | | |
(4 rows)
postgres=> alter table test_p_t disable trigger test_ptt ;
ALTER TABLE
如果使用的是pg15以前的版本，要禁用分区表触发器有两种方式
1）使用superuser禁用
2）普通用户drop trigger，需要使用时再次创建
Q4. PG的约束是否可以延迟生效
问题描述
PG的约束是否可以延迟生效
问题解答
PG的约束可以延迟生效，PostgreSQL的约束延迟生效功能，是指在执行数据更新操作时，可以先暂时允许违反约束条件，直到事务提交或回滚时，才对约束条件进行检查和生效。约束延迟生效功能可以帮助用户在数据更新时，避免由于约束条件限制而导致的数据更新失败。
主要包含以下三种：
1.非延迟(NOT DEFERRABLE)
2.延迟生效之 DEFERRABLE INITIALLY IMMEDIATE
3.延迟生效之 DEFERRABLE INITIALLY DEFERRED
这个功能仅影响 UNIQUE，PRIMARY KEY，REFERENCES （外键）和 EXCLUDE 约束，他们是可延迟的，而NOT NULL和CHECK是不可以做延迟生效的。这点和ORACLE不同，ORACLE中check也可以延迟。并且IMMEDIATE会在每一个语句执行后进行约束检查，DEFERRED则只会在事务结束时才检查约束。（DEFERRED 只是推迟检查而不是不检查）
当约束设置可延迟生效时可以使用set constraints命令来推迟检查直到事务结束
postgres=# \help SET CONSTRAINTS
Command: SET CONSTRAINTS
Description: set constraint check timing for the current transaction
Syntax:
SET CONSTRAINTS { ALL | name [, ...] } { DEFERRED | IMMEDIATE }
创建表时指定
create table t_nd(id int primary key not deferrable);
除了创建表的时候就加上约束的延迟选项外，也可以使用ALTER TABLE语句去增加约束延迟
ALTER TABLE table_name ALTER CONSTRAINT constraint_name DEFERRABLE INITIALLY DEFERRED;
参考：https://support.enmotech.com/article/5711/publish ----（作者：李孟洁）
最后修改时间：2023-04-10 16:46:08
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
