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

From: Kyotaro HORIGUCHI <horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp>
To: ashutosh(dot)bapat(at)enterprisedb(dot)com
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Problem while updating a foreign table pointing to a partitioned table on foreign server
Date: 2018-04-19 06:08:13
Message-ID: 20180419.150813.05888429.horiguchi.kyotaro@lab.ntt.co.jp
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

At Wed, 18 Apr 2018 13:23:06 +0530, Ashutosh Bapat <ashutosh(dot)bapat(at)enterprisedb(dot)com> wrote in <CAFjFpReWZnJ_raxAroaxb3_uRVpxnrnh8w3BjKs0kgy0Ya2+kA(at)mail(dot)gmail(dot)com>
> On Wed, Apr 18, 2018 at 9:43 AM, Kyotaro HORIGUCHI
> <horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp> wrote:
> >
> > Anyway I think we should warn or error out if one nondirect
> > update touches two nor more tuples in the first place.
> >
> > =# UPDATE fplt SET b = (CASE WHEN random() <= 1 THEN 10 ELSE 20 END) WHERE a = 1;
> > ERROR: updated 2 rows for a tuple identity on the remote end
>
> I liked that idea. But I think your patch wasn't quite right, esp.
> when the returning had an SRF in it. Right now n_rows tracks the
> number of rows returned if there is a returning list or the number of
> rows updated/deleted on the foreign server. If there is an SRF, n_rows
> can return multiple rows for a single updated or deleted row. So, I
> changed your code to track number of rows updated/deleted and number
> of rows returned separately. BTW, your patch didn't handle DELETE
> case.

Yeah, sorry. It was to just show how the error looks
like. Attached 0002 works and looks fine except the following.

> /* No rows should be returned if no rows were updated. */
> Assert(n_rows_returned == 0 || n_rows_updated > 0);

The assertion is correct but I think that we shouldn't crash
server by any kind of protocol error. I think ERROR is suitable.

> I have attached a set of patches
> 0001 adds a test case showing the issue.
> 0002 modified patch based on your idea of throwing an error
> 0003 WIP patch with a partial fix for the issue as discussed upthread
>
> The expected output in 0001 is set to what it would when the problem
> gets fixed. The expected output in 0002 is what it would be when we
> commit only 0002 without a complete fix.
> >
> >
> >> There are two ways to fix this
> >> 1. Use WHERE CURRENT OF with cursors to update rows. This means that
> >> we fetch only one row at a time and update it. This can slow down the
> >> execution drastically.
> >> 2. Along with ctid use tableoid as a qualifier i.e. WHERE clause of
> >> UPDATE/DELETE statement has ctid = $1 AND tableoid = $2 as conditions.
> >>
> >> PFA patch along the lines of 2nd approach and along with the
> >> testcases. The idea is to inject tableoid attribute to be fetched from
> >> the foreign server similar to ctid and then add it to the DML
> >> statement being constructed.
> >>
> >> It does fix the problem. But the patch as is interferes with the way
> >> we handle tableoid currently. That can be seen from the regression
> >> diffs that the patch causes. RIght now, every tableoid reference gets
> >> converted into the tableoid of the foreign table (and not the tableoid
> >> of the foreign table). Somehow we need to differentiate between the
> >> tableoid injected for DML and tableoid references added by the user in
> >> the original query and then use tableoid on the foreign server for the
> >> first and local foreign table's oid for the second. Right now, I don't
> >> see a simple way to do that.
> >
> > We cannot add no non-system (junk) columns not defined in foreign
> > table columns.
>
> Why? That's a probable way of fixing this problem.

In other words, tuples returned from ForeignNext
(postgresIterateForeignScan) on a foreign (base) relation cannot
contain a non-system column which is not a part of the relation,
since its tuple descriptor doesn't know of and does error out it.
The current 0003 stores remote tableoid in tuples' existing
tableOid field (not a column data), which is not proper since
remote tableoid is bogus for the local server. I might missing
something here, though. If we can somehow attach an blob at the
end of t_data and it is finally passed to
ExecForeignUpdate/Delete, the problem would be resolved.

> > We could pass tableoid via a side channel but we
> > get wrong value if the scan is not consists of only one foreign
> > relation. I don't think adding remote_tableoid in HeapTupleData
> > is acceptable.
>
> I am thinking of adding remote_tableoid in HeapTupleData since not all
> FDWs will have the concept of tableoid. But we need to somehow
> distinguish the tableoid resjunk added for DMLs and tableoid requested
> by the user.

I don't think it is acceptable but (hopefully) almost solves this
problem if we allow that. User always sees the conventional
tableOid and all ExecForeignUpdate/Delete have to do is to use
remote_tableoid as a part of remote tuple identifier. Required to
consider how to propagate the remote_tableoid through joins or
other intermediate executor nodes, though. It is partly similar
to the way deciding join push down.

Another point is that, even though HeapTupleData is the only
expected coveyer of the tuple identification, assuming tableoid +
ctid is not adequite since FDW interface is not exlusive for
postgres_fdw. The existig ctid field is not added for the purpose
and just happened to (seem to) work as tuple identifier for
postgres_fdw but I think tableoid is not.

> > Explicity defining remote_tableoid column in
> > foreign relation might work but it makes things combersome..
> >
>
> Not just cumbersome, it's not going to be always right, if the things
> change on the foreign server e.g. OID of the table changes because it
> got dropped and recreated on the foreign server or OID remained same
> but the table got inherited and so on.

The same can be said on ctid. Maybe my description was
unclear. Specifically, I intended to say something like:

- If we want to update/delete remote partitioned/inhtance tables
without direct modify, the foreign relation must have a columns
defined as "tableoid as remote_tableoid" or something. (We
could change the column name by a fdw option.)

- ForeignScan for TableModify adds "remote_tableoid" instead of
tableoid to receive remote tableoid and returns it as a part of
a ordinary return tuple.

- ForeignUpdate/Delete sees the remote_tableoid instead of
tuple's tableOid field.

Yes, it is dreadfully bad interface, especially it is not
guaranteed to be passed to modify side if users don't write a
query to do so. So, yes, the far bad than cumbersome.

> I think we should try getting 0001 and 0002 at least committed
> independent of 0003.

Agreed on 0002. 0001 should be committed with 0003?

regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Michael Paquier 2018-04-19 06:10:42 Re: Is there a memory leak in commit 8561e48?
Previous Message Teodor Sigaev 2018-04-19 05:47:22 Re: WIP: Covering + unique indexes.