|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>|
|Subject:||Re: [PATCH] Add UPDATE WHERE OFFSET IN clause|
|Views:||Raw Message | Whole Thread | Download mbox | Resend email|
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.
> regards, tom lane
|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 Messageemail@example.com||2022-02-15 12:05:40||RE: [Proposal] Add foreign-server health checks infrastructure|