On Sun, May 11, 2025 at 7:07 PM Matthias van de Meent < boekewurm+postg...@gmail.com> wrote:
> > 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. > > +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 <slp...@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://neon.tech) > 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
