conn.Exec() function has a bug.

[Necoo33]

Hello, for past 2 days i'm learning how to use your driver and i'm found an interesting thing. When i use "Exec" function and try to insert a row to database, it inserts that row 2 times. Code is like below:

package main

import (
    "context"
    "os"
    "fmt"
    "github.com/jackc/pgx/v5"
        "github.com/jackc/pgx/v5/pgxpool"
)

func InsertRow(conn *pgxpool.Conn) {
    _, err := conn.Exec(context.Background(), "INSERT INTO goschema.goprods(id, ptype, price, name) VALUES(16, 'Food', '70', 'Bla Bla Food')")

    if err != nil {
        fmt.Fprintf(os.Stderr, "İnserting Failed: %v\n", err)
        os.Exit(1)
    }
}

func main() {
    conn, err := dbpool.New(context.Background(), os.Getenv("DATABASE_URL"))
    if err != nil {
        log.Fatal(err)
    }
    defer dbpool.Close(context.Background())

    singleConn, err := dbpool.Acquire(context.Background())

    if err != nil {
        fmt.Print(err)
    }

    InsertRow(singleConn)

    singleConn.Release()
}

My Go Version is 1.20.4

My pgx version v5.4.0

My Postgres Version is 15

When i use "Query" function for inserting row that worked correct but "Exec" function isn't.

[jackc]

The example doesn't compile.

[Necoo33]

Bug is in Exec function, when you do insert query it inserts twice. This is obviously will not work, because there is no postgres credentials entered on dbpool.New() functions second argument. I also deleted log import however, but the main thing is on Exec function. When i do the same query with "Query" function it inserts only once.

[jackc]

It literally doesn't compile:

jack@glados ~/dev/pgx_issues/pgx-1647 ±master⚡ » go run .
Alias tip: gor .
# github.com/jackc/pgx_issues/pgx-1647
./main.go:22:15: undefined: dbpool
./main.go:26:8: undefined: dbpool
./main.go:28:21: undefined: dbpool

[Necoo33]

Okay, i take this from my ide:

package main

import (
    "context"
    "fmt"
    "os"

    "github.com/jackc/pgx/v5"
    "github.com/jackc/pgx/v5/pgxpool"
)

func main(){
    dbpool, err := pgxpool.New(context.Background(), "There is my postgres credentials")
    if err != nil {
        fmt.Fprintf(os.Stderr, "Unable to create connection pool: %v\n", err)
        os.Exit(1)
    }

    singleConn, err := dbpool.Acquire(context.Background())

    if err != nil {
        fmt.Print(err)
    }

    InsertRow(singleConn)

    singleConn.Release()

    defer dbpool.Close()
}

func InsertRow(conn *pgxpool.Conn) {
    _, err := conn.Exec(context.Background(), "INSERT INTO goschema.goprods(id, ptype, price, name) VALUES(16, 'Food', '70', 'Bla Bla Food')")

    if err != nil {
        fmt.Fprintf(os.Stderr, "İnserting Failed: %v\n", err)
        os.Exit(1)
    }
}

[jackc]

Works for me.

jack@glados ~/dev/pgx_issues/pgx-1647 ±master⚡ » psql --no-psqlrc -f structure.sql
psql:structure.sql:1: NOTICE:  drop cascades to table goschema.goprods
DROP SCHEMA
CREATE SCHEMA
CREATE TABLE
jack@glados ~/dev/pgx_issues/pgx-1647 ±master⚡ » go run .
Alias tip: gor .
jack@glados ~/dev/pgx_issues/pgx-1647 ±master⚡ » psql --no-psqlrc -c 'select * from goschema.goprods'
 id | ptype | price |     name
----+-------+-------+--------------
 16 | Food  | 70    | Bla Bla Food
(1 row)

Where structure.sql is:

drop schema if exists goschema cascade;
create schema goschema;
create table goschema.goprods (
    id int,
    ptype text,
    price text,
    name text
);

and main.go is:

package main

import (
    "context"
    "fmt"
    "os"

    "github.com/jackc/pgx/v5/pgxpool"
)

func main() {
    dbpool, err := pgxpool.New(context.Background(), os.Getenv("DATABASE_URL"))
    if err != nil {
        fmt.Fprintf(os.Stderr, "Unable to create connection pool: %v\n", err)
        os.Exit(1)
    }

    singleConn, err := dbpool.Acquire(context.Background())

    if err != nil {
        fmt.Print(err)
    }

    InsertRow(singleConn)

    singleConn.Release()

    defer dbpool.Close()
}

func InsertRow(conn *pgxpool.Conn) {
    _, err := conn.Exec(context.Background(), "INSERT INTO goschema.goprods(id, ptype, price, name) VALUES(16, 'Food', '70', 'Bla Bla Food')")

    if err != nil {
        fmt.Fprintf(os.Stderr, "İnserting Failed: %v\n", err)
        os.Exit(1)
    }
}

[Necoo33]

sometimes it inserts only one row but most of the time it inserts same rows twice. I'm using windows 11 as operating system, visual studio code as my code editor. I created my postgres database from pg admin 4. The sql code for that database is:

CREATE TABLE IF NOT EXISTS goschema.goprods
(
    id integer NOT NULL,
    ptype text COLLATE pg_catalog."default" NOT NULL,
    price text COLLATE pg_catalog."default" NOT NULL,
    name text COLLATE pg_catalog."default",
    CONSTRAINT goproducts_pkey PRIMARY KEY (id)
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS goschema.goprods
    OWNER to neco; 

Because this individual database couldn't include rows that has same id, i take this error:

İnserting Failed: ERROR: duplicate key value violates unique constraint "goproducts_pkey" (SQLSTATE 23505) exit status 1