PostgresBuild2021 – questions feedback (1)

PostgresBuild2021 is currently ongoing and as usual, I had questions after my talk I could not immediately answer. One of the best ways to learn, is to present a topic. There are for sure questions you never thought about. The topic of my talk was “An introduction to server side programming” and I wanted to give people easy examples about how to write procedures and functions in PostgreSQL. It was not about control structures like IF/THEN/ELSE/CASE/LOOP but more about the general concepts like parameters, returning values, using exceptions, or giving more information about your functions and procedures to the optimizer.

Question 1: Can I use setof with in parameters?

The example in the talk is this one:

This is a simple function returning a setof t2, that is, all the rows in the table t2 at once. Can we use setof as a in parameters as well? Let’s try:

postgres=# create table t ( a int, b text );
CREATE TABLE
postgres=# create function f ( a setof t ) returns void as $$ select 1; $$ language sql;
ERROR:  functions cannot accept set arguments

The answer is: No.

Question 2: When I commit inside a loop, does that generate a new transaction ID with each commit?

The example in the talk is this one:

It doesn’t really matter if the commit is executed in a loop or like in the example above: Do we get a new transaction ID for each commit or not? Let’s try:

postgres=# create table t ( a xid );
CREATE TABLE
postgres=# create procedure p() as 
           $$ 
           begin 
             insert into t values (pg_current_xact_id()::xid); 
             commit; 
             begin 
               insert into t values (pg_current_xact_id()::xid); 
               commit; 
             end; 
           end; 
           $$ language plpgsql;
CREATE PROCEDURE
postgres=# call p();
CALL
postgres=# select * from t;
  a  
-----
 719
 720
(2 rows)

The answer is: Yes. The same, of course, applies for loops:

postgres=# drop procedure p();
DROP PROCEDURE
postgres=# truncate t;
TRUNCATE TABLE
postgres=# create or replace procedure p() as 
           $$ 
           begin 
             for i in 1..10 
             loop 
               insert into t values (pg_current_xact_id()::xid); 
             end loop; 
           end; 
           $$ language plpgsql;
CREATE PROCEDURE
postgres=# call p();
CALL
postgres=# select * from t;
  a  
-----
 724
 724
 724
 724
 724
 724
 724
 724
 724
 724
(10 rows)

postgres=# call p();
CALL
postgres=# select * from t;
  a  
-----
 724
 724
 724
 724
 724
 724
 724
 724
 724
 724
 726
 727
 728
 729
 730
 731
 732
 733
 734
 735
(20 rows)
postgres=# create or replace procedure p() as 
           $$ 
           begin 
             for i in 1..10 
             loop 
               insert into t values (pg_current_xact_id()::xid); 
               commit;
             end loop; 
           end; 
           $$ language plpgsql;
CREATE PROCEDURE

The answer, again is: Yes.

More to come as soon as I have the whole list of all the questions.