pgwire: multiple active result sets (portals) not supported

[jordanlewis]

CockroachDB does not currently support multiple active pgwire portals on the same session. This means that you can't perform the following sequence of events:

1. open portal for query a, which will eventually return 1000 rows
2. ask for 500 rows of query a
3. open portal for query b
4. ask for 500 rows of query b
5. ask for 500 rows of query a

This sequence will fail at step 3, unless the user closes the portal for query a first, in which case this sequence will fail at step 5.

Jira issue: CRDB-5559
Epic: CRDB-17622

[rafiss]

The pgjcbc test org.postgresql.test.jdbc2.CursorFetchTest.testGetRow used to fail because of this lack of support, but that test started passing. I don't know why. #41366

[imalsogreg]

We (myself and @robo-corg) hit an edge-case related to this issue. When we use the rust sqlx postgres bindings, queries run inside of transactions trigger the "multiple portals" issue. This happens even when we run just a single query, which we think creates just one portal.

Here's a wireshark/tcpdump of a session with sqlx that exhibits this behavior. We can attach the .pcap file if it's helpful.

[rafiss]

@imalsogreg Thanks for sharing this case! A wireshark .pcap would definitely be very helpful for us. We can use that to more easily write a test within our project.

[imalsogreg]

@rafiss Cool. Here's the .pcap from when I ran the minimal reproducing case. It corresponds to the left panel in the screenshot above.

packets4.pcap.zip

[bka9]

After upgrading from 20.1.12 to 20.2.7 our use of snaplogic to query data has failed with the following stack trace:

com.snaplogic.snap.api.SnapDataException: Failed to execute select query.
	at com.snaplogic.snaps.sql.SimpleSqlSnap.process(SimpleSqlSnap.java:399)
	at com.snaplogic.snap.api.ExecutionUtil.process(ExecutionUtil.java:106)
	at com.snaplogic.snap.api.ExecutionUtil.executeForDocument(ExecutionUtil.java:118)
	at com.snaplogic.snap.api.ExecutionUtil.execute(ExecutionUtil.java:81)
	at com.snaplogic.snap.api.SimpleSnap.execute(SimpleSnap.java:70)
	at com.snaplogic.cc.snap.common.SnapRunnableImpl.executeSnap(SnapRunnableImpl.java:770)
	at com.snaplogic.cc.snap.common.SnapRunnableImpl.execute(SnapRunnableImpl.java:552)
	at com.snaplogic.cc.snap.common.SnapRunnableImpl.doRun(SnapRunnableImpl.java:836)
	at com.snaplogic.cc.snap.common.SnapRunnableImpl.call(SnapRunnableImpl.java:402)
	at com.snaplogic.cc.snap.common.SnapRunnableImpl.call(SnapRunnableImpl.java:116)
	at java.base/java.util.concurrent.FutureTask.run(Unknown Source)
	at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
	at java.base/java.util.concurrent.FutureTask.run(Unknown Source)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
	at java.base/java.lang.Thread.run(Unknown Source)
Caused by: com.snaplogic.api.ExecutionException: Failed to execute select query.
	at com.snaplogic.snap.api.sql.operations.JdbcOperationsImpl.select(JdbcOperationsImpl.java:700)
	at com.snaplogic.snaps.sql.SimpleSqlSelectSnap.selectAndSpoolResults(SimpleSqlSelectSnap.java:714)
	at com.snaplogic.snaps.sql.SimpleSqlSelectSnap.lambda$select$5(SimpleSqlSelectSnap.java:699)
	at net.jodah.failsafe.Functions$12.call(Functions.java:274)
	at net.jodah.failsafe.SyncFailsafe.call(SyncFailsafe.java:145)
	at net.jodah.failsafe.SyncFailsafe.run(SyncFailsafe.java:93)
	at com.snaplogic.snaps.sql.SimpleSqlSelectSnap.select(SimpleSqlSelectSnap.java:696)
	at com.snaplogic.snaps.sql.SimpleSqlSelectSnap.processDocument(SimpleSqlSelectSnap.java:678)
	at com.snaplogic.snaps.sql.SimpleSqlSnap.process(SimpleSqlSnap.java:390)
	... 15 more
	Suppressed: org.jooq.exception.DataAccessException: SQL [null]; ERROR: current transaction is aborted, commands ignored until end of transaction block
		at org.jooq_3.9.1.POSTGRES.debug(Unknown Source)
		at org.jooq.impl.Tools.translate(Tools.java:1983)
		at org.jooq.impl.MetaDataFieldProvider.init(MetaDataFieldProvider.java:158)
		at org.jooq.impl.MetaDataFieldProvider.<init>(MetaDataFieldProvider.java:83)
		at org.jooq.impl.SelectQueryImpl.getFields(SelectQueryImpl.java:407)
		at org.jooq.impl.AbstractResultQuery.execute(AbstractResultQuery.java:285)
		at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:349)
		at org.jooq.impl.AbstractResultQuery.fetchLazy(AbstractResultQuery.java:368)
		at com.snaplogic.snap.api.sql.operations.JdbcOperationsImpl.fetchLazy(JdbcOperationsImpl.java:841)
		at com.snaplogic.snap.api.sql.operations.JdbcOperationsImpl.select(JdbcOperationsImpl.java:694)
		... 23 more
	Caused by: org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block
		at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2468)
		at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2211)
		at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:309)
		at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:446)
		at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:370)
		at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:311)
		at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:297)
		at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:274)
		at org.postgresql.jdbc.PgStatement.executeQuery(PgStatement.java:225)
		at org.postgresql.jdbc.PgResultSetMetaData.fetchFieldMetaData(PgResultSetMetaData.java:245)
		at org.postgresql.jdbc.PgResultSetMetaData.isAutoIncrement(PgResultSetMetaData.java:57)
		at org.postgresql.jdbc.PgResultSetMetaData.getColumnTypeName(PgResultSetMetaData.java:318)
		at org.jooq.impl.MetaDataFieldProvider.init(MetaDataFieldProvider.java:129)
		... 30 more
	Caused by: org.postgresql.util.PSQLException: ERROR: unimplemented: multiple active portals not supported
  Hint: You have attempted to use a feature that is not yet implemented.
See: https://go.crdb.dev/issue-v/40195/v20.2
		at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2468)
		at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2211)
		at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:309)
		at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:446)
		at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:370)
		at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:311)
		at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:297)
		at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:274)
		at org.postgresql.jdbc.PgStatement.executeQuery(PgStatement.java:225)
		at org.postgresql.jdbc.PgResultSetMetaData.fetchFieldMetaData(PgResultSetMetaData.java:245)
		at org.postgresql.jdbc.PgResultSetMetaData.getBaseTableName(PgResultSetMetaData.java:289)
		at org.postgresql.jdbc.PgResultSetMetaData.getTableName(PgResultSetMetaData.java:285)
		at org.jooq.impl.MetaDataFieldProvider.init(MetaDataFieldProvider.java:112)
		... 30 more
Reason: SQL [null]; ERROR: current transaction is aborted, commands ignored until end of transaction block
Resolution: Please check sql query.

The query is a simple select statement.

[rafiss]

I'm coming back to this now. It seems like the error message is incorrectly used if you execute a statement with a row count limit within a transaction. Here's a test case that can be put in pkg/sql/pgwire/testdata/pgtest/ that reproduces.

send
Query {"String": "BEGIN"}
----

until
ReadyForQuery
----
{"Type":"CommandComplete","CommandTag":"BEGIN"}
{"Type":"ReadyForQuery","TxStatus":"T"}

# 83 = ASCII 'S' for Statement
# 50 = ASCII '2'
# ParameterFormatCodes = [0] for text format
send
Parse {"Name": "s6", "Query": "select $1::int8", "ParameterOIDs": [20]}
Describe {"ObjectType": 83, "Name": "s6"}
Bind {"DestinationPortal": "p6", "PreparedStatement": "s6", "ParameterFormatCodes": [0], "ResultFormatCodes": [0], "Parameters": [[50]]}
Execute {"Portal": "p6", "MaxRows": 1}
Sync
----

until
ReadyForQuery
----
{"Type":"ParseComplete"}
{"Type":"ParameterDescription","ParameterOIDs":[20]}
{"Type":"RowDescription","Fields":[{"Name":"int8","TableOID":0,"TableAttributeNumber":0,"DataTypeOID":20,"DataTypeSize":8,"TypeModifier":-1,"Format":0}]}
{"Type":"BindComplete"}
{"Type":"DataRow","Values":[{"text":"2"}]}
{"Type":"PortalSuspended"}
{"Type":"ReadyForQuery","TxStatus":"T"}

send
Query {"String": "COMMIT"}
----

until
ReadyForQuery
----
{"Type":"CommandComplete","CommandTag":"COMMIT"}
{"Type":"ReadyForQuery","TxStatus":"I"}

It works against Postgres using make test PKG=./pkg/sql/pgwire TESTS=TestPGTest TESTFLAGS="-addr=localhost:5432 -user=rafiss"

But it fails against CockroachDB make test PKG=./pkg/sql/pgwire TESTS=TestPGTest

got &pgproto3.ErrorResponse{Severity:"ERROR", SeverityUnlocalized:"", Code:"0A000", Message:"unimplemented: multiple active portals not supported", Detail:"", Hint:"You have attempted to use a feature that is not yet implemented.\nSee: https://go.crdb.dev/issue-v/40195/v21.1", Position:0, InternalPosition:0, InternalQuery:"", Where:"", SchemaName:"", TableName:"", ColumnName:"", DataTypeName:"", ConstraintName:"", File:"distsql_running.go", Line:775, Routine:"init", UnknownFields:map[uint8]string(nil)}

[rafiss]

I see that this limitation is described here: #42912 (comment) -- I'll see if we can address this now

[lacasaprivata2]

+1

[rafiss]

@rathboma Thanks for those instructions. I am getting a different error. (Also I had to switch to node14 since it wouldn't work with node16). To keep this issue more focused, let's move the conversation here: #74187

[mcanalesmayo]

Are there any plans to fix this? We are running into this issue as well with a very simple scenario where we are reading and updating rows inside a transaction, using Vertx streaming API (batches).

[markddrake]

I am getting this error attempting to process a table with 107 rows in it using node pg and node pg QueryStream. As far as I am aware I have no other active queries..

brianc/node-postgres#2767

[rafiss]

Thanks for linking that issue. It looks like node-pg is using the Flush message. We should be able to handle that in a portal, even without adding full support for multiple active portals.

[markddrake]

If you guys are going to fix this, do you want me to open a new issue on cockroach with the tetscase

[rafiss]

Yes, filing another issue with that would be helpful, thanks.

[wb14123]

I got this error when read a table with 1000 rows per batch and update them in the same transaction. But if I read the table with 100 rows per time, there is no error. This is kind of a strange behavior and is very confusing.

[lukaseder]

@bka9 regarding the jOOQ specific issue from your comment here: #40195 (comment)

It's likely you ran into this because of using a plain SQL query without explicitly specifying the projection, given this line in your stack trace:

at org.jooq.impl.MetaDataFieldProvider.init(MetaDataFieldProvider.java:112)

It's always good to provide explicit projections (SELECT clause) rather than running SELECT * queries for other performance reasons. This issue shows that fetching column meta data in a separate query causes additional performance problems. A more detailed description of the jOOQ related problem can be seen here: jOOQ/jOOQ#14392

[GoulartNogueira]

I'm building a serverless application, where a lot of threads can run in parallel, but through different instances.
Can it be accomplished with current CockroachDB version?
Or is it limited by this portals issue?

[rafiss]

I'm building a serverless application, where a lot of threads can run in parallel, but through different instances.
Can it be accomplished with current CockroachDB version?

Based on your description, it sounds like each thread is using a different SQL connection. If that's the case, then yes, this should work in existing CockroachDB versions.

---

Also an update: in v23.1 CockroachDB will support multiple active portals as a preview feature. The feature will be off by default, and can be enabled by setting the session variable multiple_active_portals_enabled to true. This will be available in v23.1.0-beta.2.

We encourage everyone to try it out for their use cases, with the caveat that there are still known issues and incomplete performance testing. The list of known issues can be found with the A-pausable-portals label, and we always welcome new bug reports.