Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

postgresql

go

pgx

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
                }
            }
  1. PosgreSQL itself needs it for something (assumption).
like image 806
Tim Avatar asked Feb 10 '19 18:02

Tim


1 Answers

@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)
}
like image 69
Tim Avatar answered Nov 03 '22 21:11

Tim