Re: ROLLBACK syntax error in pgadmin4

Malik,
I was not blaming you for posting to PgAdmin, I was just trying to advise you and direct you to the proper
forum. Please do not be so defensive. As to determining whether it is a PgAdmin error or a PostgreSQL error, that is pretty simple. If a "feature" of PgAdmin is not working properly, then it is a PgAdmin error. But if you get a SQL error after running a SQL query, then it has to be PostgreSQL.

Yes, you did give the O/S and PostgreSQL "major" version, but the minor version of PostgreSQL can be
critical in some cases, as they minor versions contain fixes for bugs that may or may not have something
to do with problem you seek help for.

Errors by themself may or may not be explained. That is why it is very important to include a scriptthat will duplicate the error. If you go to a doctor and tell him you have a pain, do you expect him to
tell you the cause right away, or would you understand if he asks you what led up to the pain?

Yes, I am a volunteer. In fact, I am a retired PostgreSQL DBA. Hopefully you will now understand thedifference between a transaction and a stand alone query. Good luck. Melvin Davidson
    Cell 720-320-0155
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.
www.youtube.com/unusedhero/videos
Folk Alley - All Folk - 24 Hours a day
www.folkalley.com

From: Malik Rumi <malik(dot)a(dot)rumi(at)gmail(dot)com>
To: Melvin Davidson <melvin6925(at)yahoo(dot)com>
Cc: PgAdmin Support <pgadmin-support(at)postgresql(dot)org>
Sent: Tuesday, November 29, 2016 5:14 PM
Subject: Re: [pgadmin-support] ROLLBACK syntax error in pgadmin4

Ok, I am not trying to be difficult. I am trying to understand. If that sounds defensive, it's because of prior experience on Stack Overflow, where the "experts" tend to be snarky, IMHO.

I did all of this in PGAdmin4, so that is why I posted here. How would I determine what is a PGAdmin4 issue and what is not?

I did tell you my OS in the very first line of my very first post on this issue. I also told you my postgres version on that same line. When you say 'exact', do you mean 9.4.8, instead of just 9.4?

True enough, I did not give you the entire SQL, but I did give you the error, which quoted what I assumed were the relevant portions of the SQL. I'm sorry if that wasn't enough.

I gave you the background on what I had done to find a solution on my own. I also asked a lot of questions which I hoped would clarify my understanding of the problem, but they have not been addressed.

All in all, I thought I was being pretty verbose and specific. Maybe too much so. When you put together the lack of response to my specific questions, and asking for OS and PG version info I already gave, well, it's kinda discouraging. But it's a free service, and I assume you are a volunteer with a life and issues of your own. At least you responded, and fairly soon. A lot of people don't even get that.

I'm going to restart and hope for the best. I've learned that fsync is supposed to be on by default, so if the uncheck of autocommit did what it was supposed to do, I should be fine. If not, I have a backup. I just wanted to learn.

On Tue, Nov 29, 2016 at 12:59 PM, Melvin Davidson <melvin6925(at)yahoo(dot)com> wrote:

Malik,
First things first.1. This is not the correct forum for this problem.
PgAdmin is a utility for managing PostgreSQL.
You are better off re-submitting this problem to pgsql-general(at)postgresql(dot)orgbut you stand a better chance if you also includeA. O/SB. Exact version of PostgreSQLC. SQL code to reproduce the problem.

As for "ERROR: current transaction is aborted, commands ignored until end of transaction block"It means all commands from BEGIN; until the error occurs are rolled back. No further commandsafter the error occurs are executed.
 Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.
www.youtube.com/unusedhero/ videos
Folk Alley - All Folk - 24 Hours a day
www.folkalley.com

From: Malik Rumi <malik(dot)a(dot)rumi(at)gmail(dot)com>
To: Melvin Davidson <melvin6925(at)yahoo(dot)com>
Cc: pgAdmin Support <pgadmin-support(at)postgresql(dot) org>
Sent: Tuesday, November 29, 2016 2:50 PM
Subject: Re: [pgadmin-support] ROLLBACK syntax error in pgadmin4

Thank you. However...

ERROR: current transaction is aborted, commands ignored until end of transaction block

I'm not even sure what this means. Which transaction is it referring to? The rollback or the update?
What is the error and how do I fix it? What is meant by "until end of transaction block"? Does that mean the entire transaction block, or that something might happen after the end of this block?

I did some more googling, and found this from the postgresql list:
If that's what you're getting, the problem was with an earlier command
that returned an error you didn't notice, not with the command you just
ran. https://www.postgresql.org/ message-id/4CAB085C.5030106@ postnewspapers.com.au I suppose that makes sense and applies here, since I came here asking about two consecutive syntax errors in the first place. But I still don't know how to safely back out of this situation. Any further advice? Anyone?

p.s. - I said I had PG 9.4. I assumed everyone would know that meant Postgresql 9.4

On Tue, Nov 29, 2016 at 10:24 AM, Melvin Davidson <melvin6925(at)yahoo(dot)com> wrote:

The problem is, ROLLBACK requires a transaction. So to use it properly you need to
start with a BEGIN statement.
eg:
BEGIN;your_statements;ROLLBACK;

https://www.postgresql.org/ docs/9.4/static/sql-begin.html

https://www.postgresql.org/ docs/9.4/static/sql-rollback. html
fyi, in the future, it is always a good idea to include the PostgreSQL version.
Melvin Davidson

I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.
www.youtube.com/unusedhero/ videos
Folk Alley - All Folk - 24 Hours a day
www.folkalley.com

From: Malik Rumi <malik(dot)a(dot)rumi(at)gmail(dot)com>
To: pgAdmin Support <pgadmin-support(at)postgresql(dot) org>
Sent: Tuesday, November 29, 2016 1:17 PM
Subject: [pgadmin-support] ROLLBACK syntax error in pgadmin4

I amusing PGAdmin4 with PG 9.4 on Ubuntu 16.04 64 bit. I wanted to do an update, but was unsureof the result, so I unselected autocommit and ran explain. It saidonly one row was affected, which I didn’t understand, because itshould have affected at least 2. Then I inferred that the ‘one row’being referred to was the plan, not the rows in my data.ThenI ran the update, again no autocommit, and it was not what Iexpected. So then I added ROLLBACK to the end of the query, ran itagain, and got this error:ERROR:syntax error at or near "ROLLBACK" LINE 1: ...de" SETchildof_id = NULL WHERE codelevelsortseq=1 ROLLBACK;Ithen did a new query, with just ROLLBACK; as I see it explained withan example in the docs:https://www.postgresql.org/ docs/9.4/static/sql-rollback. htmlNevertheless,I got the same error:ERROR:syntax error at or near "ROLLBACK" LINE 1: ...F, VERBOSEON, COSTS ON, BUFFERS OFF, TIMING OFF) ROLLBACK;
Note in this case ROLLBACK; is the only thing on Line 1. The error carat (^) is pointing at the R in both cases. Noother queries have been run in the interim. Tomy surprise, there are very few Google hits for this exact search: "syntaxerror at or near 'ROLLBACK'" About 30 results (0.34 seconds) Thefirst two hits are for a bug report from Nov. of 2006, which wasfixed then and there. I don’t think this is the same bug, if it isa bug. Idid find DISCARD in the docs:https://www.postgresql.org/ docs/9.4/static/sql-discard. htmlhowever, I have not run it because I wanted some clarity here anddidn’t want to interfere with my ROLLBACK, if it would even havethat effect.So,my questions:1.How do I discard this update and get my data back?2.How do I check to make sure it really isn’t committed withoutupsetting my ROLLBACK options? (Yes, I do have a backup I can go to,but I would rather understand what is happening and fix it).4.If it isn’t committed, and this is a bug, can I just close PGAdmin4and/or PG and discard the changes that way?5.Is this a new, different bug?6. What was that 'one row effected' about?