pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #18294: Manual savepoints do not work with JDBC param cleanupSavepoints=true
3+ messages / 3 participants
[nested] [flat]
* BUG #18294: Manual savepoints do not work with JDBC param cleanupSavepoints=true
@ 2024-01-15 10:30 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 3+ messages in thread
From: PG Bug reporting form @ 2024-01-15 10:30 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: obouda@gk-software.com
The following bug has been logged on the website:
Bug reference: 18294
Logged by: Ondrej Bouda
Email address: obouda@gk-software.com
PostgreSQL version: 16.1
Operating system: Linux
Description:
We come across a bug when it is not possible to roll back to a manual
savepoint.
The rollback fails with error: Error during rollback to savepoint.
org.postgresql.util.PSQLException: ERROR: savepoint "SAVE_TEST_01" does not
exist
Steps to reproduce:
1/ Connect via JDBC with JDBC params
autosave=always&cleanupSavepoints=true.
2/ Create a manual savepoint: savepoint SAVE_TEST_01;
3/ Try to roll back => error: rollback to SAVE_TEST_01;
Reproduced with:
* JDBC driver ver. 42.7.1
* PostgreSQL ver. 16.1 + 15.5 + 14.10
The error occurs when using SQL statements as well as when using the JDBC
API for savepoint/rollback (Connection.setSavepoint +
Connection.rollback).
The documentation for the cleanupSavepoints parameter reads: "Determines if
the SAVEPOINT created in autosave mode is released prior to the
statement."
* https://jdbc.postgresql.org/documentation/use/
* I would understand the doc in the way that it should affect just and only
the savepoint created by the driver due to the autosave=true param
(therefore, manual savepoints should not be affected).
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #18294: Manual savepoints do not work with JDBC param cleanupSavepoints=true
@ 2024-01-16 14:45 Dave Cramer <davecramer@postgres.rocks>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 3+ messages in thread
From: Dave Cramer @ 2024-01-16 14:45 UTC (permalink / raw)
To: obouda@gk-software.com; pgsql-bugs@lists.postgresql.org
On Mon, 15 Jan 2024 at 06:36, PG Bug reporting form <noreply@postgresql.org>
wrote:
> The following bug has been logged on the website:
>
> Bug reference: 18294
> Logged by: Ondrej Bouda
> Email address: obouda@gk-software.com
> PostgreSQL version: 16.1
> Operating system: Linux
> Description:
>
> We come across a bug when it is not possible to roll back to a manual
> savepoint.
> The rollback fails with error: Error during rollback to savepoint.
> org.postgresql.util.PSQLException: ERROR: savepoint "SAVE_TEST_01" does not
> exist
>
> Steps to reproduce:
> 1/ Connect via JDBC with JDBC params
> autosave=always&cleanupSavepoints=true.
> 2/ Create a manual savepoint: savepoint SAVE_TEST_01;
> 3/ Try to roll back => error: rollback to SAVE_TEST_01;
>
> Reproduced with:
> * JDBC driver ver. 42.7.1
> * PostgreSQL ver. 16.1 + 15.5 + 14.10
>
> The error occurs when using SQL statements as well as when using the JDBC
> API for savepoint/rollback (Connection.setSavepoint +
> Connection.rollback).
>
> The documentation for the cleanupSavepoints parameter reads: "Determines if
> the SAVEPOINT created in autosave mode is released prior to the
> statement."
> * https://jdbc.postgresql.org/documentation/use/
> * I would understand the doc in the way that it should affect just and only
> the savepoint created by the driver due to the autosave=true param
> (therefore, manual savepoints should not be affected).
>
Thank you for the report.
Unfortunately, the way Postgresql works as per
RELEASE SAVEPOINT releases the named savepoint and all active savepoints
that were created after the named savepoint, and frees their resources. All
changes made since the creation of the savepoint that didn't already get
rolled back are merged into the transaction or savepoint that was active
when the named savepoint was created. Changes made after RELEASE SAVEPOINT will
also be part of this active transaction or savepoint.
all savepoints made after will be released.
I'm not sure how to solve this problem
Dave
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #18294: Manual savepoints do not work with JDBC param cleanupSavepoints=true
@ 2024-01-18 22:20 David G. Johnston <david.g.johnston@gmail.com>
parent: Dave Cramer <davecramer@postgres.rocks>
0 siblings, 0 replies; 3+ messages in thread
From: David G. Johnston @ 2024-01-18 22:20 UTC (permalink / raw)
To: Dave Cramer <davecramer@postgres.rocks>; +Cc: obouda@gk-software.com; pgsql-bugs@lists.postgresql.org
On Tue, Jan 16, 2024 at 7:46 AM Dave Cramer <davecramer@postgres.rocks>
wrote:
>
>
> On Mon, 15 Jan 2024 at 06:36, PG Bug reporting form <
> noreply@postgresql.org> wrote:
>
>> The following bug has been logged on the website:
>>
>> Bug reference: 18294
>> Logged by: Ondrej Bouda
>> Email address: obouda@gk-software.com
>> PostgreSQL version: 16.1
>> Operating system: Linux
>> Description:
>>
>> We come across a bug when it is not possible to roll back to a manual
>> savepoint.
>> The rollback fails with error: Error during rollback to savepoint.
>> org.postgresql.util.PSQLException: ERROR: savepoint "SAVE_TEST_01" does
>> not
>> exist
>>
>> Steps to reproduce:
>> 1/ Connect via JDBC with JDBC params
>> autosave=always&cleanupSavepoints=true.
>> 2/ Create a manual savepoint: savepoint SAVE_TEST_01;
>> 3/ Try to roll back => error: rollback to SAVE_TEST_01;
>>
>> Reproduced with:
>> * JDBC driver ver. 42.7.1
>> * PostgreSQL ver. 16.1 + 15.5 + 14.10
>>
>> The error occurs when using SQL statements as well as when using the JDBC
>> API for savepoint/rollback (Connection.setSavepoint +
>> Connection.rollback).
>>
>> The documentation for the cleanupSavepoints parameter reads: "Determines
>> if
>> the SAVEPOINT created in autosave mode is released prior to the
>> statement."
>> * https://jdbc.postgresql.org/documentation/use/
>> * I would understand the doc in the way that it should affect just and
>> only
>> the savepoint created by the driver due to the autosave=true param
>> (therefore, manual savepoints should not be affected).
>>
>
>
> Thank you for the report.
>
> Unfortunately, the way Postgresql works as per
>
> RELEASE SAVEPOINT releases the named savepoint and all active savepoints
> that were created after the named savepoint, and frees their resources. All
> changes made since the creation of the savepoint that didn't already get
> rolled back are merged into the transaction or savepoint that was active
> when the named savepoint was created. Changes made after RELEASE SAVEPOINT will
> also be part of this active transaction or savepoint.
>
> all savepoints made after will be released.
>
> I'm not sure how to solve this problem
>
>
This is already an open item in the correct bug reporting location for the
driver.
https://github.com/pgjdbc/pgjdbc/issues/1834
Ideally at minimum the driver could give the user the option to disable the
automatic savepoint on a per-statement basis so that when sending the
manual savepoint the user can explicitly choose to not protect it with an
automatically released savepoint. Though I haven't and won't be testing
this theory myself it seems like it should be doable.
For statements that are just a manual savepoint such a temporary mode
switch could be done automatically.
Hack the server to have savepoints that do not get automatically released,
but can only be released at commit or by naming them explicitly.
BEGIN;
SAVEPOINT AUTO1
INSERT
RELEASE SAVEPOINT AUTO1
SAVEPOINT AUTO2
SAVEPOINT MANUAL1 FIXED
INSERT
RELEASE SAVEPOINT AUTO2
-- MANUAL1 still exists here
SAVEPOINT AUTO3
INSERT
RELEASE SAVEPOINT AUTO3
RELEASE SAVEPOINT MANUAL1
-- No more savepoints
COMMIT;
The manual bypass per-statement seems like a fairly simple solution to at
least provide usability if not the most ideal user experience.
Auto-detection would end up being interesting if the statement has multiple
commands of which savepoint is just one of them.
Hacking the server seems unlikely to be accepted.
David J.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2024-01-18 22:20 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-01-15 10:30 BUG #18294: Manual savepoints do not work with JDBC param cleanupSavepoints=true PG Bug reporting form <noreply@postgresql.org>
2024-01-16 14:45 ` Dave Cramer <davecramer@postgres.rocks>
2024-01-18 22:20 ` David G. Johnston <david.g.johnston@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox