Re: Document efficient self-joins / UPDATE LIMIT techniques.

Поиск
Список
Период
Сортировка
От Corey Huinker
Тема Re: Document efficient self-joins / UPDATE LIMIT techniques.
Дата
Msg-id CADkLM=fymj8fbk0YhZSNdceRj0+fTXvtPXhiwSHobwjnC9rsgg@mail.gmail.com
обсуждение исходный текст
Ответ на Re: Document efficient self-joins / UPDATE LIMIT techniques.  ("Joel Jacobson" <joel@compiler.org>)
Ответы Re: Document efficient self-joins / UPDATE LIMIT techniques.  ("Joel Jacobson" <joel@compiler.org>)
Список pgsql-hackers
Дерево обсуждения
On Tue, Feb 13, 2024 at 11:51 AM Joel Jacobson <joel@compiler.org> wrote:
On Tue, Feb 13, 2024, at 10:28, Laurenz Albe wrote:
> On Mon, 2024-02-12 at 12:24 -0500, Corey Huinker wrote:
>> > Do you plan to add it to the commitfest?  If yes, I'd set it "ready for committer".
>>
>> Commitfest entry reanimated.
>
> Truly... you created a revenant in the already closed commitfest.
>
> I closed that again and added a new entry in the open commitfest.
>
> Yours,
> Laurenz Albe

This thread reminded me of the old discussion "LIMIT for UPDATE and DELETE" from 2014 [1].

Back in 2014, it was considered a "fringe feature" by some. It is thought to be more commonplace today?

/Joel

[1] https://www.postgresql.org/message-id/flat/CADB9FDf-Vh6RnKAMZ4Rrg_YP9p3THdPbji8qe4qkxRuiOwm%3Dmg%40mail.gmail.com

This patch came out of a discussion at the last PgCon with the person who made the "fringe feature" quote, who seemed quite supportive of documenting the technique. The comment may have been in regards to actually implementing a LIMIT clause on UPDATE and DELETE, which isn't in the SQL standard and would be difficult to implement as the two statements have no concept of ordering. Documenting the workaround would alleviate some interest in implementing a nonstandard feature.

As for whether it's commonplace, when I was a consultant I had a number of customers that I had who bemoaned how large updates caused big replica lag, basically punishing access to records they did care about in order to properly archive or backfill records they don't care about. I used the technique a lot, putting the update/delete in a loop, and often running multiple copies of the same script at times when I/O contention was low, but if load levels rose it was trivial to just kill a few of the scripts until things calmed down.

В списке pgsql-hackers по дате отправления:

Предыдущее
От: vignesh C
Дата:
Сообщение: Re: Why is subscription/t/031_column_list.pl failing so much?
Следующее
От: "David E. Wheeler"
Дата:
Сообщение: Re: Patch: Add parse_type Function