Re: ROLLBACK syntax error in pgadmin4

<div style="color:#000; background-color:#fff; font-family:Courier New, courier, monaco, monospace,
sans-serif;font-size:16px"><divid="yui_3_16_0_1_1480447941789_21878"><b>Malik,</b></div><div
id="yui_3_16_0_1_1480447941789_21877"><b><br/></b></div><div id="yui_3_16_0_1_1480447941789_21936"><b
id="yui_3_16_0_1_1480447941789_22068">Firstthings first.</b></div><div id="yui_3_16_0_1_1480447941789_21876"><b
id="yui_3_16_0_1_1480447941789_22067">1.This is not the correct forum for this problem.</b></div><div
id="yui_3_16_0_1_1480447941789_21811"><b><br/></b></div><div dir="ltr" id="yui_3_16_0_1_1480447941789_21812"><b
id="yui_3_16_0_1_1480447941789_22663">PgAdminis a utility for managing PostgreSQL.</b></div><div dir="ltr"
id="yui_3_16_0_1_1480447941789_21937"><b><br/></b></div><div dir="ltr" id="yui_3_16_0_1_1480447941789_21938"><b
id="yui_3_16_0_1_1480447941789_22066">Youare better off re-submitting this problem to pgsql-general</b><wbr
id="yui_3_16_0_1_1480447941789_21944"/><b id="yui_3_16_0_1_1480447941789_22417">@postgresql.org</b></div><div dir="ltr"
id="yui_3_16_0_1_1480447941789_21991"><bid="yui_3_16_0_1_1480447941789_22065">but you stand a better chance if you also
include</b></div><divdir="ltr" id="yui_3_16_0_1_1480447941789_21992"><b id="yui_3_16_0_1_1480447941789_22416">A.
O/S</b></div><divdir="ltr" id="yui_3_16_0_1_1480447941789_22022"><b id="yui_3_16_0_1_1480447941789_22418">B. <font
color="#cd232c"id="yui_3_16_0_1_1480447941789_22569">Exact version of PostgreSQL</font></b></div><div dir="ltr"
id="yui_3_16_0_1_1480447941789_22109"><bid="yui_3_16_0_1_1480447941789_22112">C. <font color="#cd232c">SQL code to
reproducethe problem.</font><br /></b></div><div id="yui_3_16_0_1_1480447941789_21765"><span><br /></span></div><div
dir="ltr"id="yui_3_16_0_1_1480447941789_22211"><b id="yui_3_16_0_1_1480447941789_22328"><span
id="yui_3_16_0_1_1480447941789_22208">Asfor "</span>ERROR: current transaction is aborted, commands ignored until end
oftransaction block"</b></div><div dir="ltr" id="yui_3_16_0_1_1480447941789_22212"><b
id="yui_3_16_0_1_1480447941789_22327">Itmeans all commands from BEGIN; until the error occurs are rolled back. No
furthercommands</b></div><div dir="ltr" id="yui_3_16_0_1_1480447941789_22301"><b
id="yui_3_16_0_1_1480447941789_22662">afterthe error occurs are executed.<br /></b></div><div
id="yui_3_16_0_1_1480447941789_21760"> </div><divclass="signature" id="yui_3_16_0_1_1480447941789_21751"><div
id="RTEContent"><fontsize="4"><span style="font-weight:bold;font-family:arial;color:rgb(0, 0, 255);">Melvin Davidson
</span></font><br/><font id="yui_3_16_0_1_1480447941789_21750" size="3" style="font-weight:bold;"><span
id="yui_3_16_0_1_1480447941789_21759"style="color:rgb(128, 0, 255);">I reserve the right to fantasize.  Whether or not
you</span><br style="color:rgb(128, 0, 255);" /><span id="yui_3_16_0_1_1480447941789_21749" style="color:rgb(128, 0,
255);">wishto share my fantasy is entirely up to you. </span><img data-id="4fec765a-8f8b-58d7-65bf-334e4ce665d7"
src="http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif"style="color:rgb(128, 0, 255);" /><br /></font><font
id="yui_3_16_0_ym19_1_1467319472979_3360"size="3" style="font-weight:bold;">www.youtube.com/unusedhero/videos<br
/></font><fontid="yui_3_16_0_1_1480447941789_21758" size="3"><span id="yui_3_16_0_1_1480447941789_21757"
style="font-weight:bold;font-family:courier;">FolkAlley - All Folk - 24 Hours a day </span><span
id="yui_3_16_0_1_1480447941789_22621"style="font-weight:bold;font-family:courier;"><br />www.folkalley.com<br /><br
/></span></font></div></div><divclass="qtdSeparateBR" id="yui_3_16_0_1_1480447941789_22107"><br /><br /></div><div
class="yahoo_quoted"id="yui_3_16_0_1_1480447941789_22103" style="display: block;"><div
id="yui_3_16_0_1_1480447941789_22102"style="font-family: Courier New, courier, monaco, monospace, sans-serif;
font-size:16px;"><div id="yui_3_16_0_1_1480447941789_22101" style="font-family: HelveticaNeue, Helvetica Neue,
Helvetica,Arial, Lucida Grande, sans-serif; font-size: 16px;"><div dir="ltr"
id="yui_3_16_0_1_1480447941789_22194"><fontface="Arial" id="yui_3_16_0_1_1480447941789_22624" size="2"><hr
id="yui_3_16_0_1_1480447941789_22623"size="1" /><b><span style="font-weight:bold;">From:</span></b> Malik Rumi
<malik.a.rumi@gmail.com><br/><b><span style="font-weight: bold;">To:</span></b> Melvin Davidson
<melvin6925@yahoo.com><br /><b><span style="font-weight: bold;">Cc:</span></b> pgAdmin Support
<pgadmin-support@postgresql.org><br/><b><span style="font-weight: bold;">Sent:</span></b> Tuesday, November 29,
20162:50 PM<br /><b><span style="font-weight: bold;">Subject:</span></b> Re: [pgadmin-support] ROLLBACK syntax error in
pgadmin4<br/></font></div><div class="y_msg_container" id="yui_3_16_0_1_1480447941789_22100"><br /><div
id="yiv2305844011"><divid="yui_3_16_0_1_1480447941789_22099"><div dir="ltr" id="yui_3_16_0_1_1480447941789_22098"><div
id="yui_3_16_0_1_1480447941789_22097"><divid="yui_3_16_0_1_1480447941789_22096"><div
id="yui_3_16_0_1_1480447941789_22095">Thankyou. However...<br clear="none" /><br clear="none" /><div
id="yui_3_16_0_1_1480447941789_22094"style="margin-left:40px;">ERROR: current transaction is aborted, commands ignored
untilend of transaction block<br clear="none" /></div><br clear="none" /></div>I'm not even sure what this means. Which
transactionis it referring to? The rollback or the update?<br clear="none" /></div>What is the error and how do I fix
it?What is meant by "<i><u>until</u></i> end of transaction block"? Does that mean the entire transaction block, or
thatsomething might happen after the end of this block?<br clear="none" /><br clear="none" /></div><div
id="yui_3_16_0_1_1480447941789_22104">Idid some more googling, and found this from the postgresql list:<br clear="none"
/><preid="yui_3_16_0_1_1480447941789_22139" style="margin-left:40px;">If that's what you're getting, the problem was
withan earlier command  
that returned an error you didn't notice, not with the command you just
ran. <a href="https://www.postgresql.org/message-id/4CAB085C.5030106@postnewspapers.com.au"
id="yui_3_16_0_1_1480447941789_22140"rel="nofollow" shape="rect"
target="_blank">https://www.postgresql.org/message-id/4CAB085C.5030106@postnewspapers.com.au</a></pre>I suppose that
makessense and applies here, since I came here asking about two consecutive syntax errors in the first place. But I
stilldon't know how to safely back out of this situation. Any further advice? Anyone?<br clear="none" /></div><div
id="yui_3_16_0_1_1480447941789_22145"><brclear="none" /></div>p.s. - I said I had PG 9.4. I assumed everyone would know
thatmeant Postgresql 9.4<br clear="none" /></div><div class="yiv2305844011yqt1358800897"
id="yiv2305844011yqt78314"><divclass="yiv2305844011gmail_extra" id="yui_3_16_0_1_1480447941789_22146"><br clear="none"
/><divclass="yiv2305844011gmail_quote" id="yui_3_16_0_1_1480447941789_22149">On Tue, Nov 29, 2016 at 10:24 AM, Melvin
Davidson<span dir="ltr" id="yui_3_16_0_1_1480447941789_22148"><<a href="mailto:melvin6925@yahoo.com"
id="yui_3_16_0_1_1480447941789_22147"rel="nofollow" shape="rect" target="_blank"
ymailto="mailto:melvin6925@yahoo.com">melvin6925@yahoo.com</a>></span>wrote:<br clear="none" /><blockquote
class="yiv2305844011gmail_quote"id="yui_3_16_0_1_1480447941789_22153" style="margin:0 0 0 .8ex;border-left:1px #ccc
solid;padding-left:1ex;"><divid="yui_3_16_0_1_1480447941789_22152"><div id="yui_3_16_0_1_1480447941789_22151"
style="color:#000;background-color:#fff;font-family:CourierNew, courier, monaco, monospace,
sans-serif;font-size:16px;"><divid="yiv2305844011m_5682970348618810673yui_3_16_0_1_1480442930679_8438"><b
id="yui_3_16_0_1_1480447941789_22150">Theproblem is, ROLLBACK requires a transaction. So to use it properly you need to
<brclear="none" /></b></div><div dir="ltr"
id="yiv2305844011m_5682970348618810673yui_3_16_0_1_1480442930679_8400"><b>startwith a BEGIN statement.</b></div><div
id="yiv2305844011m_5682970348618810673yui_3_16_0_1_1480442930679_8439"><b><brclear="none" /></b></div><div
id="yiv2305844011m_5682970348618810673yui_3_16_0_1_1480442930679_8387"><b>eg:</b></div><div
id="yiv2305844011m_5682970348618810673yui_3_16_0_1_1480442930679_8399"><b><brclear="none" /></b></div><div
id="yiv2305844011m_5682970348618810673yui_3_16_0_1_1480442930679_8375"><b>BEGIN;</b></div><div
id="yiv2305844011m_5682970348618810673yui_3_16_0_1_1480442930679_8347"><b
id="yiv2305844011m_5682970348618810673yui_3_16_0_1_1480442930679_8847">your_statements;</b></div><div
id="yiv2305844011m_5682970348618810673yui_3_16_0_1_1480442930679_8363"><b>ROLLBACK;<brclear="none" /></b></div><div
id="yiv2305844011m_5682970348618810673yui_3_16_0_1_1480442930679_8313"><b><span><brclear="none" /></span></b></div><div
id="yiv2305844011m_5682970348618810673yui_3_16_0_1_1480442930679_8630"><b
id="yiv2305844011m_5682970348618810673yui_3_16_0_1_1480442930679_8848"><a
class="yiv2305844011m_5682970348618810673edited-link-editor"
href="https://www.postgresql.org/docs/9.4/static/sql-begin.html"
id="yiv2305844011m_5682970348618810673yui_3_16_0_1_1480442930679_8631"rel="nofollow" shape="rect"
target="_blank">https://www.postgresql.org/docs/9.4/static/sql-begin.html<br clear="none" /></a></b></div><div
id="yiv2305844011m_5682970348618810673yui_3_16_0_1_1480442930679_8629"><b><brclear="none" /></b></div><div
id="yiv2305844011m_5682970348618810673yui_3_16_0_1_1480442930679_8850"><b
id="yiv2305844011m_5682970348618810673yui_3_16_0_1_1480442930679_8849"><a
class="yiv2305844011m_5682970348618810673edited-link-editor"
href="https://www.postgresql.org/docs/9.4/static/sql-rollback.html"
id="yiv2305844011m_5682970348618810673yui_3_16_0_1_1480442930679_8786"rel="nofollow" shape="rect"
target="_blank">https://www.postgresql.org/docs/9.4/static/sql-rollback. html</a></b></div><div dir="ltr"
id="yiv2305844011m_5682970348618810673yui_3_16_0_1_1480442930679_8851"><b><a
class="yiv2305844011m_5682970348618810673edited-link-editor"
href="https://www.postgresql.org/docs/9.4/static/sql-rollback.html"
id="yiv2305844011m_5682970348618810673yui_3_16_0_1_1480442930679_8786"rel="nofollow" shape="rect" target="_blank"><br
clear="none"/></a></b></div><div id="yiv2305844011m_5682970348618810673yui_3_16_0_1_1480442930679_8314"><div dir="ltr"
id="yiv2305844011m_5682970348618810673yui_3_16_0_1_1480442930679_8853"><b
id="yiv2305844011m_5682970348618810673yui_3_16_0_1_1480442930679_8852">fyi,in the future, it is always a good idea to
includethe PostgreSQL version.</b></div><div dir="ltr"
id="yiv2305844011m_5682970348618810673yui_3_16_0_1_1480442930679_8854"><b><brclear="none" /></b></div></div><div
class="yiv2305844011m_5682970348618810673signature"
id="yiv2305844011m_5682970348618810673yui_3_16_0_1_1480442930679_8318"><div
id="yiv2305844011m_5682970348618810673RTEContent"><font
id="yiv2305844011m_5682970348618810673yui_3_16_0_1_1480442930679_8502"size="4"><span
id="yiv2305844011m_5682970348618810673yui_3_16_0_1_1480442930679_8501"
style="font-weight:bold;font-family:arial;color:rgb(0,0,255);">MelvinDavidson </span></font><br clear="none" /><font
id="yiv2305844011m_5682970348618810673yui_3_16_0_1_1480442930679_8671"size="3"><br clear="none" /></font><font
id="yiv2305844011m_5682970348618810673yui_3_16_0_1_1480442930679_8441"size="3" style="font-weight:bold;"><span
id="yiv2305844011m_5682970348618810673yui_3_16_0_1_1480442930679_8440"style="color:rgb(128,0,255);">I reserve the right
tofantasize.  Whether or not you </span><br clear="none" style="color:rgb(128,0,255);" /><span
id="yiv2305844011m_5682970348618810673yui_3_16_0_1_1480442930679_8457"style="color:rgb(128,0,255);">wish to share my
fantasyis entirely up to you. </span><img data-id="2a3e9983-7850-4e47-54d4-4af9c0f91191"
src="http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif"style="color:rgb(128,0,255);" /><br clear="none"
/></font><fontid="yiv2305844011m_5682970348618810673yui_3_16_0_ym19_1_1467319472979_3360" size="3"
style="font-weight:bold;"><ahref="http://www.youtube.com/unusedhero/videos" id="yui_3_16_0_1_1480447941789_22647"
rel="nofollow"shape="rect" target="_blank">www.youtube.com/unusedhero/ videos</a><br clear="none" /></font><font
id="yiv2305844011m_5682970348618810673yui_3_16_0_1_1480442930679_8670"size="3"><span
id="yiv2305844011m_5682970348618810673yui_3_16_0_1_1480442930679_8669"
style="font-weight:bold;font-family:courier;">FolkAlley - All Folk - 24 Hours a day </span><span
id="yiv2305844011m_5682970348618810673yui_3_16_0_1_1480442930679_8673"style="font-weight:bold;font-family:courier;"><br
clear="none"/><a href="http://www.folkalley.com/" rel="nofollow" shape="rect" target="_blank">www.folkalley.com</a><br
clear="none"/><br clear="none" /></span></font></div></div><div class="yiv2305844011m_5682970348618810673qtdSeparateBR"
id="yiv2305844011m_5682970348618810673yui_3_16_0_1_1480442930679_8442"><brclear="none" /><br clear="none" /></div><div
class="yiv2305844011m_5682970348618810673yahoo_quoted"
id="yiv2305844011m_5682970348618810673yui_3_16_0_1_1480442930679_8446"style="display:block;"><div
id="yiv2305844011m_5682970348618810673yui_3_16_0_1_1480442930679_8445"style="font-family:Courier New, courier, monaco,
monospace,sans-serif;font-size:16px;"><div id="yiv2305844011m_5682970348618810673yui_3_16_0_1_1480442930679_8444"
style="font-family:HelveticaNeue,Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif;font-size:16px;"><div
dir="ltr"id="yiv2305844011m_5682970348618810673yui_3_16_0_1_1480442930679_8503"><font face="Arial"
id="yiv2305844011m_5682970348618810673yui_3_16_0_1_1480442930679_8674"size="2"> </font><hr size="1" /><b><span
style="font-weight:bold;">From:</span></b>Malik Rumi <<a href="mailto:malik.a.rumi@gmail.com"
id="yui_3_16_0_1_1480447941789_22630"rel="nofollow" shape="rect" target="_blank"
ymailto="mailto:malik.a.rumi@gmail.com">malik.a.rumi@gmail.com</a>><brclear="none" /><b><span
style="font-weight:bold;">To:</span></b>pgAdmin Support <<a href="mailto:pgadmin-support@postgresql.org"
rel="nofollow"shape="rect" target="_blank" ymailto="mailto:pgadmin-support@postgresql.org">pgadmin-support@postgresql.
org</a>><br clear="none" /><b><span style="font-weight:bold;">Sent:</span></b> Tuesday, November 29, 2016 1:17 PM<br
clear="none"/><b><span style="font-weight:bold;">Subject:</span></b> [pgadmin-support] ROLLBACK syntax error in
pgadmin4<brclear="none" /></div><div id="yui_3_16_0_1_1480447941789_22718"><div class="yiv2305844011h5"
id="yui_3_16_0_1_1480447941789_22717"><divclass="yiv2305844011m_5682970348618810673y_msg_container"
id="yiv2305844011m_5682970348618810673yui_3_16_0_1_1480442930679_8443"><brclear="none" /><div
id="yiv2305844011m_5682970348618810673yiv6295819857"><divdir="ltr"
id="yiv2305844011m_5682970348618810673yui_3_16_0_1_1480442930679_8447">Iam using PGAdmin4 with PG 9.4 on Ubuntu 16.04
64bit. I wanted to do an update, but was unsure of the result, so I unselected autocommit and ran explain. It said only
onerow was affected, which I didn’t understand, because it should have affected at least 2. Then I inferred that the
‘onerow’ being referred to was the plan, not the rows in my data. <div
class="yiv2305844011m_5682970348618810673yiv6295819857gmail-western"
style="margin-bottom:0in;line-height:100%;"></div><div
class="yiv2305844011m_5682970348618810673yiv6295819857gmail-western"
id="yiv2305844011m_5682970348618810673yui_3_16_0_1_1480442930679_8448"style="margin-bottom:0in;line-height:100%;">Then
Iran the update, again no autocommit, and it was not what I expected. So then I added ROLLBACK to the end of the query,
ranit again, and got this error:</div><div class="yiv2305844011m_5682970348618810673yiv6295819857gmail-western"
id="yiv2305844011m_5682970348618810673yui_3_16_0_1_1480442930679_8449"
style="margin-bottom:0in;line-height:100%;margin-left:40px;">ERROR:syntax error at or near "ROLLBACK" LINE 1: ...de"
SETchildof_id = NULL WHERE codelevelsortseq=1 ROLLBACK;</div><div
class="yiv2305844011m_5682970348618810673yiv6295819857gmail-western"style="margin-bottom:0in;line-height:100%;">I then
dida new query, with just ROLLBACK; as I see it explained with an example in the docs: <a
href="https://www.postgresql.org/docs/9.4/static/sql-rollback.html"rel="nofollow" shape="rect"
target="_blank">https://www.postgresql.org/docs/9.4/static/sql-rollback. html</a></div><div
class="yiv2305844011m_5682970348618810673yiv6295819857gmail-western"
id="yiv2305844011m_5682970348618810673yui_3_16_0_1_1480442930679_8451"
style="margin-bottom:0in;line-height:100%;">Nevertheless,I got the same error:</div><div
class="yiv2305844011m_5682970348618810673yiv6295819857gmail-western"
style="margin-bottom:0in;line-height:100%;margin-left:40px;">ERROR:syntax error at or near "ROLLBACK" LINE 1: ...F,
VERBOSEON, COSTS ON, BUFFERS OFF, TIMING OFF) ROLLBACK;</div><br clear="none" />Note in this case ROLLBACK; is the only
thingon Line 1. The error carat (^) is pointing at the R in both cases. No other queries have been run in the interim.
<divclass="yiv2305844011m_5682970348618810673yiv6295819857gmail-western"
id="yiv2305844011m_5682970348618810673yui_3_16_0_1_1480442930679_8455"style="margin-bottom:0in;line-height:100%;">To my
surprise,there are very few Google hits for this exact search: </div><div
class="yiv2305844011m_5682970348618810673yiv6295819857gmail-western"
style="margin-bottom:0in;line-height:100%;margin-left:40px;">"syntaxerror at or near 'ROLLBACK'" About 30 results (0.34
seconds)</div><div class="yiv2305844011m_5682970348618810673yiv6295819857gmail-western"
style="margin-bottom:0in;line-height:100%;">Thefirst two hits are for a bug report from Nov. of 2006, which was fixed
thenand there. I don’t think this is the same bug, if it is a bug. </div><div
class="yiv2305844011m_5682970348618810673yiv6295819857gmail-western"
id="yiv2305844011m_5682970348618810673yui_3_16_0_1_1480442930679_8453"style="margin-bottom:0in;line-height:100%;">I did
findDISCARD in the docs: <a href="https://www.postgresql.org/docs/9.4/static/sql-discard.html" rel="nofollow"
shape="rect"target="_blank">https://www.postgresql.org/ docs/9.4/static/sql-discard. html</a> however, I have not run
itbecause I wanted some clarity here and didn’t want to interfere with my ROLLBACK, if it would even have that
effect.</div><divclass="yiv2305844011m_5682970348618810673yiv6295819857gmail-western"
style="margin-bottom:0in;line-height:100%;">So,my questions:</div><div
class="yiv2305844011m_5682970348618810673yiv6295819857gmail-western"style="margin-bottom:0in;line-height:100%;">1. How
doI discard this update and get my data back?</div><div
class="yiv2305844011m_5682970348618810673yiv6295819857gmail-western"id="yui_3_16_0_1_1480447941789_22721"
style="margin-bottom:0in;line-height:100%;">2.How do I check to make sure it really isn’t committed without upsetting
myROLLBACK options? (Yes, I do have a backup I can go to, but I would rather understand what is happening and fix
it).</div><divclass="yiv2305844011m_5682970348618810673yiv6295819857gmail-western"
style="margin-bottom:0in;line-height:100%;">4.If it isn’t committed, and this is a bug, can I just close PGAdmin4
and/orPG and discard the changes that way?</div><div
class="yiv2305844011m_5682970348618810673yiv6295819857gmail-western"id="yui_3_16_0_1_1480447941789_22722"
style="margin-bottom:0in;line-height:100%;">5.Is this a new, different bug?</div><div
class="yiv2305844011m_5682970348618810673yiv6295819857gmail-western"id="yui_3_16_0_1_1480447941789_22724"
style="margin-bottom:0in;line-height:100%;">6.What was that 'one row effected' about?<br clear="none"
/></div></div></div><brclear="none" /><br clear="none"
/></div></div></div></div></div></div></div></div></blockquote></div><brclear="none" /></div></div></div></div><br
/><br/></div></div></div></div></div>