英文:
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())
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论