Shulou(Shulou.com)06/02 Report--

It is believed that many inexperienced people do not know what to do about how to use BEGIN blocks to deal with transactions in PostgreSQL stored procedures. Therefore, this paper summarizes the causes and solutions of the problems. Through this article, I hope you can solve this problem.

1. Simulate with BEGIN block

Build a random table:

CREATE TABLE a (col1 int)

The stored procedure is as follows:

CREATE OR REPLACE FUNCTION ins_t () RETURNS voidAS $$BEGIN INSERT INTO a VALUES; BEGIN INSERT INTO a VALUES; RAISE 'any error'; EXCEPTION WHEN others THEN null; END;END;$$ LANGUAGE plpgsql

Test:

Flying=# SELECT ins_t (); ins_t- (1 row) flying=# SELECT * FROM a; col1- 100 (1 row)

As you can see, the INSERT operation in the embedded BEGIN block does not take effect.

2. How to realize PL/pgSQL

The block is defined in pl_gram.y, you can see it for yourself if you are interested, and the execution code of the block is located in src/pl/plpgsql/src/pl_exec.c.

Static intexec_stmt_block (PLpgSQL_execstate * estate, PLpgSQL_stmt_block * block) {. If (block- > exceptions) {... BeginInternalSubTransaction (NULL);... PG_TRY ();... PG_CATCH ();... RollbackAndReleaseCurrentSubTransaction ();...

The action of creating a subtransaction occurs only when the block is defined with an EXCEPTION section, otherwise it will only be treated as a normal statement (within its corresponding else branch). When an exception is thrown, the subtransaction will be rolled out.

3. What will happen without EXCEPTION

Remove the exception handling part

CREATE OR REPLACE FUNCTION ins_t () RETURNS voidAS $$BEGIN INSERT INTO a VALUES; BEGIN INSERT INTO a VALUES; RAISE 'any error'; END;END;$$ LANGUAGE plpgsql

As a result, there is nothing, because it does not enable child transactions, so it will all be rolled back.

Flying=# select ins_t (); ERROR: any errorCONTEXT: PL/pgSQL function ins_t () line 6 at RAISEflying=# select * from a; col1- (0 rows) flying=#

4. Magical PG_TRY and PG_CATCH

They use it in many places in sigsetjmp,PG, and understanding this may require some knowledge of assembly language, as well as an understanding of the error-throwing mechanism, which can be discussed in more detail when you have the opportunity.

If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.