SET results_buffer_size doesn't work as a session variable about cockroach HOT 7 OPEN

A customer indicated this would be good to have in a backport to 23.2.7 since they are testing read committed and this helps minimize transaction retry errors.

from cockroach.

The setting should be configured in one of two ways:

• use the cluster setting sql.defaults.results_buffer.size.
• specify results_buffer_size in the connection string.

Changing the buffer size in a running session is not supported, and would be a somewhat high effort to implement, since it requires changing the size of the buffer while it's being used in different goroutines.

from cockroach.

Related issue: #124049

from cockroach.

Thanks! Someday we may wanna do it if restarting the session becomes unacceptable. But for now we should probably remove it from this doc page, I'll make a separate issue for that:
https://www.cockroachlabs.com/docs/v23.2/set-vars#supported-variables

from cockroach.

Specifying results_buffer_size in the connection string does not seem to work:

~ % cockroach sql --insecure --url 'postgresql://root@localhost:26257/foo?options=-c%20results_buffer_size=4mib' ERROR: options: parameter "results_buffer_size" cannot be changed SQLSTATE: 55P02 Failed running "sql"

from cockroach.

Ah I think there's a bit of a rough edge. It doesn't work in the options parameter, but it does work as a top-level query parameter.

❯ ./cockroach sql --insecure --url 'postgresql://root@localhost:26257/defaultdb?results_buffer_size=4mib'
#
# Welcome to the CockroachDB SQL shell.
# All statements must be terminated by a semicolon.
# To exit, type: \q.
#
# Server version: CockroachDB CCL v24.2.0-alpha.00000000-dev (darwin arm64, built , go1.22.3 X:nocoverageredesign) (same version as client)
# Cluster ID: 4f3c4c15-b9a5-4bb9-a744-b7c39228c19d
#
# Enter \? for a brief introduction.
#
root@localhost:26257/defaultdb> show results_buffer_size;
  results_buffer_size
-----------------------
  4194304
(1 row)

Let me try to get the bug with options fixed; ideally that should work as well.

from cockroach.

Thanks. Some (maybe all?) JDBC libraries don't seem to honor results_buffer_size as a top-level connection setting, so being able to set this as a session variable on connect with options would be helpful.

from cockroach.