2

I have an PostgreSQL function which takes around 30 minutes to complete. When running that function I want to insert a row to a separate table called running_status at the start of the function and update that record after completing the function.

CREATE OR REPLACE FUNCTION fun1(arg1 numeric, arg2 numeric)
  RETURNS numeric
LANGUAGE plpgsql
AS $$
DECLARE
    -- <declare variabls>
BEGIN
    BEGIN
        -- <insert status record>
    END;

    -- <function body>

    BEGIN
        -- <update status record>
    END;
END;
$$;

But the problem is, since the function is defined within transaction block (BEGIN, END block) it will be executed as a transaction. So this status record is not inserted to the database until this whole block completed.

So my question is how to insert this record to the database before start the function body?

Start Function -> insert record to db -> commit -> run function body -> commit -> End function
Chathura Buddhika
  • 2,067
  • 1
  • 21
  • 35
  • look on SO - answered. can do with dblink hack for instance – Vao Tsun Jan 10 '18 at 10:39
  • what do you mean by dblink hack? – Chathura Buddhika Jan 10 '18 at 10:43
  • Possible duplicate of [Committing transactions while executing a postgreql Function](https://stackoverflow.com/questions/22351039/committing-transactions-while-executing-a-postgreql-function) – Vao Tsun Jan 10 '18 at 10:45
  • 3
    You can use dblink to open another connection to the same database and perform the `INSERT` there. Then it can be committed separately. But maybe you can find a way to split the function in two parts and commit inbetween, that might be cleaner and less expensive. – Laurenz Albe Jan 10 '18 at 10:46

1 Answers1

1

As Laurenz pointed out in the comments,

You can use dblink to open another connection to the same database and perform the INSERT there. Then it can be committed separately.

But maybe you can find a way to split the function in two parts and commit inbetween, that might be cleaner and less expensive

Imalka Sewwnadi
  • 108
  • 5