Named prepared statement in pgx lib, how does it work?

Question

Introduction

database/sql

In the Go standard sql library, the *Stmt type has methods defined like:

func (s *Stmt) Exec(args ...interface{}) (Result, error)
func (s *Stmt) Query(args ...interface{}) (*Rows, error)

The a new (unnamed) statement is prepared by:

func (db *DB) Prepare(query string) (*Stmt, error)

1. Connection pool is abstracted and not directly accessible
2. A transaction is prepared on a single connection
3. If the connection is not available at statment execution time, it will be re-prepared on a new connection.

pgx

The PreparedStatement type doesn't have any methods defined. A new named prepared statement is prepared by:

func (p *ConnPool) Prepare(name, sql string) (*PreparedStatement, error)

1. Operations are directly on the connection pool
2. The transaction gets prepared on all connections of the pool
3. There is no clear way how to execute the prepared statement

In a Github comment, the author explains better the differences of architecture between pgx and database/sql. The documentation on Prepare also states (emphasis mine):

Prepare is idempotent; i.e. it is safe to call Prepare multiple times with the same name and sql arguments. This allows a code path to Prepare and Query/Exec/PrepareEx without concern for if the statement has already been prepared.

Small example

package main

import (
    "github.com/jackc/pgx"
)

func main() {
    conf := pgx.ConnPoolConfig{
        ConnConfig: pgx.ConnConfig{
            Host:     "/run/postgresql",
            User:     "postgres",
            Database: "test",
        },
        MaxConnections: 5,
    }
    db, err := pgx.NewConnPool(conf)
    if err != nil {
        panic(err)
    }
    _, err = db.Prepare("my-query", "select $1")
    if err != nil {
        panic(err)
    }
    // What to do with the prepared statement?
}

Question(s)

1. The name argument gives me the impression it can be executed by calling it by name, but how?
2. The documentation gives the impression that Query/Exec methods somehow leverage the prepared statements. However, those methods don't take a name argument. How does it match them?
3. Presumably, matching is done by the query content. Then what's the whole point of naming statements?

Possible answers

This is how far I got myself:

1. There are no methods that refer to the queries by name (assumption)
2. Matching is done on the query body in conn.ExecEx(). If it is not yet prepared, it will be done:

ps, ok := c.preparedStatements[sql]
            if !ok {
                var err error
                ps, err = c.prepareEx("", sql, nil)
                if err != nil {
                    return "", err
                }
            }

3. PosgreSQL itself needs it for something (assumption).

Chiccory

(1.), (2.) from 1. it's clear that you can pass the name as the sql argument to Query/QueryRow/Exec. this and this confirm this and the sendPreparedQuery method then prepends the name with EXECUTE. – Interception

(3.) Don't get confused by the name of the parameter, when you pass in the name of the prepared statement then sql will hold the name of the prepared statement, that's when you get a match on the lines linked in 2. – Interception

Answer 1

@mkopriva pointed out that the sql text was misleading me. It has a double function here. If the sql variable does not match to a key in the c.preparedStatements[sql] map, the query contained in the sql gets prepared and a new *PreparedStatement struct is appointed to ps. If it did match a key, the ps variable will point to an entry of the map.

So effectively you can do something like:

package main

import (
    "fmt"

    "github.com/jackc/pgx"
)

func main() {
    conf := pgx.ConnPoolConfig{
        ConnConfig: pgx.ConnConfig{
            Host:     "/run/postgresql",
            User:     "postgres",
            Database: "test",
        },
        MaxConnections: 5,
    }
    db, err := pgx.NewConnPool(conf)
    if err != nil {
        panic(err)
    }
    if _, err := db.Prepare("my-query", "select $1::int"); err != nil {
        panic(err)
    }
    row := db.QueryRow("my-query", 10)
    var i int
    if err := row.Scan(&i); err != nil {
        panic(err)
    }
    fmt.Println(i)
}

Chiccory

Prepare() isn't a Pool method, its a Connection method. So you need to pick a connection from the pool, and then do Prepare on it – Expellant