Re: 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-begin.html>*
>
> *https://www.postgresql.org/docs/9.4/static/sql-rollback.html
> <https://www.postgresql.org/docs/9.4/static/sql-rollback.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
> Cell 720-320-0155 <(720)%20320-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:* 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 am using PGAdmin4 with PG 9.4 on Ubuntu 16.04 64 bit. I wanted to do an
> update, but was unsure of the result, so I unselected autocommit and ran
> explain. It said only one row was affected, which I didn’t understand,
> because it should have affected at least 2. Then I inferred that the ‘one
> row’ being referred to was the plan, not the rows in my data.
> Then I ran the update, again no autocommit, and it was not what I
> expected. So then I added ROLLBACK to the end of the query, ran it again,
> and got this error:
> ERROR: syntax error at or near "ROLLBACK" LINE 1: ...de" SET childof_id =
> NULL WHERE codelevelsortseq=1 ROLLBACK;
> I then did a new query, with just ROLLBACK; as I see it explained with an
> example in the docs: https://www.postgresql.org/
> docs/9.4/static/sql-rollback.html
> Nevertheless, I got the same error:
> ERROR: syntax error at or near "ROLLBACK" LINE 1: ...F, VERBOSE ON, 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. No other queries have been run in
> the interim.
> To my surprise, there are very few Google hits for this exact search:
> "syntax error at or near 'ROLLBACK'" About 30 results (0.34 seconds)
> The first two hits are for a bug report from Nov. of 2006, which was fixed
> then and there. I don’t think this is the same bug, if it is a bug.
> I did find DISCARD in the docs: https://www.postgresql.org/
> docs/9.4/static/sql-discard.html however, I have not run it because I
> wanted some clarity here and didn’t want to interfere with my ROLLBACK, if
> it would even have that 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 without upsetting
> 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 PGAdmin4
> and/or PG and discard the changes that way?
> 5. Is this a new, different bug?
> 6. What was that 'one row effected' about?
>
>
>