| From: | Chao Li <li(dot)evan(dot)chao(at)gmail(dot)com> |
|---|---|
| To: | PostgreSQL-development <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
| Cc: | Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com>, Paul A Jungwirth <pj(at)illuminatedcomputing(dot)com> |
| Subject: | Fix RETURNING side effects for FOR PORTION OF leftover rows |
| Date: | 2026-07-16 06:19:31 |
| Message-ID: | 07C125E5-F6ED-460C-A394-E6503DAE18FB@gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
Hi,
Dean raised a comment on my patch [1] about RETURNING processing for UPDATE ... FOR PORTION OF. While addressing that comment, I found a new problem: UPDATE/DELETE ... FOR PORTION OF currently processes RETURNING for leftover rows as well, which seems wrong because it can cause unexpected side effects.
Here is a simple repro using a sequence:
```
evantest=# create sequence s;
CREATE SEQUENCE
evantest=# create table t (r int4range, val int);
CREATE TABLE
evantest=# insert into t values ('[1,4)', 0);
INSERT 0 1
evantest=# update t for portion of r from 2 to 3 set val=1 returning nextval('s') as n;
n
---
3
(1 row)
UPDATE 1
evantest=# select last_value from s;
last_value
------------
3
(1 row)
```
The UPDATE FOR PORTION OF command generates two leftover rows, and both cause RETURNING to be processed. As a result, the sequence is advanced three times, which seems unexpected from the user's perspective.
To fix the problem, since leftover rows are inserted by calling ExecInsert(), and ExecInsert() is a local static function, I think a simple and safe solution is to add a new parameter to ExecInsert() to indicate whether to process RETURNING for this insert. Then UPDATE/DELETE FOR PORTION OF can bypass RETURNING processing for leftover rows.
See the attached patch for details.
[1] https://postgr.es/m/6C34A987-AC50-4477-BD71-2D4AFEE1A589@gmail.com
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
| Attachment | Content-Type | Size |
|---|---|---|
| v1-0001-Avoid-RETURNING-side-effects-for-FOR-PORTION-OF-l.patch | application/octet-stream | 7.9 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Zhijie Hou (Fujitsu) | 2026-07-16 06:31:05 | RE: walsummarizer can get stuck when switching timelines |
| Previous Message | shveta malik | 2026-07-16 06:15:13 | Re: Support EXCEPT for TABLES IN SCHEMA publications |