cockroachdb / cockroachdb/cockroach

sql: Increase limit on DECLARE variables and improve confusing error message for better PostgreSQL compatibility

Open
#159,620 1 comment 0 reactions 0 assignees View on GitHub
C-enhancement T-sql-queries
Dominant language
Go
Stars
32.5k
Forks
4.1k
PR merge metrics
PR metrics pending

Description

**Is your feature request related to a problem? Please describe.**
Yes. Currently, CockroachDB limits the number of variables in a DECLARE statement to 100. In PostgreSQL, this limit is effectively unlimited (constrained only by memory/stack).

This low limit creates significant friction for users migrating stored procedures or large anonymous blocks from PostgreSQL to CockroachDB. Users are forced to refactor working logic into smaller chunks, which adds unnecessary overhead to migration efforts.

Additionally, when this limit is hit, the error message is confusing and leaks internal implementation details: `ERROR: functions cannot have more than 100 arguments.`

This error is misleading because the user is defining variables in a DECLARE block, not passing arguments to a function. It confuses users who do not know that DECLARE blocks might be implemented as functions/lambdas internally.

**Describe the solution you'd like**
- Increase the Limit: Raise the maximum number of DECLARE variables to a more reasonable default (e.g., 500 or 1000), or remove the arbitrary limit entirely to match PostgreSQL parity.
- Improve Error Messaging: If a hard limit must remain, the error message should clearly indicate that the limit applies to declared variables, rather than "function arguments."

**Describe alternatives you've considered**
- Refactoring large SQL blocks into multiple smaller nested blocks or separate transactions (high effort for users migrating legacy code).

**Additional context / Reproduction**

To reproduce the confusing error message and the limit:

```
-- This script attempts to declare 101 variables
DO $$
DECLARE
v1 INT; v2 INT; v3 INT; v4 INT; v5 INT; v6 INT; v7 INT; v8 INT; v9 INT; v10 INT;
v11 INT; v12 INT; v13 INT; v14 INT; v15 INT; v16 INT; v17 INT; v18 INT; v19 INT; v20 INT;
v21 INT; v22 INT; v23 INT; v24 INT; v25 INT; v26 INT; v27 INT; v28 INT; v29 INT; v30 INT;
v31 INT; v32 INT; v33 INT; v34 INT; v35 INT; v36 INT; v37 INT; v38 INT; v39 INT; v40 INT;
v41 INT; v42 INT; v43 INT; v44 INT; v45 INT; v46 INT; v47 INT; v48 INT; v49 INT; v50 INT;
v51 INT; v52 INT; v53 INT; v54 INT; v55 INT; v56 INT; v57 INT; v58 INT; v59 INT; v60 INT;
v61 INT; v62 INT; v63 INT; v64 INT; v65 INT; v66 INT; v67 INT; v68 INT; v69 INT; v70 INT;
v71 INT; v72 INT; v73 INT; v74 INT; v75 INT; v76 INT; v77 INT; v78 INT; v79 INT; v80 INT;
v81 INT; v82 INT; v83 INT; v84 INT; v85 INT; v86 INT; v87 INT; v88 INT; v89 INT; v90 INT;
v91 INT; v92 INT; v93 INT; v94 INT; v95 INT; v96 INT; v97 INT; v98 INT; v99 INT; v100 INT;
v101 INT; -- The 101st variable triggering the error
BEGIN
v1 := 1;
END $$;
```
Current Output: `ERROR: functions cannot have more than 100 arguments`

Expected Output: Successful execution, or (if limit remains) an error like: `ERROR: cannot declare more than 100 variables`

Jira issue: CRDB-57955

Contributor guide

Open the contributing guide

Research direction

Start by running the supplied DO block that declares 101 variables and confirm the current error. Review how CockroachDB enforces the DECLARE-variable limit and compare the behavior with PostgreSQL. Done means the supported limit and user-facing error are aligned with the chosen compatibility outcome, with coverage for the reproduction case.

Written by the indexing model from the issue text.

Assessment

Tech stack
postgresql, sql
Domain
database
Issue type
Feature
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
42/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.