pgconn

package module
v1.1.0-reader Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Dec 14, 2019 License: MIT Imports: 31 Imported by: 0

README

Build Status

pgconn

Package pgconn is a low-level PostgreSQL database driver. It operates at nearly the same level as the C library libpq. It is primarily intended to serve as the foundation for higher level libraries such as https://github.com/jackc/pgx. Applications should handle normal queries with a higher level library and only use pgconn directly when required for low-level access to PostgreSQL functionality.

Example Usage

pgConn, err := pgconn.Connect(context.Background(), os.Getenv("DATABASE_URL"))
if err != nil {
	log.Fatalln("pgconn failed to connect:", err)
}
defer pgConn.Close()

result := pgConn.ExecParams(context.Background(), "SELECT email FROM users WHERE id=$1", [][]byte{[]byte("123")}, nil, nil, nil)
for result.NextRow() {
	fmt.Println("User 123 has email:", string(result.Values()[0]))
}
_, err := result.Close()
if err != nil {
	log.Fatalln("failed reading result:", err)
})

Testing

The pgconn tests require a PostgreSQL database. It will connect to the database specified in the PGX_TEST_CONN_STRING environment variable. The PGX_TEST_CONN_STRING environment variable can be a URL or DSN. In addition, the standard PG* environment variables will be respected. Consider using direnv to simplify environment variable handling.

Example Test Environment

Connect to your PostgreSQL server and run:

create database pgx_test;

Now you can run the tests:

PGX_TEST_CONN_STRING="host=/var/run/postgresql dbname=pgx_test" go test ./...
Connection and Authentication Tests

Pgconn supports multiple connection types and means of authentication. These tests are optional. They will only run if the appropriate environment variable is set. Run go test -v | grep SKIP to see if any tests are being skipped. Most developers will not need to enable these tests. See travis.yml for an example set up if you need change authentication code.

Documentation

Overview

Package pgconn is a low-level PostgreSQL database driver.

pgconn provides lower level access to a PostgreSQL connection than a database/sql or pgx connection. It operates at nearly the same level is the C library libpq.

Establishing a Connection

Use Connect to establish a connection. It accepts a connection string in URL or DSN and will read the environment for libpq style environment variables.

Executing a Query

ExecParams and ExecPrepared execute a single query. They return readers that iterate over each row. The Read method reads all rows into memory.

Executing Multiple Queries in a Single Round Trip

Exec and ExecBatch can execute multiple queries in a single round trip. They return readers that iterate over each query result. The ReadAll method reads all query results into memory.

Context Support

All potentially blocking operations take a context.Context. If a context is canceled while the method is in progress the method immediately returns. In most circumstances, this will close the underlying connection.

The CancelRequest method may be used to request the PostgreSQL server cancel an in-progress query without forcing the client to abort.

Example 
package main

import (
	"context"
	"fmt"
	"log"
	"os"

	"github.com/Slava/pgconn"
)

func main() {
	pgConn, err := pgconn.Connect(context.Background(), os.Getenv("PGX_TEST_CONN_STRING"))
	if err != nil {
		log.Fatalln(err)
	}
	defer pgConn.Close(context.Background())

	result := pgConn.ExecParams(context.Background(), "select generate_series(1,3)", nil, nil, nil, nil).Read()
	if result.Err != nil {
		log.Fatalln(result.Err)
	}

	for _, row := range result.Rows {
		fmt.Println(string(row[0]))
	}

	fmt.Println(result.CommandTag)
}

Output:

1
2
3
SELECT 3

Index

Examples

Constants

This section is empty.

Variables

This section is empty.

Functions

func NetworkAddress 

func NetworkAddress(host string, port uint16) (network, address string)

NetworkAddress converts a PostgreSQL host and port into network and address suitable for use with net.Dial.

func SafeToRetry 

func SafeToRetry(err error) bool

SafeToRetry checks if the err is guaranteed to have occurred before sending any data to the server.

func Timeout 

func Timeout(err error) bool

Timeout checks if err was was caused by a timeout. To be specific, it is true if err is or was caused by a context.Canceled, context.Canceled or an implementer of net.Error where Timeout() is true.

func ValidateConnectTargetSessionAttrsReadWrite 

func ValidateConnectTargetSessionAttrsReadWrite(ctx context.Context, pgConn *PgConn) error

ValidateConnectTargetSessionAttrsReadWrite is an ValidateConnectFunc that implements libpq compatible target_session_attrs=read-write.

Types

type AfterConnectFunc 

type AfterConnectFunc func(ctx context.Context, pgconn *PgConn) error

type Batch 

type Batch struct {
	// contains filtered or unexported fields
}

Batch is a collection of queries that can be sent to the PostgreSQL server in a single round-trip.

func (*Batch) ExecParams 

func (batch *Batch) ExecParams(sql string, paramValues [][]byte, paramOIDs []uint32, paramFormats []int16, resultFormats []int16)

ExecParams appends an ExecParams command to the batch. See PgConn.ExecParams for parameter descriptions.

func (*Batch) ExecPrepared 

func (batch *Batch) ExecPrepared(stmtName string, paramValues [][]byte, paramFormats []int16, resultFormats []int16)

ExecPrepared appends an ExecPrepared e command to the batch. See PgConn.ExecPrepared for parameter descriptions.

type BuildFrontendFunc 

type BuildFrontendFunc func(r io.Reader, w io.Writer) Frontend

BuildFrontendFunc is a function that can be used to create Frontend implementation for connection.

type CommandTag 

type CommandTag []byte

CommandTag is the result of an Exec function

func (CommandTag) RowsAffected 

func (ct CommandTag) RowsAffected() int64

RowsAffected returns the number of rows affected. If the CommandTag was not for a row affecting command (e.g. "CREATE TABLE") then it returns 0.

func (CommandTag) String 

func (ct CommandTag) String() string

type Config 

type Config struct {
	Host          string // host (e.g. localhost) or path to unix domain socket directory (e.g. /private/tmp)
	Port          uint16
	Database      string
	User          string
	Password      string
	TLSConfig     *tls.Config // nil disables TLS
	DialFunc      DialFunc    // e.g. net.Dialer.DialContext
	LookupFunc    LookupFunc  // e.g. net.Resolver.LookupHost
	BuildFrontend BuildFrontendFunc
	RuntimeParams map[string]string // Run-time parameters to set on connection as session default values (e.g. search_path or application_name)

	Fallbacks []*FallbackConfig

	// ValidateConnect is called during a connection attempt after a successful authentication with the PostgreSQL server.
	// It can be used to validate that the server is acceptable. If this returns an error the connection is closed and the next
	// fallback config is tried. This allows implementing high availability behavior such as libpq does with target_session_attrs.
	ValidateConnect ValidateConnectFunc

	// AfterConnect is called after ValidateConnect. It can be used to set up the connection (e.g. Set session variables
	// or prepare statements). If this returns an error the connection attempt fails.
	AfterConnect AfterConnectFunc

	// OnNotice is a callback function called when a notice response is received.
	OnNotice NoticeHandler

	// OnNotification is a callback function called when a notification from the LISTEN/NOTIFY system is received.
	OnNotification NotificationHandler
	// contains filtered or unexported fields
}

Config is the settings used to establish a connection to a PostgreSQL server. It must be created by ParseConfig and then it can be modified. A manually initialized Config will cause ConnectConfig to panic.

func ParseConfig 

func ParseConfig(connString string) (*Config, error)

ParseConfig builds a []*Config with similar behavior to the PostgreSQL standard C library libpq. It uses the same defaults as libpq (e.g. port=5432) and understands most PG* environment variables. connString may be a URL or a DSN. It also may be empty to only read from the environment. If a password is not supplied it will attempt to read the .pgpass file. 

# Example DSN
user=jack password=secret host=pg.example.com port=5432 dbname=mydb sslmode=verify-ca

# Example URL
postgres://jack:secret@pg.example.com:5432/mydb?sslmode=verify-ca

ParseConfig supports specifying multiple hosts in similar manner to libpq. Host and port may include comma separated values that will be tried in order. This can be used as part of a high availability system. See https://www.postgresql.org/docs/11/libpq-connect.html#LIBPQ-MULTIPLE-HOSTS for more information. 

# Example URL
postgres://jack:secret@foo.example.com:5432,bar.example.com:5432/mydb

ParseConfig currently recognizes the following environment variable and their parameter key word equivalents passed via database URL or DSN: 

PGHOST
PGPORT
PGDATABASE
PGUSER
PGPASSWORD
PGPASSFILE
PGSSLMODE
PGSSLCERT
PGSSLKEY
PGSSLROOTCERT
PGAPPNAME
PGCONNECT_TIMEOUT
PGTARGETSESSIONATTRS

See http://www.postgresql.org/docs/11/static/libpq-envars.html for details on the meaning of environment variables.

See https://www.postgresql.org/docs/11/libpq-connect.html#LIBPQ-PARAMKEYWORDS for parameter key word names. They are usually but not always the environment variable name downcased and without the "PG" prefix.

Important TLS Security Notes:

ParseConfig tries to match libpq behavior with regard to PGSSLMODE. This includes defaulting to "prefer" behavior if not set.

See http://www.postgresql.org/docs/11/static/libpq-ssl.html#LIBPQ-SSL-PROTECTION for details on what level of security each sslmode provides.

"verify-ca" mode currently is treated as "verify-full". e.g. It has stronger security guarantees than it would with libpq. Do not rely on this behavior as it may be possible to match libpq in the future. If you need full security use "verify-full".

Other known differences with libpq:

If a host name resolves into multiple addresses, libpq will try all addresses. pgconn will only try the first.

When multiple hosts are specified, libpq allows them to have different passwords set via the .pgpass file. pgconn does not.

In addition, ParseConfig accepts the following options: 

min_read_buffer_size
	The minimum size of the internal read buffer. Default 8192.

type DialFunc 

type DialFunc func(ctx context.Context, network, addr string) (net.Conn, error)

DialFunc is a function that can be used to connect to a PostgreSQL server.

type FallbackConfig 

type FallbackConfig struct {
	Host      string // host (e.g. localhost) or path to unix domain socket directory (e.g. /private/tmp)
	Port      uint16
	TLSConfig *tls.Config // nil disables TLS
}

FallbackConfig is additional settings to attempt a connection with when the primary Config fails to establish a network connection. It is used for TLS fallback such as sslmode=prefer and high availability (HA) connections.

type Frontend 

type Frontend interface {
	Receive() (pgproto3.BackendMessage, error)
}

Frontend used to receive messages from backend.

type LookupFunc 

type LookupFunc func(ctx context.Context, host string) (addrs []string, err error)

LookupFunc is a function that can be used to lookup IPs addrs from host.

type MultiResultReader 

type MultiResultReader struct {
	// contains filtered or unexported fields
}

MultiResultReader is a reader for a command that could return multiple results such as Exec or ExecBatch.

func (*MultiResultReader) Close 

func (mrr *MultiResultReader) Close() error

Close closes the MultiResultReader and returns the first error that occurred during the MultiResultReader's use.

func (*MultiResultReader) NextResult 

func (mrr *MultiResultReader) NextResult() bool

NextResult returns advances the MultiResultReader to the next result and returns true if a result is available.

func (*MultiResultReader) ReadAll 

func (mrr *MultiResultReader) ReadAll() ([]*Result, error)

ReadAll reads all available results. Calling ReadAll is mutually exclusive with all other MultiResultReader methods.

func (*MultiResultReader) ResultReader 

func (mrr *MultiResultReader) ResultReader() *ResultReader

ResultReader returns the current ResultReader.

type Notice 

type Notice PgError

Notice represents a notice response message reported by the PostgreSQL server. Be aware that this is distinct from LISTEN/NOTIFY notification.

type NoticeHandler 

type NoticeHandler func(*PgConn, *Notice)

NoticeHandler is a function that can handle notices received from the PostgreSQL server. Notices can be received at any time, usually during handling of a query response. The *PgConn is provided so the handler is aware of the origin of the notice, but it must not invoke any query method. Be aware that this is distinct from LISTEN/NOTIFY notification.

type Notification 

type Notification struct {
	PID     uint32 // backend pid that sent the notification
	Channel string // channel from which notification was received
	Payload string
}

Notification is a message received from the PostgreSQL LISTEN/NOTIFY system

type NotificationHandler 

type NotificationHandler func(*PgConn, *Notification)

NotificationHandler is a function that can handle notifications received from the PostgreSQL server. Notifications can be received at any time, usually during handling of a query response. The *PgConn is provided so the handler is aware of the origin of the notice, but it must not invoke any query method. Be aware that this is distinct from a notice event.

type PgConn 

type PgConn struct {
	// contains filtered or unexported fields
}

PgConn is a low-level PostgreSQL connection handle. It is not safe for concurrent usage.

func Connect 

func Connect(ctx context.Context, connString string) (*PgConn, error)

Connect establishes a connection to a PostgreSQL server using the environment and connString (in URL or DSN format) to provide configuration. See documention for ParseConfig for details. ctx can be used to cancel a connect attempt.

func ConnectConfig 

func ConnectConfig(ctx context.Context, config *Config) (pgConn *PgConn, err error)

Connect establishes a connection to a PostgreSQL server using config. config must have been constructed with ParseConfig. ctx can be used to cancel a connect attempt.

If config.Fallbacks are present they will sequentially be tried in case of error establishing network connection. An authentication error will terminate the chain of attempts (like libpq: https://www.postgresql.org/docs/11/libpq-connect.html#LIBPQ-MULTIPLE-HOSTS) and be returned as the error. Otherwise, if all attempts fail the last error is returned.

func (*PgConn) CancelRequest 

func (pgConn *PgConn) CancelRequest(ctx context.Context) error

CancelRequest sends a cancel request to the PostgreSQL server. It returns an error if unable to deliver the cancel request, but lack of an error does not ensure that the query was canceled. As specified in the documentation, there is no way to be sure a query was canceled. See https://www.postgresql.org/docs/11/protocol-flow.html#id-1.10.5.7.9

func (*PgConn) Close 

func (pgConn *PgConn) Close(ctx context.Context) error

Close closes a connection. It is safe to call Close on a already closed connection. Close attempts a clean close by sending the exit message to PostgreSQL. However, this could block so ctx is available to limit the time to wait. The underlying net.Conn.Close() will always be called regardless of any other errors.

func (*PgConn) Conn 

func (pgConn *PgConn) Conn() net.Conn

Conn returns the underlying net.Conn.

func (*PgConn) CopyFrom 

func (pgConn *PgConn) CopyFrom(ctx context.Context, r io.Reader, sql string) (CommandTag, error)

CopyFrom executes the copy command sql and copies all of r to the PostgreSQL server.

Note: context cancellation will only interrupt operations on the underlying PostgreSQL network connection. Reads on r could still block.

func (*PgConn) CopyTo 

func (pgConn *PgConn) CopyTo(ctx context.Context, w io.Writer, sql string) (CommandTag, error)

CopyTo executes the copy command sql and copies the results to w.

func (*PgConn) EscapeString 

func (pgConn *PgConn) EscapeString(s string) (string, error)

EscapeString escapes a string such that it can safely be interpolated into a SQL command string. It does not include the surrounding single quotes.

The current implementation requires that standard_conforming_strings=on and client_encoding="UTF8". If these conditions are not met an error will be returned. It is possible these restrictions will be lifted in the future.

func (*PgConn) Exec 

func (pgConn *PgConn) Exec(ctx context.Context, sql string) *MultiResultReader

Exec executes SQL via the PostgreSQL simple query protocol. SQL may contain multiple queries. Execution is implicitly wrapped in a transaction unless a transaction is already in progress or SQL contains transaction control statements.

Prefer ExecParams unless executing arbitrary SQL that may contain multiple queries.

func (*PgConn) ExecBatch 

func (pgConn *PgConn) ExecBatch(ctx context.Context, batch *Batch) *MultiResultReader

ExecBatch executes all the queries in batch in a single round-trip. Execution is implicitly transactional unless a transaction is already in progress or SQL contains transaction control statements.

func (*PgConn) ExecParams 

func (pgConn *PgConn) ExecParams(ctx context.Context, sql string, paramValues [][]byte, paramOIDs []uint32, paramFormats []int16, resultFormats []int16) *ResultReader

ExecParams executes a command via the PostgreSQL extended query protocol.

sql is a SQL command string. It may only contain one query. Parameter substitution is positional using $1, $2, $3, etc.

paramValues are the parameter values. It must be encoded in the format given by paramFormats.

paramOIDs is a slice of data type OIDs for paramValues. If paramOIDs is nil, the server will infer the data type for all parameters. Any paramOID element that is 0 that will cause the server to infer the data type for that parameter. ExecParams will panic if len(paramOIDs) is not 0, 1, or len(paramValues).

paramFormats is a slice of format codes determining for each paramValue column whether it is encoded in text or binary format. If paramFormats is nil all results will be in text protocol. ExecParams will panic if len(paramFormats) is not 0, 1, or len(paramValues).

resultFormats is a slice of format codes determining for each result column whether it is encoded in text or binary format. If resultFormats is nil all results will be in text protocol.

ResultReader must be closed before PgConn can be used again.

func (*PgConn) ExecPrepared 

func (pgConn *PgConn) ExecPrepared(ctx context.Context, stmtName string, paramValues [][]byte, paramFormats []int16, resultFormats []int16) *ResultReader

ExecPrepared enqueues the execution of a prepared statement via the PostgreSQL extended query protocol.

paramValues are the parameter values. It must be encoded in the format given by paramFormats.

paramFormats is a slice of format codes determining for each paramValue column whether it is encoded in text or binary format. If paramFormats is nil all results will be in text protocol. ExecPrepared will panic if len(paramFormats) is not 0, 1, or len(paramValues).

resultFormats is a slice of format codes determining for each result column whether it is encoded in text or binary format. If resultFormats is nil all results will be in text protocol.

ResultReader must be closed before PgConn can be used again.

func (*PgConn) IsBusy 

func (pgConn *PgConn) IsBusy() bool

IsBusy reports if the connection is busy.

func (*PgConn) IsClosed 

func (pgConn *PgConn) IsClosed() bool

IsClosed reports if the connection has been closed.

func (*PgConn) PID 

func (pgConn *PgConn) PID() uint32

PID returns the backend PID.

func (*PgConn) ParameterStatus 

func (pgConn *PgConn) ParameterStatus(key string) string

ParameterStatus returns the value of a parameter reported by the server (e.g. server_version). Returns an empty string for unknown parameters.

func (*PgConn) Prepare 

func (pgConn *PgConn) Prepare(ctx context.Context, name, sql string, paramOIDs []uint32) (*StatementDescription, error)

Prepare creates a prepared statement. If the name is empty, the anonymous prepared statement will be used. This allows Prepare to also to describe statements without creating a server-side prepared statement.

func (*PgConn) ReceiveMessage 

func (pgConn *PgConn) ReceiveMessage(ctx context.Context) (pgproto3.BackendMessage, error)

ReceiveMessage receives one wire protocol message from the PostgreSQL server. It must only be used when the connection is not busy. e.g. It is an error to call ReceiveMessage while reading the result of a query. The messages are still handled by the core pgconn message handling system so receiving a NotificationResponse will still trigger the OnNotification callback.

This is a very low level method that requires deep understanding of the PostgreSQL wire protocol to use correctly. See https://www.postgresql.org/docs/current/protocol.html.

func (*PgConn) SecretKey 

func (pgConn *PgConn) SecretKey() uint32

SecretKey returns the backend secret key used to send a cancel query message to the server.

func (*PgConn) SendBytes 

func (pgConn *PgConn) SendBytes(ctx context.Context, buf []byte) error

SendBytes sends buf to the PostgreSQL server. It must only be used when the connection is not busy. e.g. It is as error to call SendBytes while reading the result of a query.

This is a very low level method that requires deep understanding of the PostgreSQL wire protocol to use correctly. See https://www.postgresql.org/docs/current/protocol.html.

func (*PgConn) TxStatus 

func (pgConn *PgConn) TxStatus() byte

TxStatus returns the current TxStatus as reported by the server.

func (*PgConn) WaitForNotification 

func (pgConn *PgConn) WaitForNotification(ctx context.Context) error

WaitForNotification waits for a LISTON/NOTIFY message to be received. It returns an error if a notification was not received.

type PgError 

type PgError struct {
	Severity         string
	Code             string
	Message          string
	Detail           string
	Hint             string
	Position         int32
	InternalPosition int32
	InternalQuery    string
	Where            string
	SchemaName       string
	TableName        string
	ColumnName       string
	DataTypeName     string
	ConstraintName   string
	File             string
	Line             int32
	Routine          string
}

PgError represents an error reported by the PostgreSQL server. See http://www.postgresql.org/docs/11/static/protocol-error-fields.html for detailed field description.

func ErrorResponseToPgError 

func ErrorResponseToPgError(msg *pgproto3.ErrorResponse) *PgError

ErrorResponseToPgError converts a wire protocol error message to a *PgError.

func (*PgError) Error 

func (pe *PgError) Error() string

type Result 

type Result struct {
	FieldDescriptions []pgproto3.FieldDescription
	Rows              [][][]byte
	CommandTag        CommandTag
	Err               error
}

Result is the saved query response that is returned by calling Read on a ResultReader.

type ResultReader 

type ResultReader struct {
	// contains filtered or unexported fields
}

ResultReader is a reader for the result of a single query.

func (*ResultReader) Close 

func (rr *ResultReader) Close() (CommandTag, error)

Close consumes any remaining result data and returns the command tag or error.

func (*ResultReader) FieldDescriptions 

func (rr *ResultReader) FieldDescriptions() []pgproto3.FieldDescription

FieldDescriptions returns the field descriptions for the current result set. The returned slice is only valid until the ResultReader is closed.

func (*ResultReader) NextRow 

func (rr *ResultReader) NextRow() bool

NextRow advances the ResultReader to the next row and returns true if a row is available.

func (*ResultReader) Read 

func (rr *ResultReader) Read() *Result

Read saves the query response to a Result.

func (*ResultReader) Values 

func (rr *ResultReader) Values() [][]byte

Values returns the current row data. NextRow must have been previously been called. The returned [][]byte is only valid until the next NextRow call or the ResultReader is closed. However, the underlying byte data is safe to retain a reference to and mutate.

type StatementDescription 

type StatementDescription struct {
	Name      string
	SQL       string
	ParamOIDs []uint32
	Fields    []pgproto3.FieldDescription
}

type ValidateConnectFunc 

type ValidateConnectFunc func(ctx context.Context, pgconn *PgConn) error

Source Files

View all Source files

Directories

Path Synopsis
internal
ctxwatch
Package stmtcache is a cache that can be used to implement lazy prepared statements.
 Package stmtcache is a cache that can be used to implement lazy prepared statements.

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.