Re: Problem while updating a foreign table pointing to a partitioned table on foreign server

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Etsuro Fujita <fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp>
Cc: Kyotaro HORIGUCHI <horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp>, ashutosh(dot)bapat(at)enterprisedb(dot)com, alvherre(at)2ndquadrant(dot)com, robertmhaas(at)gmail(dot)com, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Problem while updating a foreign table pointing to a partitioned table on foreign server
Date: 2018-11-16 18:35:15
Message-ID: 1590.1542393315@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers

Etsuro Fujita <fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp> writes:
> [ fix-foreign-modify-efujita-2.patch ]

Um ... wow, I do not like anything about this. Adding a "tableoid = X"
constraint to every remote update query seems awfully expensive,
considering that (a) it's useless for non-partitioned tables, and
(b) the remote planner will have exactly no intelligence about handling
it. We could improve (b) probably, but that'd be another big chunk of
work, and it wouldn't help when talking to older servers.

(Admittedly, I'm not sure I have a better idea. If we knew which
remote tables were partitioned, we could avoid sending unnecessary
tableoid constraints; but we don't have any good way to track that.)

I think the proposed hacks on the planner's Param handling are a
mess as well. You can't go and change the contents of a Param node
sometime after creating it --- that will for example break equal()
comparisons that might be done in between. (No, I don't buy that
you know exactly what will be done in between.) The cost of what
you've added to join tlist creation and setrefs processing seems
unduly high, too.

I wonder whether we'd be better off thinking of a way to let FDWs
invent additional system column IDs for their tables, so that
something like a remote table OID could be represented in the
natural way as a Var with negative varattno. This'd potentially
also be a win for FDWs whose underlying storage has a row identifier,
but it's not of type "tid". Instead of trying to shoehorn their
row ID into SelfItemPointerAttributeNumber, they could define a
new system column that has a more appropriate data type. Admittedly
there'd be some infrastructure work to do to make this happen, maybe
a lot of it; but it's a bullet we really need to bite at some point.

regards, tom lane

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2018-11-16 19:03:46 Re: Optimze usage of immutable functions as relation
Previous Message Melanie Plageman 2018-11-16 18:31:47 Re: BUG #15160: planner overestimates number of rows in join when there are more than 200 rows coming from CTE