Documentation
¶
Overview ¶
Package pgio reads and writes gobi Frames against PostgreSQL / PostGIS databases.
Depends on github.com/jackc/pgx/v5 in native mode (not the database/sql wrapper). Native mode is chosen deliberately:
- pgx.CopyFrom gives bulk INSERT throughput 10-100× the naive "INSERT ... VALUES" loop that database/sql would force. PostGIS workflows tend to be bulk-load heavy, and losing this for architectural consistency with gpkgio (which uses database/sql via modernc.org/sqlite) is a bad trade.
- pgx's pgtype layer knows how to decode PostGIS geometry OIDs into structured values. WKB / EWKB round-trips are cleaner without an intermediate sql.RawBytes conversion.
Everything is pure Go — pgx has no cgo dependency, matching the gobi-wide constraint.
The package offers three entry points, mirroring the shape of parquetio / gpkgio:
ReadQuery / ReadTable materialize the whole result set as a single Frame. Peak memory scales with the query's row count.
ReadTableChunksFunc / ReadQueryChunksFunc stream results as record-batch-sized Frames. Peak memory bounded to one batch.
ScanTable / ScanQuery return gobi.LazyFrames. Participates in the optimizer's projection + predicate pushdown — SELECT column lists narrow to what the plan actually uses, and Filter above the scan translates to SQL WHERE via gobi.ExprToSQL.
Writes go through WriteTable, which uses pgx.CopyFrom under the hood for throughput. The target table must already exist — PostgreSQL's DDL is too tied to the caller's schema conventions for us to auto-create.
Geometry columns are returned as arrow Binary tagged with gobi.GeometryField metadata carrying the source SRID. The underlying bytes are WKB (Well-Known Binary) — pgio strips the EWKB SRID prefix on read and reattaches it on write via the gpkg-style GPB header. Callers who need EWKB directly can use pgx.Query themselves.
Index ¶
- Variables
- func ReadQuery(ctx context.Context, conn Conn, sql string, args ...any) (*gobi.Frame, error)
- func ReadQueryChunksFunc(ctx context.Context, conn Conn, sql string, args []any, pool memory.Allocator, ...) error
- func ReadTable(ctx context.Context, conn Conn, table string, opts *ReadOptions) (*gobi.Frame, error)
- func ReadTableChunksFunc(ctx context.Context, conn Conn, table string, opts *ReadOptions, ...) error
- func ScanSchema(ctx context.Context, conn Conn, table string, opts *ReadOptions) (*arrow.Schema, error)
- func ScanTable(ctx context.Context, conn Conn, table string, opts *ReadOptions) *gobi.LazyFrame
- func WriteTable(ctx context.Context, conn Conn, table string, df *gobi.Frame, ...) error
- type Conn
- type ReadOptions
- type WriteOptions
Constants ¶
This section is empty.
Variables ¶
var ErrCopyFromRequired = errors.New("pgio: Conn does not support CopyFrom; use *pgx.Conn or *pgxpool.Pool")
ErrCopyFromRequired is returned when WriteTable is called with a Conn that doesn't support CopyFrom. Standard pgx.Conn and pgxpool.Pool both do; the error surfaces if a custom Conn implementation left the method un-implemented.
Functions ¶
func ReadQuery ¶
ReadQuery runs sql against conn and materializes the result into a Frame. Column types are inferred from the query's field descriptions (pgx returns the PostgreSQL OID for each column, which maps to an arrow.DataType via oidToArrowType).
Geometry columns are detected by comparing the column's OID against the connection's geometry OID (queried lazily via selectGeometryOID). Geometry values come back as raw EWKB bytes; pgio strips the EWKB SRID header so the resulting Binary column carries plain WKB, matching gobi's on-Frame convention.
Peak memory scales with the query's row count. For bounded memory, use ReadQueryChunksFunc.
func ReadQueryChunksFunc ¶
func ReadQueryChunksFunc(ctx context.Context, conn Conn, sql string, args []any, pool memory.Allocator, batchSize int, fn func(*gobi.Frame) error) error
ReadQueryChunksFunc streams the result of sql through fn one batch at a time (batches of ~64k rows by default). Peak memory bounded to one batch.
pool overrides the arrow allocator for the produced Frames; pass nil for memory.DefaultAllocator. Callers plumbing through a ReadOptions should use resolveAllocator(opts) at the call site.
The Frame passed to fn owns its arrow buffers; if the callback wants to keep the Frame past its return, call frame.Retain() and match it with a Release() later.
func ReadTable ¶
func ReadTable(ctx context.Context, conn Conn, table string, opts *ReadOptions) (*gobi.Frame, error)
ReadTable reads a whole table (with optional projection + WHERE) into a Frame. Convenience wrapper over ReadQuery that builds the SELECT internally, including `ST_AsBinary(geom_col) AS geom_col` wrappers for known geometry columns — so callers don't have to hand-craft the query to get WKB out.
func ReadTableChunksFunc ¶
func ReadTableChunksFunc(ctx context.Context, conn Conn, table string, opts *ReadOptions, fn func(*gobi.Frame) error) error
ReadTableChunksFunc streams a whole table (with optional filters via opts) through fn in batches. Same shape as ReadQueryChunksFunc but uses the built-in table-select builder.
func ScanSchema ¶
func ScanSchema(ctx context.Context, conn Conn, table string, opts *ReadOptions) (*arrow.Schema, error)
ScanSchema returns the arrow.Schema ReadTable would produce for the given table/opts. Requires a live connection — schema inference is done by prepared-statement introspection against PostgreSQL. Cheap: one round-trip.
func ScanTable ¶
ScanTable returns a LazyFrame that reads a whole table when Collect is called. Participates in gobi's optimizer:
- Projection pushdown: Frame.Select() above the scan narrows the SELECT column list at Collect time.
- Predicate pushdown: Frame.FilterExpr() above the scan is translated to a SQL WHERE clause via gobi.ExprToSQL. Parts that translate run in PostgreSQL (skipping decode + row transfer for filtered-out rows); parts that don't stay in the executor. Splits at top-level ANDs so partial translation still delivers a real win.
- Streaming: reads flow one batch at a time via ReadTableChunksFunc.
The scan doesn't automatically use PostGIS spatial indexes — that would require ExprToSQL knowing about `ST_Intersects` and friends, which it doesn't yet. Callers who need spatial index hits can construct the query themselves and use ScanQuery.
Placeholder syntax: pgio uses `$1`, `$2`, ... rather than `?` — PostgreSQL doesn't accept `?` in native mode. ExprToSQL emits `?` markers, which pgio rewrites to `$N` at scan-build time.
func WriteTable ¶
func WriteTable(ctx context.Context, conn Conn, table string, df *gobi.Frame, opts *WriteOptions) error
WriteTable inserts df into the named table using pgx's CopyFrom protocol — 10-100× faster than a naive INSERT loop for bulk loads. The target table MUST exist; pgio doesn't attempt to create it because PostgreSQL DDL is too coupled to the caller's conventions (index strategy, constraints, tablespace, etc.).
The Frame's arrow columns are mapped to PostgreSQL types via standard Go-value promotion in pgx: Int64 → int8, Float64 → float8, String → text, Boolean → bool, Timestamp → timestamptz, Binary → bytea. Geometry-tagged columns are encoded as EWKB (PostGIS's SRID-carrying WKB variant) with the SRID sourced from opts.SRID or the field's MetaGeometryCRS metadata.
When opts.Truncate is true, WriteTable issues `TRUNCATE TABLE ...` before the copy — useful for full-refresh ETL. Truncate + copy aren't wrapped in a single transaction: if the copy fails after truncate, the table is left empty. Wrap the two in your own pgx.Tx if that's a problem for your workflow.
Types ¶
type Conn ¶
type Conn interface {
Query(ctx context.Context, sql string, args ...any) (pgx.Rows, error)
Exec(ctx context.Context, sql string, args ...any) (pgconn.CommandTag, error)
// CopyFrom is the fast-path bulk-loader. WriteTable requires it.
// Callers passing a Conn that doesn't support CopyFrom (rare —
// pgx.Conn and pgxpool.Pool both do) will hit an error at
// WriteTable time, not construction time.
CopyFrom(ctx context.Context, tableName pgx.Identifier, columnNames []string, rowSrc pgx.CopyFromSource) (int64, error)
}
Conn is the small subset of *pgx.Conn / *pgxpool.Pool that pgio needs. Accepting an interface (rather than a concrete type) lets callers pass whichever they use — direct connections for short-lived tools, pooled connections for long-running services. The pgx types satisfy this shape automatically.
type ReadOptions ¶
type ReadOptions struct {
// Schema is the PostgreSQL schema qualifying Table. Defaults to
// "public".
Schema string
// Columns restricts the SELECT to the named columns. nil / empty
// means "select every column" (SELECT *). When set, geometry
// columns are included automatically only if they appear in the
// list — unlike gpkgio, pgio doesn't sneak geometry columns
// back in on projection, because a PostgreSQL table can have
// multiple geometry columns (or none) and there's no canonical
// choice.
Columns []string
// Where is an optional SQL fragment appended after WHERE. Use
// `$1` / `$2` placeholders and supply values via WhereArgs.
// ScanTable's predicate-pushdown callback fills these in
// automatically from Frame.FilterExpr; direct ReadTable callers
// supply their own.
Where string
// WhereArgs is the positional args for Where's placeholders.
WhereArgs []any
// Limit caps the number of rows returned. 0 = unlimited.
Limit int64
// GeomColumns names columns that should be treated as PostGIS
// geometries. When empty, pgio queries the geometry_columns view
// to detect them — a small extra query but zero-config. Setting
// this explicitly skips that lookup, which matters on tables
// where the geometry_columns registry might be out of date
// (e.g., unregistered geometry columns created via ALTER TABLE
// without the AddGeometryColumn helper).
GeomColumns []string
// Allocator overrides the arrow allocator used for the produced
// Frame's columns. nil = memory.DefaultAllocator. Provided for
// callers who pool arrow buffers across pipelines.
Allocator memory.Allocator
// contains filtered or unexported fields
}
ReadOptions controls ReadQuery / ReadTable / ScanTable behavior.
type WriteOptions ¶
type WriteOptions struct {
// Schema qualifies the target table. Defaults to "public".
Schema string
// Truncate, when true, issues `TRUNCATE TABLE ...` before the
// bulk insert. Convenient for full-refresh ETL patterns; leave
// off for append workflows.
Truncate bool
// GeomCol names a column that should be encoded as PostGIS
// EWKB. When empty and the Frame has a geometry-tagged column,
// pgio uses that. Explicit GeomCol wins over inference — needed
// when a Frame has multiple geometry columns.
GeomCol string
// SRID overrides the geometry column's SRID when writing. When
// zero, pgio uses the SRID carried by the geometry column's
// arrow-field metadata (MetaGeometryCRS). If the metadata is
// also empty, defaults to 4326 (WGS 84).
SRID int32
}
WriteOptions controls WriteTable behavior.
Source Files