Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitc6bc655

Browse files
committed
Error out if SKIP LOCKED and WITH TIES are both specified
Both bugs #16676[1] and #17141[2] illustrate that the combination ofSKIP LOCKED and FETCH FIRST WITH TIES break expectations when it comesto rows returned to other sessions accessing the same row. Since thissituation 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.orgBackpatch-through: 13, where WITH TIES was introducedAuthor: David Christensen <david.christensen@crunchydata.com>Discussion:https://postgr.es/m/CAOxo6XLPccCKru3xPMaYDpa+AXyPeWFs+SskrrL+HKwDjJnLhg@mail.gmail.com
1 parentd186d23 commitc6bc655

File tree

5 files changed

+29
-2
lines changed

5 files changed

+29
-2
lines changed

‎doc/src/sgml/ref/select.sgml

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1515,7 +1515,8 @@ FETCH { FIRST | NEXT } [ <replaceable class="parameter">count</replaceable> ] {
15151515
The <literal>WITH TIES</literal> option is used to return any additional
15161516
rows that tie for the last place in the result set according to
15171517
the <literal>ORDER BY</literal> clause; <literal>ORDER BY</literal>
1518-
is mandatory in this case.
1518+
is mandatory in this case, and <literal>SKIP LOCKED</literal> is
1519+
not allowed.
15191520
<literal>ROW</literal> and <literal>ROWS</literal> as well as
15201521
<literal>FIRST</literal> and <literal>NEXT</literal> are noise
15211522
words that don't influence the effects of these clauses.

‎src/backend/commands/matview.c

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -185,7 +185,8 @@ ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
185185
if (concurrent&&stmt->skipData)
186186
ereport(ERROR,
187187
(errcode(ERRCODE_SYNTAX_ERROR),
188-
errmsg("CONCURRENTLY and WITH NO DATA options cannot be used together")));
188+
errmsg("%s and %s options cannot be used together",
189+
"CONCURRENTLY","WITH NO DATA")));
189190

190191
/*
191192
* Check that everything is correct for a refresh. Problems at this point

‎src/backend/parser/gram.y

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -16816,6 +16816,21 @@ insertSelectOptions(SelectStmt *stmt,
1681616816
ereport(ERROR,
1681716817
(errcode(ERRCODE_SYNTAX_ERROR),
1681816818
errmsg("WITH TIES cannot be specified without ORDER BY clause")));
16819+
if (limitClause->limitOption == LIMIT_OPTION_WITH_TIES && stmt->lockingClause)
16820+
{
16821+
ListCell *lc;
16822+
16823+
foreach(lc, stmt->lockingClause)
16824+
{
16825+
LockingClause *lock = lfirst_node(LockingClause, lc);
16826+
16827+
if (lock->waitPolicy == LockWaitSkip)
16828+
ereport(ERROR,
16829+
(errcode(ERRCODE_SYNTAX_ERROR),
16830+
errmsg("%s and %s options cannot be used together",
16831+
"SKIP LOCKED", "WITH TIES")));
16832+
}
16833+
}
1681916834
stmt->limitOption = limitClause->limitOption;
1682016835
}
1682116836
if (withClause)

‎src/test/regress/expected/limit.out

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -619,6 +619,11 @@ SELECT thousand
619619
0
620620
(2 rows)
621621

622+
-- SKIP LOCKED and WITH TIES are incompatible
623+
SELECT thousand
624+
FROM onek WHERE thousand < 5
625+
ORDER BY thousand FETCH FIRST 1 ROW WITH TIES FOR UPDATE SKIP LOCKED;
626+
ERROR: SKIP LOCKED and WITH TIES options cannot be used together
622627
-- should fail
623628
SELECT ''::text AS two, unique1, unique2, stringu1
624629
FROM onek WHERE unique1 > 50

‎src/test/regress/sql/limit.sql

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -173,6 +173,11 @@ SELECT thousand
173173
FROM onekWHERE thousand<5
174174
ORDER BY thousand FETCH FIRST2 ROW ONLY;
175175

176+
-- SKIP LOCKED and WITH TIES are incompatible
177+
SELECT thousand
178+
FROM onekWHERE thousand<5
179+
ORDER BY thousand FETCH FIRST1 ROW WITH TIES FORUPDATE SKIP LOCKED;
180+
176181
-- should fail
177182
SELECT''::textAS two, unique1, unique2, stringu1
178183
FROM onekWHERE unique1>50

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp