Statement-level rollback

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

Statement-level rollback

Tsunakawa, Takayuki
Hello,

As I stated here and at the PGConf.ASIA developer meeting last year, I'd like to propose statement-level rollback feature.  To repeat myself, this is requested for users to migrate from other DBMSs to PostgreSQL.  They expect that a failure of one SQL statement should not abort the entire transaction and their apps (client programs and stored procedures) can continue the transaction with a different SQL statement.


SPECIFICATION
==================================================

START TRANSACTION ROLLBACK SCOPE { TRANSACTION | STATEMENT };

This syntax controls the behavior of the transaction when an SQL statement fails.  TRANSACTION (default) is the traditional behavior (i.e. rolls back the entire transaction or subtransaction).  STATEMENT rolls back the failed SQL statement.

Just like the isolation level and access mode, default_transaction_rollback_scope GUC variable is also available.


DESIGN
==================================================

Nothing much to talk about... it merely creates a savepoint before each statement execution and destroys it after the statement finishes.  This is done in postgres.c for top-level SQL statements.

The stored function hasn't been handled yet; I'll submit the revised patch soon.


CONSIDERATIONS AND REQUESTS
==================================================

The code for stored functions is not written yet, but I'd like your feedback for the specification and design based on the current patch.  I'll add this patch to CommitFest 2017-3.

The patch creates and destroys a savepoint for each message of the extended query protocol (Parse, Bind, Execute and Describe).  I'm afraid this will add significant overhead, but I don't find a better way, because those messages could be send arbitrarily for different statements, e.g. Parse stmt1, Parse stmt2, Bind stmt1, Execute stmt1, Bind stmt2, Execute stmt2.


Regards
Takayuki Tsunakawa



--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

stmt_rollback.patch (30K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Statement-level rollback

Tom Lane-2
"Tsunakawa, Takayuki" <[hidden email]> writes:
> As I stated here and at the PGConf.ASIA developer meeting last year, I'd
> like to propose statement-level rollback feature.

I do not really see how this would ever get past the compatibility
problems that forced us to give up on server-side autocommit years ago.

If you want to provide a client-side facility for this, perhaps that could
fly.

                        regards, tom lane


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

Re: Statement-level rollback

Tsunakawa, Takayuki
From: [hidden email]
> [mailto:[hidden email]] On Behalf Of Tom Lane
> "Tsunakawa, Takayuki" <[hidden email]> writes:
> > As I stated here and at the PGConf.ASIA developer meeting last year,
> > I'd like to propose statement-level rollback feature.
>
> I do not really see how this would ever get past the compatibility problems
> that forced us to give up on server-side autocommit years ago.

Could you tell me more about that problem?  What kind of incompatibility would this feature introduce?

> If you want to provide a client-side facility for this, perhaps that could
> fly.

Do you mean a feature of psqlODBC that implicitly issues SAVEPOINT and RELEASE SAVEPOINT for each SQL statement?  One reason I want to implement the feature is to avoid eliminate those round-trips for performance.  Or, do you mean a client-side connection parameter like "rollback_scope={transaction | statement}?"  Yes, I'll implement it for major client drivers so that the driver issues "SET SESSION CHARACTERISTICS FOR TRANSACTION ROLLBACK SCOPE {TRANSACTION | STATEMENT}" upon connection.  psqlODBC has already a connection parameter, Protocol, for that purpose.

Regards
Takayuki Tsunakawa





--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

Re: Statement-level rollback

Peter Eisentraut-6
In reply to this post by Tom Lane-2
On 2/28/17 08:17, Tom Lane wrote:
> I do not really see how this would ever get past the compatibility
> problems that forced us to give up on server-side autocommit years ago.

I think it's different because it's not a global setting, it's only a
behavior you select explicitly when you start a transaction block.

--
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

Re: Statement-level rollback

Peter Eisentraut-6
In reply to this post by Tsunakawa, Takayuki
On 2/28/17 02:39, Tsunakawa, Takayuki wrote:
> I'd like to propose statement-level rollback feature.  To repeat myself, this is requested for users to migrate from other DBMSs to PostgreSQL.  They expect that a failure of one SQL statement should not abort the entire transaction and their apps (client programs and stored procedures) can continue the transaction with a different SQL statement.

Can you provide some references on how other systems provide this feature?

--
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

Re: Statement-level rollback

Tom Lane-2
In reply to this post by Peter Eisentraut-6
Peter Eisentraut <[hidden email]> writes:
> On 2/28/17 08:17, Tom Lane wrote:
>> I do not really see how this would ever get past the compatibility
>> problems that forced us to give up on server-side autocommit years ago.

> I think it's different because it's not a global setting, it's only a
> behavior you select explicitly when you start a transaction block.

Yeah, that's the same it-won't-affect-you-if-you-don't-use-it argument
that we heard for server-side autocommit-off.  I don't buy it.
I can think of two reasons even without any caffeine:

1. The argument for this is mostly, if not entirely, "application
compatibility".  But it won't succeed at providing that if every
BEGIN has to be spelled differently than it would be on other DBMSes.
Therefore there is going to be enormous pressure to allow enabling
the feature through a GUC, or some other environment-level way,
and as soon as we do that we've lost.

2. The proposed feature would affect the internal operation of PL
functions, so that those would need to become bulletproof against
being invoked in either operating environment.  Likewise, all sorts
of intermediate tools like connection poolers would no doubt be broken
if they don't know about this and support both modes.  (We would have
to start by fixing postgres_fdw and dblink, for instance.)

In short, you can't make fundamental changes in transactional behavior
without enormous breakage.  That was the lesson we learned from the
autocommit fiasco and I do not believe that it's inapplicable here.

                        regards, tom lane


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

Re: Statement-level rollback

Tsunakawa, Takayuki
In reply to this post by Peter Eisentraut-6
From: [hidden email]
> [mailto:[hidden email]] On Behalf Of Peter Eisentraut
> On 2/28/17 02:39, Tsunakawa, Takayuki wrote:
> > I'd like to propose statement-level rollback feature.  To repeat myself,
> this is requested for users to migrate from other DBMSs to PostgreSQL.  They
> expect that a failure of one SQL statement should not abort the entire
> transaction and their apps (client programs and stored procedures) can
> continue the transaction with a different SQL statement.
>
> Can you provide some references on how other systems provide this feature?

Oracle doesn't.

SQL Server provides like this:

SET XACT_ABORT
https://msdn.microsoft.com/en-us/library/ms188792.aspx

MySQL doesn't.  BTW, MySQL enables changing autocommit mode with SET statement:

16.5.2.2 autocommit, Commit, and Rollback
https://dev.mysql.com/doc/refman/8.0/en/innodb-autocommit-commit-rollback.html



And above all, I've found EnterpriseDB supports statement-level rollback with GUC!  So PostgreSQL should be able to do.

https://www.edbpostgres.com/docs/en/9.6/asguide/EDB_Postgres_Advanced_Server_Guide.1.17.html#pID0E0QUD0HA

----------------------------------------
edb_stmt_level_tx is set to TRUE, then an exception will not automatically roll back prior uncommitted database updates. If edb_stmt_level_tx is set to FALSE, then an exception will roll back uncommitted database updates.

Note: Use edb_stmt_level_tx set to TRUE only when absolutely necessary, as this may cause a negative performance impact.
----------------------------------------


Regards
Takayuki Tsunakawa



--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

Re: Statement-level rollback

Tsunakawa, Takayuki
In reply to this post by Tom Lane-2
From: Tom Lane [mailto:[hidden email]]
> 1. The argument for this is mostly, if not entirely, "application
> compatibility".  But it won't succeed at providing that if every BEGIN has
> to be spelled differently than it would be on other DBMSes.
> Therefore there is going to be enormous pressure to allow enabling the
> feature through a GUC, or some other environment-level way, and as soon
> as we do that we've lost.

I thought so, too.  I believe people who want to migrate from other DBMSs would set the GUC in postgresql.conf, or with ALTER DATABASE/USER just for applications which are difficult to modify.

> 2. The proposed feature would affect the internal operation of PL functions,
> so that those would need to become bulletproof against being invoked in
> either operating environment.  Likewise, all sorts of intermediate tools
> like connection poolers would no doubt be broken if they don't know about
> this and support both modes.  (We would have to start by fixing postgres_fdw
> and dblink, for instance.)

Yes, I'm going to modify the PL's behavior.  I'll also check the dblink and postgres_fdw as well.  In addition, I'll have a quick look at the code of pgpool-II and pgBouncer to see how they depend on the transaction state.  I'll run the regression tests of contribs, pgpool-II and pgBouncer with default_transaction_rollback_scope set to 'statement'.

But I don't see how badly the statement-level rollback affects those features other than PL.  I think the only relevant thing to those client-side programs is whether the transaction is still running, which is returned with ReadyForQuery.  Both of statement-level rollback and the traditional behavior leave the transaction running when an SQL statement fails.  Server-side autocommit differs in that respect.

Regards
Takayuki Tsunakawa






--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

Re: Statement-level rollback

David Steele
On 3/3/17 2:43 AM, Tsunakawa, Takayuki wrote:

> From: Tom Lane [mailto:[hidden email]]
>> 1. The argument for this is mostly, if not entirely, "application
>> compatibility".  But it won't succeed at providing that if every BEGIN has
>> to be spelled differently than it would be on other DBMSes.
>> Therefore there is going to be enormous pressure to allow enabling the
>> feature through a GUC, or some other environment-level way, and as soon
>> as we do that we've lost.
>
> I thought so, too.  I believe people who want to migrate from other DBMSs would set the GUC in postgresql.conf, or with ALTER DATABASE/USER just for applications which are difficult to modify.
>
>> 2. The proposed feature would affect the internal operation of PL functions,
>> so that those would need to become bulletproof against being invoked in
>> either operating environment.  Likewise, all sorts of intermediate tools
>> like connection poolers would no doubt be broken if they don't know about
>> this and support both modes.  (We would have to start by fixing postgres_fdw
>> and dblink, for instance.)
>
> Yes, I'm going to modify the PL's behavior.  I'll also check the dblink and postgres_fdw as well.  In addition, I'll have a quick look at the code of pgpool-II and pgBouncer to see how they depend on the transaction state.  I'll run the regression tests of contribs, pgpool-II and pgBouncer with default_transaction_rollback_scope set to 'statement'.
>
> But I don't see how badly the statement-level rollback affects those features other than PL.  I think the only relevant thing to those client-side programs is whether the transaction is still running, which is returned with ReadyForQuery.  Both of statement-level rollback and the traditional behavior leave the transaction running when an SQL statement fails.  Server-side autocommit differs in that respect.

Whatever the merits of this patch, it's a pretty major behavioral change
with a large potential impact.  Even if what is enumerated here is the
full list (which I doubt), it's pretty big.

Given that this landed on March 28 with no discussion beforehand, I
recommend that we immediately move this patch to the 2017-07 CF.

--
-David
[hidden email]


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

Re: Statement-level rollback

Andres Freund
On 2017-03-03 11:54:06 -0500, David Steele wrote:
> Given that this landed on March 28 with no discussion beforehand, I
> recommend that we immediately move this patch to the 2017-07 CF.

Seconded.


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

Re: Statement-level rollback

David Steele
On 3/3/17 12:01 PM, Andres Freund wrote:
> On 2017-03-03 11:54:06 -0500, David Steele wrote:
>> Given that this landed on March 28 with no discussion beforehand, I
>> recommend that we immediately move this patch to the 2017-07 CF.
>
> Seconded.

And of course I meant Feb 28.

--
-David
[hidden email]


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

Re: Statement-level rollback

Peter Geoghegan-4
In reply to this post by Andres Freund
On Fri, Mar 3, 2017 at 9:01 AM, Andres Freund <[hidden email]> wrote:
> On 2017-03-03 11:54:06 -0500, David Steele wrote:
>> Given that this landed on March 28 with no discussion beforehand, I
>> recommend that we immediately move this patch to the 2017-07 CF.
>
> Seconded.

+1


--
Peter Geoghegan


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

Re: Statement-level rollback

Tsunakawa, Takayuki
In reply to this post by David Steele
From: David Steele [mailto:[hidden email]]
> Whatever the merits of this patch, it's a pretty major behavioral change
> with a large potential impact.  Even if what is enumerated here is the full
> list (which I doubt), it's pretty big.
>
> Given that this landed on March 28 with no discussion beforehand, I recommend
> that we immediately move this patch to the 2017-07 CF.

OK, I moved it to 2017-7.  I will participate in the review of existing patches.  In parallel with that, I'll keep developing this feature and sometimes submit revised patches and new findings.  I'd be happy if anyone could give feedback then.

Regards
Takayuki Tsunakawa



--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

Re: Statement-level rollback

Robert Haas
In reply to this post by Tsunakawa, Takayuki
On Fri, Mar 3, 2017 at 2:15 AM, Tsunakawa, Takayuki
<[hidden email]> wrote:

> From: [hidden email]
>> [mailto:[hidden email]] On Behalf Of Peter Eisentraut
>> On 2/28/17 02:39, Tsunakawa, Takayuki wrote:
>> > I'd like to propose statement-level rollback feature.  To repeat myself,
>> this is requested for users to migrate from other DBMSs to PostgreSQL.  They
>> expect that a failure of one SQL statement should not abort the entire
>> transaction and their apps (client programs and stored procedures) can
>> continue the transaction with a different SQL statement.
>>
>> Can you provide some references on how other systems provide this feature?
>
> Oracle doesn't.

Really?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

Re: Statement-level rollback

Tsunakawa, Takayuki
From: [hidden email]
> [mailto:[hidden email]] On Behalf Of Robert Haas
> >> Can you provide some references on how other systems provide this feature?
> >
> > Oracle doesn't.
>
> Really?

Sorry, my sentence was misleading.
I meant by "Oracle/MySQL doesn't" that they do not provide a configuration parameter or START TRANSACTION mode to choose between statement rollback and transaction rollback.  They just rolls back the failed statement.  I wish Postgres could behave the same way.

Regards
Takayuki Tsunakawa



--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

Re: Statement-level rollback

legrand legrand
Hello,

EDB Oracle compatibility proposes edb_stmt_level_tx parameter,
psql uses ON_ERROR_ROLLBACK = 'on',
ODBC has a parameter for this
JDBC has nothing and developers has to play with savepoint as described
http://blog.endpoint.com/2015/02/postgres-onerrorrollback-explained.html

This feature (as a GUC at server level) would be very helpfull for Oracle applications migration.

Regards
PAscal
Reply | Threaded
Open this post in threaded view
|

Re: Statement-level rollback

Michael Banck-2
On Tue, Mar 07, 2017 at 01:49:29PM -0700, legrand legrand wrote:
> JDBC has nothing and developers has to play with savepoint as described
> http://blog.endpoint.com/2015/02/postgres-onerrorrollback-explained.html

JDBC has it since 9.4.1210 (2016-09-07), unless I am mistaken:

https://github.com/pgjdbc/pgjdbc/commit/adc08d57d2a9726309ea80d574b1db835396c1c8


Michael

--
Michael Banck
Projektleiter / Senior Berater
Tel.: +49 2166 9901-171
Fax:  +49 2166 9901-100
Email: [hidden email]

credativ GmbH, HRB Mönchengladbach 12080
USt-ID-Nummer: DE204566209
Trompeterallee 108, 41189 Mönchengladbach
Geschäftsführung: Dr. Michael Meskes, Jörg Folz, Sascha Heuer


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

Re: Statement-level rollback

Dave Cramer-8

On 7 March 2017 at 16:18, Michael Banck <[hidden email]> wrote:
On Tue, Mar 07, 2017 at 01:49:29PM -0700, legrand legrand wrote:
> JDBC has nothing and developers has to play with savepoint as described
> http://blog.endpoint.com/2015/02/postgres-onerrorrollback-explained.html

JDBC has it since 9.4.1210 (2016-09-07), unless I am mistaken:

https://github.com/pgjdbc/pgjdbc/commit/adc08d57d2a9726309ea80d574b1db835396c1c8

I thought he meant we have to play with savepoints. 

Yes, we do it for you now


Reply | Threaded
Open this post in threaded view
|

RE: Statement-level rollback

legrand legrand
In reply to this post by Michael Banck-2

Thanks !

that's a very good new !


I'm still receiving the famous

    "current transaction is aborted" error

when usingversion 42.0.0 with 

     jdbc:postgresql://localhost:5432/postgres?autosave=always


But I will see that with pgjdbc team ;o)

Regards
PAscal
Reply | Threaded
Open this post in threaded view
|

Re: Statement-level rollback

Dave Cramer-8
You have to turn it on using the autosave parameter. it's not on by default, and apparently not documented 


On 7 March 2017 at 17:15, legrand legrand <[hidden email]> wrote:

Thanks !

that's a very good new !


I'm still receiving the famous

    "current transaction is aborted" error

when usingversion 42.0.0 with 

     jdbc:postgresql://localhost:5432/postgres?autosave=always


But I will see that with pgjdbc team ;o)

Regards
PAscal


View this message in context: RE: Statement-level rollback

Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

123