From: | Amit Langote <amitlangote09(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
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-29 11:49:28 |
Message-ID: | CA+HiwqEm-0viOd7aCz9yzZ_dsa6odSwzYq70bWMV8fWnwZA51A@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | vignesh C | 2021-03-29 12:00:41 | Re: [PATCH] Provide more information to filter_prepare |
Previous Message | wenjing | 2021-03-29 11:44:54 | Re: [Proposal] Global temporary tables |