[PATCH] Error out if SKIP LOCKED and WITH TIES are both specified

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)

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

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