NextResultSet

[madelynnblue]

Has there been any work done on adding NextResultSet? I'd like to try out pgx but this is a requirement. I'm willing to work on adding it myself but would like to avoid duplicating work. I looked at using the Batch interface, but Batch sends individual messages and I need to send multiple statements in the same string. Batches don't work for me because my backing system, cockroachdb, can automatically retry failed transactions of single strings.

Update: I forgot some of my use case. What I really need is a thing that is ambiguous about result types. If I send the string: select 1; set time zone default; select 3, 4; drop table a; select * from blah, I want an interface that can provide either command tags or results from each statement. The problem with the Go 1.8 NextResultSet is that it only works with things that return results, since it's defined on the Row type.

Some of us had a longer discussion about this in lib/pq#601, but restricting ourselves to the database/sql API made it fairly difficult to implement. With pgx the problems listed in that issue could be resolved by an explicit option on QueryExOptions to enable this multi mode. I'm really interested in doing this work because lib/pq is getting difficult to work with. But I want to make sure you'd accept it and it's done with a good design.

[jackc]

I'm interested, but I don't entirely understand the use case. It's unfortunate that your queries have to be bundled as a single string. Is there any way around that? It seems that would let the existing batch interface solve the problem.

Part of the reason I went for the current batch interface is the difficulty in determining when a batched request is done if I don't know how many queries were sent. I'm sure there's a solution, but it looked tricky.

[madelynnblue]

I want this for CockroachDB, which is able to automatically retry failed transactions IF they showed up as a single string. We need this when accepting input on our CLI. So the batch interface isn't good enough. This is a pretty specialized request, I understand. So I'm working on my own implementation of what I need. I think it may be a better fit than in a generalized library like this or lib/pq.

[jackc]

You might find the pgproto3 sub-package of pgx useful if you need to go down to the protocol level. It can handle the serialization of pg messages for you.

Alternately, it may be possible to adjust the batch interface to do what you need without unduly disturbing the rest of the interface. I took a fresh look at how the simple query interface handles multiple queries in a single message and it only sends the ReadyForQuery message after it has finished processing all the queries in the string. That provides a way of knowing when the request is done without knowing how many queries were sent.

frontend: {"Type":"Query","String":"select 1; select 2; select 3"}
backend: {"Type":"RowDescription","Fields":[{"Name":"?column?","TableOID":0,"TableAttributeNumber":0,"DataTypeOID":23,"DataTypeSize":4,"TypeModifier":4294967295,"Format":0}]}
backend: {"Type":"DataRow","Values":[{"text":"1"}]}
backend: {"Type":"CommandComplete","CommandTag":"SELECT 1"}
backend: {"Type":"RowDescription","Fields":[{"Name":"?column?","TableOID":0,"TableAttributeNumber":0,"DataTypeOID":23,"DataTypeSize":4,"TypeModifier":4294967295,"Format":0}]}
backend: {"Type":"DataRow","Values":[{"text":"2"}]}
backend: {"Type":"CommandComplete","CommandTag":"SELECT 1"}
backend: {"Type":"RowDescription","Fields":[{"Name":"?column?","TableOID":0,"TableAttributeNumber":0,"DataTypeOID":23,"DataTypeSize":4,"TypeModifier":4294967295,"Format":0}]}
backend: {"Type":"DataRow","Values":[{"text":"3"}]}
backend: {"Type":"CommandComplete","CommandTag":"SELECT 1"}
backend: {"Type":"ReadyForQuery","TxStatus":"I"}

[madelynnblue]

Ok I'm back to thinking about this again and would like to work in it. I've tried a few things outside of pgx and now think that trying to find an API that fits nicely into pgx would work well. However we have no desire to maintain a fork of pgx (we did that for lib/pq for a bit and it was not great), so I'd like to discuss some possible APIs with you to make sure what we need could fit here. If we can't find something, that's fine and we'll figure something else out.

The API proposed in lib/pq#601 (comment) was fairly good, but pretty verbose due to how it had to integrate with database/sql. What if we could do this:

func (c *Conn) SimpleQuery(ctx context.Context, sql string) (Message, error)

type Message struct { ... }

// Can return a:
// - CommandTag
// - *Rows
// - PgError
// - nil (when done)
//
// The second return value (the error) is only populated when there was a
// problem fetching the next Message: it is nil even if the query produced
// an error.
func (m *Message) Message() (interface{}, error)

And usage like:

msg := conn.SimpleQuery(ctx, "INSERT INTO Company (Name) values ('cono1'); SELECT * from Account;")
defer msg.Close()
for {
	m, err := msg.Message(ctx)
	if err != nil {
		return err
	}
	switch m := m.(type) {
	case nil:
		break
	case pgx.PgError:
		log.Println(m)
	case pgx.CommandTag:
		fmt.Println(m)
	case *pgx.Rows:
		for m.Next() {
			if err := m.Scan(vals); err != nil {
				// handle err
			}
		}
		m.Close()
	}
}

I kind of like this API, although I'm not thrilled about the use of interface{}, but I don't have a better proposal. I'm also concerned about the use of a real *pgx.Rows because of its Close method, which I'm worried will do something to future results. But maybe it could be taught about this API and probably just ignore the call to Close or something. Thoughts?

[jackc]

Ideally we would not have to introduce an entirely new API for functionality so close to the existing batch functionality. As I understand it, the problems with the existing batch system are:

1. Can't accept a single SQL string containing multiple commands.
2. Need to disable automatic transaction wrapping.

If those could be handled does the rest of the batch API work for your use case?

See also #324. There might be a solution that handles both cases.

[madelynnblue]

We no longer care too much about 1, and are now fine with splitting up the queries. 2 is required, yes. The big missing thing, though, is that we don't know what the result type will be, so we don't know if we need to call Exec or Query. We need an API that can either tell us what is the next kind of thing or return an interface like the above proposal with one of the possible return types. If we had those then the batch API would work.

It's not clear to me how #324 would help here, but I'm certainly open to a common solution.

[jackc]

I think something similar to your message API could work on the existing batch interface. We already have ExecResults() and QueryResults(). Maybe we add AnyResults() / UnknownResults() / Results() that returns (interface{}, error). The interface{} would be a CommandTag or *Rows.

[jackc]

As of the v4 release all of these are possible.

The simplest way is if you are using the batch interface. The Rows interface now has a CommandTag to retrieve the the result of a query execution. So you can always read the results with Query. If it wasn't a result returning query there will be no rows and you can read the command tag instead.

In addition, it is now possible to execute a SQL string that contains multiple statements directly. This is done by accessing the lower level pgconn interface with Conn.PgConn() and using the the Exec method. The results have to be read with the returned pgconn.MultiResultReader. The rows values can be scanned by using the pgx.ScanRow function.

[martingallagher]

@mjibson did you manage to get v4 working?

I'm still seeing a conn busy issue when using like so, unsure at this point if it's due to using the tx after dropping down to use PgConn:

err := db.Tx(ctx, conn, func(tx pgx.Tx) error {
	r := tx.Conn().PgConn().Exec(ctx, multiQueryInsert)

	if !r.NextResult() {
		return errors.New("no results")
	}

	rr := r.ResultReader()
	count := rr.Read().CommandTag.RowsAffected()
	err := r.Close()

	// Then go on to use `tx` in the same transaction:
	_, err := tx.Exec(ctx, anotherQuery)

	return err
})

db.Tx is a simple wrapper around CockroachDB's transaction retry package:

func Tx(ctx context.Context, conn crdbpgx.Conn, fn func(pgx.Tx) error, opts ...crdb.Option) error {
	opts = append([]crdb.Option{crdb.WithMaxRetries(0)}, opts...)

	for {
		err := crdbpgx.ExecuteTx(ctx, conn, fn, opts...)

		if ErrRetryable(err) {
			continue
		}

		return err
	}
}

[madelynnblue]

This isn't a thing I'm interested in any longer, and I didn't do any followup since my previous comment.

[martingallagher]

After a quick look into the code, for CockroachDB the batch interface does work great, it just depends on PreferSimpleProtocol = true for ConnConfig.