Re: Suggestion to add --continue-client-on-abort option to pgbench

Hi Stepan and Matthias,

Thank you both for your replies. I agree with Matthias's detailed explanation regarding the purpose of the patch.

> Regarding the patch code, I noticed that there are duplicate case
> entries in the command-line option handling (specifically for case 18
> or case ESTATUS_OTHER_SQL_ERROR, the continue-client-on-error option).
> These duplicated cases can be merged to simplify the logic and reduce
> redundancy.

I also appreciate​ you for pointing out my mistakes in the previous version of the patch. I fixed the duplicated lines. I’ve attached the updated patch.

Best regards,
Rintaro Ikeda






>> On Sat, May 10, 2025 at 8:45 PM ikedarintarof
> <ikedarintarof@oss.nttdata.com> wrote:
>>>
>>> Hi hackers,
>>>
>>> I would like to suggest adding a new option to pgbench, which
> enables
>>> the client to continue processing transactions even if some errors
> occur
>>> during a transaction.
>>> Currently, a client stops sending requests when its transaction is
>>> aborted due to reasons other than serialization failures or
> deadlocks. I
>>> think in some cases, especially when using custom scripts, the
> client
>>> should be able to rollback the failed transaction and start a new
> one.
>>>
>>> For example, my custom script (insert_to_unique_column.sql)
> follows:
>>> ```
>>> CREATE TABLE IF NOT EXISTS test (col1 serial, col2 int unique);
>>> INSERT INTO test (col2) VALUES (random(0, 50000));
>>> ```
>>> Assume we need to continuously apply load to the server using 5
> clients
>>> for a certain period of time. However, a client sometimes stops
> when its
>>> transaction in my custom script is aborted due to a check
> constraint
>>> violation. As a result, the load on the server is lower than
> expected,
>>> which is the problem I want to address.
>>>
>>> The proposed new option solves this problem. When
>>> --continue-client-on-abort is set to true, the client rolls back
> the
>>> failed transaction and starts a new one. This allows all 5 clients
> to
>>> continuously apply load to the server, even if some transactions
> fail.
>
> +1. I've had similar cases before too, where I'd wanted pgbench to
> continue creating load on the server even if a transaction failed
> server-side for any reason. Sometimes, I'd even want that type of
> load.
>
> On Sat, 10 May 2025 at 17:02, Stepan Neretin <slpmcf@gmail.com> wrote:
>> INSERT INTO test (col2) VALUES (random(0, 50000));
>>
>> can be rewritten as:
>>
>> \setrandom val 0 50000
>> INSERT INTO test (col2) VALUES (:val) ON CONFLICT DO NOTHING;
>
> That won't test the same execution paths, so an option to explicitly
> rollback or ignore failed transactions (rather than stopping the
> benchmark) would be a nice feature.
> With e.g. ON CONFLICT DO NOTHING you'll have much higher workload if
> there are many conflicting entries, as that triggers and catches
> per-row errors, rather than per-statement. E.g. INSERT INTO ... SELECT
> ...multiple rows could conflict on multiple rows, but will fail on the
> first conflict. DO NOTHING would cause full execution of the SELECT
> statement, which has an inherently different performance profile.
>
>> This avoids transaction aborts entirely in the presence of
> uniqueness violations and ensures the client continues to issue load
> without interruption. In many real-world benchmarking scenarios, this
> is the preferred and simplest approach.
>>
>> So from that angle, could you elaborate on specific cases where this
> SQL-level workaround wouldn't be sufficient? Are there error types you
> intend to handle that cannot be gracefully avoided or recovered from
> using SQL constructs like ON CONFLICT, or SAVEPOINT/ROLLBACK TO?
>
> The issue isn't necessarily whether you can construct SQL scripts that
> don't raise such errors (indeed, it's possible to do so for nearly any
> command; you can run pl/pgsql procedures or DO blocks which catch and
> ignore errors), but rather whether we can make pgbench function in a
> way that can keep load on the server even when it notices an error.
>
> Kind regards,
>
> Matthias van de Meent
> Neon (https://urldefense.com/v3/__https://neon.tech__;!!GCTRfqYYOYGmgK_z!92h3wOeDsDRQ1abfcL8-tRZqrAQ0w5RXwLNofOa_guIgDHdYknrizKqUGkvSn1_OU-xzMRv2halvtpUX7BFE8e3aPO_1-CZDhQ$ )

Hi Matthias,

Thanks for your detailed explanation — it really helped clarify the
usefulness of the patch. I agree that the feature is indeed valuable,
and it's great to see it being pushed forward.

Regarding the patch code, I noticed that there are duplicate case
entries in the command-line option handling (specifically for case 18 or
case ESTATUS_OTHER_SQL_ERROR, the continue-client-on-error option).
These duplicated cases can be merged to simplify the logic and reduce
redundancy.

Best regards,
Stepan Neretin