conn closed when trying to make an insert into postgresql

Question

I try to insert a user into postgres database and get three parameters back for futher processing but always get the error 'conn closed':

package db

import (
    "context"
    "os"

    "github.com/jackc/pgx/v4"
)

const (
    insertSql = "INSERT into users (name, email, created, status, role, password, activation_code) VALUES ($1, $2, $3, $4, $5, $6, $7) RETURNING name, email, activation_code;"
)

type userRepository struct {
    connect *pgx.Conn
}

func NewUserRepository(c *pgx.Conn) domain.UserRepository {
    return &userRepository{
        connect: c,
    }
}

func (r *userRepository) Save(u *domain.User) (string, string, string, *resterrors.RestErr) {
    var name, email, activation_code string
    if r.connect == nil {
        os.Exit(1)
    }
    if err := r.connect.QueryRow(context.Background(), insertSql, u.Name, u.Email, u.Created, u.Status, u.Role, u.Password, u.ActivationCode).Scan(&name, &email, &activation_code); err != nil {
        logger.Error("error saving user to the database: " + err.Error())
        return "", "", "", resterrors.NewIntenalServerError(resterrors.DB_ERROR)
    }
    return name, email, activation_code, nil
}

connect is inserted in application.go when respository is created

repository := db.NewUserRepository(postgresql.Connect)
userHandler := controller.NewUserHandler(service.NewUserService(repository))

Connect is globally declared in postgresql package

package postgresql

import (
    "context"
    "fmt"
    "net/url"

    "github.com/jackc/pgx/v4"
)
var (
    Connect *pgx.Conn
    username = "postgres"
    password = "***********" 
    host     = "127.0.0.1"
    port     = "5432"
    dbName   = "test"
)

func init() {
    //prepare URL to connect to database
    var err error
    datasourceName := fmt.Sprintf("postgres://%s:%s@%s:%s/%s", username, password, host, port, dbName)
    Connect, err = pgx.Connect(context.Background(), datasourceName)
    if err != nil {
        logger.Error("unable to connect to database: " + err.Error())
        panic(fmt.Errorf("unable to connect to database: %w", err))
      }
    logger.Info("successsfully connected to postgres database")
      // to close DB connection
    defer Connect.Close(context.Background())
}

What could be wrong with it?

score 2 · Answer 1 · answered Jan 18 '22 at 22:19

A single DB Connection is not concurrency safe and has no mechanism for reestablishing connections. Your issue is likely that concurrent access borked the connection, or it was closed for some other reason.

You want a DB Connection Pool so that it can create new connections for concurrent operations (requests for net/http servers handle connections concurrently). From https://pkg.go.dev/github.com/jackc/pgx/v4#hdr-Connection_Pool:

*pgx.Conn represents a single connection to the database and is not concurrency safe. Use sub-package pgxpool for a concurrency safe connection pool.

You should have better results with pgxpool - see https://pkg.go.dev/github.com/jackc/pgx/v4@v4.14.1/pgxpool#hdr-Establishing_a_Connection

Thanks for your advice. I did the suggested update and it works now! — Andrey Yaroshenko, Jan 18 '22 at 22:30

score 2 · Answer 2 · answered Jan 19 '22 at 09:33

2

The reason for the conn closed error was that the connection was closed within init() function straight after the application start.

defer Connect.Close(context.Background())

answered Jan 19 '22 at 09:33

Andrey Yaroshenko

155
9

conn closed when trying to make an insert into postgresql

2 Answers2