Обсуждение: Re: Proposal to allow DELETE/UPDATE on partitioned tables with unsupported foreign partitions

Поиск
Список
Период
Сортировка

Re: Proposal to allow DELETE/UPDATE on partitioned tables with unsupported foreign partitions

От
Etsuro Fujita
Дата:
On Tue, Jul 1, 2025 at 2:24 AM Shirisha Shirisha
<shirisha.sn@broadcom.com> wrote:
> Just wanted to follow up on the patch, any additional feedback or improvement in the patch would be very helpful.

Did you add the patch to the CommitFest App?

https://commitfest.postgresql.org

If not, I will recommend that to get more feedback from developers.

Best regards,
Etsuro Fujita

Re: Proposal to allow DELETE/UPDATE on partitioned tables with unsupported foreign partitions

От
Shirisha Shirisha
Дата:
> Did you add the patch to the CommitFest App?

Yes, I had attempted to register the patch earlier but had to wait for the cool-off period to pass for my newly created account.

I’ve now successfully registered the patch on the CommitFest app
https://commitfest.postgresql.org/patch/5901

Thanks and Regards,
Shirisha
Broadcom Inc.

On Sun, Jul 6, 2025 at 2:34 PM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
On Tue, Jul 1, 2025 at 2:24 AM Shirisha Shirisha
<shirisha.sn@broadcom.com> wrote:
> Just wanted to follow up on the patch, any additional feedback or improvement in the patch would be very helpful.

Did you add the patch to the CommitFest App?

https://commitfest.postgresql.org

If not, I will recommend that to get more feedback from developers.

Best regards,
Etsuro Fujita

This electronic communication and the information and any files transmitted with it, or attached to it, are confidential and are intended solely for the use of the individual or entity to whom it is addressed and may contain information that is confidential, legally privileged, protected by privacy laws, or otherwise restricted from disclosure to anyone else. If you are not the intended recipient or the person responsible for delivering the e-mail to the intended recipient, you are hereby notified that any use, copying, distributing, dissemination, forwarding, printing, or copying of this e-mail is strictly prohibited. If you received this e-mail in error, please return the e-mail to the sender, delete it from your computer, and destroy any printed copy of it.

Re: Proposal to allow DELETE/UPDATE on partitioned tables with unsupported foreign partitions

От
Etsuro Fujita
Дата:
On Mon, Jul 7, 2025 at 6:46 PM Shirisha Shirisha
<shirisha.sn@broadcom.com> wrote:
> > Did you add the patch to the CommitFest App?
>
> Yes, I had attempted to register the patch earlier but had to wait for the cool-off period to pass for my newly
createdaccount. 
>
> I’ve now successfully registered the patch on the CommitFest app
> https://commitfest.postgresql.org/patch/5901

Thanks for that.  I tested/reviewed the patch quickly.

The patch does not work well with table inheritance:

create table pt (a text, b int);
insert into pt values ('AAA', 42);
create foreign table ft (a text, b int) server file_server OPTIONS
(filename 'path-to-file', format 'csv', delimiter ',');
select * from ft;
  a  | b
-----+----
 BBB | 42
(1 row)

alter foreign table ft inherit pt;
update pt set b = b + 1000 where a = 'AAA';
ERROR:  cannot update foreign table "ft"

Why doesn't the patch cover this case?

--- a/src/test/regress/sql/partition_info.sql
+++ b/src/test/regress/sql/partition_info.sql
@@ -127,3 +127,31 @@ SELECT pg_partition_root('ptif_li_child');
 DROP VIEW ptif_test_view;
 DROP MATERIALIZED VIEW ptif_test_matview;
 DROP TABLE ptif_li_parent, ptif_li_child;
+
+-- Test UPDATE/DELETE on partition table with foreign partitions
+\getenv abs_srcdir PG_ABS_SRCDIR
+CREATE EXTENSION file_fdw;
+CREATE SERVER file_server FOREIGN DATA WRAPPER file_fdw;

I think the regression tests should be moved to file_fdw.

That is it.  I will do the rest of the review in Commitfest PG19-2 (as
this was registered for it).

Best regards,
Etsuro Fujita

Re: Proposal to allow DELETE/UPDATE on partitioned tables with unsupported foreign partitions

От
Shirisha Shirisha
Дата:
Thanks Etsuro for the review comments. 
I've addressed all of them in this updated patch:

- Rebased the patch on the latest changes
- Added support for inherited foreign tables
- Moved the regression test to file_fdw
- Added test for inherited foreign tables

Please find the updated patch attached.
CommitFest link - https://commitfest.postgresql.org/patch/5901

Thanks and Regards,
Shirisha
Broadcom Inc.


On Wed, Jul 9, 2025 at 5:06 PM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
On Mon, Jul 7, 2025 at 6:46 PM Shirisha Shirisha
<shirisha.sn@broadcom.com> wrote:
> > Did you add the patch to the CommitFest App?
>
> Yes, I had attempted to register the patch earlier but had to wait for the cool-off period to pass for my newly created account.
>
> I’ve now successfully registered the patch on the CommitFest app
> https://commitfest.postgresql.org/patch/5901

Thanks for that.  I tested/reviewed the patch quickly.

The patch does not work well with table inheritance:

create table pt (a text, b int);
insert into pt values ('AAA', 42);
create foreign table ft (a text, b int) server file_server OPTIONS
(filename 'path-to-file', format 'csv', delimiter ',');
select * from ft;
  a  | b
-----+----
 BBB | 42
(1 row)

alter foreign table ft inherit pt;
update pt set b = b + 1000 where a = 'AAA';
ERROR:  cannot update foreign table "ft"

Why doesn't the patch cover this case?

--- a/src/test/regress/sql/partition_info.sql
+++ b/src/test/regress/sql/partition_info.sql
@@ -127,3 +127,31 @@ SELECT pg_partition_root('ptif_li_child');
 DROP VIEW ptif_test_view;
 DROP MATERIALIZED VIEW ptif_test_matview;
 DROP TABLE ptif_li_parent, ptif_li_child;
+
+-- Test UPDATE/DELETE on partition table with foreign partitions
+\getenv abs_srcdir PG_ABS_SRCDIR
+CREATE EXTENSION file_fdw;
+CREATE SERVER file_server FOREIGN DATA WRAPPER file_fdw;

I think the regression tests should be moved to file_fdw.

That is it.  I will do the rest of the review in Commitfest PG19-2 (as
this was registered for it).

Best regards,
Etsuro Fujita
Вложения

Re: Proposal to allow DELETE/UPDATE on partitioned tables with unsupported foreign partitions

От
Etsuro Fujita
Дата:
On Wed, Sep 17, 2025 at 1:38 AM Shirisha Shirisha
<shirisha.sn@broadcom.com> wrote:
> I've addressed all of them in this updated patch:
>
> - Rebased the patch on the latest changes
> - Added support for inherited foreign tables
> - Moved the regression test to file_fdw
> - Added test for inherited foreign tables
>
> Please find the updated patch attached.
> CommitFest link - https://commitfest.postgresql.org/patch/5901

Will review.

Thanks for updating the patch!

Best regards,
Etsuro Fujita