在一个事务中，pg报错了current transaction is aborted：
|
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来绕过：
|
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=> |
我们来看一下ON_ERROR_ROLLBACK和另外一个类似的ON_ERROR_STOP。
这2个参数，可以\set观看当前设置，默认值都是off：
|
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=> |
这2个参数，一般用在数据导入到时候，
|
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=> |
可以看到，设置ON_ERROR_STOP true的时候，导入数据时，一旦报错，就停止了，不再导入后面的数据。
|
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=> |
可以看到，设置ON_ERROR_ROLLBACK true的时候，导入数据时，报错的命令是回滚了，但是后续的命令可以继续。