Thread: troubleshooting PGError
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?
thank you.
On 9 November 2011 06:02, slavix <mikerin.slava@gmail.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.
On Tuesday, November 08, 2011 9:02:40 pm slavix 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?
What version of Postgres?
Where are you running the above from?
>
> thank you.
--
Adrian Klaver
adrian.klaver@gmail.com
your transaction had an error, and any query after the first one that has failed will be ignored.
On Thursday, November 10, 2011 8:47:39 am you wrote: > not sure about version.. (latest i presume because installed recently) > same problem on my local machine running Ubuntu and on Heroku server > what command to get version? > select version(); -- Adrian Klaver adrian.klaver@gmail.com
On Thursday, November 10, 2011 8:56:56 am you wrote: > "PostgreSQL 8.4.9 on i686-pc-linux-gnu, compiled by GCC gcc-4.4.real > (Ubuntu/Linaro 4.4.4-14ubuntu5) 4.4.5, 32-bit" > Please reply to the list also. That gets your issue in front of more eyes:) The above answers my first question. The second question is still out there: Where are you running the above from? You say it runs fine in PgAdmin, so where are running the query to get the error message? -- Adrian Klaver adrian.klaver@gmail.com
On Thursday, November 10, 2011 9:10:10 am Slava Mikerin wrote: > I am running Rails 3.1 that uses a db. I used phpmyadmin for > development and deployed to Heroku which uses postgres. When I got > this error on the Heroku server I switched to postgres locally to > investigate and got same error. So what do the postgres logs show? The message shows that something caused an error within a transaction block, at that point all over commands are ignored until a ROLLBACK is given. Look in the log and see if you find anything suspicious. > > ruby-1.9.2-p290 and pg-0.11.0 gem used > -- Adrian Klaver adrian.klaver@gmail.com
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,
becausewe 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
querybefore 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
youcan 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@gmail.com> wrote:
>> On 9 November 2011 06:02, slavix <mikerin.slava@gmail.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.
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of slavix
Sent: Wednesday, November 09, 2011 12:03 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] troubleshooting PGError
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?
thank you.
--------------------------------
The error message is very clear; if you understand what a transaction is.
You are executing a number of statements in a single transaction and one of
previous ones failed. You should see another error message earlier that was
the true failure. All this message is saying that because the transaction
had a failure all subsequent statements that belong to the same transaction
will be ignored since, at the end of the transaction, everything is going to
be rolled-back anyway.
If you are still confused you need to provide more details about your
programming environment and all the statements that you are bundling into
the same transaction at the statement shown; and a significant section of
the PostgreSQL log file would help as well.
This normally doesn't show up since you usually want to stop processing a
transaction as soon as a failure occurs. If you provide the entire
transaction to PostgreSQL all at once then PostgreSQL will normally stop
processing as soon as it encounters an error and will return that error.
However, if you take manual control of the transaction in your programming
environment then if you attempt to execute additional statements even after
an error is thrown PostgreSQL will throw the error you are seeing saying
that, in effect, your attempt to execute a statement is pointless.
David J.
Thank you. I found the problem. It was with the previous query, not the one that was showing up in the prompt. I found the actual query in one of the log files.
I am running Rails 3.1 that uses a db. I used phpmyadmin for development and deployed to Heroku which uses postgres. When I got this error on the Heroku server I switched to postgres locally to investigate and got same error. ruby-1.9.2-p290 and pg-0.11.0 gem used On Thu, Nov 10, 2011 at 9:04 AM, Adrian Klaver <adrian.klaver@gmail.com> wrote: > On Thursday, November 10, 2011 8:56:56 am you wrote: >> "PostgreSQL 8.4.9 on i686-pc-linux-gnu, compiled by GCC gcc-4.4.real >> (Ubuntu/Linaro 4.4.4-14ubuntu5) 4.4.5, 32-bit" >> > > Please reply to the list also. That gets your issue in front of more eyes:) > > The above answers my first question. > The second question is still out there: > Where are you running the above from? > You say it runs fine in PgAdmin, so where are running the query to get the error > message? > > > -- > Adrian Klaver > adrian.klaver@gmail.com >