plpgsql: implement PLpgSQL cursors #109709

DrewKimball · 2023-08-30T01:26:39Z

This issue tracks adding support for declaring, opening, fetching from, and closing cursors in PLpgSQL routines. Postgres docs for reference: https://www.postgresql.org/docs/current/plpgsql-cursors.html

Epic: CRDB-799

Jira issue: CRDB-31060

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 cockroachdb#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.

This patch adds support for opening an "unnamed" cursor in a PLpgSQL routine. An PLpgSQL cursor is unnamed when the value for the cursor variable is `NULL`. When an unnamed cursor is opened, a name will be generated for it like `<unnamed portal 1>` that is guaranteed not to conflict with an existing cursor or portal name. Informs cockroachdb#109709 Release note (sql change): Added support for unnamed PLpgSQL cursors, which generate a unique name when no cursor name was specified.

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 cockroachdb#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.

This patch adds the undocumentd `crdb_internal.plpgsql_fetch` builtin function, which seeks a cursor by the specified number of rows in the specified direction, and then returns the row at the ending location (if any). It reuses the same logic as the SQL FETCH and MOVE statements. Note that it differs from the SQL behavior for the `ALL` variants, since it only returns one row. This is consistent with PLpgSQL cursor behavior. The builtin function has 4 parameters: the name of the cursor, the seek direction (an integer representing `tree.FetchType`), the seek count (0 if not applicable), and a tuple containing typed NULL values that represents the expected return types for the columns. This type argument is similar to the one for `crdb_internal.assignment_cast`, with one addition: the result columns are padded with NULLs or truncated as necessary to fit the number of expected types. When the actual types returned by the cursor must be coerced to the expected types, an explicit cast is used, but width truncation is disallowed. This is in line with PG behavior, which allows casting a String to an Int, but does not allow casting a string like 'abc' to a Char. Informs cockroachdb#109709 Release note: None

This patch adds support for the PLpgSQL FETCH and MOVE statements, which seek and return rows from a cursor. This is handled by a builtin function, `crdb_internal.plpgsql_fetch`, which calls into the same logic that implements SQL FETCH and MOVE. Since it is possible to call `FETCH` with `INTO` variables of different types, the `crdb_internal.plpgsql_fetch` builtin takes an argument that supplies the expected column types as a tuple of typed NULL values like this: `(NULL::INT, NULL::BOOL)`. The actual types supplied by the cursor are coerced into the expected types. Note that the current implementation does not support using dynamic expressions in the FETCH/MOVE direction; only constant integer values. Dynamic direction counts like `FORWARD x` are not allowed in SQL syntax, but are allowed by PLpgSQL. Informs cockroachdb#109709 Release note (sql change): Added support for PLpgSQL FETCH and MOVE statements. Similar to SQL FETCH/MOVE statements, commands that would seek the cursor backward will fail. In addition, expressions other than constant integers are not yet supported for the `count` option.

This patch adds a builtin function `crdb_internal.plpgsql_gen_cursor_name` that generates a unique name for a PLpgSQL cursor if the supplied name is NULL. It then returns the resulting name to be used when opening a cursor. This will be used in a following commit to implement unnamed PLpgSQL cursors. Informs cockroachdb#109709 Release note: None

This patch adds the `crdb_internal.plpgsql_close` builtin, which closes the cursor with the given name. It returns a `34000` error if there is no cursor with the given name. A following commit will use this to implement the PLpgSQL CLOSE statement. Informs cockroachdb#109709 Release note: None

This patch implements the PLpgSQL CLOSE statement, which allows a PLpgSQL routine to close a cursor with the name specified by a cursor variable. Closing the cursor is handled by the internal builtin function `crdb_internal.plpgsql_close`. Informs cockroachdb#109709 Release note (sql change): Added support for the PLpgSQL CLOSE statement.

This patch adds support for opening an "unnamed" cursor in a PLpgSQL routine. An PLpgSQL cursor is unnamed when the value for the cursor variable is `NULL`. When an unnamed cursor is opened, a name will be generated for it like `<unnamed portal 1>` that is guaranteed not to conflict with an existing cursor or portal name. The PLpgSQL variable that represents the cursor's name is updated to reflect the generated name. Informs cockroachdb#109709 Release note (sql change): Added support for unnamed PLpgSQL cursors, which generate a unique name when no cursor name was specified.

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 cockroachdb#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.

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 <[email protected]> Co-authored-by: Arul Ajmani <[email protected]>

This patch adds a builtin function `crdb_internal.plpgsql_gen_cursor_name` that generates a unique name for a PLpgSQL cursor if the supplied name is NULL. It then returns the resulting name to be used when opening a cursor. This will be used in a following commit to implement unnamed PLpgSQL cursors. Informs cockroachdb#109709 Release note: None

This patch adds support for opening an "unnamed" cursor in a PLpgSQL routine. A PLpgSQL cursor is unnamed when the value for the cursor variable is `NULL`. When an unnamed cursor is opened, a name will be generated for it like `<unnamed portal 1>` that is guaranteed not to conflict with an existing cursor or portal name. The PLpgSQL variable that represents the cursor's name is updated to reflect the generated name. Informs cockroachdb#109709 Release note (sql change): Added support for unnamed PLpgSQL cursors, which generate a unique name when no cursor name was specified.

This patch adds a builtin function `crdb_internal.plpgsql_gen_cursor_name` that generates a unique name for a PLpgSQL cursor if the supplied name is NULL. It then returns the resulting name to be used when opening a cursor. This will be used in a following commit to implement unnamed PLpgSQL cursors. Informs cockroachdb#109709 Release note: None

111612: master: Update pkg/testutils/release/cockroach_releases.yaml r=rail a=github-actions[bot] Update pkg/testutils/release/cockroach_releases.yaml with recent values. Epic: None Release note: None 111678: storage: misc fixes after excise cluster setting addition r=jbowens a=itsbilal This change addresses some follow-up code review points from #111437. One of these was to update a cluster setting's description to better reflect its interaction with another cluster setting, and another is to address one missing guard in cluster versions when ratcheting forward. Epic: none Release note: None 111735: plpgsql: integrate exception-handling with the OPEN statement r=DrewKimball a=DrewKimball This patch integrates the PLpgSQL `OPEN` statement, which opens a cursor, with PLpgSQL exception handling. When a cursor is opened in a routine with an exception handler, its name is tracked in the shared block state of the routine. In the event of an error, all tracked cursors are closed before control reaches the exception handler. This mirrors the rollback of changes to the database state that also happens during exception handling. Informs #109709 Release note (sql change): It is now possible to open a cursor within a PLpgSQL function or procedure with an exception block. If an error occurs, creation of the cursor is rolled back before control reaches the exception handler. 111788: utilccl: simplify the ergonomics of licensing r=dt a=knz As suggested by `@stevendanna` Fixes #96221. Epic: CRDB-26689. Prior to this patch, with cluster virtualization the license setting had class "application level" which meant it had to be set separately through the system interface and the virtual cluster. This was cumbersome. Through product research, we found this can be simplified. For now, we are comfortable applying a single license configuration to an entire CockroachDB deployment, covering all its virtual clusters simultaneously. To effect this new vision, the present commit updates the class of `cluster.organization` and `enterprise.license` to become "system visible". This way, changing it from the system interface propagates it to all virtual clusters automatically. Co-authored-by: craig <[email protected]> Co-authored-by: Bilal Akhtar <[email protected]> Co-authored-by: Drew Kimball <[email protected]> Co-authored-by: Raphael 'kena' Poss <[email protected]>

This patch adds the undocumentd `crdb_internal.plpgsql_fetch` builtin function, which seeks a cursor by the specified number of rows in the specified direction, and then returns the row at the ending location (if any). It reuses the same logic as the SQL FETCH and MOVE statements. Note that it differs from the SQL behavior for the `ALL` variants, since it only returns one row. This is consistent with PLpgSQL cursor behavior. The builtin function has 4 parameters: the name of the cursor, the seek direction (an integer representing `tree.FetchType`), the seek count (0 if not applicable), and a tuple containing typed NULL values that represents the expected return types for the columns. This type argument is similar to the one for `crdb_internal.assignment_cast`, with one addition: the result columns are padded with NULLs or truncated as necessary to fit the number of expected types. When the actual types returned by the cursor must be coerced to the expected types, an explicit cast is used, but width truncation is disallowed. This is in line with PG behavior, which allows casting a String to an Int, but does not allow casting a string like 'abc' to a Char. Informs cockroachdb#109709 Release note: None

This patch adds support for the PLpgSQL FETCH and MOVE statements, which seek and return rows from a cursor. This is handled by a builtin function, `crdb_internal.plpgsql_fetch`, which calls into the same logic that implements SQL FETCH and MOVE. Since it is possible to call `FETCH` with `INTO` variables of different types, the `crdb_internal.plpgsql_fetch` builtin takes an argument that supplies the expected column types as a tuple of typed NULL values like this: `(NULL::INT, NULL::BOOL)`. The actual types supplied by the cursor are coerced into the expected types. Note that the current implementation does not support using dynamic expressions in the FETCH/MOVE direction; only constant integer values. Dynamic direction counts like `FORWARD x` are not allowed in SQL syntax, but are allowed by PLpgSQL. Informs cockroachdb#109709 Release note (sql change): Added support for PLpgSQL FETCH and MOVE statements. Similar to SQL FETCH/MOVE statements, commands that would seek the cursor backward will fail. In addition, expressions other than constant integers are not yet supported for the `count` option.

111318: plpgsql: add support for FETCH and MOVE statements r=DrewKimball a=DrewKimball #### plpgsql: implement builtin function for FETCH statements This patch adds the undocumentd `crdb_internal.plpgsql_fetch` builtin function, which seeks a cursor by the specified number of rows in the specified direction, and then returns the row at the ending location (if any). It reuses the same logic as the SQL FETCH and MOVE statements. Note that it differs from the SQL behavior for the `ALL` variants, since it only returns one row. This is consistent with PLpgSQL cursor behavior. The builtin function has 4 parameters: the name of the cursor, the seek direction (an integer representing `tree.FetchType`), the seek count (0 if not applicable), and a tuple containing typed NULL values that represents the expected return types for the columns. This type argument is similar to the one for `crdb_internal.assignment_cast`, with one addition: the result columns are padded with NULLs or truncated as necessary to fit the number of expected types. When the actual types returned by the cursor must be coerced to the expected types, an explicit cast is used, but width truncation is disallowed. This is in line with PG behavior, which allows casting a String to an Int, but does not allow casting a string like 'abc' to a Char. Informs #109709 Release note: None #### plpgsql: add support for FETCH and MOVE statements This patch adds support for the PLpgSQL FETCH and MOVE statements, which seek and return rows from a cursor. This is handled by a builtin function, `crdb_internal.plpgsql_fetch`, which calls into the same logic that implements SQL FETCH and MOVE. Since it is possible to call `FETCH` with `INTO` variables of different types, the `crdb_internal.plpgsql_fetch` builtin takes an argument that supplies the expected column types as a tuple of typed NULL values like this: `(NULL::INT, NULL::BOOL)`. The actual types supplied by the cursor are coerced into the expected types. Note that the current implementation does not support using dynamic expressions in the FETCH/MOVE direction; only constant integer values. Dynamic direction counts like `FORWARD x` are not allowed in SQL syntax, but are allowed by PLpgSQL. Informs #109709 Release note (sql change): Added support for PLpgSQL FETCH and MOVE statements. Similar to SQL FETCH/MOVE statements, commands that would seek the cursor backward will fail. In addition, expressions other than constant integers are not yet supported for the `count` option. 111546: kv: bump timestamp cache when resolving replicated locks r=nvanbenschoten a=arulajmani This patch teaches ResolveIntent and ResolveIntentRange requests to bump the timestamp cache if any replicated shared/exclusive locks were resolved by them (if the transaction that held the lock was committed). In all other cases (only unreplicated locks, no shared or exclusive locks, or aborted lock holder transaction) the timestamp cache is not bumped. The handling of ResolveIntentRange requests deserves some words -- for these, we choose to bump the timestamp cache over the entire keyspan they operated over if there's a single replicated {shared, exclusive} lock. This means we're losing fidelity over specific keys that had point locks on them; we choose this approach instead of trying to plumb high fidelity information back up. Lastly, it's worth noting that `EndTxn` requests also resolve local locks. As such, any replicated {shared, exclusive} locks resolved by a EndTxn request also need to be handled in similar fashion. This patch does not do that -- we leave that to an subsequent patch, at which point the linked issue can be closed. Informs #111536 Release note: None Co-authored-by: Drew Kimball <[email protected]> Co-authored-by: Arul Ajmani <[email protected]>

This patch integrates the `REFCURSOR` data type (added in cockroachdb#111392) with the PLpgSQL OPEN, CLOSE, FETCH, and MOVE statements, which now require the cursor variable to have type `REFCURSOR` instead of `STRING`. Attempts to use a variable of another type will result in an error like the following: ``` variable "curs" must be of type cursor or refcursor ``` Informs cockroachdb#109709 Release note: None

111815: plpgsql: integrate PLpgSQL cursors with the REFCURSOR data type r=DrewKimball a=DrewKimball #### plpgsql: integrate PLpgSQL cursors with the REFCURSOR data type This patch integrates the `REFCURSOR` data type (added in #111392) with the PLpgSQL OPEN, CLOSE, FETCH, and MOVE statements, which now require the cursor variable to have type `REFCURSOR` instead of `STRING`. Attempts to use a variable of another type will result in an error like the following: ``` variable "curs" must be of type cursor or refcursor ``` Informs #109709 Release note: None Co-authored-by: Drew Kimball <[email protected]>

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 cockroachdb#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.

This patch adds a builtin function `crdb_internal.plpgsql_gen_cursor_name` that generates a unique name for a PLpgSQL cursor if the supplied name is NULL. It then returns the resulting name to be used when opening a cursor. This will be used in a following commit to implement unnamed PLpgSQL cursors. Informs cockroachdb#109709 Release note: None

This patch adds support for opening an "unnamed" cursor in a PLpgSQL routine. A PLpgSQL cursor is unnamed when the value for the cursor variable is `NULL`. When an unnamed cursor is opened, a name will be generated for it like `<unnamed portal 1>` that is guaranteed not to conflict with an existing cursor or portal name. The PLpgSQL variable that represents the cursor's name is updated to reflect the generated name. Informs cockroachdb#109709 Release note (sql change): Added support for unnamed PLpgSQL cursors, which generate a unique name when no cursor name was specified.

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 cockroachdb#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.

DrewKimball · 2023-10-10T23:25:56Z

This should be done. There's more work to do, but tracked in other issues.

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

DrewKimball self-assigned this Aug 30, 2023

DrewKimball added this to SQL Queries Aug 30, 2023

github-project-automation bot moved this to Triage in SQL Queries Aug 30, 2023

DrewKimball moved this from Triage to Active in SQL Queries Aug 30, 2023

DrewKimball mentioned this issue Aug 21, 2023

plpgsql: tracking issue for 23.2 PLpgSQL features #105254

Closed

9 tasks

DrewKimball mentioned this issue Sep 15, 2023

plpgsql: implement OPEN statements #110709

Merged

DrewKimball mentioned this issue Sep 22, 2023

plpgsql: add support for unnamed cursors #111093

Closed

DrewKimball mentioned this issue Sep 26, 2023

plpgsql: add support for FETCH and MOVE statements #111318

Merged

DrewKimball mentioned this issue Sep 27, 2023

plpgsql: add support for unnamed cursors #111329

Merged

DrewKimball mentioned this issue Sep 27, 2023

plpgsql: implement CLOSE statements #111330

Merged

DrewKimball mentioned this issue Oct 5, 2023

plpgsql: integrate PLpgSQL cursors with the REFCURSOR data type #111815

Merged

DrewKimball closed this as completed Oct 10, 2023

github-project-automation bot moved this from Active to Done in SQL Queries Oct 10, 2023

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

plpgsql: implement PLpgSQL cursors #109709

plpgsql: implement PLpgSQL cursors #109709

DrewKimball commented Aug 30, 2023 •

edited by cockroach-jira-scripts

Loading

DrewKimball commented Oct 10, 2023

plpgsql: implement PLpgSQL cursors #109709

plpgsql: implement PLpgSQL cursors #109709

Comments

DrewKimball commented Aug 30, 2023 • edited by cockroach-jira-scripts Loading

DrewKimball commented Oct 10, 2023

DrewKimball commented Aug 30, 2023 •

edited by cockroach-jira-scripts

Loading