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

On Tue, Nov 26, 2019 at 12:37 PM Etsuro Fujita wrote: > I was planning to work on this in this commitfest, but sorry, I didn't > have time due to other priorities. Probably, I won't have time for > this in the development cycle for v13. So I'll mark this as RWF, > unless anyone wants to work on

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

Hi Michael-san, On Mon, Nov 25, 2019 at 4:13 PM Michael Paquier wrote: > On Tue, Sep 03, 2019 at 12:37:52AM +0900, Etsuro Fujita wrote: > > On Wed, Aug 14, 2019 at 11:51 AM Etsuro Fujita > > wrote: > >> This is my TODO item for PG13, but I'll give priority to other things > >> in the next

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

On Tue, Sep 03, 2019 at 12:37:52AM +0900, Etsuro Fujita wrote: > On Wed, Aug 14, 2019 at 11:51 AM Etsuro Fujita > wrote: >> This is my TODO item for PG13, but I'll give priority to other things >> in the next commitfest. If anyone wants to work on it, feel free; >> else I'll move this to the

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

On Wed, Aug 14, 2019 at 11:51 AM Etsuro Fujita wrote: > This is my TODO item for PG13, but I'll give priority to other things > in the next commitfest. If anyone wants to work on it, feel free; > else I'll move this to the November commitfest when it opens. Moved. Best regards, Etsuro Fujita

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

Hi Alvaro and Michael, On Tue, Aug 13, 2019 at 11:04 PM Alvaro Herrera wrote: > On 2019-Aug-13, Michael Paquier wrote: > > On Mon, Aug 12, 2019 at 05:32:08PM -0400, Alvaro Herrera wrote: > > > So do we have an updated patch for this? It's been a while since this > > > patch saw any movement ...

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

On 2019-Aug-13, Michael Paquier wrote: > On Mon, Aug 12, 2019 at 05:32:08PM -0400, Alvaro Herrera wrote: > > So do we have an updated patch for this? It's been a while since this > > patch saw any movement ... > > Please note that this involves a couple of people in Japan, and this > week is

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

On Mon, Aug 12, 2019 at 05:32:08PM -0400, Alvaro Herrera wrote: > So do we have an updated patch for this? It's been a while since this > patch saw any movement ... Please note that this involves a couple of people in Japan, and this week is the Obon vacation season for a lot of people. So

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

On 2018-Nov-16, Tom Lane wrote: > Etsuro Fujita 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

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

(2019/02/08 10:09), Michael Paquier wrote: On Thu, Feb 07, 2019 at 09:55:18PM +0900, Etsuro Fujita wrote: I 100% agree with Tom, and actually, I tried to address his comments, but I haven't come up with a clear solution for that yet. I really want to address this, but I won't have much time to

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

On Thu, Feb 07, 2019 at 09:55:18PM +0900, Etsuro Fujita wrote: > I 100% agree with Tom, and actually, I tried to address his comments, but I > haven't come up with a clear solution for that yet. I really want to > address this, but I won't have much time to work on that at least until > after

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

(2019/02/02 10:21), Michael Paquier wrote: On Fri, Nov 16, 2018 at 01:35:15PM -0500, Tom Lane wrote: 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

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

On Fri, Nov 16, 2018 at 01:35:15PM -0500, Tom Lane wrote: > 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

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

Etsuro Fujita 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

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

(2018/10/02 21:16), Etsuro Fujita wrote: Attached is an updated version of the patch. Changes: That patch conflicts the recent executor changes, so I'm attaching a rebased patch, in which I also added a fast path to add_params_to_result_rel and did some comment editing for consistency.

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

(2018/09/21 20:03), Etsuro Fujita wrote: (2018/09/18 21:14), Kyotaro HORIGUCHI wrote: At Fri, 14 Sep 2018 22:01:39 +0900, Etsuro Fujita wrote in<5b9bb133.1060...@lab.ntt.co.jp> I wrote a patch using the Param-based approach, and compared the two approaches. I don't think there would be any

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

(2018/09/18 21:14), Kyotaro HORIGUCHI wrote: At Fri, 14 Sep 2018 22:01:39 +0900, Etsuro Fujita wrote in<5b9bb133.1060...@lab.ntt.co.jp> @@ -126,8 +173,18 @@ get_relation_info(PlannerInfo *root, Oid relationObjectId,\ bool inhparent, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),

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

Hello. At Fri, 14 Sep 2018 22:01:39 +0900, Etsuro Fujita wrote in <5b9bb133.1060...@lab.ntt.co.jp> > (2018/08/24 16:58), Kyotaro HORIGUCHI wrote: > > At Tue, 21 Aug 2018 11:01:32 +0900 (Tokyo Standard Time), Kyotaro > > HORIGUCHI wrote > >

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

(2018/08/24 16:58), Kyotaro HORIGUCHI wrote: At Tue, 21 Aug 2018 11:01:32 +0900 (Tokyo Standard Time), Kyotaro HORIGUCHI wrote in<20180821.110132.261184472.horiguchi.kyot...@lab.ntt.co.jp> You wrote: Several places seems to be assuming that fdw_scan_tlist may be used foreign scan on

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

Hello. At Wed, 05 Sep 2018 20:02:04 +0900, Etsuro Fujita wrote in <5b8fb7ac.5020...@lab.ntt.co.jp> > (2018/08/30 21:58), Etsuro Fujita wrote: > > (2018/08/30 20:37), Kyotaro HORIGUCHI wrote: > >> At Fri, 24 Aug 2018 21:45:35 +0900, Etsuro > >> Fujita wrote > >>

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

(2018/08/30 21:58), Etsuro Fujita wrote: (2018/08/30 20:37), Kyotaro HORIGUCHI wrote: At Fri, 24 Aug 2018 21:45:35 +0900, Etsuro Fujita wrote in<5b7ffdef.6020...@lab.ntt.co.jp> (2018/08/21 11:01), Kyotaro HORIGUCHI wrote: At Tue, 14 Aug 2018 20:49:02 +0900, Etsuro Fujita wrote

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

(2018/08/30 20:37), Kyotaro HORIGUCHI wrote: At Fri, 24 Aug 2018 21:45:35 +0900, Etsuro Fujita wrote in<5b7ffdef.6020...@lab.ntt.co.jp> (2018/08/21 11:01), Kyotaro HORIGUCHI wrote: At Tue, 14 Aug 2018 20:49:02 +0900, Etsuro Fujita wrote in<5b72c1ae.8010...@lab.ntt.co.jp> (2018/08/09

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

Hello. At Fri, 24 Aug 2018 21:45:35 +0900, Etsuro Fujita wrote in <5b7ffdef.6020...@lab.ntt.co.jp> > (2018/08/21 11:01), Kyotaro HORIGUCHI wrote: > > At Tue, 14 Aug 2018 20:49:02 +0900, Etsuro > > Fujita wrote > > in<5b72c1ae.8010...@lab.ntt.co.jp> > >> (2018/08/09 22:04), Etsuro Fujita wrote:

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

(2018/08/21 11:01), Kyotaro HORIGUCHI wrote: At Tue, 14 Aug 2018 20:49:02 +0900, Etsuro Fujita wrote in<5b72c1ae.8010...@lab.ntt.co.jp> (2018/08/09 22:04), Etsuro Fujita wrote: (2018/08/08 17:30), Kyotaro HORIGUCHI wrote: I spent more time looking at the patch. ISTM that the patch well

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

Sorry, I sent older version, which is logically same but contains some whitespace problems. I resend only 0003 by this mail. At Fri, 24 Aug 2018 16:51:31 +0900 (Tokyo Standard Time), Kyotaro HORIGUCHI wrote in <20180824.165131.45788857.horiguchi.kyot...@lab.ntt.co.jp> > Hello. > > At Tue, 21

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

Hello. At Tue, 21 Aug 2018 11:01:32 +0900 (Tokyo Standard Time), Kyotaro HORIGUCHI wrote in <20180821.110132.261184472.horiguchi.kyot...@lab.ntt.co.jp> > > You wrote: > > >Several places seems to be assuming that fdw_scan_tlist may be > > >used foreign scan on simple relation but I

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

Fujita-san thank you for the comment. At Tue, 14 Aug 2018 20:49:02 +0900, Etsuro Fujita wrote in <5b72c1ae.8010...@lab.ntt.co.jp> > (2018/08/09 22:04), Etsuro Fujita wrote: > > (2018/08/08 17:30), Kyotaro HORIGUCHI wrote: > >> I choosed to expand tuple descriptor for junk column added to > >>

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

(2018/08/09 22:04), Etsuro Fujita wrote: (2018/08/08 17:30), Kyotaro HORIGUCHI wrote: I choosed to expand tuple descriptor for junk column added to foreign relaions. We might be better to have new member in ForeignScan but I didn't so that we can backpatch it. I've not looked at the patch

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

(2018/08/08 17:30), Kyotaro HORIGUCHI wrote: Please find the attached. Thanks for the patch, Horiguchi-san! At Fri, 3 Aug 2018 11:48:38 +0530, Ashutosh Bapat wrote in The purpose of non-Var node is to avoid adding the attribute to relation descriptor. Idea is to create a new node, which

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

Hello. Please find the attached. At Fri, 3 Aug 2018 11:48:38 +0530, Ashutosh Bapat wrote in > The purpose of non-Var node is to avoid adding the attribute to > relation descriptor. Idea is to create a new node, which will act as a > place holder for table oid or row id (whatever) to be

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

On Fri, Aug 3, 2018 at 9:43 AM, Kyotaro HORIGUCHI wrote: > > Something-like-but-other-hanVar node? I'm not sure it is needed, > because whatever node we add to the relation-tlist, we must add > the correspondence to the relation descriptor. And if we do that, > a Var works to point it. (Am I

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

Hello. At Tue, 26 Jun 2018 10:19:45 +0530, Ashutosh Bapat wrote in > On Tue, Jun 26, 2018 at 9:59 AM, Kyotaro HORIGUCHI > wrote: > > > >> But good thing is because of join and aggregate > >> push-down we already have ability to push arbitrary kinds of nodes > >> down to the foreign server

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

Hello, thank you for the comment. At Wed, 01 Aug 2018 21:21:57 +0900, Etsuro Fujita wrote in <5b61a5e5.6010...@lab.ntt.co.jp> > (2018/06/12 12:19), Kyotaro HORIGUCHI wrote: > > I have demonstrated and actually shown a problem of the > > PARAM_EXEC case. > > > A. Just detecting and

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

(2018/06/12 12:19), Kyotaro HORIGUCHI wrote: I have demonstrated and actually shown a problem of the PARAM_EXEC case. A. Just detecting and reporting/erroring the problematic case. B. Giving to Sort-like nodes an ability to convert PARAMS into junk columns. C. Adding a space for 64bit

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

On Tue, Jun 26, 2018 at 9:59 AM, Kyotaro HORIGUCHI wrote: > >> But good thing is because of join and aggregate >> push-down we already have ability to push arbitrary kinds of nodes >> down to the foreign server through the targetlist. We should be able >> to leverage that capability. It looks

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

Hello. At Fri, 15 Jun 2018 11:19:21 +0530, Ashutosh Bapat wrote in > On Tue, Jun 12, 2018 at 8:49 AM, Kyotaro HORIGUCHI > wrote: > > Thanks for the discussion. > > > > At Thu, 7 Jun 2018 19:16:57 +0530, Ashutosh Bapat > > wrote in > > > >> What's the problem that you faced? > > > > The

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

On Tue, Jun 12, 2018 at 8:49 AM, Kyotaro HORIGUCHI wrote: > Thanks for the discussion. > > At Thu, 7 Jun 2018 19:16:57 +0530, Ashutosh Bapat > wrote in > >> On Tue, Jun 5, 2018 at 3:40 PM, Kyotaro HORIGUCHI >> wrote: >> > Hello. >> > >> > At Mon, 04 Jun 2018 20:58:28 +0900 (Tokyo Standard

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

Thanks for the discussion. At Thu, 7 Jun 2018 19:16:57 +0530, Ashutosh Bapat wrote in > On Tue, Jun 5, 2018 at 3:40 PM, Kyotaro HORIGUCHI > wrote: > > Hello. > > > > At Mon, 04 Jun 2018 20:58:28 +0900 (Tokyo Standard Time), Kyotaro HORIGUCHI > > wrote in > >

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

On Tue, Jun 5, 2018 at 3:40 PM, Kyotaro HORIGUCHI wrote: > Hello. > > At Mon, 04 Jun 2018 20:58:28 +0900 (Tokyo Standard Time), Kyotaro HORIGUCHI > wrote in > <20180604.205828.208262556.horiguchi.kyot...@lab.ntt.co.jp> >> It fails on some join-pushdown cases since it doesn't add tid >> columns

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

Hello. At Mon, 04 Jun 2018 20:58:28 +0900 (Tokyo Standard Time), Kyotaro HORIGUCHI wrote in <20180604.205828.208262556.horiguchi.kyot...@lab.ntt.co.jp> > It fails on some join-pushdown cases since it doesn't add tid > columns to join tlist. I suppose that build_tlist_to_deparse > needs

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

At Mon, 04 Jun 2018 20:58:28 +0900 (Tokyo Standard Time), Kyotaro HORIGUCHI wrote in <20180604.205828.208262556.horiguchi.kyot...@lab.ntt.co.jp> > Hello. > > At Fri, 1 Jun 2018 10:21:39 -0400, Ashutosh Bapat > wrote in > > > I am not suggesting to commit 0003 in my patch set, but just 0001

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

Hello. At Fri, 1 Jun 2018 10:21:39 -0400, Ashutosh Bapat wrote in > I am not suggesting to commit 0003 in my patch set, but just 0001 and > 0002 which just raise an error when multiple rows get updated when > only one row is expected to be updated. I reconsidered Tom's suggestion and found a

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

On Fri, Jun 1, 2018 at 7:43 AM, Kyotaro HORIGUCHI wrote: > On Thu, May 31, 2018 at 11:34 AM, Ashutosh Bapat > wrote: >> On Thu, May 31, 2018 at 7:36 PM, Tom Lane wrote: >>> What you want for the first part of that is basically like >>> generate_new_param() in subselect.c. We don't expose that

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

On Thu, May 31, 2018 at 11:34 AM, Ashutosh Bapat wrote: > On Thu, May 31, 2018 at 7:36 PM, Tom Lane wrote: >> What you want for the first part of that is basically like >> generate_new_param() in subselect.c. We don't expose that publicly >> at the moment, but we could, or maybe better to

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

On Thu, May 31, 2018 at 7:36 PM, Tom Lane wrote: > Kyotaro HORIGUCHI writes: >> If my understanding about non-system junk columns in a base relation >> and identifiers of a foreign tuples are correct, what is needed here >> is giving base relations the ability to have such junk column. > > The

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

Kyotaro HORIGUCHI writes: > If my understanding about non-system junk columns in a base relation > and identifiers of a foreign tuples are correct, what is needed here > is giving base relations the ability to have such junk column. The core of the problem, I think, is the question of exactly

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

Thanks. > I don't think this thread has reached a consensus on a design for a fix Right. If my understanding about non-system junk columns in a base relation and identifiers of a foreign tuples are correct, what is needed here is giving base relations the ability to have such junk column. I'm

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

Hello I don't think this thread has reached a consensus on a design for a fix, has it? Does anybody have a clear idea on a path forward? Is anybody working on a patch? Thanks -- Álvaro Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA,

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

At Fri, 18 May 2018 15:31:07 -0400, Robert Haas wrote in > On Fri, May 18, 2018 at 4:29 AM, Kyotaro HORIGUCHI > wrote: > > I have reached to the same thought. > > > > The

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

On Fri, May 18, 2018 at 4:29 AM, Kyotaro HORIGUCHI wrote: > I have reached to the same thought. > > The point here is that it is a base relation, which is not > assumed to have additional columns not in its definition, > including nonsystem junk columns. I'm not

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

At Fri, 18 May 2018 10:19:30 +0530, Ashutosh Bapat wrote in ashutosh.bapat> On Thu, May 17, 2018 at 11:56 PM, Robert Haas wrote: > > On Thu, May 17, 2018 at 2:10 AM,

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

On Thu, May 17, 2018 at 11:56 PM, Robert Haas wrote: > On Thu, May 17, 2018 at 2:10 AM, Ashutosh Bapat > wrote: >> The second would mean that SELECT * from foreign table reports >> remotetableoid as well, which is awkward. > > No it

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

Alvaro Herrera writes: > Can we just add a new junk attr, with its own fixed system column > number? I think that's what Robert was proposing. Junk attr yes, "fixed system column number" no. That's not how junk attrs work. What it'd need is a convention for the name

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

Robert Haas writes: > Yeah, but I'm not sure I like that solution very much. I don't think > abusing the tableoid to store a remote table OID is very nice. I'd say it's totally unacceptable. Tableoid *has to* be something that you can look up in the local pg_class

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

On Thu, May 17, 2018 at 2:10 AM, Ashutosh Bapat wrote: > The second would mean that SELECT * from foreign table reports > remotetableoid as well, which is awkward. No it wouldn't. You'd just make the additional column resjunk, same as we do for wholerow. >

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

On Wed, May 16, 2018 at 11:31 PM, Robert Haas wrote: > On Mon, Apr 16, 2018 at 7:35 AM, Ashutosh Bapat > wrote: >> It does fix the problem. But the patch as is interferes with the way >> we handle tableoid currently. That can be seen from

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

On Mon, Apr 16, 2018 at 7:35 AM, Ashutosh Bapat wrote: > 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

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

On Thu, Apr 19, 2018 at 11:38 AM, Kyotaro HORIGUCHI wrote: > >> /* 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

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

At Wed, 18 Apr 2018 13:23:06 +0530, Ashutosh Bapat wrote in > On Wed, Apr 18, 2018 at 9:43 AM, Kyotaro HORIGUCHI > wrote: > > > > Anyway I think we should warn

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

On Wed, Apr 18, 2018 at 9:43 AM, Kyotaro HORIGUCHI 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 > =

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

Hello. At Mon, 16 Apr 2018 17:05:28 +0530, Ashutosh Bapat wrote in > Hi, > Consider this scenario > > postgres=# CREATE TABLE plt (a int, b int) PARTITION BY LIST(a); > postgres=# CREATE