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>

1 files changed, 39 insertions, 0 deletions

diff --git a/internal/database/database_test.go b/internal/database/database_test.go
index 668da288..54e3f572 100644
--- a/internal/database/database_test.go
+++ b/internal/database/database_test.go
@@ -17,6 +17,9 @@ import (
 	"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 TestTransactSerializable(t *testing.T) {
 	}
 
 }
+
+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)
+	}
+}