DataGrip retry strategies: ON CONFLICT upsert vs transaction rollback for idempotent writes

Question

GoalDetermine the most reliable approach in DataGrip to retry failed write operations without creating duplicate records when the initial attempt partially succeeded or raised a constraint violation.ConstraintThe solution must work across common databases supported by DataGrip (PostgreSQL, MySQL, SQL Server) and integrate with DataGrip's built-in 'Run' configuration 'Attempts' field for automatic retries.UncertaintyWhile both INSERT ... ON CONFLICT and transaction rollback with explicit cleanup can prevent duplicates, it is unclear which pattern DataGrip's retry mechanism handles more safely when a write fails after committing some statements within a multi-statement script.QuestionsDoes DataGrip's 'Attempts' retry feature preserve transaction boundaries when automatically re-executing a script, or does it run each attempt as a separate transaction?For PostgreSQL upsert via ON CONFLICT, how does DataGrip surface the operation outcome when a conflict triggers an update instead of an insert?

Tasadduq Burney · Answer

Direct AnswersEach retry attempt runs in a fresh transaction. DataGrip's "Attempts" setting re-executes the entire script from the start for every retry. Any partial commits from a failed attempt are rolled back before the next attempt begins. Transaction boundaries are not preserved across retries.PostgreSQL ON CONFLICT outcomes appear as row-count pairs. When a conflict triggers an UPDATE, the Result pane shows Rows updated: N and Rows inserted: 0. A clean insert shows Rows inserted: 1 and Rows updated: 0.How DataGrip Retries WorkThe "Attempts" field in a Run Configuration controls whole-script re-execution. If you set Attempts = 3 and the script fails on attempt 1, DataGrip rolls back that transaction, opens a new one, and runs the script again. This means:No partial state survives a failed attempt.Idempotency must hold within a single attempt; cross-attempt deduplication is automatic because each attempt starts from a clean snapshot.If your script contains multiple statements separated by semicolons without explicit BEGIN/COMMIT, DataGrip treats them as one transaction. Adding explicit transaction blocks changes this behavior and may interfere with the retry rollback.Upsert vs. Rollback for Idempotent WritesApproachPostgreSQLMySQLSQL ServerDataGrip VisibilityON CONFLICT / ON DUPLICATE KEY / MERGEINSERT ... ON CONFLICT DO UPDATEINSERT ... ON DUPLICATE KEY UPDATEMERGE or trigger-basedResult grid shows insert/update counts per attemptTransaction rollback + cleanupManual DELETE in catch blockSameSameOnly final commit visible; intermediate rolls hiddenUpsert syntax is preferred because:Single statement = atomic within the attempt.DataGrip surfaces whether an insert or update occurred via row counts.No extra cleanup logic to maintain.Verification StepsOpen Run Configuration → set Attempts = 3.Run a script containing a PostgreSQL upsert that will conflict on the second attempt.Inspect the Result window for each attempt: you should see Rows inserted: 1 on attempt 1, then Rows updated: 1 / Rows inserted: 0 on retries.Query the table directly to confirm only one row exists.One Diagnostic Detail NeededDoes your script use explicit BEGIN/COMMIT blocks or rely on DataGrip's implicit transaction handling? Explicit blocks can prevent the automatic rollback between retries and change the idempotency guarantees.

DataGrip retry strategies: ON CONFLICT upsert vs transaction rollback for idempotent writes

Goal

Constraint

Uncertainty

Questions

1 answer

Direct Answers

How DataGrip Retries Work

Upsert vs. Rollback for Idempotent Writes

Verification Steps

One Diagnostic Detail Needed

Your perspective belongs here.

0 question comments

Approach	PostgreSQL	MySQL	SQL Server	DataGrip Visibility
`ON CONFLICT` / `ON DUPLICATE KEY` / `MERGE`	`INSERT ... ON CONFLICT DO UPDATE`	`INSERT ... ON DUPLICATE KEY UPDATE`	`MERGE` or trigger-based	Result grid shows insert/update counts per attempt
Transaction rollback + cleanup	Manual `DELETE` in catch block	Same	Same	Only final commit visible; intermediate rolls hidden