Statement-level rollback

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
28 messages Options
12
Reply | Threaded
Open this post in threaded view
|

Re: Statement-level rollback

Alvaro Herrera-9
On 2018-Dec-08, Andres Freund wrote:

> On 2018-12-08 17:10:27 -0300, Alvaro Herrera wrote:

> > This is what patch 0001 does -- it's only allowed in the connection
> > string, or on ALTER USER / ALTER DATABASE.  Setting it in
> > postgresql.conf is forbidden, as well as changing from transaction to
> > statement in SET (the opposite is allowed, though.)
>
> I don't think allowing to set it on a per-user basis is acceptable
> either, it still leaves the client in a state where they'll potentially
> be confused about it.

Hmm, true.

> Do you have a proposal to address the issue that this makes it just
> about impossible to write UDFs in a safe way?

Not yet, but I'll give it a think next week.

--
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Reply | Threaded
Open this post in threaded view
|

RE: Statement-level rollback

Tsunakawa, Takayuki
In reply to this post by Andres Freund
From: Andres Freund [mailto:[hidden email]]
> Isn't the realistic scenario for migrations that people will turn this
> feature on on a more global basis? If they need to do per transaction choices,
> that makes this much less useful for easy migrations.

Agreed.  My approach of per transaction choice may be overkill.  Actually, I didn't think per-transaction change was really necessary in practice.  But I didn't think of any reason to prohibit per transaction change, so I just wanted to allow flexibility.

I think per database/user change (ALTER DATABASE/USER) can be useful, in cases where applications are migrated from other DBMSs to a PostgreSQL instance.  That is, database consolidation for easier data analytics and database management.  One database/schema holds data for a PostgreSQL application, and another one stores data for a migrated application.

Or, should we recommend a separate PostgreSQL instance on a different VM or container, and just introduce the parameter only in postgresql.conf?


Regards
Takayuki Tsunakawa

Reply | Threaded
Open this post in threaded view
|

Re: Statement-level rollback

Andres Freund
In reply to this post by Alvaro Herrera-9
On 2018-12-08 17:55:03 -0300, Alvaro Herrera wrote:

> On 2018-Dec-08, Andres Freund wrote:
>
> > On 2018-12-08 17:10:27 -0300, Alvaro Herrera wrote:
>
> > > This is what patch 0001 does -- it's only allowed in the connection
> > > string, or on ALTER USER / ALTER DATABASE.  Setting it in
> > > postgresql.conf is forbidden, as well as changing from transaction to
> > > statement in SET (the opposite is allowed, though.)
> >
> > I don't think allowing to set it on a per-user basis is acceptable
> > either, it still leaves the client in a state where they'll potentially
> > be confused about it.
>
> Hmm, true.
>
> > Do you have a proposal to address the issue that this makes it just
> > about impossible to write UDFs in a safe way?
>
> Not yet, but I'll give it a think next week.

Is this still in development? Or should we mark this as returned with
feedback?

Greetings,

Andres Freund

Reply | Threaded
Open this post in threaded view
|

Re: Statement-level rollback

Greg Stark
In reply to this post by Robert Haas


On Fri 7 Dec 2018, 21:43 Robert Haas <[hidden email] wrote:
On Fri, Dec 7, 2018 at 3:34 PM Alvaro Herrera <[hidden email]> wrote:
> Yeah, I agree that this downside is real.  I think our only protection
> against that is to say "don't do that".  Like any other tool, it has
> upsides and downsides; we shouldn't keep it away from users only because
> they might misuse it.

I have a hard time arguing against that given that EDB has this thing
in our bag of tricks, but if it weren't for that I'd be fighting
against this tooth and nail.  Behavior-changing GUCs suuuuck.

This looks like repeating the autocommit mistakes of the past. 

And based on that experience wouldn't the replacement approach be to do this client side? If libpq had a library option to wrap every statement in a subtransaction by adding begin/end then this problem would be completely sidestepped.
Reply | Threaded
Open this post in threaded view
|

Re: Statement-level rollback

Alvaro Herrera-9
On 2019-Jan-31, Greg Stark wrote:

> This looks like repeating the autocommit mistakes of the past.

Yeah, this argument has been made before.  Peter E argued against that:
https://postgr.es/m/ea395aa8-5ac4-6bcd-366d-aab2ff2b05ef@...

> And based on that experience wouldn't the replacement approach be to do
> this client side?

JDBC has a feature for this already (see "autosave" in [1]), but
apparently you get a good performance improvement by doing it
server-side, which is why this was written in the first place.  psql
also has ON_ERROR_ROLLBACK [2].

> If libpq had a library option to wrap every statement in a
> subtransaction by adding begin/end then this problem would be
> completely sidestepped.

I don't think anyone's talked about doing it in libpq before
specifically, and if you did that, it would obviously have to be
implemented separately on JDBC.

[1] https://jdbc.postgresql.org/documentation/head/connect.html#connection-parameters
[2] https://www.postgresql.org/docs/current/app-psql.html

--
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Reply | Threaded
Open this post in threaded view
|

Re: Statement-level rollback

Michael Paquier-2
In reply to this post by Andres Freund
On Thu, Jan 31, 2019 at 04:38:27AM -0800, Andres Freund wrote:
> Is this still in development? Or should we mark this as returned with
> feedback?

Marked as returned with feedback, as it has already been two months.
If you have an updated patch set, please feel free to resubmit.
--
Michael

signature.asc (849 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Statement-level rollback

Alvaro Herrera-9
On 2019-Feb-04, Michael Paquier wrote:

> On Thu, Jan 31, 2019 at 04:38:27AM -0800, Andres Freund wrote:
> > Is this still in development? Or should we mark this as returned with
> > feedback?
>
> Marked as returned with feedback, as it has already been two months.
> If you have an updated patch set, please feel free to resubmit.

It was reasonable to close this patch as returned-with-feedback in
January commitfest, but what you did here was first move it to the March
commitfest and then close it as returned-with-feedback in the March
commitfest, which has not even started.  That makes no sense.

--
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Reply | Threaded
Open this post in threaded view
|

Re: Statement-level rollback

Michael Paquier-2
On Tue, Feb 12, 2019 at 07:13:45PM -0300, Alvaro Herrera wrote:
> It was reasonable to close this patch as returned-with-feedback in
> January commitfest, but what you did here was first move it to the March
> commitfest and then close it as returned-with-feedback in the March
> commitfest, which has not even started.  That makes no sense.

Except if the CF app does not leave any place for error, particularly
as it is not possible to move back a patch to a previous commit fest
once it has been moved to the next one.  In this case, it looks that I
did a mistake in moving the patch first, my apologies for that.  There
were many patches to classify last week, so it may be possible that I
did similar mistakes for some other patches.
--
Michael

signature.asc (849 bytes) Download Attachment
12