Why call rows.Close() could take long time

[stasvo]

Why call rows.Close() takes too long time when I call it after I exit from loop rows.Next() before processing all elements of loop.
Its happen when I make request which returns huge amount of data (around 300 000 rows).
This problem doesn't exists when amount of rows is not so big.

func SelectHugeAmountOfRows() {
	query := `SELECT * FROM big_table`
	rows, _ := Conn.Query(context.Background(), query)
	defer func() {
		fmt.Println("Start close rows")
		start := time.Now()
		rows.Close()
		duration := time.Since(start)
		fmt.Println("rowsClose duration:", duration)
	}()

	for rows.Next() {
		rowValues, err := rows.Values()
		if err != nil {
			fmt.Println(err)
			return
		}

		// do something with rowValues and get error in process
		err = func() error {
			fmt.Println(rowValues)
			return errors.New("some error")
		}()

		if err != nil {
			return
		}
	}

	if err := rows.Err(); err != nil {
		fmt.Println(err)
		return
	}
}

rowsClose duration: 1m2.5488669s
Interesting that duration of rows.Close() in this case in same as duration which I will have if will process all elements of rows.Next() loop without break it.

[jackc]

The PostgreSQL server will still send all the rows.

Interesting that duration of rows.Close() in this case in same as duration which I will have if will process all elements of rows.Next() loop without break it.

That's basically what pgx is doing. It still has to read everything that comes over the wire until it receives the ReadyForQuery message.

You can use https://pkg.go.dev/github.com/jackc/pgx/[email protected]/pgconn#PgConn.CancelRequest to send the PostgreSQL server a cancel request message. However, due to limitations of the PostgreSQL protocol, it is difficult to use in a perfectly reliable manner.

An alternative is to cancel the context used for your query. However, partially due to the before mentioned limitations, it will close the connection entirely rather than just cancel the query. If you are using pgxpool this should not be an issue as it will transparently create a new connection, but if you are specifically using a single connection that behavior would be inconvenient.

[stasvo]

Thank you!