pgwire: Flush message is not supported during execution of a portal

[markddrake]

Attempting to use PG Query stream to stream a large dataset results in error: unimplemented: multiple active portals not supported, even though a single table is being accessed,

Running the following code against Postgres 14 works as expected

import pg                             from 'pg';
const {Client,Pool} = pg;
import QueryStream                    from 'pg-query-stream'
import {
  Transform
}                                     from 'stream';

import {
  pipeline
}                                     from 'stream/promises';

const props = {
          user: 'postgres',
          host: 'yadamu-db2',
          database: 'yadamu',
          password: 'oracle',
          port: 5432
}

class t extends Transform {

  constructor() {
    super({objectMode: true });  
  }
  
  async doTransform(data) {
    return JSON.stringify(data)
  }

  _transform(data,enc,callback) {
    this.doTransform(data).then((row) => { this.push(row);callback()}).catch((e) => { callback(e)})
  }

}

async function main() {
              process.on('unhandledRejection', (err, p) => {
                            console.log(err);
                            process.exit()
                          })

                      const pgClient = new Client(props);
                      console.log('connecting')
                      await pgClient.connect();
                      console.log('connected')
                      const queryStream = new QueryStream(`select * from INFORMATION_SCHEMA.COLUMNS`,[],{rowMode : "array"})
                      const inputStream = await pgClient.query(queryStream)   					  
					  await pipeline(inputStream,new t,process.stdout,{end:false})
					  await pgClient.end();
                      console.log('disconnected')

}

main().then(() => {console.log('DONE')}).catch((e) => {console.log(e)});

However when run against Cockroack it fails with

C:\Development\YADAMU>node src\scratch\postgres\pgTestStream.js
connecting
connected
error: unimplemented: multiple active portals not supported
    at Parser.parseErrorMessage (C:\Development\YADAMU\src\node_modules\pg-protocol\dist\parser.js:287:98)
    at Parser.handlePacket (C:\Development\YADAMU\src\node_modules\pg-protocol\dist\parser.js:126:29)
    at Parser.parse (C:\Development\YADAMU\src\node_modules\pg-protocol\dist\parser.js:39:38)
    at Socket.<anonymous> (C:\Development\YADAMU\src\node_modules\pg-protocol\dist\index.js:11:42)
    at Socket.emit (node:events:527:28)
    at addChunk (node:internal/streams/readable:324:12)
    at readableAddChunk (node:internal/streams/readable:297:9)
    at Socket.Readable.push (node:internal/streams/readable:234:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {
  length: 285,
  severity: 'ERROR',
  code: '0A000',
  detail: 'cannot perform operation sql.Flush while a different portal is open',
  hint: 'You have attempted to use a feature that is not yet implemented.\n' +
    'See: https://go.crdb.dev/issue-v/40195/v22.1',
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'distsql_running.go',
  line: '1181',
  routine: 'init'
}

Since the code only peforms a single query against a single table I would have expected it to work.

Per Rafi's comments here #40195 (comment)

it looks like this can be fixed prior to a full solution for the "multiple active portals" issue referenced here

#40195

This was reproduced using the Cockroach docker image

---

Note from @rafiss : Since Flush doesn't really do anything, we should just be able to ignore it rather than returning an error.

---

Jira issue: CRDB-17174
Epic CRDB-2466

[blathers-crl]

Hello, I am Blathers. I am here to help you get the issue triaged.

It looks like you have not filled out the issue in the format of any of our templates. To best assist you, we advise you to use one of these templates.

I have CC'd a few people who may be able to assist you:

• @cockroachdb/sql-queries (found keywords: distsql)
• @rafiss (commented on pgwire: multiple active result sets (portals) not supported #40195)
• @jordanlewis (author of pgwire: multiple active result sets (portals) not supported #40195)
• @knz (commented on pgwire: multiple active result sets (portals) not supported #40195)
• @cockroachdb/sql-experience (found keywords: ORM,import)
• @rytaft (commented on pgwire: multiple active result sets (portals) not supported #40195)

If we have not gotten back to your issue within a few business days, you can try the following:

• Join our community slack channel and ask on #cockroachdb.
• Try find someone from here if you know they worked closely on the area and CC them.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is otan.}

[markddrake]

Cloned from brianc/node-postgres#2767

[markddrake]

Any idea when the official docker image will contain this fix

[rafiss]

The current schedule is that v22.1.4 and v21.2.14 will be released at the beginning of August. The timelines are subject to change, but those are the two release tags to watch out for.

[markddrake]

Thank you

[markddrake]

Picked up the docker image containing CockroachDB CCL v22.1.5 (x86_64-pc-linux-gnu, built 2022/07/28 14:58:04, go1.17.11) and can confirm issue is fixed.