Re: ROLLBACK syntax error in pgadmin4 - Mailing list pgadmin-support
Thank you. However...
I'm not even sure what this means. Which transaction is it referring to? The rollback or the update?ERROR: current transaction is aborted, commands ignored until end of transaction 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.auI 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?
On Tue, Nov 29, 2016 at 10:24 AM, Melvin Davidson <melvin6925@yahoo.com> wrote:
The problem is, ROLLBACK requires a transaction. So to use it properly you need tostart with a BEGIN statement.eg:BEGIN;your_statements;ROLLBACK;fyi, in the future, it is always a good idea to include the PostgreSQL version.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.a.rumi@gmail.com>
To: pgAdmin Support <pgadmin-support@postgresql.org>
Sent: Tuesday, November 29, 2016 1:17 PM
Subject: [pgadmin-support] ROLLBACK syntax error in pgadmin4I 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?
pgadmin-support by date: