database/sql: storing driver.Value type string into type *time.Time

[daxzhuo]

Describe the bug database/sql: unsupported Scan, storing driver.Value type string into type *time.Time

To Reproduce

 CREATE TABLE vc (   
     ts timestamp
 );
INSERT INTO vc (ts) VALUES ('infinity');

package main

import (
	"context"
	"log"
	"os"
        "time"
        "database/sql"

	_ "github.com/jackc/pgx/v5/stdlib"
)

func main() {
	db, err := sql.Open("pgx", os.Getenv("DATABASE_URL"))
	if err != nil {
		fmt.Fprintf(os.Stderr, "Unable to connect to database: %v\n", err)
		os.Exit(1)
	}
	defer db.Close()

        rows, err := db.Query("select * from vc")
	if err != nil {
		t.Error(err)
		return
	}

	for rows.Next() {
		columnTypes, _ := rows.ColumnTypes()
		for _, ct := range columnTypes {
			fmt.Println(ct.ScanType().Name())
		}
		ts := time.Time{}
		err = rows.Scan(&ts)
		if err != nil {
			t.Error(err)
		}
		fmt.Println(ts)
	}
}

Expected behavior Return without error.

Actual behavior Error in rows.Scan: unsupported Scan, storing driver.Value type string into type *time.Time

This is actually a simplify demo when using gorm library and pgx as driver, the default column type in go for timestamp is time.Time, but when the timestamp value set to infinity, the actual driver.Value is string type, hence the scan error happened. I think this is a problem about how to represent infinity value in time.Time. For code reference: https://github.com/jackc/pgx/blob/master/pgtype/timestamp.go#L58 Version

• Go: go version go1.19.6 darwin/amd64
• PostgreSQL: PostgreSQL 14.5 (Debian 14.5-2.pgdg110+2) on x86_64-pc-linux-gnu, compiled by gc c (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
• pgx: v5.3.0

Additional context Add any other context about the problem here.

[jackc]

I don't think there is any way around this. The Go time.Time type can't represent the PostgreSQL infinity value. However, pgtype.Timestamp and pgtype.Timestamptz can.

[daxzhuo]

I don't think there is any way around this. The Go time.Time type can't represent the PostgreSQL infinity value. However, pgtype.Timestamp and pgtype.Timestamptz can.

A possible workaround: https://pkg.go.dev/github.com/lib/pq#EnableInfinityTs Not an elegant solution, but can prevent errors, the rest could be handled by user's code

[jackc]

I suppose a toggle is possible to treat -infinity / infinity as the min / max time values. This flag would probably be in pgtype.TimestamptzCodec and pgtype.TimestampCodec. Then they would need to somehow pass that mode down to the encode and decode plans. A user would activate this mode in an after connect hook.

I don't plan on implementing it myself, but I think this feature could be merged if someone submitted a PR.

[Tachi107]

I'd personally like to have a solution which doesn't rely on having to use pgx-specific APIs, since, just like in @daxzhuo's example, in my project we try to rely only on standard SQL and generic Go APIs. This also means that we don't have infinity in our database, since the concept isn't present in standard SQL. Having pgx/stdlib automatically convert SQL TIME types would be really nice when not using Postgres-specific features.

[trevex]

I looked into this issue today. I abandoned my efforts (as I don't need infinity clamping for now), but one straight forward way to change how infinity values are treated for time.Time is to prepend a wrapper (similar to https://github.com/jackc/pgx-gofrs-uuid/blob/master/uuid.go#L122).

The code is basically copied and adapted from https://github.com/jackc/pgx/blob/master/pgtype/pgtype.go and https://github.com/jackc/pgx/blob/master/pgtype/builtin_wrappers.go#L398C1-L497C1.

A simple working example looked as follows:

const (
	secondsPerMinute       = 60
	secondsPerHour         = 60 * 60
	secondsPerDay          = 24 * secondsPerHour
	unixToInternal   int64 = (1969*365 + 1969/4 - 1969/100 + 1969/400) * secondsPerDay
)

var (
	MinTime = time.Unix(0, 0)
	MaxTime = time.Unix(1<<63-1-unixToInternal, 999999999)
)

func TryWrapTimeEncodePlan(value interface{}) (plan pgtype.WrappedEncodePlanNextSetter, nextValue interface{}, ok bool) {
	switch value := value.(type) {
	case time.Time:
		return &wrapTimeEncodePlan{}, timeWrapper(value), true
	}

	return nil, nil, false
}

type wrapTimeEncodePlan struct {
	next pgtype.EncodePlan
}

func (plan *wrapTimeEncodePlan) SetNext(next pgtype.EncodePlan) { plan.next = next }

func (plan *wrapTimeEncodePlan) Encode(value any, buf []byte) (newBuf []byte, err error) {
	return plan.next.Encode(timeWrapper(value.(time.Time)), buf)
}

func TryWrapTimeScanPlan(target any) (plan pgtype.WrappedScanPlanNextSetter, nextDst any, ok bool) {
	switch target := target.(type) {
	case *time.Time:
		return &wrapTimeScanPlan{}, (*timeWrapper)(target), true
	}

	return nil, nil, false
}

type wrapTimeScanPlan struct {
	next pgtype.ScanPlan
}

func (plan *wrapTimeScanPlan) SetNext(next pgtype.ScanPlan) { plan.next = next }

func (plan *wrapTimeScanPlan) Scan(src []byte, dst any) error {
	return plan.next.Scan(src, (*timeWrapper)(dst.(*time.Time)))
}

type timeWrapper time.Time

func (w *timeWrapper) ScanTimestamptz(v pgtype.Timestamptz) error {
	if !v.Valid {
		return fmt.Errorf("cannot scan NULL into *time.Time")
	}

	switch v.InfinityModifier {
	case pgtype.Finite:
		*w = timeWrapper(v.Time)
		return nil
	case pgtype.Infinity:
		*w = timeWrapper(MaxTime)
		return nil
	case pgtype.NegativeInfinity:
		*w = timeWrapper(MinTime)
		return nil
	default:
		return fmt.Errorf("invalid InfinityModifier: %v", v.InfinityModifier)
	}
}

func (w timeWrapper) TimestamptzValue() (pgtype.Timestamptz, error) {
	infinityModifier := pgtype.Finite
	if time.Time(w).Equal(MaxTime) {
		infinityModifier = pgtype.Infinity
	} else if time.Time(w).Equal(MinTime) {
		infinityModifier = pgtype.NegativeInfinity
	}
	return pgtype.Timestamptz{Time: time.Time(w), InfinityModifier: infinityModifier, Valid: true}, nil
}

The above was tested as follows:

// ...
	config.AfterConnect = func(ctx context.Context, conn *pgx.Conn) error {
		tm := conn.TypeMap()
		tm.TryWrapEncodePlanFuncs = append([]pgtype.TryWrapEncodePlanFunc{TryWrapTimeEncodePlan}, tm.TryWrapEncodePlanFuncs...)
		tm.TryWrapScanPlanFuncs = append([]pgtype.TryWrapScanPlanFunc{TryWrapTimeScanPlan}, tm.TryWrapScanPlanFuncs...)
		return nil
	}
// ...
	var tmin, tmax, tval time.Time
	err := pool.
		QueryRow(context.Background(), "select '-infinity'::timestamptz, 'infinity'::timestamptz, '2024-01-01T00:00:00Z'::timestamptz").
		Scan(&tmin, &tmax, &tval)
// ...