diff options
| author | Alvaro Herrera | 2021-10-01 21:29:18 +0000 |
|---|---|---|
| committer | Alvaro Herrera | 2021-10-01 21:29:18 +0000 |
| commit | 170206e458bce1d4be34bb805f884b2735430519 (patch) | |
| tree | c017ac7c85e6b4f13cd62d5184985d2eb90d30ba /src | |
| parent | 7adbe186f7fd7e036d2cab7d8ed8ee99354ec219 (diff) | |
Error out if SKIP LOCKED and WITH TIES are both specified
Both bugs #16676[1] and #17141[2] illustrate that the combination of
SKIP LOCKED and FETCH FIRST WITH TIES break expectations when it comes
to rows returned to other sessions accessing the same row. Since this
situation is detectable from the syntax and hard to fix otherwise,
forbid for now, with the potential to fix in the future.
[1] https://postgr.es/m/16676-fd62c3c835880da6@postgresql.org
[2] https://postgr.es/m/17141-913d78b9675aac8e@postgresql.org
Backpatch-through: 13, where WITH TIES was introduced
Author: David Christensen <david.christensen@crunchydata.com>
Discussion: https://postgr.es/m/CAOxo6XLPccCKru3xPMaYDpa+AXyPeWFs+SskrrL+HKwDjJnLhg@mail.gmail.com
Diffstat (limited to 'src')
| -rw-r--r-- | src/backend/commands/matview.c | 3 | ||||
| -rw-r--r-- | src/backend/parser/gram.y | 15 | ||||
| -rw-r--r-- | src/test/regress/expected/limit.out | 5 | ||||
| -rw-r--r-- | src/test/regress/sql/limit.sql | 5 |
4 files changed, 27 insertions, 1 deletions
diff --git a/src/backend/commands/matview.c b/src/backend/commands/matview.c index 4c9d1387ef6..2ba5172f7c6 100644 --- a/src/backend/commands/matview.c +++ b/src/backend/commands/matview.c @@ -185,7 +185,8 @@ ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString, if (concurrent && stmt->skipData) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), - errmsg("CONCURRENTLY and WITH NO DATA options cannot be used together"))); + errmsg("%s and %s options cannot be used together", + "CONCURRENTLY", "WITH NO DATA"))); /* * Check that everything is correct for a refresh. Problems at this point diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 3adc087e3ff..74339aa4db8 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -16163,6 +16163,21 @@ insertSelectOptions(SelectStmt *stmt, ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("WITH TIES cannot be specified without ORDER BY clause"))); + if (limitClause->limitOption == LIMIT_OPTION_WITH_TIES && stmt->lockingClause) + { + ListCell *lc; + + foreach(lc, stmt->lockingClause) + { + LockingClause *lock = lfirst_node(LockingClause, lc); + + if (lock->waitPolicy == LockWaitSkip) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("%s and %s options cannot be used together", + "SKIP LOCKED", "WITH TIES"))); + } + } stmt->limitOption = limitClause->limitOption; } if (withClause) diff --git a/src/test/regress/expected/limit.out b/src/test/regress/expected/limit.out index b75afcc01a3..8a98bbea8eb 100644 --- a/src/test/regress/expected/limit.out +++ b/src/test/regress/expected/limit.out @@ -619,6 +619,11 @@ SELECT thousand 0 (2 rows) +-- SKIP LOCKED and WITH TIES are incompatible +SELECT thousand + FROM onek WHERE thousand < 5 + ORDER BY thousand FETCH FIRST 1 ROW WITH TIES FOR UPDATE SKIP LOCKED; +ERROR: SKIP LOCKED and WITH TIES options cannot be used together -- should fail SELECT ''::text AS two, unique1, unique2, stringu1 FROM onek WHERE unique1 > 50 diff --git a/src/test/regress/sql/limit.sql b/src/test/regress/sql/limit.sql index d2d4ef132df..6f0cda98701 100644 --- a/src/test/regress/sql/limit.sql +++ b/src/test/regress/sql/limit.sql @@ -173,6 +173,11 @@ SELECT thousand FROM onek WHERE thousand < 5 ORDER BY thousand FETCH FIRST 2 ROW ONLY; +-- SKIP LOCKED and WITH TIES are incompatible +SELECT thousand + FROM onek WHERE thousand < 5 + ORDER BY thousand FETCH FIRST 1 ROW WITH TIES FOR UPDATE SKIP LOCKED; + -- should fail SELECT ''::text AS two, unique1, unique2, stringu1 FROM onek WHERE unique1 > 50 |