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

Question

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

Answer 1

This error occurs when a previous query has failed and the client still issues queries in that transaction. The only thing to do in that state is a ROLLBACK.

It's not particularly tied to deadlocks or even having two concurrent sessions.

Example with psql:

test=> begin;
BEGIN

test=*> select 1/0;
ERROR:  division by zero

test=!> select 2;
ERROR:  current transaction is aborted, commands ignored until end of transaction block

test=!> 

In your test with pgAdmin, you want to submit your queries one by one. Do not submit the whole BEGIN......COMMIT block as a whole. Presumably this is why you don't see the deadlock error: it's immediately replaced by the error of the next query.