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 |
Message-ID: | 8353063d-bbf5-8203-e913-3b07608e6405@pr.hu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
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
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 | kuroda.hayato@fujitsu.com | 2022-02-15 12:05:40 | RE: [Proposal] Add foreign-server health checks infrastructure |