hangs when same primary key used in different tests

[nhjk]

When you insert the same primary key in two different transactions your tests will hang.

With the code below, the following happens

A inserting
A inserted
A db closed
B inserting <-- hangs here

db schema

-- CREATE DATABASE example;
CREATE TABLE example_table (
  name text PRIMARY KEY
);

test file example_test.go

package main

import (
    "database/sql"
    "fmt"
    "log"
    "testing"
    "time"

    txdb "github.com/DATA-DOG/go-txdb"
    _ "github.com/jackc/pgx/stdlib"
)

var (
    dsn    = "postgres://postgres@localhost/example?sslmode=disable"
    query  = "insert into example_table (name) values ('hello world')"
    driver = "pgx"
)

func init() {
    txdb.Register("txdb", driver, dsn)
}

func TestA(t *testing.T) {
    // setup
    db, err := sql.Open("txdb", "A")
    if err != nil {
        panic(err)
    }
    defer func() {
        db.Close()
        fmt.Println("A db closed")
    }()

    // insert
    fmt.Println("A inserting")
    _, err = db.Query(query)
    if err != nil {
        t.Fatal(err)
    }
    fmt.Println("A inserted")
}

func TestB(t *testing.T) {
    // setup
    db, err := sql.Open("txdb", "B")
    if err != nil {
        panic(err)
    }
    defer func() {
        db.Close()
        fmt.Println("B db closed")
    }()

    // insert
    fmt.Println("B inserting")
    _, err = db.Query(query)
    if err != nil {
        panic(err)
    }
    fmt.Println("B inserted")
}

I have tried this code with the postgres drivers _ "github.com/jackc/pgx/stdlib" and _ github.com/lib/pq

[l3pp4rd]

Hi, very interesting, because it may be a pq driver issue. try to make the same two trasactions in pq driver. Because txdb only wraps up the driver usage, it does not add anything to it, just every new connection opened is running on a separate transaction, that is all the magic it does. If the issue would be coming from txdb, it would be a surprise.

so could you try just to replicate it without txdb, if that cannot happen, then I will need to see where a problem might be, an interesting issue indeed

[nhjk]

I thought it might be the driver that's why I tried both pq and pgx, I also did tests with just the drivers. Full test file (with pq this time):

package main

import (
    "database/sql"
    "fmt"
    "log"
    "testing"

    txdb "github.com/DATA-DOG/go-txdb"
    _ "github.com/lib/pq"
)

var (
    dsn    = "postgres://postgres@localhost/example?sslmode=disable"
    query  = "insert into example_table (name) values ('hello world')"
    driver = "postgres"
)

func init() {
    txdb.Register("txdb", driver, dsn)
}

func TestA(t *testing.T) {
    // setup
    db, err := sql.Open("txdb", "A")
    if err != nil {
        panic(err)
    }
    defer func() {
        db.Close()
        fmt.Println("A db closed")
    }()

    // insert
    fmt.Println("A inserting")
    _, err = db.Query(query)
    if err != nil {
        t.Fatal(err)
    }
    fmt.Println("A inserted")
}

func TestB(t *testing.T) {
    // setup
    db, err := sql.Open("txdb", "B")
    if err != nil {
        panic(err)
    }
    defer func() {
        db.Close()
        fmt.Println("B db closed")
    }()

    // insert
    fmt.Println("B inserting")
    _, err = db.Query(query)
    if err != nil {
        panic(err)
    }
    fmt.Println("B inserted")
}

func TestC(t *testing.T) {
    fmt.Println("C")
    // setup
    db, err := sql.Open(driver, dsn)
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    tx, err := db.Begin()
    if err != nil {
        panic(err)
    }
    defer func() {
        if err := tx.Rollback(); err != nil {
            fmt.Println("C rollback err", err)
        }
    }()

    // insert
    fmt.Println("C inserting")
    _, err = tx.Query(query)
    if err != nil {
        t.Fatal(err)
    }
    fmt.Println("C inserted")
}

func TestD(t *testing.T) {
    fmt.Println("D")
    // setup
    db, err := sql.Open(driver, dsn)
    if err != nil {
        log.Fatal(err)
    }

    tx, err := db.Begin()
    if err != nil {
        panic(err)
    }
    defer func() {
        if err := tx.Rollback(); err != nil {
            fmt.Println("D rollback err", err)
        }
    }()

    // insert
    fmt.Println("D inserting")
    _, err = tx.Query(query)
    if err != nil {
        t.Fatal(err)
    }
    fmt.Println("D inserted")
}

If you do go test -run 'C|D':

C
C inserting
C inserted
D
D inserting
D inserted
PASS
ok      [redacted dir]      0.033s

But these tests apparently run sequentially so that's not what we're aiming for.

A better way to test is to 1) run psql begin a transaction and insert name 'hello world' psql postgres://postgres@localhost/example?sslmode=disable

example=# begin;
BEGIN
example=# insert into example_table (name) values ('hello world');
INSERT 0 1

2) run a single driver only test, it hangs as expected go test -run C outputs:

C
C inserting

3) rollback the transaction in psql

example=# rollback;
ROLLBACK

4) the go test now finishes

C inserted
PASS
ok      [redacted dir]     128.532s

Testing this way passes with both pq (go test -run C) and txdb (go test -run A). I'm not sure why this only hangs when running tests.

[l3pp4rd]

For some reason connection is busy, maybe there are number of connections limited to 1?

Test:

func TestC(t *testing.T) {
    fmt.Println("C")
    // setup
    db, err := sql.Open(driver, dsn)
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    txA, err := db.Begin()
    if err != nil {
        panic(err)
    }

    fmt.Println("C inserting A")
    _, err = txA.Query(query)
    if err != nil {
        t.Fatal(err)
    }
    fmt.Println("C inserted A")
    if err := txA.Rollback(); err != nil {
        t.Fatal(err)
    }

    txB, err := db.Begin()
    if err != nil {
        panic(err)
    }

    fmt.Println("C inserting B")
    _, err = txB.Query(query)
    if err != nil {
        t.Fatal(err)
    }
    fmt.Println("C inserted B")
    if err := txB.Rollback(); err != nil {
        t.Fatal(err)
    }
}

[l3pp4rd]

you are not closing rows, from Query, that is the reason

[l3pp4rd]

func TestC(t *testing.T) {
    fmt.Println("C")
    // setup
    db, err := sql.Open(driver, dsn)
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    if err := db.Ping(); err != nil {
        log.Fatal(err)
    }

    txA, err := db.Begin()
    if err != nil {
        panic(err)
    }

    fmt.Println("C inserting A")
    rows, err := txA.Query(query)
    if err != nil {
        t.Fatal(err)
    }
    rows.Close()

    fmt.Println("C inserted A")
    if err := txA.Rollback(); err != nil {
        t.Fatal(err)
    }

    txB, err := db.Begin()
    if err != nil {
        panic(err)
    }

    fmt.Println("C inserting B")
    rows, err = txB.Query(query)
    if err != nil {
        t.Fatal(err)
    }
    rows.Close()

    fmt.Println("C inserted B")
    if err := txB.Rollback(); err != nil {
        t.Fatal(err)
    }
}

you must close the rows, otherwise it blocks connection

[l3pp4rd]

though, it may not be easy to get it first, but when you open a transaction it sticks to that connection, any operation you will do in that tx, it will always use that single connection, so if you do query rows, you must read it fully and close it before doing something else to that transaction.

[nhjk]

Thanks so much! Sorry about the issue I should have caught that.

[l3pp4rd]

no problem, cheers