Re: Problem with transition tables on partitioned tables with foreign-table partitions

From: Amit Langote <amitlangote09(at)gmail(dot)com>
To: Etsuro Fujita <etsuro(dot)fujita(at)gmail(dot)com>
Cc: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Re: Problem with transition tables on partitioned tables with foreign-table partitions
Date: 2025-07-10 14:54:41
Message-ID: CA+HiwqE-QPhjPApLH5nLhDZP2Zkfpi-q72ycvyDZFawJfLF-zg@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On Thu, Jul 10, 2025 at 22:20 Amit Langote <amitlangote09(at)gmail(dot)com> wrote:

> Fujita-san,
>
> On Wed, Jul 9, 2025 at 5:07 PM Etsuro Fujita <etsuro(dot)fujita(at)gmail(dot)com>
> wrote:
> > On Tue, Jul 1, 2025 at 11:55 AM Etsuro Fujita <etsuro(dot)fujita(at)gmail(dot)com>
> wrote:
> > > So I would
> > > like to propose to fix this by the following: 1) disable using direct
> > > modify to modify foreign-table partitions if there are any
> > > transition-table triggers on the partitioned table, and then 2) throw
> > > an error in
> ExecARInsertTriggers()/ExecARUpdateTriggers()/ExecARDeleteTriggers()
> > > if they collects transition tuple(s) from a foreign-table partition.
> > >
> > > Attached is a WIP patch for that.
> >
> > Here is an updated version of the patch, in which I added 1) an Assert
> > to ExecAR* functions to ensure that the passed-in ResultRelInfo
> > pointer is not NULL, 2) added/tweaked comments a bit more, and 3)
> > added regression tests.
>
> Thanks for the new patch. LGTM.
>
> While reading it, I noticed that the functions performing table_open()
> are repeatedly called in this condition, which runs for every
> qualifying foreign child relations:
>
> if (fdwroutine != NULL &&
> fdwroutine->PlanDirectModify != NULL &&
> fdwroutine->BeginDirectModify != NULL &&
> fdwroutine->IterateDirectModify != NULL &&
> fdwroutine->EndDirectModify != NULL &&
> withCheckOptionLists == NIL &&
> !has_row_triggers(root, rti, operation) &&
> !has_stored_generated_columns(root, rti))
>
> That seems a bit expensive. It might be worth using *_valid flags to
> avoid redundant table_open() calls like you're doing for transition
> table checking. Maybe something to consider in a separate patch.

Ah, scratch that because I missed that transition table checking is done
for the “named” relation and these are checking it for child relations.

- Amit

>

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Tomas Vondra 2025-07-10 15:20:50 Re: Adding basic NUMA awareness - Preliminary feedback and outline for an extensible approach
Previous Message ivan.kush 2025-07-10 14:41:07 Re: [PoC] Federated Authn/z with OAUTHBEARER