On Sat, May 10, 2025 at 8:45 PM ikedarintarof <ikedarinta...@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. > > ``` > % 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
