Re: [HACKERS] Statement-level rollback

[Thomas Munro]

On Wed, Nov 1, 2017 at 6:47 AM, MauMau  wrote:
> From: Simon Riggs
> On 14 August 2017 at 23:58, Peter Eisentraut
>  wrote:
>> On 2/28/17 02:39, Tsunakawa, Takayuki wrote:
>>> 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.
>>
>> This patch needs to be rebased for the upcoming commit fest.
>
> I'm willing to review this if the patch is going to be actively worked
> on.
>
>
> I'm very sorry I couldn't reply to your kind offer.  I rebased the
> patch and will add it to CF 2017/11.  I hope I will complete the patch
> in this CF.

Hi Tsunakawa-san,

With your v2 patch "make docs" fails.  Here is a small patch to apply
on top of yours to fix that and some small copy/paste errors, if I
understood correctly.

-- 
Thomas Munro
http://www.enterprisedb.com


docs-suggestion.patch
Description: Binary data

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

Re: [HACKERS] Statement-level rollback

[MauMau]

From: Thomas Munro
With your v2 patch "make docs" fails.  Here is a small patch to apply
on top of yours to fix that and some small copy/paste errors, if I
understood correctly.

Ouch, thanks.  I'd like to merge your fix when I submit the next
revision of my patch.

Regards
MauMau




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

Re: [HACKERS] Statement-level rollback

[Tsunakawa, Takayuki]

From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Simon Riggs
> A backend-based solution is required for PL procedures and functions.
> 
> We could put this as an option into PL/pgSQL, but it seems like it is
> a function of the transaction manager rather than the driver.

Exactly.  Thanks.

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

Re: [HACKERS] Statement-level rollback

[Simon Riggs]

On 2 November 2017 at 01:33, Peter Eisentraut
 wrote:

> The proposed statement-level rollback feature works in a slightly
> different context.  It does not change when or how a transaction or
> transaction block begins and ends.  It only changes what happens inside
> explicit transaction blocks.

Yes, this is not the same thing as autocommit. There should be no
concerns there.

> The difference is how error recovery works.

Yes

> So this will necessarily be
> tied to how the client code or other surrounding code is structured or
> what the driver or framework is doing in the background to manage
> transactions.  It would also be bad if client code was not prepared for
> this new behavior, reported the transaction as complete while some
> commands in the middle were omitted.

This new feature allows a simplified development style because earlier
statements don't need to be re-executed, nor do we have to manually
wrap everything in savepoints.

It changes the assumptions of error recovery, so this will break code
already written for PostgreSQL. The purpose is to allow new code to be
written using the easier style.

Compare this with SERIALIZABLE mode - no need for time consuming
additional coding.

> Drivers can already achieve this behavior and do do that by issuing
> savepoint commands internally.  The point raised in this thread was that
> that creates too much network overhead, so a backend-based solution
> would be preferable.  We haven't seen any numbers or other evidence to
> quantify that claim, so maybe it's worth looking into that some more.
>
> In principle, a backend-based solution that drivers just have to opt
> into would save a lot of duplication.  But the drivers that care or
> require it according to their standards presumably already implement
> this behavior in some other way, so it comes back to whether there is a
> performance or other efficiency gain here.
>
> Another argument was that other SQL implementations have this behavior.
> This appears to be the case.  But as far as I can tell, it is also tied
> to their particular interfaces and the structure and flow control they
> provide.  So a client-side solution like psql already provides or
> something in the various drivers would work just fine here.
>
> So my summary for the moment is that a GUC or similar run-time setting
> might be fine, with appropriate explanation and warnings.  But it's not
> clear whether it's worth it given the existing alternatives.

This is about simplicity for the developer, not so much about performance.

A backend-based solution is required for PL procedures and functions.

We could put this as an option into PL/pgSQL, but it seems like it is
a function of the transaction manager rather than the driver.

-- 
Simon Riggshttp://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

Re: [HACKERS] Statement-level rollback

[Vladimir Sitnikov]

Tsunakawa>So the statement-level rollback is newer to users, isn't it?

Technically speaking, the feature was listed in the changelog.

Tsunakawa>Doesn't PgJDBC execute RELEASE after each SQL statement?

It does not.

Tsunakawa>That said, even with RELEASE, the server memory bloat is not
solved.

That is what I mean.

Vladimir

Re: [HACKERS] Statement-level rollback

[Tsunakawa, Takayuki]

From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Vladimir
> Sitnikov
> Tsunakawa> PgJDBC has supported the feature with autosave parameter only
> Tsunakawa> recently
> 
> PgJDBC has the implementation for more than a year (REL9.4.1210, 2016-09-07,
> see https://github.com/pgjdbc/pgjdbc/pull/477 )

And I heard from someone in PgJDBC community that the autosave parameter was 
not documented in the manual for a while, which I confirmed.  So the 
statement-level rollback is newer to users, isn't it?


> The performance overhead for "SELECT" statement (no columns, just select)
> statement over localhost is 36±4 us vs 38±3 us (savepoint is pipelined along
> with user-provided query). That is network overhead is close to negligible.

That's good news, because it also means that the overhead of creating a 
savepoint is negligible.



> As far as I understand, the main problem with savepoints is they would
> consume memory even in case the same savepoint is reassigned again and again.
> In other words, "savepoint; insert;savepoint; insert;savepoint;
> insert;savepoint; insert;savepoint; insert;" would allocate xids and might
> blow up backend's memory.
> I see no way driver can workaround that, so it would be great if backend
> could release memory or provide a way to do so.

Doesn't PgJDBC execute RELEASE after each SQL statement?  That said, even with 
RELEASE, the server memory bloat is not solved.  The current server 
implementation allocates a memory chunk of 8KB called CurTranContext for each 
subtransaction, and retains them until the end of top-level transaction.  
That's another (separate) issue to address.

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

Re: [HACKERS] Statement-level rollback

[Craig Ringer]

On 2 November 2017 at 13:59, Vladimir Sitnikov
 wrote:

> The performance overhead for "SELECT" statement (no columns, just select)
> statement over localhost is 36±4 us vs 38±3 us (savepoint is pipelined along
> with user-provided query). That is network overhead is close to negligible.

Yep. Not for psqlODBC or other libpq-based drives that can't pipeline
queries though.

> In other words, "savepoint; insert;savepoint; insert;savepoint;
> insert;savepoint; insert;savepoint; insert;" would allocate xids and might
> blow up backend's memory.

RELEASE SAVEPOINT, like psqlODBC does.

> Adding protocol messages would blow pgbouncer, etc things, so it makes sense
> to refrain from new messages unless it is absolutely required.

Yeah, it'd affect proxies, true. But it'd let us get rid of a lot of
very ugly log spam too. And unlike some of the prior protocol tweaks
I've been interested in, it'd be client-initiated so it should be
pretty safe.

-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, 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

Re: [HACKERS] Statement-level rollback

[Vladimir Sitnikov]

Tsunakawa> PgJDBC has supported the feature with autosave parameter only
recently

PgJDBC has the implementation for more than a year (REL9.4.1210,
2016-09-07, see https://github.com/pgjdbc/pgjdbc/pull/477 )

Tsunakawa> The point raised in this thread was that that creates
Tsunakawa> too much network overhead, so a backend-based solution would be
preferable.
Tsunakawa> We haven't seen any numbers or other evidence to quantify that
claim, so
Tsunakawa> maybe it's worth looking into that some more

The performance overhead for "SELECT" statement (no columns, just select)
statement over localhost is 36±4 us vs 38±3 us (savepoint is pipelined
along with user-provided query). That is network overhead is close to
negligible.

As far as I understand, the main problem with savepoints is they would
consume memory even in case the same savepoint is reassigned again and
again.
In other words, "savepoint; insert;savepoint; insert;savepoint;
insert;savepoint; insert;savepoint; insert;" would allocate xids and might
blow up backend's memory.
I see no way driver can workaround that, so it would be great if backend
could release memory or provide a way to do so.

Adding protocol messages would blow pgbouncer, etc things, so it makes
sense to refrain from new messages unless it is absolutely required.

Vladimir

Re: [HACKERS] Statement-level rollback

[Tsunakawa, Takayuki]

From: Craig Ringer [mailto:cr...@2ndquadrant.com]
> The example often cited is some variant of
> 
> BEGIN;
> CREATTE TABLE t2 AS SELECT * FROM t1;
> DROP TABLE t1;
> ALTER TABLE t2 RENAME TO t1;
> COMMIT;
> 
> Right now, we do the right thing here. With default statement level rollback,
> you just dropped t1 and all your data. oops.

That's a horrible example.  So I think the default behavior should be what it 
is now for existing PostgreSQL users.


> On a related note, psql's -v ON_ERROR_STOP=1 is horrible and hard to discover
> UI, and one of the top FAQs on Stack Overflow is some variant of "I'm getting
> random and incomprehensible errors restoring a dump, wtf?". So I'd really
> love to make it the default, but we'd face similar issues where a SQL script
> that's currently correct instead produces dangerously wrong results with
> ON_ERROR_STOP=1 .

Yes.  And although unrelated, psql's FETCH_SIZE is also often invisible to 
users.  They report out-of-memory trouble when they do SELECT on a large table 
with psql.



> What about if we add protocol-level savepoint support? Two new messages:
> 
> BeginUnnamedSavepoint
> 
> and
> 
> EndUnnamedSavepoint
> 
> where the latter does a rollback-to-last-unnamed-savepoint if the txn state
> is bad, or a release-last-unnamed-savepoint if the txn state is ok. That
> means the driver doesn't have to wait for the result of the statement. It
> knows the conn state and query outcome from our prior messages, and knows
> that as a result of this message any failed state has been rolled back.
> 
> This would, with appropriate libpq support, give people who want statement
> level error handling pretty much what they want. And we could expose it
> in psql too. No GUCs needed, no fun surprises for apps. psqlODBC could adopt
> it to replace its current slow and super-log-spammy statement rollback
> model.
> 
> Downside is that it needs support in each client driver.

Yes, I believe we should avoid the downside.  It's tough to develop and 
maintain a client driver, so we should minimize the burdon with server-side 
support.

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

Re: [HACKERS] Statement-level rollback

[Tsunakawa, Takayuki]

From: Peter Eisentraut [mailto:peter.eisentr...@2ndquadrant.com]
> The difference is how error recovery works.  So this will necessarily be
> tied to how the client code or other surrounding code is structured or what
> the driver or framework is doing in the background to manage transactions.
> It would also be bad if client code was not prepared for this new behavior,
> reported the transaction as complete while some commands in the middle were
> omitted.
> 
> Drivers can already achieve this behavior and do do that by issuing savepoint
> commands internally.  The point raised in this thread was that that creates
> too much network overhead, so a backend-based solution would be preferable.
> We haven't seen any numbers or other evidence to quantify that claim, so
> maybe it's worth looking into that some more.
> 
> In principle, a backend-based solution that drivers just have to opt into
> would save a lot of duplication.  But the drivers that care or require it
> according to their standards presumably already implement this behavior
> in some other way, so it comes back to whether there is a performance or
> other efficiency gain here.
> 
> Another argument was that other SQL implementations have this behavior.
> This appears to be the case.  But as far as I can tell, it is also tied
> to their particular interfaces and the structure and flow control they
> provide.  So a client-side solution like psql already provides or something
> in the various drivers would work just fine here.
> 
> So my summary for the moment is that a GUC or similar run-time setting might
> be fine, with appropriate explanation and warnings.  But it's not clear
> whether it's worth it given the existing alternatives.

I can think of four reasons why the server-side support is necessary or 
desirable.

First, the server log could be filled with SAVEPOINT and RELEASE lines when you 
need to investigate performance or audit activity.

Second, the ease of use for those who migrate from other DBMSs.  With the 
server-side support, only the DBA needs to be aware of the configuration in 
postgresql.conf.  Other people don't need to be aware of the client-side 
parameter when they deploy applications.

Third, lack of server-side support causes trouble to driver developers.  In a 
recent discussion with the psqlODBC committer, he had some trouble improving or 
fixing the statement-rollback support.  Npgsql doesn't have the 
statement-rollback yet.  PgJDBC has supported the feature with autosave 
parameter only recently.  Do the drivers for other languages like Python, Go, 
JavaScript have the feature?  We should reduce the burdon on the driver 
developers.

Fourth, the runtime performance.  In a performance benchmark of one of our 
customers, where a batch application ran 1.5 or 5 million small SELECTs with 
primary key access, the execution time of the whole batch became shorter by 
more than 30% (IIRC) when the local connection was used instead of the remote 
TCP/IP one.  The communication overhead is not small.

Also, in the PostgreSQL documentation, the communication overhead is treated 
seriously as follows:


https://www.postgresql.org/docs/devel/static/plpgsql-overview.html#plpgsql-advantages

[Excerpt]
--
That means that your client application must send each query to the database 
server, wait for it to be processed, receive and process the results, do some 
computation, then send further queries to the server. All this incurs 
interprocess communication and will also incur network overhead if your client 
is on a different machine than the database server.

With PL/pgSQL you can group a block of computation and a series of queries 
inside the database server, thus having the power of a procedural language and 
the ease of use of SQL, but with considerable savings of client/server 
communication overhead.


•Extra round trips between client and server are eliminated


•Intermediate results that the client does not need do not have to be marshaled 
or transferred between server and client


•Multiple rounds of query parsing can be avoided


This can result in a considerable performance increase as compared to an 
application that does not use stored functions.
--


Craig reports the big communication overhead:

PATCH: Batch/pipelining support for libpq
https://www.postgresql.org/message-id/CAMsr+YFUjJytRyV4J-16bEoiZyH=4nj+sQ7JP9ajwz=b4dm...@mail.gmail.com#CAMsr+YFUjJytRyV4J-16bEoiZyH=4nj+sQ7JP9ajwz=b4dm...@mail.gmail.com

Re: foreign table batch insert
https://www.postgresql.org/message-id/CAMsr+YFgDUiJ37DEfPRk8WDBuZ58psdAYJd8iNFSaGxtw=w...@mail.gmail.com

[Excerpt]
--
The time difference for 10k inserts on the local host over a unix socket
shows a solid improvement:

batch insert elapsed:  0.244293s
sequential insert elapsed: 0.375402s

... but over, say, a connection to a random 

Re: [HACKERS] Statement-level rollback

[Craig Ringer]

On 2 November 2017 at 09:33, Peter Eisentraut
 wrote:

> If you turned the autocommit setting off, then this code would
> effectively silently do nothing, and that is obviously quite bad.

Right.

The example often cited is some variant of

BEGIN;
CREATTE TABLE t2 AS SELECT * FROM t1;
DROP TABLE t1;
ALTER TABLE t2 RENAME TO t1;
COMMIT;

Right now, we do the right thing here. With default statement level
rollback, you just dropped t1 and all your data. oops.


On a related note, psql's -v ON_ERROR_STOP=1 is horrible and hard to
discover UI, and one of the top FAQs on Stack Overflow is some variant
of "I'm getting random and incomprehensible errors restoring a dump,
wtf?". So I'd really love to make it the default, but we'd face
similar issues where a SQL script that's currently correct instead
produces dangerously wrong results with ON_ERROR_STOP=1 .

> In principle, a backend-based solution that drivers just have to opt
> into would save a lot of duplication.  But the drivers that care or
> require it according to their standards presumably already implement
> this behavior in some other way, so it comes back to whether there is a
> performance or other efficiency gain here.

There definitely would be over SQL-level savepoints. They're horrible
for performance, especially since libpq can't yet pipeline work so you
need three round-trips for each successful statement: SAVEPOINT,
statement, RELEASE SAVEPOINT. It produces massive log spam too.

What about if we add protocol-level savepoint support? Two new messages:

BeginUnnamedSavepoint

and

EndUnnamedSavepoint

where the latter does a rollback-to-last-unnamed-savepoint if the txn
state is bad, or a release-last-unnamed-savepoint if the txn state is
ok. That means the driver doesn't have to wait for the result of the
statement. It knows the conn state and query outcome from our prior
messages, and knows that as a result of this message any failed state
has been rolled back.

This would, with appropriate libpq support, give people who want
statement level error handling pretty much what they want. And we
could expose it in psql too. No GUCs needed, no fun surprises for
apps. psqlODBC could adopt it to replace its current slow and
super-log-spammy statement rollback model.

Because we'd know it was a special savepoint used for statement level
rollback we might still have some optimisation opportunities.

Downside is that it needs support in each client driver.

-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, 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

Re: [HACKERS] Statement-level rollback

[Peter Eisentraut]

On 10/31/17 13:47, MauMau wrote:
> I'm very sorry I couldn't reply to your kind offer.  I rebased the
> patch and will add it to CF 2017/11.  I hope I will complete the patch
> in this CF.

I've been thinking about this a little bit.  Many are worried about
repeating the mistakes of the autocommit feature, so it's worth
comparing that.

The problem with the autocommit setting, or at least the one I remember,
is that code is currently written expecting that

connect
exec SQL statement
disconnect

will succeed in executing and committing the SQL statement, unless an
error is reported.

If you turned the autocommit setting off, then this code would
effectively silently do nothing, and that is obviously quite bad.  So
the autocommit setting would break a large proportion of all code out
there, and was thus not really usable, and hence it was removed.

The proposed statement-level rollback feature works in a slightly
different context.  It does not change when or how a transaction or
transaction block begins and ends.  It only changes what happens inside
explicit transaction blocks.  Considering code like

START TRANSACTION;
SQL1;
SQL2;
SQL3;
COMMIT;

currently an error would cause all subsequent commands to fail.  Under
statement-level rollback, a failed command would effectively be ignored
and the transaction would continue until COMMIT.

Therefore, a successful transaction block would always work the same way
under either setting.

The difference is how error recovery works.  So this will necessarily be
tied to how the client code or other surrounding code is structured or
what the driver or framework is doing in the background to manage
transactions.  It would also be bad if client code was not prepared for
this new behavior, reported the transaction as complete while some
commands in the middle were omitted.

Drivers can already achieve this behavior and do do that by issuing
savepoint commands internally.  The point raised in this thread was that
that creates too much network overhead, so a backend-based solution
would be preferable.  We haven't seen any numbers or other evidence to
quantify that claim, so maybe it's worth looking into that some more.

In principle, a backend-based solution that drivers just have to opt
into would save a lot of duplication.  But the drivers that care or
require it according to their standards presumably already implement
this behavior in some other way, so it comes back to whether there is a
performance or other efficiency gain here.

Another argument was that other SQL implementations have this behavior.
This appears to be the case.  But as far as I can tell, it is also tied
to their particular interfaces and the structure and flow control they
provide.  So a client-side solution like psql already provides or
something in the various drivers would work just fine here.

So my summary for the moment is that a GUC or similar run-time setting
might be fine, with appropriate explanation and warnings.  But it's not
clear whether it's worth it given the existing alternatives.

-- 
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

Re: [HACKERS] Statement-level rollback

[MauMau]

From: Simon Riggs
On 14 August 2017 at 23:58, Peter Eisentraut
 wrote:
> On 2/28/17 02:39, Tsunakawa, Takayuki wrote:
>> 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.
>
> This patch needs to be rebased for the upcoming commit fest.

I'm willing to review this if the patch is going to be actively worked
on.


I'm very sorry I couldn't reply to your kind offer.  I rebased the
patch and will add it to CF 2017/11.  I hope I will complete the patch
in this CF.

Regards
Takayuki Tsunakawa




stmt_rollback_v2.patch
Description: Binary data

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

Re: [HACKERS] Statement-level rollback

[Daniel Gustafsson]

> On 15 Sep 2017, at 16:19, Daniel Gustafsson  wrote:
> 
>> On 01 Sep 2017, at 13:44, Simon Riggs  wrote:
>> 
>> On 14 August 2017 at 23:58, Peter Eisentraut
>>  wrote:
>>> On 2/28/17 02:39, Tsunakawa, Takayuki wrote:
 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.
>>> 
>>> This patch needs to be rebased for the upcoming commit fest.
>> 
>> I'm willing to review this if the patch is going to be actively worked on.
> 
> This sounds like a too good offer to pass up on, can we expect a rebased patch
> for the commitfest?

Since this patch was Waiting for author during the entire commitfest without
updates, I’m marking it Returned with Feedback.  When a new version is ready it
can be re-submitted to the then open commitfest.

cheers ./daniel

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

Re: [HACKERS] Statement-level rollback

[Daniel Gustafsson]

> On 01 Sep 2017, at 13:44, Simon Riggs  wrote:
> 
> On 14 August 2017 at 23:58, Peter Eisentraut
>  wrote:
>> On 2/28/17 02:39, Tsunakawa, Takayuki wrote:
>>> 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.
>> 
>> This patch needs to be rebased for the upcoming commit fest.
> 
> I'm willing to review this if the patch is going to be actively worked on.

This sounds like a too good offer to pass up on, can we expect a rebased patch
for the commitfest?

cheers ./daniel

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

Re: [HACKERS] Statement-level rollback

[Simon Riggs]

On 14 August 2017 at 23:58, Peter Eisentraut
 wrote:
> On 2/28/17 02:39, Tsunakawa, Takayuki wrote:
>> 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.
>
> This patch needs to be rebased for the upcoming commit fest.

I'm willing to review this if the patch is going to be actively worked on.

-- 
Simon Riggshttp://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

Re: [HACKERS] Statement-level rollback

[Peter Eisentraut]

On 2/28/17 02:39, Tsunakawa, Takayuki wrote:
> 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.

This patch needs to be rebased for the upcoming commit fest.

-- 
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

Re: [HACKERS] Statement-level rollback

[Simon Riggs]

On 1 March 2017 at 16:05, Tom Lane  wrote:
> Peter Eisentraut  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.

This is a frequently requested feature and I think we should push
ahead with it in the next cycle.

We're the World's Most Advanced Open Source Database, so a new
transaction feature fits in with our goals.

> 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.

We already use GUCs for various other transaction level features and
they work just fine.

What we need is a feature that works the way other DBMS do, as an
option. If it should be desirable.

I do accept there are problems and we do have some experience of those problems.

> 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.

I think the point we should take from Tom's comments is...

a) This feature won't be a replacement for PostgreSQL's default
behaviour, at least not in any short/medium term.

b) If we get this feature, about 80% of the work will be fixing all
the small breakages that happen with other tools, plugins etc.. So it
is no small task. If accepted this would be a major feature and will
take much work.

If we want this in Postgres11 then we must have a fully working patch
by start of Sept 2017, plus some analysis of all of the various
breakage points we are expecting to see. So lets do the analysis, so
we know how deep the mud is before we decide to walk through it.

-- 
Simon Riggshttp://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

Re: [HACKERS] Statement-level rollback

[Ideriha, Takeshi]

Hello,

This feature hasn't been updated for a long time, 
but I've just been interested in this feature and looking into the mailing list.

From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
> 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.

I've just wanted to confirm what "autocommit fiasco" points out.
Are the below threads and git-log relevant discussion?
(If there are any other threads, could you please tell me the link?)

https://www.postgresql.org/message-id/flat/3E54526A.121EBEE5%40tpf.co.jp#3e54526a.121eb...@tpf.co.jp
 
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=f85f43dfb5b9043ea6b01d8b824c195cd7f9ed3c

Regards,
Takeshi Ideriha



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

Re: [HACKERS] Statement-level rollback

[legrand legrand]

There was a mistake in my driver definition,

this works fine with autosave=always (but not with autoSave ...)


Thanks Again


De : Vladimir Sitnikov [via PostgreSQL] 

Envoyé : mardi 7 mars 2017 22:32:27
À : legrand legrand
Objet : Re: Statement-level rollback

Please disregard my previous message.
pgjdbc is already doing upcase conversion, so I would like to see a test case 
that reproduces the error.

Alternatively, could you please capture and share TRACE log? ( 
https://jdbc.postgresql.org/documentation/head/logging.html#configuration )

Vladimir

ср, 8 мар. 2017 г. в 1:26, Vladimir Sitnikov <[hidden 
email]>:
legrand>when usingversion 42.0.0 with
legrand> jdbc:postgresql://localhost:5432/postgres?autosave=always

The pitfall there is the value should be written with upper case like 
autosave=ALWAYS.

I've filed https://github.com/pgjdbc/pgjdbc/issues/769 to improve that at some 
point.


Vladimir



If you reply to this email, your message will be added to the discussion below:
http://www.postgresql-archive.org/Statement-level-rollback-tp5946725p5948059.html
To unsubscribe from Statement-level rollback, click 
here.
NAML




--
View this message in context: 
http://www.postgresql-archive.org/Statement-level-rollback-tp5946725p5948076.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

Re: [HACKERS] Statement-level rollback

[Vladimir Sitnikov]

Please disregard my previous message.
pgjdbc is already doing upcase conversion, so I would like to see a test
case that reproduces the error.

Alternatively, could you please capture and share TRACE log? (
https://jdbc.postgresql.org/documentation/head/logging.html#configuration )

Vladimir

ср, 8 мар. 2017 г. в 1:26, Vladimir Sitnikov :

> legrand>when usingversion 42.0.0 with
> legrand> jdbc:postgresql://localhost:5432/postgres?autosave=always
>
> The pitfall there is the value should be written with upper case like
> autosave=ALWAYS.
>
> I've filed https://github.com/pgjdbc/pgjdbc/issues/769 to improve that at
> some point.
>
>
> Vladimir
>

Re: [HACKERS] Statement-level rollback

[Vladimir Sitnikov]

legrand>when usingversion 42.0.0 with
legrand> jdbc:postgresql://localhost:5432/postgres?autosave=always

The pitfall there is the value should be written with upper case like
autosave=ALWAYS.

I've filed https://github.com/pgjdbc/pgjdbc/issues/769 to improve that at
some point.


Vladimir

Re: [HACKERS] Statement-level rollback

[legrand legrand]

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.

Re: [HACKERS] Statement-level rollback

[Dave Cramer]

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

Dave Cramer

da...@postgresintl.com
www.postgresintl.com

On 7 March 2017 at 17:15, legrand legrand 
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.
>

Re: [HACKERS] Statement-level rollback

[Dave Cramer]

On 7 March 2017 at 16:18, Michael Banck  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/adc08d57d2a9726309ea80d574b1db
> 835396c1c8


I thought he meant we have to play with savepoints.

Yes, we do it for you now


Dave Cramer

da...@postgresintl.com
www.postgresintl.com

Re: [HACKERS] Statement-level rollback

[Michael Banck]

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.ba...@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

Re: [HACKERS] 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




--
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

Re: [HACKERS] Statement-level rollback

[Tsunakawa, Takayuki]

From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@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

Re: [HACKERS] Statement-level rollback

[Robert Haas]

On Fri, Mar 3, 2017 at 2:15 AM, Tsunakawa, Takayuki
 wrote:
> From: pgsql-hackers-ow...@postgresql.org
>> [mailto:pgsql-hackers-ow...@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

Re: [HACKERS] Statement-level rollback

[Tsunakawa, Takayuki]

From: David Steele [mailto:da...@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

Re: [HACKERS] Statement-level rollback

[Peter Geoghegan]

On Fri, Mar 3, 2017 at 9:01 AM, 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.

+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

Re: [HACKERS] 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
da...@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

Re: [HACKERS] 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 (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Statement-level rollback

[David Steele]

On 3/3/17 2:43 AM, Tsunakawa, Takayuki wrote:
> From: Tom Lane [mailto:t...@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
da...@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

Re: [HACKERS] Statement-level rollback

[Tsunakawa, Takayuki]

From: Tom Lane [mailto:t...@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

Re: [HACKERS] Statement-level rollback

[Tsunakawa, Takayuki]

From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@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.

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 (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Statement-level rollback

[Tom Lane]

Peter Eisentraut  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

Re: [HACKERS] Statement-level rollback

[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?

-- 
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

Re: [HACKERS] Statement-level rollback

[Peter Eisentraut]

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

Re: [HACKERS] Statement-level rollback

[Tsunakawa, Takayuki]

From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Tom Lane
> "Tsunakawa, Takayuki"  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

Re: [HACKERS] Statement-level rollback

[Tom Lane]

"Tsunakawa, Takayuki"  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

[HACKERS] 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



stmt_rollback.patch
Description: stmt_rollback.patch

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