2022. 02. 08. 2:05 keltezéssel, Tom Lane írta:
> Chapman Flack <chap@anastigmatix.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
