cursoriterator

README

go-pgx-cursor-iterator

A golang package for fetching big chunks of rows from a postgres database using a cursor.

package main

import (
	"context"
	"fmt"

	cursoriterator "github.com/Eun/go-pgx-cursor-iterator/v2"
	"github.com/jackc/pgx/v5/pgxpool"
)

type User struct {
	Name string `db:"name"`
	Role string `db:"role"`
}

func main() {
	ctx := context.Background()
	pool, _ := pgxpool.New(ctx, "example-connection-url")

	values := make([]User, 1000)
	iter, err := cursoriterator.NewCursorIterator(pool, values, "SELECT * FROM users WHERE role = $1", "Guest")
	if err != nil {
		panic(err)
	}
	defer iter.Close(ctx)
	for iter.Next(ctx) {
		fmt.Printf("Name: %s\n", values[iter.ValueIndex()].Name)
	}
	if err := iter.Error(); err != nil {
		panic(err)
	}
}

Behind the scenes

With the first Next() call the iterator will start a transaction and define the cursor.
After that it will fetch the first chunk of rows.

With the following Next() calls the iterator will first consume the already fetched rows.
If it iterated over all (pre)fetched rows it will fetch the next chunk and repeats the process.

With a chunk/batch size of 100 items and 250 rows in the database, the iterator will perform 3 fetches:

1. Fetching 100 items (150 rows left)
2. Fetching 100 items (50 rows left)
3. Fetching 100 items, but will only return 50 (0 rows left)

The passed in values slice will be used as storage. So don't rely on the contents besides fetching the data from it.
Therefore, you should not reference an item in the values slice since it will most likely be replaced sooner or later. (depending on its size)

Documentation

Overview

Package cursoriterator provides functionality to iterate over big batches of postgres rows.

Index

• type CursorIterator
  • func NewCursorIterator(connector PgxConnector, values interface{}, query string, args ...interface{}) (*CursorIterator, error)
  • func (iter *CursorIterator) Close(ctx context.Context) error
  • func (iter *CursorIterator) Error() error
  • func (iter *CursorIterator) Next(ctx context.Context) bool
  • func (iter *CursorIterator) ValueIndex() int
• type PgxConnector

Types

type CursorIterator

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

CursorIterator will be returned by NewCursorIterator(). It provides functionality to loop over postgres rows and holds all necessary internal information for the functionality.

func NewCursorIterator

func NewCursorIterator(
	connector PgxConnector,
	values interface{},
	query string, args ...interface{},
) (*CursorIterator, error)

NewCursorIterator can be used to create a new iterator. Required parameters:

connector                 most likely a *pgx.Conn or *pgxpool.Pool, needed to start a transaction on the database
values                    a slice where the fetched values should be stored in.
maxDatabaseExecutionTime  how long should one database operation be allowed to run.
query                     the query to fetch the rows
args                      arguments for the query

Example Usage:

values := make([]User, 1000)
iter, err := NewCursorIterator(pool, values, time.Minute, "SELECT * FROM users WHERE role = $1", "Guest")
if err != nil {
	panic(err)
}
defer iter.Close()
for iter.Next() {
	fmt.Printf("Name: %s\n", values[iter.ValueIndex()].Name)
}
if err := iter.Error(); err != nil {
	panic(err)
}

func (*CursorIterator) Close

func (iter *CursorIterator) Close(ctx context.Context) error

Close will close the iterator and all Next() calls will return false. After Close the iterator is unusable and can not be used again.

func (*CursorIterator) Error

func (iter *CursorIterator) Error() error

Error will return the last error that appeared during fetching.

func (*CursorIterator) Next

func (iter *CursorIterator) Next(ctx context.Context) bool

Next will return true if there is a next value available, false if there is no next value available. Next will also fetch next values when all current values have been iterated.

func (*CursorIterator) ValueIndex

func (iter *CursorIterator) ValueIndex() int

ValueIndex will return the current value index that can be used to fetch the current value. Notice that it will return values below 0 when there is no next value available or the iteration didn't started yet.

type PgxConnector

type PgxConnector interface {
	Begin(ctx context.Context) (pgx.Tx, error)
}

PgxConnector implements the Begin() function from the pgx and pgxpool packages.