Golang查询扫描未正确将查询结果扫描到结构体中。

huangapple go评论84阅读模式
英文:

Golang query scan not scanning query correctly into struct

问题

我在使用Golang中的pgx查询时遇到了问题。id字段总是指向最后一条记录的id。如果我取消注释函数顶部的var person Person声明,每个id都是3。我的数据库中有3条id从1到3的记录。当我注释掉那个声明,并在rows.Next()循环中声明变量时,我得到了正确的id。我无法弄清楚为什么personId没有被正确地覆盖。

在函数顶部声明变量后,使用marshalled JSON输出如下:

[{"person_id":3,"first_name":"Mark","last_name":"Brown"},{"person_id":3,"first_name":"Sam","last_name":"Smith"},{"person_id":3,"first_name":"Bob","last_name":"Jones"}]

在每次扫描循环中声明变量后,输出如下:

[{"person_id":1,"first_name":"Mark","last_name":"Brown"},{"person_id":2,"first_name":"Sam","last_name":"Smith"},{"person_id":3,"first_name":"Bob","last_name":"Jones"}]

这是我的结构体定义:

// Person model
type Person struct {
    PersonId   *int64  `json:"person_id"`
    FirstName  *string `json:"first_name"`
    LastName   *string `json:"last_name"`
}

这是我的查询函数:

func getPersons(rs *appResource, companyId int64) ([]Person, error) {

    // var person Person

    var persons []Person

    queryString := `SELECT 
        user_id, 
        first_name, 
        last_name
        FROM users 
        WHERE company_id = $1`

    rows, err := rs.db.Query(context.Background(), queryString, companyId)

    if err != nil {
        return persons, err
    }

    for rows.Next() {

        var person Person

        err = rows.Scan(
            &person.PersonId,
            &person.FirstName,
            &person.LastName)

        if err != nil {
            return persons, err
        }

        log.Println(*person.PersonId) // 无论使用哪种变量模式,输出都是1、2、3

        persons = append(persons, person)
    }

    if rows.Err() != nil {
        return persons, rows.Err()
    }

    return persons, err
}

希望这能帮助到你解决问题!

英文:

I am having trouble with scanning from a pgx query in Golang. The id field is always that of the last record. If I un-comment the var person Person declaration at the top of the function, every id is 3. There are 3 records with id's from 1 to 3 in my db. When I comment that declaration and declare the variable in the rows.Next() loop I get the correct id's. I can't figure out why the personId isn't being correctly overwritten

output from marshalled JSON with the var declared at the top of the function.

> [{"person_id":3,"first_name":"Mark","last_name":"Brown"},{"person_id":3,"first_name":"Sam","last_name":"Smith"},{"person_id":3,"first_name":"Bob","last_name":"Jones"}]

output after declaring person every iteration of the scan loop

> [{"person_id":1,"first_name":"Mark","last_name":"Brown"},{"person_id":2,"first_name":"Sam","last_name":"Smith"},{"person_id":3,"first_name":"Bob","last_name":"Jones"}]

I have this struct

// Person model
type Person struct {
	PersonId       *int64   `json:"person_id"`
	FirstName      *string  `json:"first_name"`
	LastName       *string  `json:"last_name"`
}

Here is my query function

func getPersons(rs *appResource, companyId int64) ([]Person, error) {

	//	var person Person

	var persons []Person

	queryString := `SELECT 
	  user_id, 
	  first_name, 
	  last_name,
      FROM users 
	  WHERE company_id = $1`

	rows, err := rs.db.Query(context.Background(), queryString, companyId)

	if err != nil {
		return persons, err
	}

	for rows.Next() {

		var person Person

		err = rows.Scan(
			&person.PersonId,
			&person.FirstName,
			&person.LastName)

		if err != nil {
			return persons, err
		}

		log.Println(*person.PersonId) // 1, 2, 3 for both var patterns

		persons = append(persons, person)
	}

	if rows.Err() != nil {
		return persons, rows.Err()
	}

	return persons, err
}

答案1

得分: 2

我相信你在github.com/jackc/pgx/v4中发现了一个错误(或者至少是意外的行为)。在运行Scan时,似乎如果指针(即person.PersonId)不是nil,那么它指向的内容将被重用。为了证明这一点,我复制了这个问题,并确认你也可以通过以下方式修复它:

persons = append(persons, person)
person.PersonId = nil

我可以用以下简化的代码复制这个问题:

conn, err := pgx.Connect(context.Background(), "postgresql://user:password@127.0.0.1:5432/schema?sslmode=disable")
if err != nil {
	panic(err)
}
defer conn.Close(context.Background())

queryString := `SELECT num::int FROM generate_series(1, 3) num`

var scanDst *int64
var slc []*int64

rows, err := conn.Query(context.Background(), queryString)

if err != nil {
	panic(err)
}

for rows.Next() {
	err = rows.Scan(&scanDst)

	if err != nil {
		panic(err)
	}

	slc = append(slc, scanDst)
	// scanDst = nil
}

if rows.Err() != nil {
	panic(err)
}

for _, i := range slc {
	fmt.Printf("%v %d\n", i, *i)
}

这个代码的输出是:

0xc00009f168 3
0xc00009f168 3
0xc00009f168 3

你会注意到指针在每种情况下都是相同的。我进行了一些进一步的测试:

  • 在上面的代码中取消注释scanDst = nil可以修复这个问题。
  • 当使用database/sql(使用"github.com/jackc/pgx/stdlib"驱动程序)时,代码按预期工作。
  • 如果PersonId*string(并且查询使用num::text),它按预期工作。

问题似乎归结为convert.go中的以下代码:

if v := reflect.ValueOf(dst); v.Kind() == reflect.Ptr {
	el := v.Elem()
	switch el.Kind() {
	// if dst is a pointer to pointer, strip the pointer and try again
	case reflect.Ptr:
		if el.IsNil() {
			// allocate destination
			el.Set(reflect.New(el.Type().Elem()))
		}
		return int64AssignTo(srcVal, srcStatus, el.Interface())

因此,这段代码处理了目标是指针的情况(对于某些数据类型)。代码检查它是否为nil,如果是,则创建一个新的相应类型的实例作为目标。如果不是nil,它只是重用指针。注意:我已经有一段时间没有使用过reflect,所以我的解释可能有问题。

由于这种行为与database/sql不同,并且可能会引起困惑,我认为这可能是一个错误(我猜它可能是为了减少分配而做的尝试)。我快速查看了一下问题,没有找到任何已报告的问题(稍后会进行更详细的查看)。

英文:

I believe that you have discovered a bug (or, at least, unexpected behaviour) in github.com/jackc/pgx/v4. When running Scan it appears that if the pointer (so person.PersonId) is not nil then whatever it is pointing to is being reused. To prove this I replicated the issue and confirmed that you can also fix it with:

persons = append(persons, person)
person.PersonId = nil

I can duplicate the issue with this simplified code:

conn, err := pgx.Connect(context.Background(), "postgresql://user:password@127.0.0.1:5432/schema?sslmode=disable")
if err != nil {
	panic(err)
}
defer conn.Close(context.Background())

queryString := `SELECT num::int FROM generate_series(1, 3) num`

var scanDst *int64
var slc []*int64

rows, err := conn.Query(context.Background(), queryString)

if err != nil {
	panic(err)
}

for rows.Next() {
	err = rows.Scan(&scanDst)

	if err != nil {
		panic(err)
	}

	slc = append(slc, scanDst)
	// scanDst = nil
}

if rows.Err() != nil {
	panic(err)
}

for _, i := range slc {
	fmt.Printf("%v %d\n", i, *i)
}

The output from this is:

0xc00009f168 3
0xc00009f168 3
0xc00009f168 3

You will note that the pointer is the same in each case. I have done some further testing:

  • Uncommenting scanDst = nil in the above fixes the issue.
  • When using database/sql (with the "github.com/jackc/pgx/stdlib" driver) the code works as expected.
  • If PersonId is *string (and query uses num::text) it works as expected.

The issue appears to boil down to the following in convert.go:

if v := reflect.ValueOf(dst); v.Kind() == reflect.Ptr {
	el := v.Elem()
	switch el.Kind() {
	// if dst is a pointer to pointer, strip the pointer and try again
	case reflect.Ptr:
		if el.IsNil() {
			// allocate destination
			el.Set(reflect.New(el.Type().Elem()))
		}
		return int64AssignTo(srcVal, srcStatus, el.Interface())

So this handles the case where the destination is a pointer (for some datatypes). The code checks if it is nil and, if so, creates a new instance of the relevant type as a destination. If it's not nil it just reuses the pointer. Note: I've not used reflect for a while so there may be issues with my interpretation.

As the behaviour differs from database/sql and is likely to cause confusion I believe it's probably a bug (I guess it could be an attempt to reduce allocations). I have had a quick look at the issues and could not find anything reported (will have a more detailed look later).

huangapple
  • 本文由 发表于 2022年3月8日 14:26:46
  • 转载请务必保留本文链接:https://go.coder-hub.com/71390835.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定