From 8ed90e7096a136a2cd0cd4abf4097d5ddd9c4fad Mon Sep 17 00:00:00 2001 From: Jonathan Amsterdam Date: Wed, 2 Dec 2020 09:25:38 -0500 Subject: 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 Run-TryBot: Jonathan Amsterdam Reviewed-by: Julie Qiu --- internal/database/database_test.go | 39 ++++++++++++++++++++++++++++++++++++++ 1 file changed, 39 insertions(+) (limited to 'internal/database/database_test.go') 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) + } +} -- cgit v1.3