Transaction control in PL procedures
A couple of months back, I wrote about how we now have the ability to write Stored Procedures in PostgreSQL. This post follows up on that and talks about the next step that was implemented: transaction control in PL procedures. The feature was committed on 22-Jan-2018.
With this addition, you now have the ability to call COMMIT and ROLLBACK commands in PL/pgSQL from within a procedure. To illustrate:
CREATE TABLE test1 (a int);
CREATE PROCEDURE transaction_test1()
FOR i IN 0..9 LOOP
INSERT INTO test1 (a) VALUES (i);
IF i % 2 = 0 THEN
RAISE NOTICE 'i=%, txid=% will be committed', i, txid_current();
My colleague Alvaro Herrera has been working on a series of connected features for PostgreSQL 11. It's worth explaining what these are rather than trying to piece together what is happening from reading commit messages.
The overall idea is to allow Partitioned tables to have Referential Integrity, by way of Primary Keys and Foreign Keys, as well as some additional tweaks.
To achieve that, we need to understand the structure of features in PostgreSQL.
Foreign Keys (FKs) are implemented using row Triggers, so we must allow Triggers to be executed on Partitioned Tables. FKs also require Primary Keys (PKs), so we must add those also.
Primary Keys are implemented using Unique Indexes, so we need to add indexes and allow them to be unique. I'll write about partitioned indexes
Last week marked a rather big step in the PostgreSQL world that went largely unnoticed. Thanks to the work done by 2ndQuadrant contributors, we now have the ability to write Stored Procedures in PostgreSQL!
(Ok, well not exactly now but we will have the ability once PostgreSQL 11 comes out)
A procedure is essentially a set of commands to be executed in a particular order. As opposed to functions, procedures are not required to return a value. With this addition, you can now invoke a procedure by simply using the new CALL statement rather than using SELECT. The implementation is fully compliant with the SQL standard and will allow users to write procedures that are somewhat compatible with DB2, MySQL, and to a lesser extent, Oracle.
The commit from last week adds new commands CALL