unexpected EOF / conn closed

[mhclaudiu]

Hello! And sorry for bother you. I've switched from standard pq package to pgx. I'm using latest postgres v14 on debian. Standard pq query that i was using:

func (db *_DB) LoadCfg(app *_App, print bool) error {

    ctx, cancel := context.WithTimeout(context.Background(), 2*time.Second)
    defer cancel()

    q := fmt.Sprintf("SELECT cfg FROM services WHERE name = '%s'", app.Name)
    err := db.Conn.QueryRowContext(ctx, q).Scan(&app.Cfg)
    if err != nil {
        if print {
            log.Println((err.Error() + " - " + q))
        }
        return errors.New((err.Error() + " - " + q))
    }
        log.Printf("%v", app.Cfg)

    return nil
}

With Pgx only this line is different: err = db.Conn.QueryRow(ctx, q).Scan(&app.Cfg) LoadCfg is a func called once in main, which will query the database(and run succesfully), after that a cron is calling it every 10seconds, and now errors will come. In standard pq there were no such error messages, is something that i've done wrong?

2022/03/31 09:50:00 {15 80 4 [02:00 04:00] true}
2022/03/31 09:50:10 unexpected EOF
2022/03/31 09:50:20 conn closed
2022/03/31 09:50:30 conn closed

The first line is the first call of LoadCfg function, after that all cals are at every 10seconds.

[jackc]

My guess is you're using a connection when you want to use a pool.

[mhclaudiu]

Thanks! Well this is the connect func

func (db *_DB) Connect() error {

    var err error
    ctx, cancel := context.WithTimeout(context.Background(), 2*time.Second)
    defer cancel()
    db.Conn, err = pgx.Connect(ctx, fmt.Sprintf("postgres://%s:%s@%s/%s?sslmode=disable))
}

And after that i.m using db.Conn.QueryRowContext I don't understand how to use a 'pool' instead of the connection itself. As i can see from SQL package, using Query without context it will use the same connection, and spawning a new connection with QueryContext. And in pgx all querys are by default with Context?

Edit: I've changed my code to this: Connect func:

func (db *_DB) Connect() error {
    db.Conn, err = pgxpool.Connect(context.Background(),...)
}

Query func:

func (db *_DB) LoadCfg(app *_App, print bool) error {
c, err := db.Conn.Acquire(context.Background())
    if err != nil {
        log.Println(err)
    }
    defer c.Release()
    err = c.Ping(context.Background())
    if err != nil {
        log.Println(err)
    }

    q := fmt.Sprintf("SELECT cfg::json FROM services WHERE name = '%s'", app.Name)
    err = c.QueryRow(context.Background(), q).Scan(&app.Cfg)
}

After the change:

2022/04/04 10:56:20 {15 20 4 00:00-04:00 true 10} 2022/04/04 10:56:30 unexpected EOF 2022/04/04 10:56:30 {15 20 4 00:00-04:00 true 10} 2022/04/04 10:56:40 unexpected EOF

After each query, i'm having unexpected EOF

[jackc]

Can you make a standalone example that exhibits this behavior?

Also, a couple other notes:

• You don't need to manually acquire a connection from the pool unless you are running a sequence of queries you want to ensure are run on the same connection. You can call Query, QueryRow, etc. directly on the pool.
• You should not use fmt.Sprintf to interpolate SQL parameters. This opens you up to SQL injection. pgx supports parameterized queries. Use them instead. e.g. c.QueryRow(context.Background(), "SELECT cfg::json FROM services WHERE name = $1", app.Name)

[mhclaudiu]

Thank you for the tips! I will be the only one using this app, but it's good to use it for best practice. I have made a standalone example main.go

package main

import (
    "log"
    "time"
)

func main() {

    var app _App

    app.Name = "Memory-Guard"
    app.Cfg.Init()

    db := _DB{
        User: "services",
        Pass: "services",
        Addr: "dbfs:5432",
        Name: "services",
    }

    for err := db.Connect(); err != nil; {

        log.Printf("Retrying connection to DB.. %s", err.Error())
        time.Sleep(5000 * time.Millisecond)
    }

    log.Printf("Succesfully connected to DB Server '%s'", db.Addr)
    defer db.Close()

    log.Printf("appCFG - %v", app.Cfg)

    for err := db.LoadCfg(&app, false); err != nil; {
        log.Printf("Retrying loading config from DB.. %s", err.Error())
        time.Sleep(5000 * time.Millisecond)
    }

    log.Printf("Succesfully loaded config from DB..")

    go app.AddCron(func() { db.LoadCfg(&app, true) }, 10)

    select {}
}

app.go

package main

import (
    "time"
)

type _App struct {
    Name string `json:"name"`
    Cfg  _Cfg
}

type _Cfg struct {
    CheckInterval int64  `json:"CheckInterval"`
    MaxPercent    uint64 `json:"MaxPercentt"`
    MaxRetr       uint   `json:"MaxRetr"`
}

func (c *_Cfg) Init() {
    *c = _Cfg{
        CheckInterval: 15,
        MaxPercent:    80,
        MaxRetr:       4,
    }
}

func (app *_App) AddCron(f func(), v time.Duration) {

    for {
        time.Sleep(v * time.Second)
        f()
    }
}

db.go

package main

import (
    "context"
    "errors"
    "fmt"
    "log"

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

type _DB struct {
    User   string
    Pass   string
    Addr   string
    Name   string
    Conn   *pgx.Conn
    Status bool
    AppCfg _Cfg
}

func (db *_DB) Connected() bool {
    return db.Status
}

func (db *_DB) Close() {
    db.Conn.Close(context.Background())
}

func (db *_DB) Connect() error {

    var err error
    db.Conn, err = pgx.Connect(context.Background(), fmt.Sprintf("postgres://%s:%s@%s/%s?sslmode=disable&TimeZone=Europe%%2FBucharest", db.User, db.Pass, db.Addr, db.Name))

    if err != nil {
        db.Status = false
        return errors.New(err.Error())
    }

    db.Status = true

    return nil
}

func (db *_DB) LoadCfg(app *_App, print bool) error {

    err := db.Conn.QueryRow(context.Background(), "SELECT cfg::json FROM services WHERE name = $1", app.Name).Scan(&db.AppCfg)
    if err != nil {
        if print {
            log.Println(err)
        }
        return errors.New(err.Error())
    }

    log.Printf("dbCFG - %v", db.AppCfg)

    return nil
}

2022/04/05 11:12:22 Succesfully connected to DB Server 'dbfs:5432' 2022/04/05 11:12:22 appCFG - {15 80 4} 2022/04/05 11:12:22 Succesfully loaded config from DB.. 2022/04/05 11:12:22 dbCFG - {15 25 4} 2022/04/05 11:12:32 unexpected EOF 2022/04/05 11:12:42 conn closed

I had run app on both debian11 and windows10, with same results.

[jackc]

Works for me on MacOS:

jack@glados ~/dev/pgx_issues/pgx-1178 ±master⚡ » go run .
2022/04/09 07:57:20 Succesfully connected to DB Server 'dbfs:5432'
2022/04/09 07:57:20 appCFG - {15 80 4}
2022/04/09 07:57:20 dbCFG - {0 0 0}
2022/04/09 07:57:20 Succesfully loaded config from DB..
2022/04/09 07:57:30 dbCFG - {0 0 0}
2022/04/09 07:57:40 dbCFG - {0 0 0}
2022/04/09 07:57:50 dbCFG - {0 0 0}
2022/04/09 07:58:00 dbCFG - {0 0 0}

[mhclaudiu]

Thank you. Well this is strange since i really don't know what i'm doing wrong. More over standard lib pq is running since days without errors. Maybe the latest v14 of postgresql have some things changed? Or it matters if i'm using patroni? I really want moving to pgx but i just can't understand why i have such errors.

[jackc]

I'm unfamiliar with "patroni" but if it is anything in between your application and the actual PostgreSQL server then it is possibly the issue. Testing a direct connection would be useful.

I do know that some proxies like PgBouncer do not work well with prepared statements. Maybe try connecting with prefer_simple_protocol=true. Also, if "patroni" is some sort of proxy then presumably it has logs that might be informative.

[linabellbiu]

I have encountered the same issue with EOF error