Re: making update/delete of inheritance trees scale better

On Sun, Mar 28, 2021 at 3:11 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> I wrote:
> > ... which is what forced you to remove or lobotomize several regression
> > test cases. Now admittedly, that just moves the state of play for
> > cross-partition updates into postgres_fdw partitions from "works
> > sometimes" to "works never". But I don't like the idea that we'll
> > be taking away actual functionality.
> > I have a blue-sky idea for fixing that properly, which is to disable FDW
> > direct updates when there is a possibility of a cross-partition update,
> > instead doing it the old way with a remote cursor reading the source rows
> > for later UPDATEs.
>
> After further poking at this, I realize that there is an independent reason
> why a direct FDW update is unsuitable in a partitioned UPDATE: it fails to
> cope with cases where a row needs to be moved *out* of a remote table.
> (If you were smart and put a CHECK constraint equivalent to the partition
> constraint on the remote table, you'll get a CHECK failure. If you did
> not do that, you just silently get the wrong behavior, with the row
> updated where it is and thus no longer accessible via the partitioned
> table.) Again, backing off trying to use a direct update seems like
> the right route to a fix.

Agreed.

> So the short answer here is that postgres_fdw is about 75% broken for
> cross-partition updates anyway, so making it 100% broken isn't giving
> up as much as I thought. Accordingly, I'm not going to consider that
> issue to be a blocker for this patch. Still, if anybody wants to
> work on un-breaking it, that'd be great.

Okay, I will give that a try once we're done with the main patch.

BTW, I had forgotten to update the description in postgres-fdw.sgml of
the current limitation, which is as follows:

===
Note also that postgres_fdw supports row movement invoked by UPDATE
statements executed on partitioned tables, but it currently does not
handle the case where a remote partition chosen to insert a moved row
into is also an UPDATE target partition that will be updated later.
===

I think we will need to take out the "...table will be updated later"
part at the end of the sentence.

--
Amit Langote
EDB: http://www.enterprisedb.com