Re: troubleshooting PGError

On 10 Nov 2011, at 17:42, Slava Mikerin wrote:

> Thank you for reply Alban, I am new to postgres and don't know the
> right commands to use to troubleshoot this issue.. The error message
> is unclear and I don't know what caused the problem. can you tell me
> what exact command to use? I don't need to roll back, but to need to
> discover cause of error and eliminate.. thanks for advice.

The error you're seeing is the result of a previous error.
Without seeing that earlier error and the query that caused it nobody can tell you what's wrong with your query, because we don't even know which query you're talking about.

Errors in transactions behave like this:

postgres=> BEGIN;
BEGIN
postgres=> SELECT 1; ?column?
----------
1(1 row)

postgres=> SELECT;
ERROR: syntax error at or near ";"
LINE 1: SELECT;
^
postgres=> SELECT 1;
ERROR: current transaction is aborted, commands ignored until end of transaction block

As you can see, there is nothing wrong with the query that causes the error "current transaction is aborted". It is a query before it that threw an error and caused the transaction to be aborted.

The only ways to get out of the aborted transaction is to either roll it back or to commit (which will also roll back).

You can also use SAVEPOINTs between blocks of queries that you don't want to roll back (sub-transactions). With those you can just roll back to an earlier savepoint and continue from there.

You really should read the relevant documentation at: http://www.postgresql.org/docs/8.3/static/tutorial-transactions.html

P.S. Please don't top-post on this list.
P.P.S. And please include the list in your replies (reply all).

> On Thu, Nov 10, 2011 at 8:30 AM, Alban Hertroys <haramrae(at)gmail(dot)com> wrote:
>> On 9 November 2011 06:02, slavix <mikerin(dot)slava(at)gmail(dot)com> wrote:
>>> Hello,
>>> I am new to postgres, but need to resolve this error:
>>>
>>> PGError: ERROR: current transaction is aborted, commands ignored
>>> until end of transaction block
>>> : SELECT 1 FROM "trades" WHERE ("trades"."uuid" =
>>> 'bc8d86e6-0a8d-11e1-a345-001d09203579' AND "trades"."id" != 25) LIMIT
>>> 1
>>>
>>> Don't know what is the problem, tried running the same query in
>>> pgAdmin and the query runs fine..
>>> I don't know what the error actually is since the error message is not
>>> clear. Any ideas on how to investigate this?
>>
>> A query earlier in your transaction failed and invalidated the
>> transaction. You should probably roll it back.
>>
>> --
>> If you can't see the forest for the trees,
>> Cut the trees and you'll see there is no forest.
>>

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.