Expected behavior of :one query annotation in case of multiple rows return

[rmodpur]

I'm using sqlc with pgx/v5 in go, for a delete query with :one annotation for eg.

-- name: DeleteEmployee :one
delete
from employee
where employee_id = $1
returning employee_name;

I was expecting it to fail withpgx.ErrTooManyRows if the query returns more than one row but what i found was it picks the first row and just ignores the rest. sqlc codegen for pgx uses QueryRow method of pgx which has the behavior of ignoring all the rows but one if there are multiple, would it be possible to use Query along with CollectExactlyOneRow in place of QueryRow(jackc/pgx#1411 (comment)) ?

sqlc/internal/codegen/golang/templates/pgx/queryCode.tmpl

Lines 26 to 47 in 34f8c1b

	{{if eq .Cmd ":one"}}
	{{range .Comments}}//{{.}}
	{{end -}}
	{{- if $.EmitMethodsWithDBArgument -}}
	func (q *Queries) {{.MethodName}}(ctx context.Context, db DBTX, {{.Arg.Pair}}) ({{.Ret.DefineType}}, error) {
	row := db.QueryRow(ctx, {{.ConstantName}}, {{.Arg.Params}})
	{{- else -}}
	func (q *Queries) {{.MethodName}}(ctx context.Context, {{.Arg.Pair}}) ({{.Ret.DefineType}}, error) {
	row := q.db.QueryRow(ctx, {{.ConstantName}}, {{.Arg.Params}})
	{{- end}}
	{{- if or (ne .Arg.Pair .Ret.Pair) (ne .Arg.DefineType .Ret.DefineType) }}
	var {{.Ret.Name}} {{.Ret.Type}}
	{{- end}}
	err := row.Scan({{.Ret.Scan}})
	{{- if $.WrapErrors}}
	if err != nil {
	err = fmt.Errorf("query {{.MethodName}}: %w", err)
	}
	{{- end}}
	return {{.Ret.ReturnName}}, err
	}
	{{end}}

[karelbilek]

I made an enhancement request.

#4056

I think this should be done as adding a new annotation. I don't think :one should suddenly start erroring on places it did not error before. Someone might WANT to select just the first row from many. (see the discussion on the pgx thread too)

[kyleconroy]

What do you want to change?

Pgx now supports CollectExactlyOneRow. This returns error when there is more than 1 resulting row, but doesn't fetch them all.

This could be done by either modifying :one behaviour, or adding :exactlyone annotation.

I think adding :exactlyone annotation is better, because erroring on :one on select where sqlc currently doesn't error is backwards incompatible change, and it doesn't strike me as correct anyway (:one can mean I actually want the first from many, see the pgx thread).

See:

jackc/pgx#1411 (comment)
#3956

If this is desired, I can take a shot at this feature and try to add it myself since it doesn't strike me as that hard (and, sqlc already has annotations that are just for go and just for pgx), but, let's see :)

[kyleconroy]

@karelbilek I closed your issue, mainly so that we can continue the discussion here before we decide on anything.

:exactlyone is too long for my taste. Also, we have to remember that we want this to work for other engines / packages. Is there a way to get this behavior using database/sql?