PostgreSQL - Start A Transaction block IN Function

Question

I'm trying to use create a transaction block inside a function, so my goal is to use this function one at time, so if some one use this Function and another want to use it, he can't until the first one is finish i create this Function :

CREATE OR REPLACE FUNCTION my_job(time_to_wait integer) RETURNS INTEGER AS $$
DECLARE
  max INT;
BEGIN
   BEGIN;
       SELECT MAX(max_value) INTO max FROM sch_lock.table_concurente;
       INSERT INTO sch_lock.table_concurente(max_value, date_insertion) VALUES(max + 1, now());
       -- Sleep a wail
       PERFORM pg_sleep(time_to_wait);
       RETURN max;
   COMMIT; 
END;
$$ 
LANGUAGE plpgsql;

But it seams not work, i have a mistake Syntax error BEGIN;

Without BEGIN; and COMMIT i get a correct result, i use this query to check :

-- First user should to wait 10 second
SELECT my_job(10) as max_value;

-- First user should to wait 3 second
SELECT my_job(3) as max_value;

So the result is :

+-----+----------------------------+------------+
|  id |              date          | max_value  |
+-----+----------------------------+------------+
|  1  | 2017-02-13 13:03:58.12+00  |      1     |
+-----|----------------------------+------------+
|  2  | 2017-02-13 13:10:00.291+00 |      2     |
+-----+----------------------------+------------+
|  3  | 2017-02-13 13:10:00.291+00 |      2     |
+-----+----------------------------+------------+

But the result should be :

+-----+----------------------------+------------+
|  id |              date          | max_value  |
+-----+----------------------------+------------+
|  1  | 2017-02-13 13:03:58.12+00  |      1     |
+-----|----------------------------+------------+
|  2  | 2017-02-13 13:10:00.291+00 |      2     |
+-----+----------------------------+------------+
|  3  | 2017-02-13 13:10:00.291+00 |      3     |
+-----+----------------------------+------------+

so the third one id = 3 should have the max_value = 3 and not 2, this happen because the first user Select the max = 1 and wait 10 sec and the second user Select the max = 1 and wait 3 sec before Insertion, but the right solution is : I can't use this Function Until the First one finish, for that i want to make something secure and protected.

My questions is :

• how can i make a Transaction block inside a function?
• Do you have any suggestion how can we make this, with a secure way?

Thank you.

Answer 1

Ok so you cannot COMMIT in a function. You can have a save point and roll back to the save point however.

Your smallest possible transaction is a single statement parsed and executed by the server from the client, so every transaction is a function. Within a transaction, however, you can have save points. In this case you would look at the exception handling portions of PostgreSQL to handle this.

However that is not what you want here. You want (I think?) data to be visible during a long-running server-side operation. For that you are kind of out of luck. You cannot really increment your transaction ids while running a function.

You have a few options, in order of what I would consider to be good practices (best to worst):

1. Break down your logic into smaller slices that each move the db from one consistent state to another, and run those in separate transactions.
2. Use a message queue (like pg_message_queue)in the db, plus an external worker, and something which runs a step and yields a message for the next step. Disadvantage is this adds more maintenance.
3. Use a function or framework like dblink or pl/python, or pl/perlu to connect back to the db and run transactions there. ick....

Answer 2

You can use dblink for this. Something like :

CREATE OR REPLACE FUNCTION my_job(time_to_wait integer) RETURNS INTEGER AS $$
DECLARE
  max INT;
BEGIN
   SELECT INTO RES dblink_connect('con','dbname=local');
   SELECT INTO RES dblink_exec('con', 'BEGIN');
   ...
   SELECT INTO RES dblink_exec('con', 'COMMIT');
   SELECT INTO RES dblink_disconnect('con');
END;
$$ 
LANGUAGE plpgsql;

Answer 3

I don't know if this is a good way or not but what if we use LOCK TABLE for example like this :

CREATE OR REPLACE FUNCTION my_job(time_to_wait integer) RETURNS INTEGER AS $$
DECLARE
  max INT;
     BEGIN
       -- Lock table so no one will use it until the first one is finish
       LOCK TABLE sch_lock.table_concurente IN ACCESS EXCLUSIVE MODE;

       SELECT MAX(max_value) INTO max FROM sch_lock.table_concurente;
       INSERT INTO sch_lock.table_concurente(max_value, date_insertion) VALUES(max + 1, now());
       PERFORM pg_sleep(time_to_wait);
       RETURN max;
     END;
     $$ 
LANGUAGE plpgsql;

It gives me the right result.