Statement-level rollback
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
Attachments:
stmt_rollback.patchapplication/octet-stream; name=stmt_rollback.patchDownload+323-4
"Tsunakawa, Takayuki" <tsunakawa.takay@jp.fujitsu.com> 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 (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
From: pgsql-hackers-owner@postgresql.org
[mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Tom Lane
"Tsunakawa, Takayuki" <tsunakawa.takay@jp.fujitsu.com> 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 (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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 (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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 (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Peter Eisentraut <peter.eisentraut@2ndquadrant.com> 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 (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
From: pgsql-hackers-owner@postgresql.org
[mailto:pgsql-hackers-owner@postgresql.org] 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.
----------------------------------------
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 (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
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 (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 3/3/17 2:43 AM, Tsunakawa, Takayuki wrote:
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
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
david@pgmasters.net
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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 (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
david@pgmasters.net
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Fri, Mar 3, 2017 at 9:01 AM, Andres Freund <andres@anarazel.de> 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 (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
From: David Steele [mailto:david@pgmasters.net]
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 (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Fri, Mar 3, 2017 at 2:15 AM, Tsunakawa, Takayuki
<tsunakawa.takay@jp.fujitsu.com> wrote:
From: pgsql-hackers-owner@postgresql.org
[mailto:pgsql-hackers-owner@postgresql.org] 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 (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
From: pgsql-hackers-owner@postgresql.org
[mailto:pgsql-hackers-owner@postgresql.org] 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 (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
View this message in context: http://www.postgresql-archive.org/Statement-level-rollback-tp5946725p5948032.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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: michael.banck@credativ.de
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 (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 7 March 2017 at 16:18, Michael Banck <michael.banck@credativ.de> 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.htmlJDBC has it since 9.4.1210 (2016-09-07), unless I am mistaken:
https://github.com/pgjdbc/pgjdbc/commit/adc08d57d2a9726309ea80d574b1db
835396c1c8
I thought he meant we have to play with savepoints.
Yes, we do it for you now
Dave Cramer
davec@postgresintl.com
www.postgresintl.com
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: http://www.postgresql-archive.org/Statement-level-rollback-tp5946725p5948053.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
You have to turn it on using the autosave parameter. it's not on by
default, and apparently not documented
Dave Cramer
davec@postgresintl.com
www.postgresintl.com
On 7 March 2017 at 17:15, legrand legrand <legrand_legrand@hotmail.com>
wrote:
Show quoted text
Thanks !
that's a very good new !
I'm still receiving the famous
"current transaction is aborted" error
when usingversion 42.0.0 withjdbc: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
<http://www.postgresql-archive.org/Statement-level-rollback-tp5946725p5948053.html>Sent from the PostgreSQL - hackers mailing list archive
<http://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html> at
Nabble.com.
