pgx: Pgx v4 doesn't return all rows

I have the table that contains around 300k rows satisfying this query (when I send it to the db directly):

SELECT 
	b.id, pri.provider
FROM
	bookings AS b JOIN provider_information AS pri ON b.id = pri.id
WHERE
	pri.provider_info->>'supplier_id' = 'ID'

But when I do that via pgx, I retrieve about 3k rows:

config, err := pgxpool.ParseConfig(dbinfo)
if err != nil {
	return nil, err
}
config.ConnConfig.PreferSimpleProtocol = true
config.MaxConns = 80
pool, err := pgxpool.ConnectConfig(context.Background(), config)
if err != nil {
	return nil, err
}

const query = `SELECT 
		b.id, pri.provider_data
	FROM
		bookings AS b JOIN provider_information AS pri ON b.id = pri.id
	WHERE
		pri.provider_info->>'supplier_id' = 'ID'`


rows, err := pool.Query(context.Background(), query)
if err != nil {
	panic(errors.Wrap(err, "query bookings"))
}

for rows.Next() {
	if err := rows.Err(); err != nil {
		log.Println("next row", err)
		continue
	}

	// some code
}

About this issue

  • Original URL
  • State: closed
  • Created 5 years ago
  • Comments: 15 (7 by maintainers)

Most upvoted comments

Here are some things to check.

Are you checking rows.Err() after the for rows.Next() loop? Any errors?

Is it always the same number of rows that are returned?

Do you have an order by clause?

Putting these questions together then leads to the question is it the same the same row that it stops at each time. Try using an offset to skip some rows. Do you get the same number of rows back. In other words determine if the problem occurs after some number of rows or if the problem occurs when encountering a specific row.

I expect that answering the above questions will either give us more information or lead to the solution directly. If not then maybe the PostgreSQL log would. Lastly, you could do a packet capture on the connection – but I think that is only worth trying if everything else is inconclusive.