Calling procedure from PyQt5 using psycopg2 driver PostgreSQL Error 2D000 Invalid transaction termination

Question

I started with PostgreSQL recently and I'm running into trouble calling a procedure from a PyQt5 client using the psycopg2 driver. I set a simple procedure with 6 parameters, included a BEGIN and a COMMIT statements and run seamlessly from PgAdmin 4. When tried to call the procedure from the PyQt5 client a got error code 2D000 with the following message:Invalid transaction termination CONTEXT: PL/pgSQL function es_edit_text_report(integer,date,integer,integer,character varying,character varying) line 10 at COMMIT I found several mentions to this error both in stackoverflow and in the web but I could not figure it out why am I getting this results. The closer explanation found may be by @Laurenz Albe on Invalid transaction termination but it looks to me that none of the mentioned conditions apply. Here we have a very simple procedure being called just once from a psycopg2 connection opened from a pool for this single purpose. Following other responses I just commented the commit on the procedure and then I was able to run it from the client. This places some further questions: I understand that the transaction is not operative any longer, consequently you would not be able to run complex procedures safely unless this issue is solve. I'll appreciate your thoughts about it.

Procedure Code:

CREATE OR REPLACE PROCEDURE es_edit_text_report(IN _id integer, IN _report_date,
                                               IN _responsibleid integer,
                                               IN _categoryid integer,
                                               IN _description varchar,
                                               IN _location varchar)
AS $$
BEGIN
    UPDATE text_maintenance_news
        SET report_date = _report_date,
        reporterid = _responsibleid::smallint,
        categoryid = _categoryid::smallint,
        description = _description,
        location = _location
    WHERE id = _id;
    
COMMIT;

END; $$
LANGUAGE plpgsql

PyQt5 client code:

@pyqtSlot()
def save(self):
    try:
        with self.connPool.getconn() as conn:
            with conn.cursor() as cur:
                if self.mode == OPEN_NEW:
                    cur.execute('CALL es_load_text_report(%s, %s, %s, %s, %s)',(
                        self.reportDate.date().toString("yyyy-MM-dd"),
                        self.comboResponsible.getHiddenData(0),
                        self.comboCategory.getHiddenData(0),
                        self.txtDescription.text(),
                        self.txtLocation.text()))
                    conn.commit()
                else:
                    cur.execute('CALL es_edit_text_report(%s, %s, %s, %s, %s, %s)',
                        (self.id,
                        self.reportDate.date().toString("yyyy-MM-dd"),
                        self.comboResponsible.getHiddenData(0),
                        self.comboCategory.getHiddenData(0),
                        self.txtDescription.text(),
                        self.txtLocation.text()))
                    conn.commit()
            qry = QSqlQuery(self.db)
            qry.exec("SELECT * FROM es_load_text_reports()")
            if qry.lastError().type() != 0:
                raise DataError('save: qry', qry.lastError().text())
            self.tableNews.model().setQuery(qry)
        self.clear()

    except OSError as e:
        QMessageBox.warning(self, 'Save : ', e.args, QMessageBox.Ok)

    except DataError as e:
        QMessageBox.warning(self, e.source, e.message, QMessageBox.Ok)

    except DatabaseError as e:
        QMessageBox.warning(self, 'Save', f'{e.pgcode}  {e.pgerror}', QMessageBox.Ok)ter code here

Please notice I'm using a QSqlQuery to retrive the updated table data. The reason being is I'm still trying to figure out how to handle psycopg2 list of tuples return data.

UPDATE: So far the only workaround I was able to find is to remove the COMMIT line from the procedure. I'm under the impression that Postgres is transferring the transaction control to the driver. I wonder if this is the proper way to do it. I'll appreciate your comments about it.

bfris · Answer

With the psycopg2 driver, you have to set autocommit to True for statements that must run outside a transaction. From the docs:

A few commands (e.g. CREATE DATABASE, VACUUM, CALL on stored procedures using transaction control…) require to be run outside any transaction: in order to be able to run these commands from Psycopg, the connection must be in autocommit mode: you can use the autocommit property.

In your code add this line:

conn.autocommit = True

and then your CALL statement should run.

Here's my MRE

create procedure:

CREATE OR REPLACE PROCEDURE test_procedure(
    par1 integer,
    par2 integer)
LANGUAGE 'plpgsql'
AS $BODY$
BEGIN

COMMIT;

END; 
$BODY$;

Python code:

import sys
import psycopg2
print(sys.version)           # 3.7.1 and 3.8.2 tested
print(psycopg2.__version__)  # 2.8.6 and 2.9.3 tested

conn = psycopg2.connect('host=myhost dbname=mydb user=myuser')
cur = conn.cursor()

conn.autocommit = True # this is the key line. without this, next line will fail
cur.execute('CALL aaa.test_procedure(%s,%s)', (1,1))
conn.commit()

Calling procedure from PyQt5 using psycopg2 driver PostgreSQL Error 2D000 Invalid transaction termination

1 Answers