Thread: Savepoints in PgAdmin
<div class="Section1"><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">Hi,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-GB" style="font-size: 10.0pt;font-family:Arial">I am using PgAdmin 1.10.0 with Postgresql 8.3.7 on Windows XP Pro (SP3).</span></font><p class="MsoNormal"><fontface="Arial" size="2"><span lang="EN-GB" style="font-size: 10.0pt;font-family:Arial">The problem I am facing deals with savepoint and rollback management in PgAdmin.</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span lang="EN-GB" style="font-size: 10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-GB" style="font-size: 10.0pt;font-family:Arial">I initiate a transaction, then I run different queries, create a savepoint :</span></font><p class="MsoNormal"><fontface="Arial" size="2"><span lang="EN-GB" style="font-size: 10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-GB" style="font-size: 10.0pt;font-family:Arial">begin;</span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-GB" style="font-size: 10.0pt;font-family:Arial">CREATE temp TABLE toto (id int);</span></font><p class="MsoNormal"><font face="Arial" size="2"><spanlang="EN-GB" style="font-size: 10.0pt;font-family:Arial">INSERT INTO toto VALUES (1);</span></font><p class="MsoNormal"><font face="Arial" size="2"><spanlang="EN-GB" style="font-size: 10.0pt;font-family:Arial">SAVEPOINT my_save;</span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-GB"style="font-size: 10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-GB" style="font-size: 10.0pt;font-family:Arial">Then, I execute a wrong query :</span></font><p class="MsoNormal"><font face="Arial" size="2"><spanlang="EN-GB" style="font-size: 10.0pt;font-family:Arial">SELECT * FROM tot;</span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-GB"style="font-size: 10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-GB" style="font-size: 10.0pt;font-family:Arial">I get an error message :</span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-GB"style="font-size: 10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Times New Roman" size="3"><span lang="EN-GB" style="font-size:12.0pt">ERROR:relation "toto" does not exist<br /><br /><br /> ********** Erreur **********<br /><br />ERROR: relation "toto" does not exist<br /> État SQL :42P01</span></font><p class="MsoNormal"><font face="Times New Roman"size="3"><span lang="EN-GB" style="font-size:12.0pt"> </span></font><p class="MsoNormal"><font face="Times New Roman"size="3"><span lang="EN-GB" style="font-size:12.0pt">But I can’t rollback to my savepoint, because a complete rollbackhas already been executed by PgAdmin :</span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-GB"style="font-size: 10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-GB" style="font-size: 10.0pt;font-family:Arial">rollback TO my_save;</span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-GB"style="font-size: 10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Times New Roman" size="3"><span lang="EN-GB" style="font-size:12.0pt">ERROR:ROLLBACK TO SAVEPOINT can only be used in transaction blocks<br /><br /><br /> **********Erreur **********<br /><br /> ERROR: ROLLBACK TO SAVEPOINT can only be used in transaction blocks<br /> État SQL:25P01</span></font><p class="MsoNormal"><font face="Times New Roman" size="3"><span lang="EN-GB" style="font-size:12.0pt"> </span></font><pclass="MsoNormal"><font face="Times New Roman" size="3"><span lang="EN-GB" style="font-size:12.0pt">Thisproblem seems to come from the fact that PgAdmin executes code in a single transaction statement…</span></font><pclass="MsoNormal"><font face="Times New Roman" size="3"><span lang="EN-GB" style="font-size:12.0pt">Howis it possible to create different transaction statements within PgAdmin, in order to use savepoints?</span></font><p class="MsoNormal"><font face="Times New Roman" size="3"><span lang="EN-GB" style="font-size:12.0pt"> </span></font><pclass="MsoNormal"><font face="Times New Roman" size="3"><span lang="EN-GB" style="font-size:12.0pt">Thanksin advance for your help.</span></font><p class="MsoNormal"><font face="Times New Roman" size="3"><spanlang="EN-GB" style="font-size:12.0pt"> </span></font><p class="MsoNormal"><font face="Times New Roman" size="3"><spanlang="EN-GB" style="font-size:12.0pt">Regards,</span></font><p class="MsoNormal"><font face="Times New Roman"size="3"><span lang="EN-GB" style="font-size:12.0pt"> </span></font><p class="MsoNormal"><font face="Times New Roman"size="3"><span lang="EN-GB" style="font-size:12.0pt">C. Duprez</span></font><font face="Arial" size="2"><span lang="EN-GB"style="font-size:10.0pt;font-family:Arial"></span></font><p class="MsoNormal"><font face="Times New Roman" size="3"><spanlang="EN-GB" style="font-size:12.0pt"> </span></font></div>
Le jeudi 20 août 2009 à 16:16:07, DUPREZ Cédric a écrit :
> [...]
> This problem seems to come from the fact that PgAdmin executes code in a
> single transaction statement...
>
I'm sure it doesn't. Try to do the following steps:
BEGIN;
CREATE temp TABLE toto (id int);
INSERT INTO toto VALUES (1);
SAVEPOINT my_save;
Then:
INSERT INTO toto VALUES (2);
Then:
SELECT * FROM toto;
You should get two values. Then rollback to my_save:
ROLLBACK TO my_save;
And finally:
SELECT * FROM toto;
You should get only one value, which demonstrates than you can use savepoint.
>
> SAVEPOINT my_save;
> How is it possible to create different transaction statements within
> PgAdmin, in order to use savepoints ?
>
You're right on one point. If it fails, pgAdmin automatically does a ROLLBACK.
In the frmQuery::completeQuery() method, there is this code:
// If the transaction aborted for some reason, issue a rollback to cleanup.
if (conn->GetTxStatus() == PGCONN_TXSTATUS_INERROR) conn->ExecuteVoid(wxT("ROLLBACK;"));
We should perhaps have an option to enable/disable this behaviour. Dave, what
do you think about this? should I add a ticket on this issue?
Thanks for your report.
--
Guillaume.http://www.postgresqlfr.orghttp://dalibo.com
On Thu, Aug 20, 2009 at 10:09 PM, Guillaume
Lelarge<guillaume@lelarge.info> wrote:
> // If the transaction aborted for some reason, issue a rollback to cleanup.
> if (conn->GetTxStatus() == PGCONN_TXSTATUS_INERROR)
> conn->ExecuteVoid(wxT("ROLLBACK;"));
>
> We should perhaps have an option to enable/disable this behaviour. Dave, what
> do you think about this? should I add a ticket on this issue?
Yes, I *think* it'd be appropriate to make that optional. If memory
serves, it is only there to cleanup for the user. I'm not in a
position to check at the moment though I'm afraid.
--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com
Le vendredi 21 août 2009 à 09:14:22, Dave Page a écrit :
> On Thu, Aug 20, 2009 at 10:09 PM, Guillaume
>
> Lelarge<guillaume@lelarge.info> wrote:
> > // If the transaction aborted for some reason, issue a rollback to
> > cleanup. if (conn->GetTxStatus() == PGCONN_TXSTATUS_INERROR)
> > conn->ExecuteVoid(wxT("ROLLBACK;"));
> >
> > We should perhaps have an option to enable/disable this behaviour. Dave,
> > what do you think about this? should I add a ticket on this issue?
>
> Yes, I *think* it'd be appropriate to make that optional. If memory
> serves, it is only there to cleanup for the user. I'm not in a
> position to check at the moment though I'm afraid.
Thanks. Logged in as http://code.pgadmin.org/trac/ticket/47 .
--
Guillaume.http://www.postgresqlfr.orghttp://dalibo.com
Le vendredi 21 août 2009 à 09:51:32, Guillaume Lelarge a écrit :
> Le vendredi 21 août 2009 à 09:14:22, Dave Page a écrit :
> > On Thu, Aug 20, 2009 at 10:09 PM, Guillaume
> >
> > Lelarge<guillaume@lelarge.info> wrote:
> > > // If the transaction aborted for some reason, issue a rollback to
> > > cleanup. if (conn->GetTxStatus() == PGCONN_TXSTATUS_INERROR)
> > > conn->ExecuteVoid(wxT("ROLLBACK;"));
> > >
> > > We should perhaps have an option to enable/disable this behaviour.
> > > Dave, what do you think about this? should I add a ticket on this
> > > issue?
> >
> > Yes, I *think* it'd be appropriate to make that optional. If memory
> > serves, it is only there to cleanup for the user. I'm not in a
> > position to check at the moment though I'm afraid.
>
> Thanks. Logged in as http://code.pgadmin.org/trac/ticket/47 .
Here is a patch to add this feature. It adds a new setting ("Disable Auto
ROOLBACK") in the Query tag of the Options Dialog. This is of course for 1.11.
Comments?
--
Guillaume.http://www.postgresqlfr.orghttp://dalibo.com
Hi guys. Herewith submission for tweaks to the Code completion / prompt feature :- Minor tweak 1: Remove additional space inserted After the Ctrl-Space selection, two spaces are inserted. This is unnecessaryunless it was to cater for key sequences other than Ctrl-Space. (I am not aware of how to change the initiation sequence from Ctrl-Space) Minor tweak 2: Selection list scrolling Allow jump-to scrolling of the selection list using key-strokes. Currently onlythe arrow keys are of any use. (First letter typing escapes the dialog and inserts that letter) Enhancement Request: Additional invocation points Currently Ctrl-Space only seems to work for the first field selection after a WHERE clause If possible, allow invocation after AND, OR, JOIN, JOIN xxxx ON etc. Also allow it after the periodwith TableName-dot-Fieldname syntax. Robin. Johannesburg, South Africa.
2009/8/31 Guillaume Lelarge <guillaume@lelarge.info>:
> Here is a patch to add this feature. It adds a new setting ("Disable Auto
> ROOLBACK") in the Query tag of the Options Dialog. This is of course for 1.11.
>
> Comments?
ROLLBACK I hope :-)
Looks good - my only comment is whether the option should be to
disable or enable it. Enable seems cleaner, but of course, disable
makes sense because it's disabling the current behaviour.
Magnus - what do you think?
--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com
Le mardi 1 septembre 2009 à 12:44:08, Dave Page a écrit :
> 2009/8/31 Guillaume Lelarge <guillaume@lelarge.info>:
> > Here is a patch to add this feature. It adds a new setting ("Disable Auto
> > ROOLBACK") in the Query tag of the Options Dialog. This is of course for
> > 1.11.
> >
> > Comments?
>
> ROLLBACK I hope :-)
>
Yes :)
> Looks good - my only comment is whether the option should be to
> disable or enable it. Enable seems cleaner, but of course, disable
> makes sense because it's disabling the current behaviour.
>
Don't know either. My first version of this patch was Enable. But I changed
this to Disable because it was easier this way to write the handling of the
option and to keep the current behavior.
> Magnus - what do you think?
Yes, any comments?
--
Guillaume.http://www.postgresqlfr.orghttp://dalibo.com
On Wed, Sep 2, 2009 at 10:50, Guillaume Lelarge<guillaume@lelarge.info> wrote:
> Le mardi 1 septembre 2009 à 12:44:08, Dave Page a écrit :
>> 2009/8/31 Guillaume Lelarge <guillaume@lelarge.info>:
>> > Here is a patch to add this feature. It adds a new setting ("Disable Auto
>> > ROOLBACK") in the Query tag of the Options Dialog. This is of course for
>> > 1.11.
>> >
>> > Comments?
>>
>> ROLLBACK I hope :-)
>>
>
> Yes :)
heh.
>> Looks good - my only comment is whether the option should be to
>> disable or enable it. Enable seems cleaner, but of course, disable
>> makes sense because it's disabling the current behaviour.
>>
>
> Don't know either. My first version of this patch was Enable. But I changed
> this to Disable because it was easier this way to write the handling of the
> option and to keep the current behavior.
>
>> Magnus - what do you think?
>
> Yes, any comments?
Good question. In general we shouldn't change the default behavior in
a backpatch, but I think the use-case for the current behavior is
pretty limited.
AIUI, nothing at all changes for single-statement queries, right? It
would only affect those that explicitly open a transaction. In which
case I think it's a reasonable default to behave the same way as psql,
which means change the default for pgadmin.
Specifically on the patch I don't like an option named "disabled auto
rollback". Negations in options suck. It shuold be "Enable auto
rollback", and then whatever the default is can be argued. I'd also
find it very useful if this was available as a checkbox on the query
menu, not just in the options dialog. Thoughts?
-- Magnus HaganderMe: http://www.hagander.net/Work: http://www.redpill-linpro.com/
On Thu, Sep 3, 2009 at 12:57 PM, Magnus Hagander<magnus@hagander.net> wrote: > Good question. In general we shouldn't change the default behavior in > a backpatch, but I think the use-case for the current behavior is > pretty limited. It's not being back-patched. That code has been there for years, with only one complaint that I'm aware of. The complaint is a valid one though, which is why I think we should add the option for 1.12. > AIUI, nothing at all changes for single-statement queries, right? It > would only affect those that explicitly open a transaction. In which > case I think it's a reasonable default to behave the same way as psql, > which means change the default for pgadmin. > > Specifically on the patch I don't like an option named "disabled auto > rollback". Negations in options suck. It shuold be "Enable auto > rollback", and then whatever the default is can be argued. I'd also > find it very useful if this was available as a checkbox on the query > menu, not just in the options dialog. Thoughts? Which would do what - override the default for that window only, or control the default as well? I prefer the former, but some of the other options are stored for all future query windows, so there may be some inconsistency there. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
2009/9/3 Dave Page <dpage@pgadmin.org>: > On Thu, Sep 3, 2009 at 12:57 PM, Magnus Hagander<magnus@hagander.net> wrote: > >> Good question. In general we shouldn't change the default behavior in >> a backpatch, but I think the use-case for the current behavior is >> pretty limited. > > It's not being back-patched. That code has been there for years, with > only one complaint that I'm aware of. The complaint is a valid one > though, which is why I think we should add the option for 1.12. > >> AIUI, nothing at all changes for single-statement queries, right? It >> would only affect those that explicitly open a transaction. In which >> case I think it's a reasonable default to behave the same way as psql, >> which means change the default for pgadmin. >> >> Specifically on the patch I don't like an option named "disabled auto >> rollback". Negations in options suck. It shuold be "Enable auto >> rollback", and then whatever the default is can be argued. I'd also >> find it very useful if this was available as a checkbox on the query >> menu, not just in the options dialog. Thoughts? > > Which would do what - override the default for that window only, or > control the default as well? I prefer the former, but some of the > other options are stored for all future query windows, so there may be > some inconsistency there. That's a good question. I'd say for that window only, yes, and perhaps look over the other options to make them consistent? -- Magnus HaganderMe: http://www.hagander.net/Work: http://www.redpill-linpro.com/
Le jeudi 3 septembre 2009 à 14:02:24, Dave Page a écrit : > On Thu, Sep 3, 2009 at 12:57 PM, Magnus Hagander<magnus@hagander.net> wrote: > > Good question. In general we shouldn't change the default behavior in > > a backpatch, but I think the use-case for the current behavior is > > pretty limited. > > It's not being back-patched. That code has been there for years, with > only one complaint that I'm aware of. The complaint is a valid one > though, which is why I think we should add the option for 1.12. > I also think this is 1.12 only. > > AIUI, nothing at all changes for single-statement queries, right? It > > would only affect those that explicitly open a transaction. In which > > case I think it's a reasonable default to behave the same way as psql, > > which means change the default for pgadmin. > > > > Specifically on the patch I don't like an option named "disabled auto > > rollback". Negations in options suck. It shuold be "Enable auto > > rollback", and then whatever the default is can be argued. I'd also > > find it very useful if this was available as a checkbox on the query > > menu, not just in the options dialog. Thoughts? > > Which would do what - override the default for that window only, or > control the default as well? I prefer the former, but some of the > other options are stored for all future query windows, so there may be > some inconsistency there. I'll change it to "Enable auto rollback", and preserve the old default value. Having it on a menu in the query tool would be really useful. I don't have strong feelings on wether it should override the default. Actually, I think it would be better to overrid it only for this window. -- Guillaume.http://www.postgresqlfr.orghttp://dalibo.com
Le jeudi 3 septembre 2009 à 14:08:54, Guillaume Lelarge a écrit : > Le jeudi 3 septembre 2009 à 14:02:24, Dave Page a écrit : > > On Thu, Sep 3, 2009 at 12:57 PM, Magnus Hagander<magnus@hagander.net> wrote: > > > Good question. In general we shouldn't change the default behavior in > > > a backpatch, but I think the use-case for the current behavior is > > > pretty limited. > > > > It's not being back-patched. That code has been there for years, with > > only one complaint that I'm aware of. The complaint is a valid one > > though, which is why I think we should add the option for 1.12. > > I also think this is 1.12 only. > > > > AIUI, nothing at all changes for single-statement queries, right? It > > > would only affect those that explicitly open a transaction. In which > > > case I think it's a reasonable default to behave the same way as psql, > > > which means change the default for pgadmin. > > > > > > Specifically on the patch I don't like an option named "disabled auto > > > rollback". Negations in options suck. It shuold be "Enable auto > > > rollback", and then whatever the default is can be argued. I'd also > > > find it very useful if this was available as a checkbox on the query > > > menu, not just in the options dialog. Thoughts? > > > > Which would do what - override the default for that window only, or > > control the default as well? I prefer the former, but some of the > > other options are stored for all future query windows, so there may be > > some inconsistency there. > > I'll change it to "Enable auto rollback", and preserve the old default > value. Having it on a menu in the query tool would be really useful. I > don't have strong feelings on wether it should override the default. > Actually, I think it would be better to overrid it only for this window. Done. Here is the new patch. The check menu item behaves the same way than the others, ie it completely controls the option value. I think it is more consistent this way. -- Guillaume.http://www.postgresqlfr.orghttp://dalibo.com
2009/9/3 Guillaume Lelarge <guillaume@lelarge.info>: > Done. Here is the new patch. The check menu item behaves the same way than the > others, ie it completely controls the option value. I think it is more > consistent this way. Yeah, that looks how I'd expect. I think we should add a TODO to copy all the options that are in the Query Tool to frmOptions, which can be used to set defaults, to be overridden on a per-window basis from the window. Thoughts? -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
On Thu, Sep 3, 2009 at 21:09, Dave Page<dpage@pgadmin.org> wrote: > 2009/9/3 Guillaume Lelarge <guillaume@lelarge.info>: > >> Done. Here is the new patch. The check menu item behaves the same way than the >> others, ie it completely controls the option value. I think it is more >> consistent this way. > > Yeah, that looks how I'd expect. > > I think we should add a TODO to copy all the options that are in the > Query Tool to frmOptions, which can be used to set defaults, to be > overridden on a per-window basis from the window. Thoughts? Yeah, that would be useful. +1. -- Magnus HaganderMe: http://www.hagander.net/Work: http://www.redpill-linpro.com/
Le jeudi 3 septembre 2009 à 21:09:56, Dave Page a écrit : > 2009/9/3 Guillaume Lelarge <guillaume@lelarge.info>: > > Done. Here is the new patch. The check menu item behaves the same way > > than the others, ie it completely controls the option value. I think it > > is more consistent this way. > > Yeah, that looks how I'd expect. > Thanks. Commited. > I think we should add a TODO to copy all the options that are in the > Query Tool to frmOptions, which can be used to set defaults, to be > overridden on a per-window basis from the window. Thoughts? New ticket: http://code.pgadmin.org/trac/ticket/55 -- Guillaume.http://www.postgresqlfr.orghttp://dalibo.com