Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

plpgsql: support label and condition for EXIT and CONTINUE #115271

Closed
DingDLu opened this issue Nov 29, 2023 · 1 comment · Fixed by #120733
Closed

plpgsql: support label and condition for EXIT and CONTINUE #115271

DingDLu opened this issue Nov 29, 2023 · 1 comment · Fixed by #120733
Assignees
@DrewKimball
Labels
A-sql-routine UDFs and Stored Procedures branch-release-24.1 Used to mark GA and release blockers and technical advisories for 24.1 C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) GA-blocker T-sql-queries SQL Queries Team

Comments

@DingDLu
Copy link

DingDLu commented Nov 29, 2023 •
edited
Loading

<<block_label>>
BEGIN
-- some code
EXIT [block_label] [WHEN condition];
-- some more code
END block_label;

Jira issue: CRDB-33952
Epic: CRDB-34165
@DingDLu DingDLu added C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) T-sql-queries SQL Queries Team A-sql-routine UDFs and Stored Procedures labels Nov 29, 2023
@DrewKimball DrewKimball changed the title plpgsql: support exit from a block plpgsql: support label and condition for EXIT and CONTINUE Jan 12, 2024
@DrewKimball DrewKimball mentioned this issue Jan 12, 2024
Merged
@blathers-crl blathers[crl]

This comment was marked as resolved.

Sign in to view
@DrewKimball DrewKimball added the branch-release-24.1 Used to mark GA and release blockers and technical advisories for 24.1 label Mar 14, 2024
DrewKimball added a commit to DrewKimball/cockroach that referenced this issue Mar 19, 2024
@DrewKimball
plpgsql: implement CONTINUE/EXIT with condition
1edda17 
PL/pgSQL allows adding a `WHEN <condition>` clause to `EXIT` and
`CONTINUE` statements. This is syntactic sugar for `EXIT` or `CONTINUE`
within an `IF` statement. This commit add support for this syntax.

Informs cockroachdb#115271

Release note (sql change): It is now possible to specify a condition
for the PL/pgSQL statemenets `EXIT` and `CONTINUE`.
@DrewKimball DrewKimball mentioned this issue Mar 19, 2024
Merged
@DrewKimball DrewKimball mentioned this issue Mar 20, 2024
Merged
DrewKimball added a commit to DrewKimball/cockroach that referenced this issue Mar 20, 2024
@DrewKimball
plpgsql: implement CONTINUE/EXIT with condition
cc0a480 
PL/pgSQL allows adding a `WHEN <condition>` clause to `EXIT` and
`CONTINUE` statements. This is syntactic sugar for `EXIT` or `CONTINUE`
within an `IF` statement. This commit add support for this syntax.

Informs cockroachdb#115271

Release note (sql change): It is now possible to specify a condition
for the PL/pgSQL statemenets `EXIT` and `CONTINUE`.
DrewKimball added a commit to DrewKimball/cockroach that referenced this issue Mar 20, 2024
@DrewKimball
plpgsql: implement labels for EXIT and CONTINUE
7fefd28 
This commit adds support for specifying loop and block labels as the
target of `EXIT` and `CONTINUE` statements. This can allow an outer
loop to be the target of the statement instead of the innermost loop.
It also allows for control flow to jump to the end of a labeled block.
The following commit will fix handling for the root block and correct
some error messages.

Fixes cockroachdb#115271

Release note (sql change): The PL/pgSQL `EXIT` and `CONTINUE` statements
can now specify which loop or block is the target via labels.
DrewKimball added a commit to DrewKimball/cockroach that referenced this issue Mar 20, 2024
@DrewKimball
plpgsql: handle EXIT/CONTINUE for root block and correct errors
7bef93b 
This commit cleans up `EXIT` and `CONTINUE` with label handling. It is
now possible to `EXIT` from the root block or the routine itself, and
the various error messages that can result from incorrect usage have
been corrected.

Informs cockroachdb#115271

Release note: None
DrewKimball added a commit to DrewKimball/cockroach that referenced this issue Mar 20, 2024
@DrewKimball
plpgsql: implement CONTINUE/EXIT with condition
c68809c 
PL/pgSQL allows adding a `WHEN <condition>` clause to `EXIT` and
`CONTINUE` statements. This is syntactic sugar for `EXIT` or `CONTINUE`
within an `IF` statement. This commit add support for this syntax.

Informs cockroachdb#115271

Release note (sql change): It is now possible to specify a condition
for the PL/pgSQL statemenets `EXIT` and `CONTINUE`.
craig bot pushed a commit that referenced this issue Mar 20, 2024
@itsbilal @xinhaoz
@stevendanna @DrewKimball @renatolabs
Merge #120023 #120430 #120594 #120686 #120760
aa2e1fb 
120023: roachtest: add run-operation command to run singular operation  r=HerkoLategan a=itsbilal

NB: This change builds on #119796. Review that first.

This change adds a new roachtest command, run-operation,
that runs a singular operation defined in OperationSpec
(see #119796), in a simplified harness, with existing
clusters only. The existing cluster is not touched (eg.
stopped or wiped), unless the operation explicitly
does an action like that.

An implementation of operation.Operation is also bundled in this
change to make these operations runnable.

Epic: none

Release note: None

120430: sql/sem/tree: add FmtForFingerprint functions to collapse long lists  r=xinhaoz a=xinhaoz

Please note only the latest commit should be reviewed here.

---------------
Add functions for the `FmtForFingerprint` flag to shorten long lists
to a representative two element list where the second element is simply
`__more__` to represent the rest of the list.

Specifically, where `FmtHideConstants` would shorten tuples, arrays and
VALUES clauses to its first two elements if it coontains only literals
and placeholders, `FmtCollapseLists` shortens these lists to its first
element if all items in the list are any combination of placeholders,
literals, or a simple subexpressions containing only literals and/or
placeholders.

Enabling this behaviour for statement fingerprint generation can be done
via setting the cluster setting `sql.stats.statement_fingerprint.format_mask`
to include `FmtCollapseLists`.

```
FmtHideConstants (previous behaviour):
VALUES ((1, 2), (3, 4)), ((5, 6), (7, 8)), ((9, 10), (11, 12)) ->
VALUES ((_, _), (_, _)), ((_, _), (_, _)), ((_, _), (_, _))

ARRAY[1, 2, 3+4] ->
ARRAY[_, _, _+4]

SELECT * FROM foo WHERE a IN (1, 2, 3, 4, 5) ->
SELECT * FROM foo WHERE a IN (1, 2, __more1_10__)

---

FmtHideConstants | FmtCollapseLists:
VALUES ((1, 2), (3, 4)), ((5, 6), (7, 8)), ((9, 10), (11, 12)) ->
VALUES ((_, __more__), __more__), (__more__)

ARRAY[1, 2, 3+4] ->
ARRAY[_, __more__]

SELECT * FROM foo WHERE a IN (1, 2, 3, 4, 5) ->
SELECT * FROM foo WHERE a IN (1, __more__)
```


Epic: none
Part of: #120409
Release note: none

120594: ccl/serverccl: skip flakey assertion r=msbutler a=stevendanna

Informs #119329
Release note: None

120686: plpgsql: implement CONTINUE/EXIT with condition r=DrewKimball a=DrewKimball

PL/pgSQL allows adding a `WHEN <condition>` clause to `EXIT` and `CONTINUE` statements. This is syntactic sugar for `EXIT` or `CONTINUE` within an `IF` statement. This commit add support for this syntax.

Informs #115271

Release note (sql change): It is now possible to specify a condition for the PL/pgSQL statemenets `EXIT` and `CONTINUE`.

120760: roachprod: remove `admin-ui-port` flag from `start-sql` r=DarrylWong a=renatolabs

This flag is only applicable to the system tenant (`roachprod start`); having it on `start-sql` is misleading.

Epic: none

Release note: None

Co-authored-by: Bilal Akhtar <bilal@cockroachlabs.com>
Co-authored-by: Xin Hao Zhang <xzhang@cockroachlabs.com>
Co-authored-by: Steven Danna <danna@cockroachlabs.com>
Co-authored-by: Drew Kimball <drewk@cockroachlabs.com>
Co-authored-by: Renato Costa <renato@cockroachlabs.com>
DrewKimball added a commit to DrewKimball/cockroach that referenced this issue Mar 20, 2024
@DrewKimball
plpgsql: implement labels for EXIT and CONTINUE
cf9caa4 
This commit adds support for specifying loop and block labels as the
target of `EXIT` and `CONTINUE` statements. This can allow an outer
loop to be the target of the statement instead of the innermost loop.
It also allows for control flow to jump to the end of a labeled block.
The following commit will fix handling for the root block and correct
some error messages.

Fixes cockroachdb#115271

Release note (sql change): The PL/pgSQL `EXIT` and `CONTINUE` statements
can now specify which loop or block is the target via labels.
DrewKimball added a commit to DrewKimball/cockroach that referenced this issue Mar 20, 2024
@DrewKimball
plpgsql: handle EXIT/CONTINUE for root block and correct errors
12a7d37 
This commit cleans up `EXIT` and `CONTINUE` with label handling. It is
now possible to `EXIT` from the root block or the routine itself, and
the various error messages that can result from incorrect usage have
been corrected.

Informs cockroachdb#115271

Release note: None
DrewKimball added a commit to DrewKimball/cockroach that referenced this issue Mar 25, 2024
@DrewKimball
plpgsql: handle EXIT/CONTINUE for root block and correct errors
19fb387 
This commit cleans up `EXIT` and `CONTINUE` with label handling. It is
now possible to `EXIT` from the root block or the routine itself, and
the various error messages that can result from incorrect usage have
been corrected.

Informs cockroachdb#115271

Release note: None
craig bot pushed a commit that referenced this issue Mar 25, 2024
@DrewKimball
Merge #120733
e354a4d 
120733: plpgsql: implement labels for EXIT and CONTINUE r=DrewKimball a=DrewKimball

#### plpgsql: implement labels for EXIT and CONTINUE

This commit adds support for specifying loop and block labels as the
target of `EXIT` and `CONTINUE` statements. This can allow an outer
loop to be the target of the statement instead of the innermost loop.
It also allows for control flow to jump to the end of a labeled block.
The following commit will fix handling for the root block and correct
some error messages.

Fixes #115271

Release note (sql change): The PL/pgSQL `EXIT` and `CONTINUE` statements
can now specify which loop or block is the target via labels.

#### plpgsql: handle EXIT/CONTINUE for root block and correct errors

This commit cleans up `EXIT` and `CONTINUE` with label handling. It is
now possible to `EXIT` from the root block or the routine itself, and
the various error messages that can result from incorrect usage have
been corrected.

Informs #115271

Release note: None

Co-authored-by: Drew Kimball <drewk@cockroachlabs.com>
@craig craig bot closed this as completed in 93f240c Mar 25, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@DrewKimball DrewKimball

Labels
A-sql-routine UDFs and Stored Procedures branch-release-24.1 Used to mark GA and release blockers and technical advisories for 24.1 C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) GA-blocker T-sql-queries SQL Queries Team
Projects
SQL Queries
Archived in project
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

plpgsql: implement labels for EXIT and CONTINUE DrewKimball/cockroach
3 participants
@mgartner @DrewKimball @DingDLu