Statement-level rollback

From: "Tsunakawa, Takayuki" <tsunakawa(dot)takay(at)jp(dot)fujitsu(dot)com>
To: "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>
Subject: Statement-level rollback
Date: 2017-02-28 07:39:45
Message-ID: 0A3221C70F24FB45833433255569204D1F6A9286@G01JPEXMBYT05
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers pgsql-jdbc

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

Attachment Content-Type Size
stmt_rollback.patch application/octet-stream 22.4 KB

Responses

Browse pgsql-jdbc by date

  From Date Subject
Next Message Tom Lane 2017-02-28 13:17:17 Re: Statement-level rollback
Previous Message Dave Cramer 2017-02-27 18:50:12 Re: [HACKERS] PGSERVICEFILE as a connection string parameter

Browse pgsql-hackers by date

  From Date Subject
Next Message Masahiko Sawada 2017-02-28 07:40:28 Re: Report the number of skipped frozen pages by manual VACUUM
Previous Message David Fetter 2017-02-28 07:07:46 Re: IF (NOT) EXISTS in psql-completion