On Sun, Mar 28, 2021 at 3:11 AM Tom Lane <t...@sss.pgh.pa.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
