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?

CodePudding user response：

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/[email protected]/pgxpool#hdr-Establishing_a_Connection

CodePudding user response：

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())