How to Auto commit Postgres Function?

Question

I have function that updates some fields, I developed in PostgresSql but I have problem that when I'm executing function the programs(Navicat or Pgadmin) asking me "Excuse! function returns <>! rollback or commit" screenshot of message attached message ! It's work when I pressing either commit or rollback! but I want to commit automatically and never ask me any question!! I tried to write BEGIN , End but still message!

Here is my code:

CREATE OR REPLACE FUNCTION "whis2011"."ComputeRtbAdPlaceCtr"() RETURNS "void" 
    AS $BODY$

BEGIN


    UPDATE "whis2011"."RtbActiveSitePlaces"
    SET "Ctr" = vl."ctr",
        "AverageCpm" = vl."avcpm",
        "AverageClickCost" = vl."avclickcost",
        "IsCtrPredicted" = vl."isctrpredicted",
        "ComputedBalance" = vl."balance"
    FROM "whis2011"."RtbActiveSitePlaces" pl
    JOIN "whis2011"."View_RtbActiveSitePlacesCtrWeekly" vl on pl."Id" = vl."Id";


END;
$BODY$
LANGUAGE plpgsql
COST 100
CALLED ON NULL INPUT
SECURITY INVOKER
VOLATILE;

FROM "whis2011"."RtbActiveSitePlaces" pl You should not include the target table in the FROM ... list in Postgres's SQL. (and you dont need, plpgsql for this simple update, plain SQL will do) — wildplasser
– wildplasser, Commented Sep 10, 2016 at 14:40
The question is not clear to me. Maybe you are confusing BEGIN and END of the function with BEGIN and COMMIT for transaction handling in SQL? A Postgres function itself cannot be auto-committed - that is up to the calling SQL context. — Erwin Brandstetter
– Erwin Brandstetter, Commented Sep 11, 2016 at 11:27

Pavel Stehule · Accepted Answer · 2016-09-16 05:43:05Z

Is not possible to control transactions in PLpgSQL explicitly. Any function is running under outer transaction, and is not possible to control this outer transaction inside the function. You can raise exception, that means so outer transaction should be reverted (ROLLBACK) but from outer environment. The PLpgSQL keywords BEGIN, END has zero relevance to transactions.

PLpgSQL allows to start nested transaction, but it this transaction is nested. It is reverted implicitly before the exception handler is running, or commited, when there are not any exception.

BEGIN
  -- implicit BEGIN OF subtransaction
  x := 10; -- protected operations
  -- on the end implicit COMMIT of subtransaction
EXCEPTION WHEN name_of_handled_exception THEN
  -- implicit ROLLBACK
   RAISE NOTICE ...
END;

The PostgreSQL doesn't support anything similar to "autocommit off" mode on server side. Any statement is running under implicit transaction, or explicit transaction, when user uses BEGIN, COMMIT.

Collectives™ on Stack Overflow

How to Auto commit Postgres Function?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related