date:20201123

ERROR : invalid transaction termination : PostgreSQL v12

2020-11-23 Thread Jagmohan Kaintura

Hi Team,

We have many BATCH JOBS in Oracle which we are committing after processing
few Records. These batch Jobs process in some subsets and call transaction
control statements COMMIT in case of Success and ROLLBACK in case of
failure.

While converting to POstgreSQL we converted in Same Format with COMMIT and
ROLLBACK. But while executing it ended up with below error message.
ERROR:  invalid transaction termination
CONTEXT:  PL/pgSQL function inline_code_block line 29 at COMMIT

While reviewing the Transaction Management in PostgreSQL  "
https://www.postgresql.org/docs/12/plpgsql-transactions.html"; it
speaks about a format which is not Supported.

Transaction control is only possible in CALL or DO invocations from the top
level or nested CALL or DO invocations without any other intervening
command. For example, if the call stack is CALL proc1() → CALL proc2() → CALL
proc3(), then the second and third procedures can perform transaction
control actions. But if the call stack is CALL proc1() → SELECT func2() → CALL
proc3(), then the last procedure cannot do transaction control, because of
the SELECT in between.

My Call has :  CALL Batch Job => SELECT function Used in SQL Statements
==> Call Procedure.  We have transaction control in "CALL Batch Job" only.

Pseudo Code is like : Highlighted in BOLD is a function call. It's failing
when getting executed as we are using functions into this procedure.
*Can any help on this matter , how I can implement Batch Jobs as we wanted
to commit in few intervals of 2 records ?*
We can't remove this function from the statement as its value is dependent
on column value.

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
update tms_container_loading
   set status_code = G_CNTR_LOADING_EXPIRED
 , last_update_tm = clock_timestamp()::timestamp(0)
 , last_update_user_an = G_LAST_UPDATE_USER_SYSTEM
 , last_update_module_code = G_LAST_UPDATE_MODULE_BATCH
 where
*tms$vvd.is_vessel_departed(vessel_visit_c,trunc(clock_timestamp()::timestamp(0)))*
= 1
   and coalesce(status_code,'~') <> G_CNTR_LOADING_EXPIRED  and
ctid in (select a.ctid from tms_container_loading  where
*tms$vvd.is_vessel_departed(vessel_visit_c,trunc(clock_timestamp()::timestamp(0)))*
= 1
   and coalesce(status_code,'~') <> G_CNTR_LOADING_EXPIRED
LIMIT 2);
EXIT WHEN NOT FOUND; /* apply on SQL */
GET DIAGNOSTICS v_local_batch_count = ROW_COUNT; v_batch_count
:= v_batch_count + v_local_batch_count;
COMMIT;
END LOOP;
v_log_count := v_log_count + 1; CALL
Log(v_batch_count,'TMS_CONTAINER_LOADING',NULL, 'TMS$BATCH_JOB',
v_log_count);
COMMIT;
END;
$BODY$;







-- 
*Best Regards,*
Jagmohan
Senior Consultant, TecoreLabs.

Multiple result set not displayed in PgAdmin4

2020-11-23 Thread Muthukumar.GK

Hi Team,

I have created function in postgre sql 13 which  will return two result.
But when execute i can able to see the last cursor (query result set of
last select )result set  in Data output window of Pgadmin4. i tried
executing the  cursor inside the transaction as well (BEING.COMMIT).
but i still do not see both result set one by one (likeSQL) in data output
window. Please let me know if there is any limitation in pgadmin4 tool that
multiple result set will not be displayed in Data output Window.

Regards
Muthukumar

Re: Multiple result set not displayed in PgAdmin4

2020-11-23 Thread Adrian Klaver


On 11/22/20 8:53 PM, Muthukumar.GK wrote:

Hi Team,

I have created function in postgre sql 13 which  will return two result. 
But when execute i can able to see the last cursor (query result set of 
last select )result set  in Data output window of Pgadmin4. i tried 
executing the  cursor inside the transaction as well (BEING.COMMIT). 
but i still do not see both result set one by one (likeSQL) in data 
output window. Please let me know if there is any limitation in pgadmin4 
tool that multiple result set will not be displayed in Data output Window.


From what I remember pgAdmin4 will only display the last result.



Regards
Muthukumar



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: ERROR : invalid transaction termination : PostgreSQL v12

2020-11-23 Thread Adrian Klaver


On 11/23/20 12:36 AM, Jagmohan Kaintura wrote:

Hi Team,

We have many BATCH JOBS in Oracle which we are committing after 
processing few Records. These batch Jobs process in some subsets and 
call transaction control statements COMMIT in case of Success and 
ROLLBACK in case of failure.


While converting to POstgreSQL we converted in Same Format with COMMIT 
and ROLLBACK. But while executing it ended up with below error message.

ERROR:  invalid transaction termination
CONTEXT:  PL/pgSQL function inline_code_block line 29 at COMMIT

While reviewing the Transaction Management in PostgreSQL  
"https://www.postgresql.org/docs/12/plpgsql-transactions.html 
" it 
speaks about a format which is not Supported.


Transaction control is only possible in |CALL| or |DO| invocations from 
the top level or nested |CALL| or |DO| invocations without any other 
intervening command. For example, if the call stack is |CALL proc1()| → 
|CALL proc2()| → |CALL proc3()|, then the second and third procedures 
can perform transaction control actions. But if the call stack is |CALL 
proc1()| → |SELECT func2()| → |CALL proc3()|, then the last procedure 
cannot do transaction control, because of the |SELECT| in between.


My Call has :  CALL Batch Job => SELECT function Used in SQL Statements  
==> Call Procedure.  We have transaction control in "CALL Batch Job" only.


Pseudo Code is like : Highlighted in BOLD is a function call. It's 
failing when getting executed as we are using functions into this procedure.
*Can any help on this matter , how I can implement Batch Jobs as we 
wanted to commit in few intervals of 2 records ?*
We can't remove this function from the statement as its value is 
dependent on column value.


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
             update tms_container_loading
                set status_code = G_CNTR_LOADING_EXPIRED
                  , last_update_tm = clock_timestamp()::timestamp(0)
                  , last_update_user_an = G_LAST_UPDATE_USER_SYSTEM
                  , last_update_module_code = G_LAST_UPDATE_MODULE_BATCH
              where 
*tms$vvd.is_vessel_departed(vessel_visit_c,trunc(clock_timestamp()::timestamp(0)))* 
= 1
                and coalesce(status_code,'~') <> G_CNTR_LOADING_EXPIRED 
  and ctid in (select a.ctid from tms_container_loading  where 
*tms$vvd.is_vessel_departed(vessel_visit_c,trunc(clock_timestamp()::timestamp(0)))* 
= 1
                and coalesce(status_code,'~') <> G_CNTR_LOADING_EXPIRED 
LIMIT 2);

             EXIT WHEN NOT FOUND; /* apply on SQL */
             GET DIAGNOSTICS v_local_batch_count = ROW_COUNT; 
v_batch_count := v_batch_count + v_local_batch_count;

             COMMIT;
         END LOOP;
         v_log_count := v_log_count + 1; CALL 
Log(v_batch_count,'TMS_CONTAINER_LOADING',NULL, 'TMS$BATCH_JOB', 
v_log_count);

         COMMIT;
END;
$BODY$;


I'm still trying to figure out transaction management in procedures, so 
bear with me. Not sure what the purpose of the second COMMIT is? Also 
wonder if it is no the cause of the issue?




--
*Best Regards,*
Jagmohan
Senior Consultant, TecoreLabs.



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Multiple result set not displayed in PgAdmin4

2020-11-23 Thread Adrian Klaver

On 11/23/20 8:07 AM, Muthukumar.GK wrote:

Hi Adrian,

If PG admin 4 return only last result set then,  how can we call the  
procedure/function to get the multiple result set from dot net application.

pgAdmin4 != Postgres, it is just a GUI client and in this case has a 
limitation.

Run the procedure in the command line client psql and you will see the 
results. I'm sure you will be able to get what you want in your .Net 
application.

we are planning to migrate things from SQL to postgresql.. most of our 
existing SQL stored procedures will return multiple result sets. So we 
need to achieve Same multiple result set in postgreSql.. Kindly advise me..

Regards
Muthukumar.GK

On Mon, Nov 23, 2020, 8:49 PM Adrian Klaver > wrote:

On 11/22/20 8:53 PM, Muthukumar.GK wrote:
 > Hi Team,
 >
 > I have created function in postgre sql 13 which  will return two
result.
 > But when execute i can able to see the last cursor (query result
set of
 > last select )result set  in Data output window of Pgadmin4. i tried
 > executing the  cursor inside the transaction as well
(BEING.COMMIT).
 > but i still do not see both result set one by one (likeSQL) in data
 > output window. Please let me know if there is any limitation in
pgadmin4
 > tool that multiple result set will not be displayed in Data
output Window.

  From what I remember pgAdmin4 will only display the last result.

 >
 > Regards
 > Muthukumar

-- 
Adrian Klaver

adrian.kla...@aklaver.com 

--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Multiple result set not displayed in PgAdmin4

2020-11-23 Thread Muthukumar.GK

Hi Adrian,

If PG admin 4 return only last result set then,  how can we call the
procedure/function to get the multiple result set from dot net application.

we are planning to migrate things from SQL to postgresql.. most of our
existing SQL stored procedures will return multiple result sets. So we need
to achieve Same multiple result set in postgreSql.. Kindly advise me..

Regards
Muthukumar.GK

On Mon, Nov 23, 2020, 8:49 PM Adrian Klaver 
wrote:

> On 11/22/20 8:53 PM, Muthukumar.GK wrote:
> > Hi Team,
> >
> > I have created function in postgre sql 13 which  will return two result.
> > But when execute i can able to see the last cursor (query result set of
> > last select )result set  in Data output window of Pgadmin4. i tried
> > executing the  cursor inside the transaction as well (BEING.COMMIT).
> > but i still do not see both result set one by one (likeSQL) in data
> > output window. Please let me know if there is any limitation in pgadmin4
> > tool that multiple result set will not be displayed in Data output
> Window.
>
>  From what I remember pgAdmin4 will only display the last result.
>
> >
> > Regards
> > Muthukumar
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Hash aggregate spilling (v13) / partitions & batches

2020-11-23 Thread talk to ben

Hi,

I am testing things on hash aggregate spilling in version 13.1 and am
struggling to understand the partition thing in the two explains below.
My understanding is that a partition corresponds to a spill file which will
be treated in a later batch (which can re-spill in some cases).

Am I right to think that the second explain analyze says that PostgreSQL
was planning for 8 batches (there are 8 planned partitions) and that only
one was necessary (= no spill files) ?

regards
benoit

[local]:5433 postgres@postgres=# CREATE TABLE tableA(ac1 int, ac2 int);
CREATE TABLE
[local]:5433 postgres@postgres=# CREATE TABLE tableB(bc1 int, bc2 int);
CREATE TABLE

[local]:5433 postgres@postgres=# INSERT INTO tableA SELECT x, random()*100
FROM generate_series(1,100) AS F(x);
INSERT 0 100
[local]:5433 postgres@postgres=# INSERT INTO tableB SELECT mod(x,10),
random()*100 FROM generate_series(1,100) AS F(x);
INSERT 0 100

[local]:5433 postgres@postgres=# SELECT name, setting, unit FROM
pg_settings WHERE name IN('work_mem', 'hash_mem_multiplier');
name | setting | unit
-+-+--
 hash_mem_multiplier | 1   | NULL
 work_mem| 4096| kB
(2 rows)
[local]:5433 postgres@postgres=# EXPLAIN (ANALYZE) SELECT ac1, count(ac2),
sum(bc2) FROM tableA INNER JOIN TABLEB ON ac1 = bc1 GROUP BY Ac1;
QUERY PLAN
---
 HashAggregate  (cost=137290.50..157056.12 rows=100 width=20) (actual
time=773.405..889.020 rows=9 loops=1)
   Group Key: tablea.ac1
   Planned Partitions: 32  Batches: 33  Memory Usage: 4369kB  Disk Usage:
30456kB
   ->  Hash Join  (cost=30832.00..70728.00 rows=100 width=12) (actual
time=158.774..583.031 rows=90 loops=1)
 Hash Cond: (tableb.bc1 = tablea.ac1)
 ->  Seq Scan on tableb  (cost=0.00..14425.00 rows=100 width=8)
(actual time=0.023..77.297 rows=100 loops=1)
 ->  Hash  (cost=14425.00..14425.00 rows=100 width=8) (actual
time=158.378..158.379 rows=100 loops=1)
   Buckets: 131072  Batches: 16  Memory Usage: 3471kB
   ->  Seq Scan on tablea  (cost=0.00..14425.00 rows=100
width=8) (actual time=0.010..53.476 rows=100 loops=1)
 Planning Time: 0.824 ms
 Execution Time: 895.251 ms
(11 rows)

[local]:5433 postgres@postgres=# SET hash_mem_multiplier TO 5;
SET
[local]:5433 postgres@postgres=# EXPLAIN (ANALYZE) SELECT ac1, count(ac2),
sum(bc2) FROM tableA INNER JOIN TABLEB ON ac1 = bc1 GROUP BY Ac1;
QUERY PLAN
---
 HashAggregate  (cost=137290.50..157056.12 rows=100 width=20) (actual
time=696.684..714.198 rows=9 loops=1)
   Group Key: tablea.ac1
   Planned Partitions: 8  Batches: 1  Memory Usage: 15633kB
   ->  Hash Join  (cost=30832.00..70728.00 rows=100 width=12) (actual
time=171.789..560.692 rows=90 loops=1)
 Hash Cond: (tableb.bc1 = tablea.ac1)
 ->  Seq Scan on tableb  (cost=0.00..14425.00 rows=100 width=8)
(actual time=0.032..78.718 rows=100 loops=1)
 ->  Hash  (cost=14425.00..14425.00 rows=100 width=8) (actual
time=168.592..168.593 rows=100 loops=1)
   Buckets: 524288  Batches: 4  Memory Usage: 13854kB
   ->  Seq Scan on tablea  (cost=0.00..14425.00 rows=100
width=8) (actual time=0.018..52.796 rows=100 loops=1)
 Planning Time: 0.242 ms
 Execution Time: 717.914 ms
(11 rows)

Re: ERROR : invalid transaction termination : PostgreSQL v12

2020-11-23 Thread Jagmohan Kaintura

Hi ,
The Block is only failing immediately at First COMMIT only. It's not
supporting COMMIT.  I have removed some portion of code before the second
COMMIT.


On Mon, Nov 23, 2020 at 9:19 PM Adrian Klaver 
wrote:

> On 11/23/20 12:36 AM, Jagmohan Kaintura wrote:
> > Hi Team,
> >
> > We have many BATCH JOBS in Oracle which we are committing after
> > processing few Records. These batch Jobs process in some subsets and
> > call transaction control statements COMMIT in case of Success and
> > ROLLBACK in case of failure.
> >
> > While converting to POstgreSQL we converted in Same Format with COMMIT
> > and ROLLBACK. But while executing it ended up with below error message.
> > ERROR:  invalid transaction termination
> > CONTEXT:  PL/pgSQL function inline_code_block line 29 at COMMIT
> >
> > While reviewing the Transaction Management in PostgreSQL
> > "https://www.postgresql.org/docs/12/plpgsql-transactions.html
> > " it
> > speaks about a format which is not Supported.
> >
> > Transaction control is only possible in |CALL| or |DO| invocations from
> > the top level or nested |CALL| or |DO| invocations without any other
> > intervening command. For example, if the call stack is |CALL proc1()| →
> > |CALL proc2()| → |CALL proc3()|, then the second and third procedures
> > can perform transaction control actions. But if the call stack is |CALL
> > proc1()| → |SELECT func2()| → |CALL proc3()|, then the last procedure
> > cannot do transaction control, because of the |SELECT| in between.
> >
> > My Call has :  CALL Batch Job => SELECT function Used in SQL Statements
> > ==> Call Procedure.  We have transaction control in "CALL Batch Job"
> only.
> >
> > Pseudo Code is like : Highlighted in BOLD is a function call. It's
> > failing when getting executed as we are using functions into this
> procedure.
> > *Can any help on this matter , how I can implement Batch Jobs as we
> > wanted to commit in few intervals of 2 records ?*
> > We can't remove this function from the statement as its value is
> > dependent on column value.
> >
> > 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
> >  update tms_container_loading
> > set status_code = G_CNTR_LOADING_EXPIRED
> >   , last_update_tm = clock_timestamp()::timestamp(0)
> >   , last_update_user_an = G_LAST_UPDATE_USER_SYSTEM
> >   , last_update_module_code = G_LAST_UPDATE_MODULE_BATCH
> >   where
> >
> *tms$vvd.is_vessel_departed(vessel_visit_c,trunc(clock_timestamp()::timestamp(0)))*
>
> > = 1
> > and coalesce(status_code,'~') <> G_CNTR_LOADING_EXPIRED
> >   and ctid in (select a.ctid from tms_container_loading  where
> >
> *tms$vvd.is_vessel_departed(vessel_visit_c,trunc(clock_timestamp()::timestamp(0)))*
>
> > = 1
> > and coalesce(status_code,'~') <> G_CNTR_LOADING_EXPIRED
> > LIMIT 2);
> >  EXIT WHEN NOT FOUND; /* apply on SQL */
> >  GET DIAGNOSTICS v_local_batch_count = ROW_COUNT;
> > v_batch_count := v_batch_count + v_local_batch_count;
> >  COMMIT;
> >  END LOOP;
> >  v_log_count := v_log_count + 1; CALL
> > Log(v_batch_count,'TMS_CONTAINER_LOADING',NULL, 'TMS$BATCH_JOB',
> > v_log_count);
> >  COMMIT;
> > END;
> > $BODY$;
>
> I'm still trying to figure out transaction management in procedures, so
> bear with me. Not sure what the purpose of the second COMMIT is? Also
> wonder if it is no the cause of the issue?
>
>
> > --
> > *Best Regards,*
> > Jagmohan
> > Senior Consultant, TecoreLabs.
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>


-- 
*Best Regards,*
Jagmohan
Senior Consultant, TecoreLabs.

Re: Multiple result set not displayed in PgAdmin4

2020-11-23 Thread Joshua Drake

Howdy,

I believe you would receive more help from the proper forum for PgAdmin4:

https://www.pgadmin.org/support/list/

This is a list for PostgreSQL and it's software. PostgreSQL's client is
psql which is a command line client.

Thanks!
JD



>>
>>

Re: ERROR : invalid transaction termination : PostgreSQL v12

2020-11-23 Thread Michael Lewis

On Mon, Nov 23, 2020 at 10:03 AM Jagmohan Kaintura 
wrote:

> Hi ,
> The Block is only failing immediately at First COMMIT only. It's not
> supporting COMMIT.  I have removed some portion of code before the second
> COMMIT.
>

Please don't top-post on the Postgres lists by the way (reply with all
previous conversation copied below).

The only way this would happen that I am aware of is if you called begin
before your batch function.

>

Re: ERROR : invalid transaction termination : PostgreSQL v12

2020-11-23 Thread Jagmohan Kaintura

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
update tms_container_loading
   set status_code = G_CNTR_LOADING_EXPIRED
 , last_update_tm =
clock_timestamp()::timestamp(0)
 , last_update_user_an =
G_LAST_UPDATE_USER_SYSTEM
 , last_update_module_code =
G_LAST_UPDATE_MODULE_BATCH
 where
tms$vvd.is_vessel_departed(vessel_visit_c,trunc(clock_timestamp()::timestamp(0)))
= 1
   and coalesce(status_code,'~') <>
G_CNTR_LOADING_EXPIRED  and ctid in (select ctid from tms_container_loading
 where
tms$vvd.is_vessel_departed(vessel_visit_c,trunc(clock_timestamp()::timestamp(0)))
= 1
   and coalesce(status_code,'~') <>
G_CNTR_LOADING_EXPIRED LIMIT 20);

EXIT WHEN NOT FOUND; /* apply on SQL */
GET DIAGNOSTICS v_local_batch_count =
ROW_COUNT;

v_batch_count := v_batch_count +
v_local_batch_count;
raise info ' I came here %',v_batch_count;
END;
COMMIT;
END LOOP;
raise info ' I came here %',v_batch_count;
v_log_count := v_log_count + 1;
v_log_count);
END;
$BODY$;


while calling

INFO:   I came here 20
ERROR:  invalid transaction termination
CONTEXT:  PL/pgSQL function test_transaction() line 48 at COMMIT







On Tue, Nov 24, 2020 at 12:17 AM Michael Lewis  wrote:

> On Mon, Nov 23, 2020 at 10:03 AM Jagmohan Kaintura <
> jagmo...@tecorelabs.com> wrote:
>
>> Hi ,
>> The Block is only failing immediately at First COMMIT only. It's not
>> supporting COMMIT.  I have removed some portion of code before the second
>> COMMIT.
>>
>
> Please don't top-post on the Postgres lists by the way (reply with all
> previous conversation copied below).
>
> The only way this would happen that I am aware of is if you called begin
> before your batch function.
>
>>

-- 
*Best Regards,*
Jagmohan
Senior Consultant, TecoreLabs.

Re: ERROR : invalid transaction termination : PostgreSQL v12

2020-11-23 Thread Michael Lewis

On Mon, Nov 23, 2020 at 6:52 PM Jagmohan Kaintura 
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.

>

ERROR : invalid transaction termination : PostgreSQL v12

Multiple result set not displayed in PgAdmin4

Re: Multiple result set not displayed in PgAdmin4

Re: ERROR : invalid transaction termination : PostgreSQL v12

Re: Multiple result set not displayed in PgAdmin4

Re: Multiple result set not displayed in PgAdmin4

Hash aggregate spilling (v13) / partitions & batches

Re: ERROR : invalid transaction termination : PostgreSQL v12

Re: Multiple result set not displayed in PgAdmin4

Re: ERROR : invalid transaction termination : PostgreSQL v12

Re: ERROR : invalid transaction termination : PostgreSQL v12

Re: ERROR : invalid transaction termination : PostgreSQL v12

12 matches

Site Navigation

Mail list logo

Footer information