Re: SQL:2011 application time

On 10/24/24 17:22, Paul Jungwirth wrote:
> On 10/21/24 14:46, Paul Jungwirth wrote:
>> Here is a new set of patches, including new patches to (1) fix logical replication with WITHOUT
>> OVERLAPS indexes and (2) address some documentation lapses pointed out in jian he's feedback.
>> Since all that is against the already-commited PK/UNIQUE/FK work, I've kept them separate here
>> from the FOR PORTION OF etc patches. I've also added the logical replication problem to the v18
>> Open Items wiki page.
>
> New patches attached to fix some conflicts. I don't think there is anything else except cleaning up
> some sloppy white space.

Hi Hackers,

Here are some more updates. I think everything is ready except for the final patch adding PERIODs,
which still needs a little more work.

The biggest change here is a new patch to expose FOR PORTION OF details to plpgsql triggers via the
new TG_PERIOD_NAME and TG_PERIOD_BOUNDS variables. These were already available to C functions (and
we use them in the RI triggers), but now people will be able to access them via plpgsql as well.
One thing I don't love is that TG_PERIOD_BOUNDS is a string, because it needs to have a type at the
time you create the function, and the type of the FOR PORTION OF column could be anything:
daterange, tsrange, inetrange, textmultirange, whatever. If anyone has a better idea, I'm open to
suggestions. This patch is not really essential, but I thought authors of plpgsql triggers might
like to have it.

I also started working on allowing FOR PORTION OF in FDW updates/deletes (not included here). As far
as I can see, nothing needs to change about the API, because the FOR PORTION OF details are simply
passed as part of the node tree. We just have to remove the ereport that says FDW FOR PORTION OF is
unsupported. Then within each FDW you have to use the new FOR PORTION OF node to do the right thing
on the remote table. So I'm updating postgres_fdw to do that.

But beyond the *technical* need, do we need something to clue in FDW developers that these queries
are a possibility? With no changes to postgres_fdw, a FOR PORTION OF update/delete gets executed; it
just loses the FOR PORTION OF meaning. So we update/delete too much. That seems dangerous. It makes
me *want* to change the API, so that developers must address the possibility. But I don't want to
make things cluttered either. Any thoughts?

Rebased to fb7e27abfb.

Yours,

--
Paul ~{:-)
pj(at)illuminatedcomputing(dot)com