[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: ERROR : invalid transaction termination : PostgreSQL v12
From:       Michael Lewis <mlewis () entrata ! com>
Date:       2020-11-24 4:05:06
Message-ID: CAHOFxGqUVoQeornPQheUqTZf0-ypoAWO8BA2+La-06rtgB8WCw () mail ! gmail ! com
[Download RAW message or body]

On Mon, Nov 23, 2020 at 6:52 PM Jagmohan Kaintura <jagmohan@tecorelabs.com>
wrote:

> It doesn't works putting that block inside additional BEGIN END
>
> CREATE OR REPLACE PROCEDURE TEST_TRANSACTION(
>         )
> LANGUAGE 'plpgsql'
>     SECURITY DEFINER
> AS $BODY$
> DECLARE
>     G_LAST_UPDATE_USER_SYSTEM VARCHAR2(6) := 'SYSTEM';
>     G_LAST_UPDATE_MODULE_BATCH VARCHAR2(5) := 'BATCH';
>
>     G_CNTR_LOADING_EXPIRED TMS_CONTAINER_LOADING.STATUS_CODE%TYPE := '90';
>     G_DG_MANIFEST_DELETED TMS_DG_MANIFEST.STATUS_CODE%TYPE := '80';
>     G_DG_MANIFEST_COMPLETED TMS_DG_MANIFEST.STATUS_CODE%TYPE := '95';
>
>     v_num_day numeric;
>     v_batch_count numeric;
>     v_log_count numeric := 0;
>     v_local_batch_count numeric;
> BEGIN
>        v_batch_count := 0;
>         LOOP
>                    BEGIN
>

This isn't what I suggested. How are you connecting to the database to call
your TEST_TRANSACTION function? Some clients will issue BEGIN silently to
allow rollback. If you have a BEGIN that is called before the function
starts, then the function cannot call commit. The function must be called
while NOT in a transaction already.

>

[Attachment #3 (text/html)]

<div dir="ltr"><div dir="ltr">On Mon, Nov 23, 2020 at 6:52 PM Jagmohan Kaintura \
&lt;<a href="mailto:jagmohan@tecorelabs.com">jagmohan@tecorelabs.com</a>&gt; \
wrote:<br></div><div class="gmail_quote"><blockquote class="gmail_quote" \
style="margin:0px 0px 0px 0.8ex;border-left:1px solid \
rgb(204,204,204);padding-left:1ex"><div dir="ltr"><div \
style="font-family:verdana,sans-serif;font-size:large">It doesn&#39;t works putting \
that block inside  additional BEGIN END  </div><div \
style="font-family:verdana,sans-serif;font-size:large"><br></div><div \
style="font-family:verdana,sans-serif;font-size:large">CREATE OR REPLACE PROCEDURE \
TEST_TRANSACTION(<br>            )<br>LANGUAGE &#39;plpgsql&#39;<br>      SECURITY \
DEFINER<br>AS $BODY$<br>DECLARE<br>      G_LAST_UPDATE_USER_SYSTEM VARCHAR2(6) := \
&#39;SYSTEM&#39;;<br>      G_LAST_UPDATE_MODULE_BATCH VARCHAR2(5) := \
&#39;BATCH&#39;;<br><br>      G_CNTR_LOADING_EXPIRED \
TMS_CONTAINER_LOADING.STATUS_CODE%TYPE := &#39;90&#39;;<br>      \
G_DG_MANIFEST_DELETED TMS_DG_MANIFEST.STATUS_CODE%TYPE := &#39;80&#39;;<br>      \
G_DG_MANIFEST_COMPLETED TMS_DG_MANIFEST.STATUS_CODE%TYPE := &#39;95&#39;;<br><br>     \
v_num_day numeric;<br>      v_batch_count numeric;<br>      v_log_count numeric := \
0;<br>      v_local_batch_count numeric;<br>BEGIN<br>           v_batch_count := \
0;<br>            LOOP<br>                             \
BEGIN<br></div></div></blockquote><div><br></div><div>This isn&#39;t what I \
suggested. How are you connecting to the database to call your TEST_TRANSACTION \
function? Some clients will issue BEGIN silently to allow rollback. If you have a \
BEGIN that is called before the function starts, then the function cannot call \
commit. The function must be called while NOT in a transaction \
already.</div><blockquote class="gmail_quote" style="margin:0px 0px 0px \
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"> \
</blockquote></div></div>



[prev in list] [next in list] [prev in thread] [next in thread]