聚合国内IT技术精华文章,分享IT技术精华,帮助IT从业人士成长

pg报错current transaction is aborted

2020-08-26 18:34 浏览: 2320 次 我要评论(0 条) 字号:

在一个事务中,pg报错了current transaction is aborted:

mumy_corehrdban_psdb=> begin;
BEGIN
mumy_corehrdban_psdb=> select * from orasup_test1 ;
 a 
---
 1
 2
 3
(3 rows)

mumy_corehrdban_psdb=> insert into orasup_test1 values(111);
INSERT 0 1
mumy_corehrdban_psdb=> select * from not_exist;
ERROR:  relation "not_exist" does not exist
LINE 1: select * from not_exist;
                      ^
mumy_corehrdban_psdb=> insert into orasup_test1 values(222);
ERROR:  current transaction is aborted, commands ignored until end of transaction block

mumy_corehrdban_psdb=> select * from orasup_test1;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
mumy_corehrdban_psdb=> 
mumy_corehrdban_psdb=> d
ERROR:  current transaction is aborted, commands ignored until end of transaction block
mumy_corehrdban_psdb=> 
mumy_corehrdban_psdb=> 
mumy_corehrdban_psdb=> rollback;
ROLLBACK
mumy_corehrdban_psdb=> select * from orasup_test1;
 a 
---
 1
 2
 3
(3 rows)

mumy_corehrdban_psdb=>

原因是在一个事务中,pg如果遇到的Error的报错,会忽略后续的命令,后续所有命令都会报错:current transaction is aborted, commands ignored until end of transaction block。 直到手工确认该事务结束(需要commit或者rollback)

这个问题,可以在psql中设置ON_ERROR_ROLLBACK true来绕过:

mumy_corehrdban_psdb=> 
mumy_corehrdban_psdb=> set ON_ERROR_ROLLBACK true
mumy_corehrdban_psdb=> begin;
BEGIN
mumy_corehrdban_psdb=> select * from orasup_test1;
 a 
---
 1
(1 row)

mumy_corehrdban_psdb=> insert into orasup_test1 values(2);
INSERT 0 1
mumy_corehrdban_psdb=> select * from not_exist;
ERROR:  relation "not_exist" does not exist
LINE 1: select * from not_exist;
                      ^
mumy_corehrdban_psdb=> insert into orasup_test1 values(3);
INSERT 0 1
mumy_corehrdban_psdb=> select * from orasup_test1;
 a 
---
 1
 2
 3
(3 rows)

mumy_corehrdban_psdb=>

我们来看一下ON_ERROR_ROLLBACK和另外一个类似的ON_ERROR_STOP。
这2个参数,可以set观看当前设置,默认值都是off:

mumy_corehrdban_psdb=> set
AUTOCOMMIT = 'on'
COMP_KEYWORD_CASE = 'preserve-upper'
DBNAME = 'mumy_corehrdban_psdb'
ECHO = 'none'
ECHO_HIDDEN = 'off'
ENCODING = 'UTF8'
FETCH_COUNT = '0'
HISTCONTROL = 'none'
HISTSIZE = '500'
HOST = '/tmp'
IGNOREEOF = '0'
LASTOID = '0'
ON_ERROR_ROLLBACK = 'off'
ON_ERROR_STOP = 'off'
PORT = '5432'
PROMPT1 = '%/%R%# '
PROMPT2 = '%/%R%# '
PROMPT3 = '>> '
QUIET = 'off'
SERVER_VERSION_NAME = '10.5'
SERVER_VERSION_NUM = '100005'
SHOW_CONTEXT = 'errors'
SINGLELINE = 'off'
SINGLESTEP = 'off'
USER = 'app_rw'
VERBOSITY = 'default'
VERSION = 'PostgreSQL 10.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5
20150623 (Red Hat 4.8.5-16), 64-bit'
VERSION_NAME = '10.5'
VERSION_NUM = '100005'
mumy_corehrdban_psdb=>

这2个参数,一般用在数据导入到时候,

-bash-4.2$ cat test.sql
select * from orasup_test1;
insert into orasup_test1 values(111);
insert into orasup_test1 values(222);
select * from not_exist;
insert into orasup_test1 values(333);
insert into orasup_test1 values(444);
select * from orasup_test1;

mumy_corehrdban_psdb=> set ON_ERROR_STOP true
mumy_corehrdban_psdb=> set
AUTOCOMMIT = 'on'
COMP_KEYWORD_CASE = 'preserve-upper'
DBNAME = 'mumy_corehrdban_psdb'
ECHO = 'none'
ECHO_HIDDEN = 'off'
ENCODING = 'UTF8'
FETCH_COUNT = '0'
HISTCONTROL = 'none'
HISTSIZE = '500'
HOST = '/tmp'
IGNOREEOF = '0'
LASTOID = '0'
ON_ERROR_ROLLBACK = 'off'
ON_ERROR_STOP = 'true'
PORT = '5432'
PROMPT1 = '%/%R%# '
PROMPT2 = '%/%R%# '
PROMPT3 = '>> '
QUIET = 'off'
SERVER_VERSION_NAME = '10.5'
SERVER_VERSION_NUM = '100005'
SHOW_CONTEXT = 'errors'
SINGLELINE = 'off'
SINGLESTEP = 'off'
USER = 'app_rw'
VERBOSITY = 'default'
VERSION = 'PostgreSQL 10.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5
20150623 (Red Hat 4.8.5-16), 64-bit'
VERSION_NAME = '10.5'
VERSION_NUM = '100005'
mumy_corehrdban_psdb=> 
mumy_corehrdban_psdb=> 
mumy_corehrdban_psdb=> i test.sql
 a 
---
 1
 2
 3
(3 rows)

INSERT 0 1
INSERT 0 1
psql:test.sql:4: ERROR:  relation "not_exist" does not exist
LINE 1: select * from not_exist;
                      ^
mumy_corehrdban_psdb=> 
mumy_corehrdban_psdb=> select * from orasup_test1 ;
  a  
-----
   1
   2
   3
 111
 222
(5 rows)

mumy_corehrdban_psdb=>

可以看到,设置ON_ERROR_STOP true的时候,导入数据时,一旦报错,就停止了,不再导入后面的数据。

mumy_corehrdban_psdb=> set ON_ERROR_ROLLBACK true
mumy_corehrdban_psdb=> set
AUTOCOMMIT = 'on'
COMP_KEYWORD_CASE = 'preserve-upper'
DBNAME = 'mumy_corehrdban_psdb'
ECHO = 'none'
ECHO_HIDDEN = 'off'
ENCODING = 'UTF8'
FETCH_COUNT = '0'
HISTCONTROL = 'none'
HISTSIZE = '500'
HOST = '/tmp'
IGNOREEOF = '0'
LASTOID = '0'
ON_ERROR_ROLLBACK = 'true'
ON_ERROR_STOP = 'off'
PORT = '5432'
PROMPT1 = '%/%R%# '
PROMPT2 = '%/%R%# '
PROMPT3 = '>> '
QUIET = 'off'
SERVER_VERSION_NAME = '10.5'
SERVER_VERSION_NUM = '100005'
SHOW_CONTEXT = 'errors'
SINGLELINE = 'off'
SINGLESTEP = 'off'
USER = 'app_rw'
VERBOSITY = 'default'
VERSION = 'PostgreSQL 10.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5
20150623 (Red Hat 4.8.5-16), 64-bit'
VERSION_NAME = '10.5'
VERSION_NUM = '100005'
mumy_corehrdban_psdb=> 
mumy_corehrdban_psdb=> select * from orasup_test1 ;
 a 
---
 1
 2
 3
(3 rows)

mumy_corehrdban_psdb=> 
mumy_corehrdban_psdb=> 
mumy_corehrdban_psdb=> i test.sql 
 a 
---
 1
 2
 3
(3 rows)

INSERT 0 1
INSERT 0 1
psql:test.sql:4: ERROR:  relation "not_exist" does not exist
LINE 1: select * from not_exist;
                      ^
INSERT 0 1
INSERT 0 1
  a  
-----
   1
   2
   3
 111
 222
 333
 444
(7 rows)

mumy_corehrdban_psdb=> 
mumy_corehrdban_psdb=> 
mumy_corehrdban_psdb=>  
mumy_corehrdban_psdb=> select * from orasup_test1 ;
-----
   1
   2
   3
 111
 222
 333
 444
(7 rows)

mumy_corehrdban_psdb=>

可以看到,设置ON_ERROR_ROLLBACK true的时候,导入数据时,报错的命令是回滚了,但是后续的命令可以继续。



网友评论已有0条评论, 我也要评论

发表评论

*

* (保密)

Ctrl+Enter 快捷回复