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.
>
> ```
> % bin/pgbench -d postgres -f ../insert_to_unique_column.sql -T 10
> --failures-detailed --continue-client-on-error
> transaction type: ../custom_script_insert.sql
> scaling factor: 1
> query mode: simple
> number of clients: 1
> number of threads: 1
> maximum number of tries: 1
> duration: 10 s
> number of transactions actually processed: 33552
> number of failed transactions: 21901 (39.495%)
> number of serialization failures: 0 (0.000%)
> number of deadlock failures: 0 (0.000%)
> number of other failures: 21901 (39.495%)
> latency average = 0.180 ms (including failures)
> initial connection time = 2.857 ms
> tps = 3356.092385 (without initial connection time)
> ```
>
> I have attached the patch. I would appreciate your feedback.
>
> Best regards,
>
> Rintaro Ikeda
> NTT DATA Corporation Japan

Hi Rintaro,

Thanks for the patch and explanation. I understand your goal is to ensure
that pgbench clients continue running even when some transactions fail due
to application-level errors (e.g., constraint violations), especially when
running custom scripts.

However, I wonder if the intended behavior can't already be achieved using
standard SQL constructs — specifically ON CONFLICT or careful transaction
structure. For example, your sample script:

CREATE TABLE IF NOT EXISTS test (col1 serial, col2 int unique);
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;

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?

Best regards,

Stepan Neretin