plpgsql: tracking issue for 23.2 PLpgSQL features #105254

DrewKimball · 2023-06-21T03:16:01Z

This is a tracking issue for PLpgSQL features that are planned for 23.2.

Basic support for IF/ELSE, LOOP, variable declarations, variable assignments, parameters, EXIT, CONTINUE, and RETURN plpgsql: add initial support for simple PL/pgSQL functions #104755
Support CONSTANT variable declarations plpgsql: add support for CONSTANT variables #105241
Support FOR and WHILE loop statements plpgsql: add support for FOR loop statements #105246
Support for raising errors and messages plpgsql: add support for raising errors and messages #105251
Support SELECT INTO syntax plpgsql: implement SELECT INTO variable #105252
Support exception blocks plpgsql: implement exception handling #105253
Support PLpgSQL in randomized testing sqlsmith: add support for PLpgSQL to query generator #106368
Implement tail-call optimization for nested PLpgSQL sub-routines
Support for cursors plpgsql: implement PLpgSQL cursors #109709

The text was updated successfully, but these errors were encountered:

This patch implements tail-call optimization for the nested routine execution that is used to handle PLpgSQL control flow. PLpgSQL sub-routines are always tail calls because they are built as "continuation" functions, so we can always use the optimization for PLpgSQL. Tail-call optimization is only possible if the plan is not distributed (although we may not currently distribute such plans anyway). The optimization is performed by setting a `deferredRoutineReceiver` field on the planner before planning and running a nested routine. This `deferredRoutineReceiver` allows a routine in tail-call position to send the information needed to evaluate itself to its parent, and then return NULL. Once the parent routine receives the result, it checks whether `deferredRoutineReceiver` received a deferred nested routine, and if so, evaluates it to obtain the actual result. Given a simple looping function like the following: ``` CREATE FUNCTION f(n INT) RETURNS INT AS $$ DECLARE i INT := 0; BEGIN LOOP IF i >= n THEN EXIT; END IF; i := i + 1; END LOOP; RETURN i; END $$ LANGUAGE PLpgSQL; ``` This optimization takes runtime on my machine for `n=100000` from >20m to ~2s. Informs cockroachdb#105254 Release note: None

106668: plpgsql: implement tail-call optimization for PLpgSQL routines r=DrewKimball a=DrewKimball This patch implements tail-call optimization for the nested routine execution that is used to handle PLpgSQL control flow. PLpgSQL sub-routines are always tail calls because they are built as "continuation" functions, so we can always use the optimization for PLpgSQL. Tail-call optimization is only possible if the plan is not distributed (although we may not currently distribute such plans anyway). The optimization is performed by setting a `deferredRoutineReceiver` field on the planner before planning and running a nested routine. This `deferredRoutineReceiver` allows a routine in tail-call position to send the information needed to evaluate itself to its parent, and then return NULL. Once the parent routine receives the result, it checks whether `deferredRoutineReceiver` received a deferred nested routine, and if so, evaluates it to obtain the actual result. Given a simple looping function like the following: ``` CREATE FUNCTION f(n INT) RETURNS INT AS $$ DECLARE i INT := 0; BEGIN LOOP IF i >= n THEN EXIT; END IF; i := i + 1; END LOOP; RETURN i; END $$ LANGUAGE PLpgSQL; ``` This optimization takes runtime on my machine for `n=100000` from >20m to ~2s. Informs #105254 Release note: None 107205: cloud: allow parallel running of cloud unit tests r=rhu713 a=rhu713 Append a random uint64 in the paths of cloud unit tests to prevent parallel executions from interfering with each other. This is necessary since these tests now run for all release branches and can run in parallel. Fixes: #107137 Fixes: #107139 Release note: None Co-authored-by: Drew Kimball <drewk@cockroachlabs.com> Co-authored-by: Rui Hu <rui@cockroachlabs.com>

This patch implements tail-call optimization for the nested routine execution that is used to handle PLpgSQL control flow. PLpgSQL sub-routines are always tail calls because they are built as "continuation" functions, so we can always use the optimization for PLpgSQL. Tail-call optimization is only possible if the plan is not distributed (although we may not currently distribute such plans anyway). The optimization is performed by setting a `deferredRoutineReceiver` field on the planner before planning and running a nested routine. This `deferredRoutineReceiver` allows a routine in tail-call position to send the information needed to evaluate itself to its parent, and then return NULL. Once the parent routine receives the result, it checks whether `deferredRoutineReceiver` received a deferred nested routine, and if so, evaluates it to obtain the actual result. Given a simple looping function like the following: ``` CREATE FUNCTION f(n INT) RETURNS INT AS $$ DECLARE i INT := 0; BEGIN LOOP IF i >= n THEN EXIT; END IF; i := i + 1; END LOOP; RETURN i; END $$ LANGUAGE PLpgSQL; ``` This optimization takes runtime on my machine for `n=100000` from >20m to ~2s. Informs cockroachdb#105254 Release note: None

This patch adds support for executing PLpgSQL `IF` statements with `ELSIF` branches (else if). `IF` statements were already executed as CASE statements under the hood, so this change only requires building the `ELSIF` branches and appending them to the `whens` list. Informs cockroachdb#105254 Release note (sql change): Added support for specifying PLpgSQL `IF` statements with `ELSIF` branches.

This patch fixes two behaviors related to RAISE statements handling NULL values: 1. When a NULL value is passed as a formatting argument, it is printed as `<NULL>` in the result string. 2. When a NULL value is passed as a RAISE option (message, detail, etc.) a runtime `RAISE statement option cannot be null` error results. Informs cockroachdb#105254 Release note: None

This patch adds support for executing PLpgSQL `IF` statements with `ELSIF` branches (else if). `IF` statements were already executed as CASE statements under the hood, so this change only requires building the `ELSIF` branches and appending them to the `whens` list. Informs cockroachdb#105254 Release note (sql change): Added support for specifying PLpgSQL `IF` statements with `ELSIF` branches.

This patch fixes two behaviors related to RAISE statements handling NULL values: 1. When a NULL value is passed as a formatting argument, it is printed as `<NULL>` in the result string. 2. When a NULL value is passed as a RAISE option (message, detail, etc.) a runtime `RAISE statement option cannot be null` error results. Informs cockroachdb#105254 Release note: None

This patch adds support for executing PLpgSQL `IF` statements with `ELSIF` branches (else if). `IF` statements were already executed as CASE statements under the hood, so this change only requires building the `ELSIF` branches and appending them to the `whens` list. Informs cockroachdb#105254 Release note (sql change): Added support for specifying PLpgSQL `IF` statements with `ELSIF` branches.

This patch adds support in the PLpgSQL parser for the following commands related to cursors: `DECLARE`, `OPEN`, `FETCH`, `MOVE`, and `CLOSE`. The `OPEN ... FOR EXECUTE ...` syntax is not currently implemented. Informs cockroachdb#105254 Release note: None

110709: plpgsql: implement OPEN statements r=DrewKimball a=DrewKimball #### plpgsql: add parser support for cursors This patch adds support in the PLpgSQL parser for the following commands related to cursors: `DECLARE`, `OPEN`, `FETCH`, `MOVE`, and `CLOSE`. The `OPEN ... FOR EXECUTE ...` syntax is not currently implemented. Informs #105254 Release note: None #### plpgsql: add execution support for OPEN statements This patch adds support for executing PLpgSQL OPEN statements, which open a SQL cursor in the current transaction. The name of the cursor is supplied through a PLpgSQL variable. Since the `REFCURSOR` type hasn't been implemented yet, this patch uses `STRING` in the meantime. Limitations that will be lifted in future PRs: 1. Unnamed cursor declarations are not supported. If a cursor is opened with no name supplied, a name should be automatically generated. 2. Bound cursors are not yet supported. It should be possible to declare a cursor in the `DECLARE` block with the query already defined, at which point it can be opened with `OPEN <cursor>;`. 3. A cursor cannot be opened in a routine with an exception block. This is because correct handling of this case is waiting on separate work to implement rollback of changes to database state on exceptions. Informs #109709 Release note (sql change): Added initial support for executing the PLpgSQL `OPEN` statement, which allows a PLpgSQL routine to create a cursor. Currently, opening bound or unnamed cursors is not supported. In addition, `OPEN` statements cannot be used in a routine with an exception block. 111388: kvserver: latching changes for replicated shared locks r=nvanbenschoten a=arulajmani Two locking requests from the same transaction that are trying to acquire replicated shared locks need to be isolated from one another. They don't need to be isolated against shared locking requests from other transactions and unreplicated shared lock attempts from the same transaction. To achieve these semantics, we introduce a per-transaction range local key that all replicated shared locking requests declare non-MVCC write latches over. Closes #109668 Release note: None Co-authored-by: Drew Kimball <drewk@cockroachlabs.com> Co-authored-by: Arul Ajmani <arulajmani@gmail.com>

This patch adds support for declaring and opening a _bound_ cursor. Bound cursors allow a query to be associated with the cursor when it is declared in a PLpgSQL `DECLARE` section. The cursor can then be opened at any point in the main body of the PLpgSQL routine with syntax like the following: ``` OPEN curs; ``` Note how there is no query supplied to this `OPEN` statement, unlike for unbound cursors. Informs cockroachdb#105254 Release note (sql change): Added support for declaring bound cursors, which associate a query with a cursor in a PLpgSQL routine before it is opened.

111092: plpgsql: add support for bound cursor declarations r=DrewKimball a=DrewKimball #### plpgsql: add support for bound cursor declarations This patch adds support for declaring and opening a _bound_ cursor. Bound cursors allow a query to be associated with the cursor when it is declared in a PLpgSQL `DECLARE` section. The cursor can then be opened at any point in the main body of the PLpgSQL routine with syntax like the following: ``` OPEN curs; ``` Note how there is no query supplied to this `OPEN` statement, unlike for unbound cursors. Informs #105254 Release note (sql change): Added support for declaring bound cursors, which associate a query with a cursor in a PLpgSQL routine before it is opened. Co-authored-by: Drew Kimball <drewk@cockroachlabs.com>

This patch adds support in the PLpgSQL parser for the following commands related to cursors: `DECLARE`, `OPEN`, `FETCH`, `MOVE`, and `CLOSE`. The `OPEN ... FOR EXECUTE ...` syntax is not currently implemented. Informs cockroachdb#105254 Release note: None

This patch adds support for declaring and opening a _bound_ cursor. Bound cursors allow a query to be associated with the cursor when it is declared in a PLpgSQL `DECLARE` section. The cursor can then be opened at any point in the main body of the PLpgSQL routine with syntax like the following: ``` OPEN curs; ``` Note how there is no query supplied to this `OPEN` statement, unlike for unbound cursors. Informs cockroachdb#105254 Release note (sql change): Added support for declaring bound cursors, which associate a query with a cursor in a PLpgSQL routine before it is opened.

Informs cockroachdb#105254 Release note: None

111970: plpgsql,clusterversion: add a version gate for PLpgSQL routines r=DrewKimball a=DrewKimball Informs #105254 Release note: None Co-authored-by: Drew Kimball <drewk@cockroachlabs.com>

This patch adds support in the PLpgSQL parser for the following commands related to cursors: `DECLARE`, `OPEN`, `FETCH`, `MOVE`, and `CLOSE`. The `OPEN ... FOR EXECUTE ...` syntax is not currently implemented. Informs cockroachdb#105254 Release note: None

This patch adds support for declaring and opening a _bound_ cursor. Bound cursors allow a query to be associated with the cursor when it is declared in a PLpgSQL `DECLARE` section. The cursor can then be opened at any point in the main body of the PLpgSQL routine with syntax like the following: ``` OPEN curs; ``` Note how there is no query supplied to this `OPEN` statement, unlike for unbound cursors. Informs cockroachdb#105254 Release note (sql change): Added support for declaring bound cursors, which associate a query with a cursor in a PLpgSQL routine before it is opened.

DrewKimball · 2023-10-10T23:26:19Z

Note: FOR loops and OTHERS branch were dropped for 23.2.

This patch ensures that PLpgSQL parsing errors are correctly propagated in all cases. Previously, there were a few cases (like variable declaration type parsing) where an error didn't halt parsing. The contract for `GetTypeFromValidSQLSyntax` is also clarified, since it is ok to call with an invalid type name as long as the error is properly handled. Informs cockroachdb#105254 Release note: None

112200: plpgsql: correctly handle parsing errors r=DrewKimball a=DrewKimball #### plpgsql: correctly handle parsing errors This patch ensures that PLpgSQL parsing errors are correctly propagated in all cases. Previously, there were a few cases (like variable declaration type parsing) where an error didn't halt parsing. The contract for `GetTypeFromValidSQLSyntax` is also clarified, since it is ok to call with an invalid type name as long as the error is properly handled. Informs #105254 Release note: None #### plpgsql: handle multiple expressions when one expression is expected Previously, the PLpgSQL parser could panic when the user supplied more than one expression in a location where only one was expected, for example, in a return statement. This was because the PLpgSQL parser delegated to the SQL parser's `ParseExpr` function, which expects exactly one input expression. This commit returns a syntax error instead of the panic by switching to use `ParseExprs`, which can handle multiple input expressions. Informs #109342 Release note: None #### plpgsql: return correct error for invalid parantheses and missing expression This patch fixes error messages in the PLpgSQL parser for the case when the parenthesis nesting is invalid, and for the case when no expression (or statement) is supplied. Previously, invalid parentheses would cause a panic without an error code, and a missing expression had the incorrect message, since it wasn't checked until the SQL parser attempted to read an empty string. Now, both cases are checked immediately by the PLpgSQL parser and the correct error is propagated. Fixes #109342 Release note: None Co-authored-by: Drew Kimball <drewk@cockroachlabs.com>

This patch ensures that PLpgSQL parsing errors are correctly propagated in all cases. Previously, there were a few cases (like variable declaration type parsing) where an error didn't halt parsing. The contract for `GetTypeFromValidSQLSyntax` is also clarified, since it is ok to call with an invalid type name as long as the error is properly handled. Informs #105254 Release note: None

DrewKimball added the C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) label Jun 21, 2023

DrewKimball added this to Triage in SQL Queries via automation Jun 21, 2023

blathers-crl bot added the T-sql-queries SQL Queries Team label Jun 21, 2023

DrewKimball self-assigned this Jun 21, 2023

DrewKimball moved this from Triage to Active in SQL Queries Jun 21, 2023

exalate-issue-sync bot unassigned DrewKimball Jun 21, 2023

exalate-issue-sync bot added T-sql-queries SQL Queries Team and removed T-sql-queries SQL Queries Team labels Jun 21, 2023

DrewKimball mentioned this issue Jul 12, 2023

plpgsql: implement tail-call optimization for PLpgSQL routines #106668

Merged

DrewKimball self-assigned this Jul 21, 2023

DrewKimball mentioned this issue Aug 4, 2023

plpgsql: add support for ELSIF branches #108211

Merged

DrewKimball mentioned this issue Aug 4, 2023

plpgsql: handle NULL values correctly for RAISE statements #108216

Merged

DrewKimball added a commit to DrewKimball/cockroach that referenced this issue Oct 7, 2023

plpgsql,clusterversion: add a version gate for PLpgSQL routines

5d93ba3

Informs cockroachdb#105254 Release note: None

DrewKimball mentioned this issue Oct 7, 2023

plpgsql,clusterversion: add a version gate for PLpgSQL routines #111970

Merged

DrewKimball added a commit to DrewKimball/cockroach that referenced this issue Oct 9, 2023

plpgsql,clusterversion: add a version gate for PLpgSQL routines

0759466

Informs cockroachdb#105254 Release note: None

craig bot pushed a commit that referenced this issue Oct 9, 2023

Merge #111970

4c14604

111970: plpgsql,clusterversion: add a version gate for PLpgSQL routines r=DrewKimball a=DrewKimball Informs #105254 Release note: None Co-authored-by: Drew Kimball <drewk@cockroachlabs.com>

DrewKimball mentioned this issue Oct 11, 2023

plpgsql: correctly handle parsing errors #112200

Merged

blathers-crl bot mentioned this issue Oct 17, 2023

release-23.2: plpgsql: correctly handle parsing errors #112508

Merged

DrewKimball closed this as completed Jan 12, 2024

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

plpgsql: tracking issue for 23.2 PLpgSQL features #105254

plpgsql: tracking issue for 23.2 PLpgSQL features #105254

DrewKimball commented Jun 21, 2023 •

edited

Loading

DrewKimball commented Oct 10, 2023

plpgsql: tracking issue for 23.2 PLpgSQL features #105254

plpgsql: tracking issue for 23.2 PLpgSQL features #105254

Comments

DrewKimball commented Jun 21, 2023 • edited Loading

DrewKimball commented Oct 10, 2023

DrewKimball commented Jun 21, 2023 •

edited

Loading