database/sql will automatically retry queries when the underlying connection is broken. This test application is an experiment to determine if database/sql's automatic retry logic is safe.
If database/sql executes a non-idempotent query, and that query is interrupted, the automatic retry logic may cause the query to execute multiple times.
This application will connect to a database over a purposefully unreliable connection by using cavein.
This application creates a simple table with one row with a value of 0.
create table t(n int not null);
insert into t(n) values(0);
It then executes 1,000 update statements that increment that row.
update t set n=n+1;
Finally, it selects that value. If there is a discrepancy between that value and the number of queries that database/sql reported were successful, then it appears that the retry logic is incorrect.
It connects to PostgreSQL with both the pq and the pgx drivers to ensure results are not specific to one database driver.
go get -u github.com/jackc/go_database_sql_retry_bug
Create a database for the test.
createdb go_database_sql_retry_bug
Start the cavein tunnel proxy that will introduce connection drops.
cavein -local=localhost:2999 -remote=localhost:5432 -minbytes=10000 -maxbytes=20000
In another terminal run the test application (supply any needed standard PG* environment variables).
PGPORT=2999 PGPASSWORD=secret go_database_sql_retry_bug
With further information from golang/go#11978 it appears that this is not a bug in database/sql, but in the database driver(s).
Testing with: github.com/jackc/pgx/stdlib
Setup database:
drop table if exists t;
create table t(n int not null);
insert into t(n) values(0);
Exec `update t set n=n+1` 1000 times
Reported errors: 6
Reported successes: 994
Actual value of `select n from t`: 1000
Testing with: github.com/lib/pq
Setup database:
drop table if exists t;
create table t(n int not null);
insert into t(n) values(0);
Exec `update t set n=n+1` 1000 times
Reported errors: 0
Reported successes: 1000
Actual value of `select n from t`: 1001
In both cases, the actual final value is higher than the number of reported successes. This is to be expected. But in the latter case with pq, the final value is actually higher than the total number of attempts. This appears to be a bug with pq.
To reproduce results, run cavein with the following arguments:
cavein -local=localhost:2999 -remote=localhost:5432 -minbytes=10000 -maxbytes=20000 -seed=5577006791947779410