尝试向PostgreSQL插入数据时连接关闭。

huangapple go评论102阅读模式
英文:

conn closed when trying to make an insert into postgresql

问题

我尝试将用户插入到PostgreSQL数据库中,并获取三个参数以供进一步处理,但总是出现错误'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
}

在创建repository时,将connect插入到application.go中:

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

postgresql包中全局声明了Connect

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() {
	// 准备连接数据库的URL
	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("successfully connected to postgres database")
	// 关闭数据库连接
	defer Connect.Close(context.Background())
}

可能出了什么问题?

英文:

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?

答案1

得分: 2

一个单独的数据库连接不具备并发安全性,并且没有重新建立连接的机制。你的问题很可能是并发访问导致连接出错,或者由于其他原因关闭了连接。

你需要一个数据库连接池,以便为并发操作创建新的连接(net/http服务器处理连接时是并发的)。请参考 https://pkg.go.dev/github.com/jackc/pgx/v4#hdr-Connection_Pool

> *pgx.Conn 表示与数据库的单个连接,不具备并发安全性。使用子包 pgxpool 来获得并发安全的连接池。

你应该使用 pgxpool 来获得更好的结果-请参考 https://pkg.go.dev/github.com/jackc/pgx/v4@v4.14.1/pgxpool#hdr-Establishing_a_Connection

英文:

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

答案2

得分: 2

conn closed错误的原因是在init()函数中,在应用程序启动后立即关闭了连接。

defer Connect.Close(context.Background())

这行代码在连接关闭之前被延迟执行。

英文:

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

huangapple
  • 本文由 发表于 2022年1月19日 06:11:30
  • 转载请务必保留本文链接:https://go.coder-hub.com/70763022.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定