PG psql --single-transaction 参数功能

PG psql --single-transaction 参数功能

• test.sql 文件

create table test1(id int);
CREATE OR REPLACE FUNCTION func_test() 
RETURNS INTEGER AS
$BODY$
BEGINxxxreturn 0;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;CREATE OR REPLACE FUNCTION func_test2() 
RETURNS INTEGER AS
$BODY$
BEGINperform func_test();return 0;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
select * from func_test2();
create table test2(id int);

• 演示

[root@node101 test]# /home/postgres/pgsql/bin/psql -Upostgres  -f test.sql --single-transaction
CREATE TABLE
psql:test.sql:11: ERROR:  syntax error at or near "xxx"
LINE 6: xxx^
psql:test.sql:22: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:test.sql:23: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:test.sql:24: ERROR:  current transaction is aborted, commands ignored until end of transaction block
[root@node101 liwenbin]# psql -Upostgres
psql (11.8)
Type "help" for help.postgres=# \dfList of functionsSchema | Name | Result data type | Argument data types | Type 
--------+------+------------------+---------------------+------
(0 rows)postgres=# \dt
Did not find any relations.
postgres=# \q

可以看到, 只要中途报错, 就会回滚到最初始状态