postgresql - 尝试插入 postgresql 时 conn 关闭

Question

我尝试将用户插入 postgres 数据库并取回三个参数以进行进一步处理，但总是收到错误“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在创建respository时插入application.go

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

Connect 在 postgresql 包中全局声明

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

这有什么问题吗？

Answer 1

单个数据库连接不是并发安全的，并且没有重新建立连接的机制。您的问题可能是并发访问中断了连接，或者由于其他原因关闭了连接。

您需要一个数据库连接池，以便它可以为并发操作创建新连接(对 net/http 服务器的请求同时处理连接)。来自 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.

使用 pgxpool 应该会有更好的结果 - 参见 https://pkg.go.dev/github.com/jackc/pgx/v4@v4.14.1/pgxpool#hdr-Establishing_a_Connection