From: Böszörményi Zoltán <zboszor(at)pr(dot)hu>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Chapman Flack <chap(at)anastigmatix(dot)net>
Cc: pgsql-hackers(at)lists(dot)postgresql(dot)org, postgres(at)cybertec(dot)at
Subject: Re: [PATCH] Add UPDATE WHERE OFFSET IN clause
Date: 2022-02-15 12:12:23
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Nitin Jadhav 2022-02-15 12:15:26 Re: Report checkpoint progress with pg_stat_progress_checkpoint (was: Report checkpoint progress in server logs)
Previous Message 2022-02-15 12:05:40 RE: [Proposal] Add foreign-server health checks infrastructure