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

> On Sat, May 10, 2025 at 8:45 PM ikedarintarof <ikedarintarof(at)oss(dot)nttdata(dot)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(at)gmail(dot)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://neon.tech)