Thread: [PATCH] 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://www.postgresql.org/message-id/16676-fd62c3c835880da6%40postgresql.org
[2] https://www.postgresql.org/message-id/17141-913d78b9675aac8e%40postgresql.org
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://www.postgresql.org/message-id/16676-fd62c3c835880da6%40postgresql.org
[2] https://www.postgresql.org/message-id/17141-913d78b9675aac8e%40postgresql.org
Proposed backpatch to 13.
Attachment
On 2021-Aug-13, David Christensen wrote: > 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. I think we should do this, given that it has show potential to bite people. We should also add a small mentioned to this in the docs, as in the attached. What do others think? -- Álvaro Herrera Valdivia, Chile — https://www.EnterpriseDB.com/ "Escucha y olvidarás; ve y recordarás; haz y entenderás" (Confucio)
Attachment
On Mon, Aug 30, 2021 at 3:51 PM Alvaro Herrera <alvherre@alvh.no-ip.org> wrote: > > On 2021-Aug-13, David Christensen wrote: > > > 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. > > I think we should do this, given that it has show potential to bite > people. We should also add a small mentioned to this in the docs, as in > the attached. > > What do others think? The patch I included had a doc mention in roughly the same place (at least the same file), but I'm not tied to the wording I used (and I may have missed more than one spot that might be appropriate). In any case, +1. David
On Mon, Aug 30, 2021 at 03:55:10PM -0500, David Christensen wrote: > On Mon, Aug 30, 2021 at 3:51 PM Alvaro Herrera <alvherre@alvh.no-ip.org> wrote: >> I think we should do this, given that it has show potential to bite >> people. We should also add a small mentioned to this in the docs, as in >> the attached. >> >> What do others think? There is a CF entry for this patch: https://commitfest.postgresql.org/34/3286/ Alvaro, are you planning to wrap that? -- Michael
Attachment
On 2021-Oct-01, Michael Paquier wrote: > On Mon, Aug 30, 2021 at 03:55:10PM -0500, David Christensen wrote: > > On Mon, Aug 30, 2021 at 3:51 PM Alvaro Herrera <alvherre@alvh.no-ip.org> wrote: > >> I think we should do this, given that it has show potential to bite > >> people. We should also add a small mentioned to this in the docs, as in > >> the attached. > >> > >> What do others think? > > There is a CF entry for this patch: > https://commitfest.postgresql.org/34/3286/ > > Alvaro, are you planning to wrap that? I've had mixed feelings about this whole idea, but I think it's the right thing to do. I'll try to get it pushed today. -- Álvaro Herrera Valdivia, Chile — https://www.EnterpriseDB.com/ "I can't go to a restaurant and order food because I keep looking at the fonts on the menu. Five minutes later I realize that it's also talking about food" (Donald Knuth)
On 2021-Aug-13, David Christensen wrote: > 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. Thank you, pushed with minimal adjustment. -- Álvaro Herrera 39°49'30"S 73°17'W — https://www.EnterpriseDB.com/ "Digital and video cameras have this adjustment and film cameras don't for the same reason dogs and cats lick themselves: because they can." (Ken Rockwell)
On Fri, Oct 01, 2021 at 06:33:01PM -0300, Alvaro Herrera wrote: > On 2021-Aug-13, David Christensen wrote: > > > 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. > > Thank you, pushed with minimal adjustment. > BTW, I just marked this one as committed in CF app -- Jaime Casanova Director de Servicios Profesionales SystemGuards - Consultores de PostgreSQL