添加链接 注册    登录
link管理
链接快照平台
  • 输入网页链接，自动生成快照
  • 标签化管理网页链接
相关文章推荐
逆袭的饭卡  ·  @ttk/vue-ui - npm·  1 周前    · 
神勇威武的炒饭  ·  表单输入绑定 | Vue.js·  5 天前    · 
愉快的铁链  ·  在select选项上绑定事件 - ·  5 天前    · 
英姿勃勃的地瓜  ·  如何使用grouping ...·  3 天前    · 
坚韧的竹笋  ·  Monitoring Disk Space ...·  14 小时前    · 
无邪的斑马  ·  Results - OpenURL ...·  3 月前    · 
一直单身的手链  ·  证券从业《证券交易》第三章真题-高顿教育·  7 月前    · 
发财的煎饼果子  ·  manga · GitHub Topics ...·  10 月前    · 
文质彬彬的豆芽  ·  绘制/草绘 - Flexijet ...·  10 月前    · 
一直单身的匕首  ·  Java ...·  11 月前    · 
link管理  ›  pg报错current transaction is aborted – OracleBlog
gcc num select
https://oracleblog.org/working-case/pg-error-current-transaction-is-aborted/
眉毛粗的硬盘
4 月前
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; (3 rows) mumy_corehrdban_psdb=>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
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; (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; (3 rows) mumy_corehrdban_psdb=>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
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 = >
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=>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
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 = >
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 (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 ; ----- (5 rows) mumy_corehrdban_psdb=>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
- 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 = >
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 ; (3 rows) mumy_corehrdban_psdb=> mumy_corehrdban_psdb=> mumy_corehrdban_psdb=> \i test.sql (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 ----- (7 rows) mumy_corehrdban_psdb=> mumy_corehrdban_psdb=> mumy_corehrdban_psdb=> mumy_corehrdban_psdb=> select * from orasup_test1 ; ----- (7 rows) mumy_corehrdban_psdb=>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
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 = >
 
推荐文章
逆袭的饭卡  ·  @ttk/vue-ui - npm
1 周前
神勇威武的炒饭  ·  表单输入绑定 | Vue.js
5 天前
愉快的铁链  ·  在select选项上绑定事件 -
5 天前
英姿勃勃的地瓜  ·  如何使用grouping sets进行多维聚合_云原生大数据计算服务 MaxCompute(MaxCompute)-阿里云帮助中心
3 天前
坚韧的竹笋  ·  Monitoring Disk Space by Size, rather than by percentage - Nagios Support Forum
14 小时前
无邪的斑马  ·  Results - OpenURL Connection - EBSCO
3 月前
一直单身的手链  ·  证券从业《证券交易》第三章真题-高顿教育
7 月前
发财的煎饼果子  ·  manga · GitHub Topics · GitHub
10 月前
文质彬彬的豆芽  ·  绘制/草绘 - Flexijet Stone - 1
10 月前
一直单身的匕首  ·  Java SQL注入危害这么大，该如何来防止呢?-腾讯云开发者社区-腾讯云
11 月前
Link管理   ·   51好读   ·   Sov5搜索   ·   小百科
link管理 - 链接快照平台