Error invalid transaction termination

Transaction management in PostgreSQL and what is different from Oracle

TL;DR: Everything is different about transaction behavior. This may also change your ideas about “database independent” applications.

I like to explain some PostgreSQL concepts from an oracle DBA point of view. There are many things that are different in the two RDBMS and it is important to understand them.

Auto commit

Here is a short example where I create a table, insert one row and rollback:

I never executed any commit but my changes are saved and published. This is auto-commit by default in psql. I think I prefer the Oracle default where the commit is my decision, and only what I’ve committed is visible:

So, is Oracle the best for transaction management?

Well… autocommit default is only one thing. Let’s go further.

First, this is only a client setting. SQL*Plus has it set to off:

But JDBC drivers may have it set to on. So, rather than a nice thing about the database, it is just a nice default of SQL*Plus.

Commit on exit

And wait… are all sqlplus defaults so smart? Let’s come back to my Oracle database where I committed nothing:

Are sqlplus defaults so smart? I didn’t commit the last insert but it was committed by default. Not because of auto-commit but because of exit-commit:

That’s not a nice default. If I quit without saying ‘commit’ I want a rollback. It is highly recommended to set exit-commit off to avoid any unexpected commit. (also recommendedvfor scripts having a WHENEVER SQLERROR EXIT that mentions ROLLBACK because COMMIT is the default).

DDL auto-commit

And that’s not all… The first row (n=1) was never explicitly committed. It was inserted with DDL (CREATE TABLE) and DDL are always auto-committed. That’s the Oracle Database, nothing to do with the client: you cannot be transactional with DDL.

Start transaction

Back to PostgreSQL, the default is auto-commit but I have the choice. I can explicitly start a transaction and then I’ll have to explicitly commit it.

See? I have the 3 rows from the first run which were all auto-committed. But now that I explicitly started a transaction, everything was transactional, even the DDL: the DROP TABLE, the CREATE TABLE, the INSERT were explicitly rolled-back. And even the last INSERT was implicitly rolled-back on exit.

Now, who is the winner in transaction management? There’s even more: you can send a multi-statement command to the backend and it will be processed as an implicit transaction.

Note that START TRANSACTION is the ANSI SQL syntax, but PostgreSQL accepts also BEGIN, BEGIN TRANSACTION and BEGIN WORK.

Set Transaction name

Don’t think that there are no “begin transaction” in Oracle. The SET TRANSACTION starts it, in order to define the isolation level, or simply to put a name to the transaction.

This example looks at the transaction address in V$SESSION and V$TRANSACTION

This illustrates when a transaction starts (visible with a TADDR in V$SESSION and a row in V$TRANSACTION): the first INSERT/DELETE/UPDATE/MERGE/SELECT FOR UPDATE or a SET TRANSACTION

Autonomous Transaction

Not available in PostgreSQL but possible in Oracle: we can have nested transactions. This is very convenient in some limited cases, like logging the error in the database (and commit this insert) before the rollback of the transaction.

This shows that there can be multiple transactions for the same session. The PADDR is only the address or the top-level one.

User Call level rollback

(Note that I’ve previously written “statement-level rollback” but this is misleading because a statement can be executed in a recursive call, as in a PL/SQL block. What I show here is about a user call statement which can actually be a SQL statement or a PL/SQL block or a procedure call ).

Still in Oracle, when a statement fails in a user call, the modifications done by this user call are rolled back, but not the previous modifications. The transaction can continue (like re-try, or do an alternative change):

ERROR: current transaction is aborted

That’s different in PostgreSQL where the transaction cannot continue when you encounter an error:

Here I rolled back. But I can also commit to terminate the transaction, but it will rollback anyway:

Savepoint

Actually, we can achieve user call level rollback even in PostgreSQL, using savepoints:

With Oracle, you don’t need to because there is an implicit savepoint before each execution.

Well, the PostgreSQL client psql do the same:

and then psql will automatically create a “pg_psql_temporary_savepoint”.

PostgreSQL 11 Procedures

Before version 11 all commands (which can be one statement, or multiple ones, or PL/pgSQL anonymous or stored procedures) were run in an atomic context, with no intermediate commits possible without a roundtrip with the client. Like this:

The first call has inserted values 10 and 12 and they were committed because I run in the default AUTOCOMMIT. But the second call has encountered a duplicate key and the whole was rolled-back. But in PostgreSQL 11 I can add an intermediate commit so that the first rows are committed before I encounter the error.

Here the rows 5 to 9 have been committed before encountering the exception for row 10.

Still, no need for an explicit BEGIN here. The COMMIT in the loop will end the transaction (started implicitly on the server as I did no BEGIN before) and start a new one. The last transaction started will be committed implicitly.

Additionally, you can look at the timestamps in the two previous demos. In PostgreSQL, current_timestamp is consistent in the transaction.

Invalid transaction termination

Here is the same, but with AUTOCOMMIT off:

Actually, this is what made me start this blog post. But that’s already a lot, and further investigation on this will be on the next post:
AUTOCOMMIT and PostgreSQL transaction management in procedures

Implicit transactions

Ok, just one more on this. The error above is not really caused by AUTOCOMMIT but by the way psql handles AUTOCOMMIT. Actually, with PostgreSQL, the AUTOCOMMIT is not a BEGIN statement added by the psql client, but the backend server creating an implicit transaction when there is not already one. It is even the opposite: when AUTOCOMMIT is off, the client adds a “BEGIN” so that the server does not create an implicit one. Which means that the “invalid transaction termination” occurs also without auto-commit when we CALL the procedure from an explicit transaction.

This long post is only a short sample of the many differences between Oracle and PostgreSQL transaction management. And I’ve only used psql and sqlplus here. Do you want to test the same from Java JDBC and Python psycopg2?

Источник

Postgresql error invalid transaction termination

All messages emitted by the PostgreSQL server are assigned five-character error codes that follow the SQL standard’s conventions for “ SQLSTATE ” codes. Applications that need to know which error condition has occurred should usually test the error code, rather than looking at the textual error message. The error codes are less likely to change across PostgreSQL releases, and also are not subject to change due to localization of error messages. Note that some, but not all, of the error codes produced by PostgreSQL are defined by the SQL standard; some additional error codes for conditions not defined by the standard have been invented or borrowed from other databases.

According to the standard, the first two characters of an error code denote a class of errors, while the last three characters indicate a specific condition within that class. Thus, an application that does not recognize the specific error code might still be able to infer what to do from the error class.

Table A.1 lists all the error codes defined in PostgreSQL 15.1. (Some are not actually used at present, but are defined by the SQL standard.) The error classes are also shown. For each error class there is a “ standard ” error code having the last three characters 000 . This code is used only for error conditions that fall within the class but do not have any more-specific code assigned.

The symbol shown in the column “ Condition Name ” is the condition name to use in PL/pgSQL . Condition names can be written in either upper or lower case. (Note that PL/pgSQL does not recognize warning, as opposed to error, condition names; those are classes 00, 01, and 02.)

For some types of errors, the server reports the name of a database object (a table, table column, data type, or constraint) associated with the error; for example, the name of the unique constraint that caused a unique_violation error. Such names are supplied in separate fields of the error report message so that applications need not try to extract them from the possibly-localized human-readable text of the message. As of PostgreSQL 9.3, complete coverage for this feature exists only for errors in SQLSTATE class 23 (integrity constraint violation), but this is likely to be expanded in future.

Table A.1. PostgreSQL Error Codes

Submit correction

If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.

Источник

I’m trying to learn PostgreSQL stored procedures. Specifically creating the procedure below in PSQL.

CREATE OR REPLACE PROCEDURE BUILD_AND_POPULATE(INOUT cresults refcursor) 
    LANGUAGE PLPGSQL 
    AS $$
    BEGIN
        BEGIN; -- I've tried removing this but the behaviour is the same
        cresults:= 'cur';
        DROP TABLE IF EXISTS procsampledata;
        CREATE TABLE procsampledata as select x,1 as c2,2 as c3, md5(random()::text) from generate_series(1,10) x;  
        COMMIT;
        OPEN cresults FOR SELECT * FROM procsampledata;  
    END;
$$;

Then I execute it like so, but receive an error:

postgres=# call build_and_populate(null);
ERROR:  invalid transaction termination
CONTEXT:  PL/pgSQL function build_and_populate(refcursor) line 6 at COMMIT

I’ve tried with AUTOCOMMIT set to both on and off.

This is my Postgres version

 PostgreSQL 11.5 on x86_64-pc-linux-musl, compiled by gcc (Alpine 8.3.0) 8.3.0, 64-bit

Can anyone see what I’m doing wrong? Thank you!

People have blogged about the fact that commit is legal in a PostgreSQL stored proc, starting with version 11—which is what yugabyteDB Version 1.3.0 uses. Here’s an example:

PostgreSQL 11 – Server-side Procedures (Part 1)

However, these blogs forget that psql (and therefore ysqlsh) have AUTOCOMMIT set to True by default. They seem to do their tests in that mode and forget that the AUTOCOMMIT mode might be False. However, things change when the AUTOCOMMIT mode is False. See the following testcase.

I would like to hear the explanation for this weird behavior. It’s add odds with the definition of autocommitting: if the insert has autocommitted, then by definition there’s nothing to commit or rollback.

Following on from this, I see no reason why call my_proc() has to start a txn. It might do no more than a calculation. As I see it, it’s the first SQL statement that’s executed from the stored proc that should start a txn. Notice that within the present regime, and given that the weakest isolation level in yugabyte DB is repeatable read, you cannot implement a stored proc to watch a table in an infinite loop and to respond when it suffers an interesting change. Unless, that is, the non-intuitive workaround that I show below helps there.

Note: this remains to be tested.

i psqlrc
set AUTOCOMMIT False
begin;
drop table if exists marbles;
commit;

begin;
create table marbles(
  pk integer
    constraint t_pk primary key,
  color text
    constraint t_c1_nn not null
    constraint t_c1_chk check(color in ('black', 'white'))
  );
commit;

begin;
insert into marbles(pk, color) values
  (1, 'black'),
  (2, 'white');
commit;

begin;
drop procedure if exists set_all_marbles_black();
commit;

begin;
create procedure set_all_marbles_black()
  language plpgsql
as $$
  begin
    -- The "start transaction" and "set transaction" errors can be
    -- understood in the context that "call" starts a transaction, if
    -- one isn't already ongoing.

    -- Commented out 'cos causes run-time error
    -- "0A000: unsupported transaction command in PL/pgSQL".
    -- start transaction;

    -- Commented out 'cos causes run-time error
    -- "25001: SET TRANSACTION ISOLATION LEVEL must be called before any query"
    -- Yet, as a top-level command, it *does* start a txn.
    -- set transaction isolation level serializable;

    update marbles set color = 'black' where color = 'white';

    -- It's hard to understand the thinking that requires this error.
    -- Especially given that it occurs when AUTOCOMMIT is False
    -- but not when it's True.
    -- Causes "2D000: invalid transaction termination"
    rollback;
  end;
$$;
commit;

select pk, color from marbles order by pk;
rollback;

set AUTOCOMMIT True
-- Runs OK, and the "rollback" has its effect.
call set_all_marbles_black();
select pk, color from marbles order by pk;

set AUTOCOMMIT False
-- This causes:
--   ysql:plpgsql_proc_txn_control.sql:63: ERROR:  invalid transaction termination
--   CONTEXT:  PL/pgSQL function set_all_marbles_black() line 21 at ROLLBACK
call set_all_marbles_black();

-- Must rollback before trying anything else.
rollback;

Here, for completeness, is another manifestation of the same issue. This quietly succeeds:

set AUTOCOMMIT True
do $$
begin
  update marbles set color = 'black' where color = 'white';
  commit;
end
$$;

But this fails with "ERROR: invalid transaction termination":

set AUTOCOMMIT False
do $$
begin
  update marbles set color = 'black' where color = 'white';
  commit;
end
$$;

Hi Jagmohan,

What is the point of COMMITting after a few records? Why not let the
whole batch run through. If there are any errors, PostgreSQL will
rollback the whole transaction anyway.

As opposed to Oracle, PostgreSQL won’t commit some records, while others
failed within the same transaction. As soon as a single operation within
a transaction fails, it’s dead and all subsequent operations will fail.

Regards,

Holger

Am 23.11.20 um 18:05 schrieb 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
> <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 20000 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 20000);
>             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

—
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012

Studying Stored Procs in Postgres 11

With Postgres 11 looming on the near horizon, it’s only appropriate to check out a recent beta and kick the tires a few times. Whether it’s improvements in parallelism, partitions, stored procedures, JIT functionality, or any number of elements in the release page, there’s a lot to investigate.

It just so happens that I ran across a fortuitous event on Twitter when deciding on an appropriate topic. Behold!

Wait! No! That’s not what stored procedures are for!

I felt so good like anything was possible

When confronted with such a blatant corruption of such a cool new feature, it’s only natural to question the wisdom of doing so. It is, after all, not a great idea to programatically consume transaction IDs. I said as much and moved on with life, certain the worst was over.

Then this happened.

Now, Magnus is infamous for two things: his technical acumen, and giddy malevolence. His advocacy of a stored procedure named “waste_xid” only proved nobody anywhere should ever run this anywhere, lest they immolate whatever system hosted the database instance.

But hey, VMs are cheap; let’s break things. How else can we learn the limits of our new toys, but by virtually atomizing them?

I hit cruise control and rubbed my eyes

Before we saddle our poor innocent Postgres 11 installation with an inadvisable stored procedure designed specifically to underhandedly reap its transaction lifespan, we should probably make the routine as evil as possible.

One thing stands out immediately: calling EXECUTE is unnecessary overhead. According to the information function documentation, txid_current will assign a new transaction ID if there isn’t one already. Since the stored procedure is constantly committing, that’s extremely handy. And since this is Pl/pgSQL, we can use direct assignment instead.

Our new procedure looks something like this:

CREATE OR REPLACE PROCEDURE waste_xid(cnt int)
AS $$
DECLARE
    i INT;
    x BIGINT;
BEGIN
    FOR i in 1..cnt LOOP
        x := txid_current();
        COMMIT;
    END LOOP;
END;
$$
LANGUAGE plpgsql;

Great! We can now waste XIDs about 4x faster than before! On this particular test VM, the maximum amount of wasted XIDs per second was about 125k. At that rate, we could blow through a billion in roughly two hours.

Since quite a few installations use the default autovacuum settings, that means we could trigger a lot of vacuums at the 200M transaction mark in about half an hour. We didn’t need that disk throughput anyway.

Workin’ on a mystery, goin’ wherever it leads

Regardless, what happens if we run it?

CALL waste_xid(1000000);

CALL

Well, that was anticlimactic. How about the logs? We set our min_log_duration_statement to 100ms for debugging purposes, so we should definitely see a long call there.

2018-10-03 20:53:02.505 UTC [11334] [email protected] LOG:  duration: 7949.540 ms  statement: call waste_xid(1000000);

Here’s something that’s actually more interesting than it might appear at first glance. Stored procedures, it would seem, are atomic. We tested explicitly by adding a long pg_sleep call in the loop, and none of those entries were logged.

Since procedures can COMMIT transactions and thus could contain semantically unrelated unrelated activity to the originating transaction, can a procedure essentially derail an existing transaction? Let’s see:

BEGIN;

CALL waste_xid(1000000);

ERROR:  invalid transaction termination
CONTEXT:  PL/pgSQL function waste_xid(integer) line 8 at COMMIT

Nope! Apparently CALL is only valid within its own context; no transactions for you. This could introduce interesting behavior in application layers that implicitly start a transaction before every command. The resulting error is a bit ambiguous however; why is the transaction termination invalid? Can’t it just say we can’t close the current transaction from within a procedure?

So stored procedures are atomic, and can do whatever they want in their own transactional context. What else are they hiding?

I put the pedal down to make some time

It seems there are special rules to how transactions are handled in stored procedures. While the discussion was hot, a couple more functional variants popped up. Here’s an interesting one:

CREATE OR REPLACE PROCEDURE check_now()
AS $$
DECLARE
    i int;
BEGIN
    FOR i in 1..5 LOOP
        RAISE NOTICE 'It is now: %', now();
        PERFORM txid_current();
        COMMIT;
        PERFORM pg_sleep(0.1);
    END LOOP;
END;
$$
LANGUAGE plpgsql;

What are we testing for here? Well, one fundamental attribute of the now() function and several other related DATETIME routines, is that they are tied to the current transaction. So in theory, the value being returned should change.

Let’s try it:

CALL check_now();

NOTICE:  It is now: 2018-10-03 21:27:07.764822+00
NOTICE:  It is now: 2018-10-03 21:27:07.764822+00
NOTICE:  It is now: 2018-10-03 21:27:07.764822+00
NOTICE:  It is now: 2018-10-03 21:27:07.764822+00
NOTICE:  It is now: 2018-10-03 21:27:07.764822+00

Uh oh. What does this actually mean? Is now() being tethered to the initial CALL instead of the current transaction? Or are PL/pgSQL transactions not real transactions? If they do operate in a different context than the outer transaction, how else does that context diverge? Or is this a bug?

Postgres 11 is still in beta, after all.

It was always cold, no sunshine

Upon adopting the PROCEDURE nomenclature, we’re also presented with a rather unique historical quirk. Consider the previous syntax for creating a trigger. More than a few triggers have been declared this way:

CREATE TRIGGER t_my_trigger
BEFORE INSERT OR UPDATE ON mytable
   FOR EACH ROW
       EXECUTE PROCEDURE my_function();

Ever prescient, the devs saw this coming and added a disclaimer to the Postgres 11 docs:

In the syntax of CREATE TRIGGER, the keywords FUNCTION and PROCEDURE are equivalent, but the referenced function must in any case be a function, not a procedure. The use of the keyword PROCEDURE here is historical and deprecated.

So in case you’d mentally linked the historical—and up until Postgres 11, current—syntax with the new stored procedure functionality, don’t. Procedures really did change more of the Postgres internals than a cursory examination could reveal.

There’s something good waitin’ down this road

Now that we’ve kicked the proverbial tires and explored a few of the neat quirks stored procedures offer, what does it all mean?

First and foremost, the ability to COMMIT or ROLLBACK within a function call allows both safeguarding activity batches or particularly critical data. No longer will one function call represent an all-or-nothing proposition. That alone is a massive enhancement to Postgres, one which users of other database engines such as Oracle have been clamoring for over the last 20 years.

More subtly, it presents a new security concern that administrators should consider. The ability to manipulate transactions is a powerful lever ripe for abuse. At minimum, DBAs should consider revoking USAGE on any languages from users that shouldn’t be granted that capability.

REVOKE USAGE ON LANGUAGE plpgsql FROM PUBLIC;

Users will still be able to invoke previously defined functions or procedures, but won’t be able to create new ones. In fact, this should probably be done on all current systems as a matter of course.

Judiciously wielded, Postgres can continue runnin’ down the dream of becoming the best and most feature-laden database. We just need to pay attention to where we’re going.

Обсуждение: ERROR : invalid transaction termination : PostgreSQL v12

ERROR : invalid transaction termination : PostgreSQL v12

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 20000);
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$;

Re: ERROR : invalid transaction termination : PostgreSQL v12

What is the point of COMMITting after a few records? Why not let the whole batch run through. If there are any errors, PostgreSQL will rollback the whole transaction anyway.

As opposed to Oracle, PostgreSQL won’t commit some records, while others failed within the same transaction. As soon as a single operation within a transaction fails, it’s dead and all subsequent operations will fail.

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 20000);
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$;

Источник

Недействительное завершение транзакции

У меня есть процедура, которая, когда я выполняю в dbeaver, отлично работает без проблем, однако, когда я вызываю ее из внешней программы, я получаю сообщение об ошибке ниже. Я не хочу копировать / вставлять здесь полную процедуру, потому что она довольно большая и работает в инструменте db. Я просто копирую / вставляю верх и низ. Что могло быть причиной этого?

Процедура:

Ошибка:

1 ответ

Управление транзакциями возможно только в вызовах CALL или DO с верхнего уровня или вложенных вызовах CALL или DO без какой-либо другой промежуточной команды. Например, если стек вызовов CALL proc1() → CALL proc2() → CALL proc3() , то вторая и третья процедуры могут выполнять действия по управлению транзакциями. Но если стек вызовов CALL proc1() → SELECT func2() → CALL proc3() , то последняя процедура не может управлять транзакцией из-за SELECT между ними.

Есть и другие, недокументированные ограничения:

Вы не можете явно начать транзакцию с помощью BEGIN и зафиксировать ее внутри транзакции. Таким образом, следующее не удастся:

Это может быть улучшено в будущих выпусках.

Все процедуры в стеке вызовов должны быть написаны на PL / pgSQL:

Как бы то ни было, контроль транзакций внутри процедур PostgreSQL несколько ограничен.

Если вы нарушите какое-либо из этих правил, вы получите сообщение об ошибке, описанное в вашем вопросе. Вероятно, вам придется обрабатывать транзакции в приложении, а не в процедуре — возможно, разделение процедуры на более мелкие части делает это возможным.

Источник

Thread: ERROR : invalid transaction termination : PostgreSQL v12

ERROR : invalid transaction termination : PostgreSQL v12

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 20000);
В В В В В В 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$;

Re: ERROR : invalid transaction termination : PostgreSQL v12

What is the point of COMMITting after a few records? Why not let the whole batch run through. If there are any errors, PostgreSQL will rollback the whole transaction anyway.

As opposed to Oracle, PostgreSQL won’t commit some records, while others failed within the same transaction. As soon as a single operation within a transaction fails, it’s dead and all subsequent operations will fail.

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 20000);
В В В В В В 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$;

Источник

Please explain the inscrutable rules for when «commit» in a stored proc succeeds or causes a run-time error #1957

People have blogged about the fact that commit is legal in a PostgreSQL stored proc, starting with version 11—which is what yugabyteDB Version 1.3.0 uses. Here’s an example:

However, these blogs forget that psql (and therefore ysqlsh ) have AUTOCOMMIT set to True by default. They seem to do their tests in that mode and forget that the AUTOCOMMIT mode might be False . However, things change when the AUTOCOMMIT mode is False . See the following testcase.

I would like to hear the explanation for this weird behavior. It’s add odds with the definition of autocommitting: if the insert has autocommitted, then by definition there’s nothing to commit or rollback .

Following on from this, I see no reason why call my_proc() has to start a txn. It might do no more than a calculation. As I see it, it’s the first SQL statement that’s executed from the stored proc that should start a txn. Notice that within the present regime, and given that the weakest isolation level in yugabyte DB is repeatable read , you cannot implement a stored proc to watch a table in an infinite loop and to respond when it suffers an interesting change. Unless, that is, the non-intuitive workaround that I show below helps there.

Note: this remains to be tested.

Here, for completeness, is another manifestation of the same issue. This quietly succeeds:

But this fails with «ERROR: invalid transaction termination» :

The text was updated successfully, but these errors were encountered:

Источник

Недействительное завершение транзакции

У меня есть процедура, которая, когда я выполняю в dbeaver, отлично работает без проблем, однако, когда я вызываю ее из внешней программы, я получаю сообщение об ошибке ниже. Я не хочу копировать / вставлять здесь полную процедуру, потому что она довольно большая и работает в инструменте db. Я просто копирую / вставляю верх и низ. Что могло быть причиной этого?

Процедура:

Ошибка:

1 ответ

Управление транзакциями возможно только в вызовах CALL или DO с верхнего уровня или вложенных вызовах CALL или DO без какой-либо другой промежуточной команды. Например, если стек вызовов CALL proc1() → CALL proc2() → CALL proc3() , то вторая и третья процедуры могут выполнять действия по управлению транзакциями. Но если стек вызовов CALL proc1() → SELECT func2() → CALL proc3() , то последняя процедура не может управлять транзакцией из-за SELECT между ними.

Есть и другие, недокументированные ограничения:

Вы не можете явно начать транзакцию с помощью BEGIN и зафиксировать ее внутри транзакции. Таким образом, следующее не удастся:

Это может быть улучшено в будущих выпусках.

Все процедуры в стеке вызовов должны быть написаны на PL / pgSQL:

Как бы то ни было, контроль транзакций внутри процедур PostgreSQL несколько ограничен.

Если вы нарушите какое-либо из этих правил, вы получите сообщение об ошибке, описанное в вашем вопросе. Вероятно, вам придется обрабатывать транзакции в приложении, а не в процедуре — возможно, разделение процедуры на более мелкие части делает это возможным.

Источник

1. 
   Home

2. 
   SQL, Database Testing

3. 
   «invalid transaction termination» in Postgres when trying to update a value using a function