Error ошибка текущая транзакция прервана команды до конца блока транзакции игнорируются postgresql

I got this error using Java and PostgreSQL doing an insert on a table. I will illustrate how you can reproduce this error:

org.postgresql.util.PSQLException: ERROR: 
current transaction is aborted, commands ignored until end of transaction block

Summary:

The reason you get this error is because you have entered a transaction and one of your SQL Queries failed, and you gobbled up that failure and ignored it. But that wasn’t enough, THEN you used that same connection, using the SAME TRANSACTION to run another query. The exception gets thrown on the second, correctly formed query because you are using a broken transaction to do additional work. PostgreSQL by default stops you from doing this.

I’m using: PostgreSQL 9.1.6 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 4.7.2 20120921 (Red Hat 4.7.2-2), 64-bit".

My PostgreSQL driver is: postgresql-9.2-1000.jdbc4.jar

Using Java version: Java 1.7

Here is the table create statement to illustrate the Exception:

CREATE TABLE moobar
(
    myval   INT
);

Java program causes the error:

public void postgresql_insert()
{
    try  
    {
        connection.setAutoCommit(false);  //start of transaction.
        
        Statement statement = connection.createStatement();
        
        System.out.println("start doing statement.execute");
        
        statement.execute(
                "insert into moobar values(" +
                "'this SQL statement fails, and it " +
                "is gobbled up by the catch, okfine'); ");
     
        //The above line throws an exception because we try to cram
        //A string into an Int.  I Expect this, what happens is we gobble 
        //the Exception and ignore it like nothing is wrong.
        //But remember, we are in a TRANSACTION!  so keep reading.

        System.out.println("statement.execute done");
        
        statement.close();
        
    }
    catch (SQLException sqle)
    {
        System.out.println("keep on truckin, keep using " +
                "the last connection because what could go wrong?");
    }
    
    try{
        Statement statement = connection.createStatement();
        
        statement.executeQuery("select * from moobar");

        //This SQL is correctly formed, yet it throws the 
        //'transaction is aborted' SQL Exception, why?  Because:
        //A.  you were in a transaction.
        //B.  You ran a SQL statement that failed.
        //C.  You didn't do a rollback or commit on the affected connection.
        
    }
    catch (SQLException sqle)
    {
        sqle.printStackTrace();
    }   

}

The above code produces this output for me:

start doing statement.execute

keep on truckin, keep using the last connection because what could go wrong?

org.postgresql.util.PSQLException: 
  ERROR: current transaction is aborted, commands ignored until 
  end of transaction block

Workarounds:

You have a few options:

  1. Simplest solution: Don’t be in a transaction. Set the connection.setAutoCommit(false); to connection.setAutoCommit(true);. It works because then the failed SQL is just ignored as a failed SQL statement. You are welcome to fail SQL statements all you want and PostgreSQL won’t stop you.

  2. Stay being in a transaction, but when you detect that the first SQL has failed, either rollback/re-start or commit/restart the transaction. Then you can continue failing as many SQL queries on that database connection as you want.

  3. Don’t catch and ignore the Exception that is thrown when a SQL statement fails. Then the program will stop on the malformed query.

  4. Get Oracle instead, Oracle doesn’t throw an exception when you fail a query on a connection within a transaction and continue using that connection.

In defense of PostgreSQL’s decision to do things this way… Oracle was making you soft in the middle letting you do dumb stuff and overlooking it.

Источник

I am trying to create a deadlock in my postgreSQL-database but the error I’m getting is not really the one I would expect:

ERROR:  current transaction is aborted, commands ignored until end of transaction block
SQL state: 25P02

My setup is basically a mix of these two:

  • https://stackoverflow.com/a/22776994/2516892
  • https://www.cybertec-postgresql.com/en/postgresql-understanding-deadlocks/

I opened two browser windows of pgAdmin 4 and have two query windows. One with the following:

BEGIN;
    UPDATE "Products"
        SET "Price" = "Price" * 0.03
        WHERE "PID" = 1
        RETURNING *;
        
    UPDATE "Products"
        SET "Price" = "Price" * 0.03 
        WHERE "PID" = 2
        RETURNING *;
COMMIT;

In the other is use this one:

BEGIN;
    UPDATE "Products"
        SET "Price" = "Price" * 3
        WHERE "PID" = 2
        RETURNING *;
        
    UPDATE "Products"
        SET "Price" = "Price" * 0.03 
        WHERE "PID" = 1
        RETURNING *;
COMMIT;

My intention was to execute both as close to each other as possible but I got that error. Strangely even when I just try to run them single the same error persists. I can’t make much of that error code. What could it be?

The table itself is as basic as it gets:

CREATE TABLE IF NOT EXISTS "Products"
(
    "PID" integer NOT NULL,
    "Name" character varying(255),
    "Price" double precision,
    "Stock" integer,
    CONSTRAINT "Products_pkey" PRIMARY KEY ("PID")
)

The answers I found online are not particularly helpful. Like this one from here:

This log event happens when a transaction fails due to a potentially unrelated error, and you try to run another query in the failed transaction.

So I have tried to simply do :

BEGIN;
    Select "Price" from "Products"
COMMIT;

And receive the same error. Without the transaction block it works

Источник

I was getting the above error, when I was submitting a model form, I tried —

1.

from django.db import transaction
transaction.rollback()

2

from django import db
from django.db.backends.util import CursorDebugWrapper
old_execute = CursorDebugWrapper.execute
old_execute_many = CursorDebugWrapper.executemany

def execute_wrapper(*args, **kwargs):
    try:
        old_execute(*args, **kwargs)
    except Exception, ex:
        logger.error("Database error:n%s" % ex)
        db.close_connection

def excecute_many_wrapper(*args, **kwargs):
    try:
        old_execute_many(*args, **kwargs)
    except Exception, ex:
        logger.error("Database error:n%s" % ex)
        db.close_connection

CursorDebugWrapper.execute = execute_wrapper
CursorDebugWrapper.executemany = excecute_many_wrapper

3 

from django.db import connection
connection._rollback()

4

manage.py migrate

or

manage.py syncdb

and then resubmitted the model form, nothing again seemed to happen.

How can I fix this?

Источник

In this post, I will tell you how to fix the error that PostgreSQL throws when you try to execute an SQL-statement.

When you execute a non-correct SQL statement, you correct it and try to execute it again, you can have an error like: ERROR: current transaction is aborted, commands ignored until end of transaction block

By executing that wrong SQL-statement, postgreSQL will not terminate the execution by itself so you need to rollback manually. You can do this by simply execute the following statement:

rollback;

Example

I want to execute the non-correct SQL-statement:

select * from account

PostgreSQL will throw the following error: syntax error at or near ‘fro’. This means my word ‘from’ is not correctly typed. So I now want to execute the correct statement:

select * from account

Here, PostgreSQL will throw the following error: current transaction is aborted, commands ignored until end of transaction block. This is because the wrong SQL-statement is still trying to commit. Now, to rollback, type the following SQL-statement:

rollback;

Now we can try to execute the correct SQL-statement again. You will notice that it works this time. Enjoy 😀

Источник

Я получил эту ошибку, используя Java и postgresql, делая вставку в таблицу. Я проиллюстрирую, как вы можете воспроизвести эту ошибку:

org.postgresql.util.PSQLException: ERROR: 
current transaction is aborted, commands ignored until end of transaction block

Резюме:

Причина, по которой вы получаете эту ошибку, связана с тем, что вы ввели транзакцию и один из ваших SQL-запросов потерпел неудачу, и вы поглотили эту ошибку и проигнорировали ее. Но этого было недостаточно, ТОГДА вы использовали то же соединение, используя SAME TRANSACTION для запуска другого запроса. Исключение получает второй, правильно сформированный запрос, потому что вы используете сломанную транзакцию для выполнения дополнительной работы. Postgresql по умолчанию останавливает вас от этого.

Я использую: PostgreSQL 9.1.6 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 4.7.2 20120921 (Red Hat 4.7.2-2), 64-bit".

Мой драйвер postgresql: postgresql-9.2-1000.jdbc4.jar

Использование java-версии: Java 1.7

Вот пример создания таблицы, чтобы проиллюстрировать Исключение:

CREATE TABLE moobar
(
    myval   INT
);

Программа Java вызывает ошибку:

public void postgresql_insert()
{
    try  
    {
        connection.setAutoCommit(false);  //start of transaction.

        Statement statement = connection.createStatement();

        System.out.println("start doing statement.execute");

        statement.execute(
                "insert into moobar values(" +
                "'this sql statement fails, and it " +
                "is gobbled up by the catch, okfine'); ");

        //The above line throws an exception because we try to cram
        //A string into an Int.  I Expect this, what happens is we gobble 
        //the Exception and ignore it like nothing is wrong.
        //But remember, we are in a TRANSACTION!  so keep reading.

        System.out.println("statement.execute done");

        statement.close();

    }
    catch (SQLException sqle)
    {
        System.out.println("keep on truckin, keep using " +
                "the last connection because what could go wrong?");
    }

    try{
        Statement statement = connection.createStatement();

        statement.executeQuery("select * from moobar");

        //This SQL is correctly formed, yet it throws the 
        //'transaction is aborted' SQL Exception, why?  Because:
        //A.  you were in a transaction.
        //B.  You ran a sql statement that failed.
        //C.  You didn't do a rollback or commit on the affected connection.

    }
    catch (SQLException sqle)
    {
        sqle.printStackTrace();
    }   

}

Приведенный выше код производит для меня этот вывод:

start doing statement.execute

keep on truckin, keep using the last connection because what could go wrong?

org.postgresql.util.PSQLException: 
  ERROR: current transaction is aborted, commands ignored until 
  end of transaction block

Обходные:

У вас есть несколько вариантов:

  • Простейшее решение: не выполняйте транзакции. Установите для параметра connection.setAutoCommit(false); значение connection.setAutoCommit(true);. Это работает, потому что неудавшийся SQL просто игнорируется как неудачный SQL-запрос. Вы можете отказаться от SQL-запросов, которые вы хотите, и postgresql не остановит вас.

  • Оставайтесь в транзакции, но когда вы обнаруживаете, что первый sql не сработал, либо откат/повторный запуск, либо фиксация/перезапуск транзакции. Затем вы можете продолжить сбой по количеству запросов sql в этом соединении с базой данных, как вы хотите.

  • Не улавливайте и игнорируйте Исключение, которое вызывается при сбое SQL-запроса. Затем программа остановится на неверном запросе.

  • Получите Oracle вместо этого, Oracle не генерирует исключение, если вы не выполняете запрос по соединению в транзакции и продолжаете использовать это соединение.

В защиту решения postgresql сделать так, что… Oracle сделал вас мягким в середине, позволяя вам делать глупые вещи и игнорировать его.

Источник

  • Error ошибка лишние данные после содержимого последнего столбца
  • Error ошибка инициализации графического устройства ок
  • Error ошибка drop database не может выполняться внутри блока транзакции
  • Error ошибка create database не может выполняться внутри блока транзакции
  • Error ошибка 201 невыполнение запроса рабочие места орион про