Re: making update/delete of inheritance trees scale better

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Amit Langote <amitlangote09(at)gmail(dot)com>
Cc: Robert Haas <robertmhaas(at)gmail(dot)com>, Heikki Linnakangas <hlinnaka(at)iki(dot)fi>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Re: making update/delete of inheritance trees scale better
Date: 2021-03-27 18:11:13
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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.

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.

regards, tom lane

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message 2021-03-27 19:28:12 Re: libpq debug log
Previous Message Alvaro Herrera 2021-03-27 18:09:13 Re: WIP: BRIN multi-range indexes