internal/database: add a test showing that pgx copy only inserts
The fast pgx CopyFrom function can only insert new rows; it does not
upsert (that is, replace existing rows). A new test demonstrates that
by copying a row with an existing primary key, and getting a
constraint violation.
This means, unfortunately, that we can't use copy for anything where
we could really use the extra speed.
However, it's still worth switching to pgx because it's still a bit
faster than lib/pq, and it's better maintained.
Change-Id: Ib11c6a80fc04cdc88d59f48f51b175b0722c4e65
Reviewed-on: https://go-review.googlesource.com/c/pkgsite/+/274215
Trust: Jonathan Amsterdam <jba@google.com>
Run-TryBot: Jonathan Amsterdam <jba@google.com>
Reviewed-by: Julie Qiu <julie@golang.org>
diff --git a/internal/database/database_test.go b/internal/database/database_test.go
index 668da28..54e3f57 100644
--- a/internal/database/database_test.go
+++ b/internal/database/database_test.go
@@ -17,6 +17,9 @@
"time"
"github.com/google/go-cmp/cmp"
+ "github.com/jackc/pgconn"
+ "github.com/jackc/pgx/v4"
+ "github.com/jackc/pgx/v4/stdlib"
"golang.org/x/pkgsite/internal/derrors"
"golang.org/x/pkgsite/internal/testing/dbtest"
)
@@ -445,3 +448,39 @@
}
}
+
+func TestCopyDoesNotUpsert(t *testing.T) {
+ // This test verifies that copying rows into a table will not overwrite existing rows.
+ ctx, cancel := context.WithTimeout(context.Background(), testTimeout)
+ defer cancel()
+ conn, err := testDB.db.Conn(ctx)
+ if err != nil {
+ t.Fatal(err)
+ }
+
+ for _, stmt := range []string{
+ `DROP TABLE IF EXISTS test_copy`,
+ `CREATE TABLE test_copy (i INTEGER PRIMARY KEY)`,
+ `INSERT INTO test_copy (i) VALUES (1)`,
+ } {
+ if _, err := testDB.Exec(ctx, stmt); err != nil {
+ t.Fatal(err)
+ }
+ }
+
+ err = conn.Raw(func(c interface{}) error {
+ stdConn, ok := c.(*stdlib.Conn)
+ if !ok {
+ t.Skip("DB driver is not pgx")
+ }
+ rows := [][]interface{}{{1}, {2}}
+ _, err = stdConn.Conn().CopyFrom(ctx, []string{"test_copy"}, []string{"i"}, pgx.CopyFromRows(rows))
+ return err
+ })
+
+ const constraintViolationCode = "23505"
+ var gerr *pgconn.PgError
+ if !errors.As(err, &gerr) || gerr.Code != constraintViolationCode {
+ t.Errorf("got %v, wanted code %s", gerr, constraintViolationCode)
+ }
+}
