Re: [PATCH] Add UPDATE WHERE OFFSET IN clause

2022. 02. 08. 2:05 keltezéssel, Tom Lane írta:
> Chapman Flack <chap(at)anastigmatix(dot)net> writes:
>> On 02/07/22 00:59, Böszörményi Zoltán wrote:
>>> UPDATE ... WHERE OFFSET n IN cursor;
>
>> If added to UPDATE, should this be added to DELETE also?

Yes, it should be added, too.

> FWIW, I think this is a really horrid hack.

Thanks for your kind words. :-D

> For one thing, it's not
> robust against not-strictly-linear FETCH/MOVE of the cursor. It looks
> to me like "OFFSET n" really means "the row that we read N reads ago",
> not "the row N before the current cursor position". I see that the
> documentation does explain it that way, but it fails to account for
> optimizations such as whether we implement moves by reading backwards
> or rewind-and-read-forwards. I don't think we want to expose that
> sort of implementation detail.
>
> I'm also pretty displeased with causing unbounded memory consumption for
> every use of nodeLockRows, whether it has anything to do with a cursor or
> not (never mind whether the cursor will ever be used for WHERE OFFSET IN).
> Yeah, it's only a few bytes per row, but that will add up in queries that
> process lots of rows.

Does PostgreSQL have SQL hints now? I.e. some kind of "pragma"
parsed from SQL comments to indicate the subsequent usage pattern?
Such a hint would allow using either storing the single row information
for DELETE/UPDATE or the list.

Dumping the list to a disk file will be added later so memory
usage is not unbounded.

I was just testing the waters for the idea.

Best regards,
Zoltán Böszörményi

>
> regards, tom lane