Horton

Thread

Commits

  1. Fix creation of resjunk tlist entries for inherited mixed UPDATE/DELETE.

  1. Bug in ExecModifyTable function and trigger issues for foreign tables

    Ildus Kurbangaliev <i.kurbangaliev@postgrespro.ru> — 2017-05-14T12:05:25Z

    Hello hackers,
i was experimenting with fdw tables recently, and discovered two bugs
in postgres core code (tested on stable 9.6 and master).

Steps to reproduce:

1) create parent table
2) create child local table
3) create child foreign table
4) create 'before row update` trigger at foreign table
5) make update query on parent table.

I attached sql file with these steps. At the end postgres will show an
error like:

ERROR:  could not open file "base/12410/33037": No such file or
directory

33037 is relid of the foreign table.  Bug is related with the fact that
postgres will try use latest scanned tupleid from local table to try
get an old tuple for trigger of foreign table.

It should be fixed with the patch like:

--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -1324,7 +1324,6 @@ ExecModifyTable(ModifyTableState *node)
        JunkFilter *junkfilter;
        TupleTableSlot *slot;
        TupleTableSlot *planSlot;
-       ItemPointer tupleid = NULL;
        ItemPointerData tuple_ctid;
        HeapTupleData oldtupdata;
        HeapTuple       oldtuple;
@@ -1381,6 +1380,8 @@ ExecModifyTable(ModifyTableState *node)
         */
        for (;;)
        {
+               ItemPointer tupleid = NULL;
+

After this patch the second bug will appear:

TRAP: FailedAssertion("!(((const void*)(fdw_trigtuple) != ((void *)0))
^ ((bool) (((const void*)(tupleid) != ((void *)0)) &&
((tupleid)->ip_posid != 0))))", File: "trigger.c", Line: 2428)

I'm not sure how it should be fixed, because as I see `oldtuple` will
be set only for AFTER ROW triggers by `wholerow` junk attribute.

Regards,
Ildus Kurbangaliev

  2. Re: Bug in ExecModifyTable function and trigger issues for foreign tables

    Dilip Kumar <dilipbalaut@gmail.com> — 2017-05-14T16:24:41Z

    On Sun, May 14, 2017 at 5:35 PM, Ildus Kurbangaliev
<i.kurbangaliev@postgrespro.ru> wrote:
> TRAP: FailedAssertion("!(((const void*)(fdw_trigtuple) != ((void *)0))
> ^ ((bool) (((const void*)(tupleid) != ((void *)0)) &&
> ((tupleid)->ip_posid != 0))))", File: "trigger.c", Line: 2428)
>
> I'm not sure how it should be fixed, because as I see `oldtuple` will
> be set only for AFTER ROW triggers by `wholerow` junk attribute.

There is comment on the ExecUpdate function

 *  When updating a foreign table,
 * tupleid is invalid; the FDW has to figure out which row to
 * update using data from the planSlot.  oldtuple is passed to
 * foreign table triggers; it is NULL when the foreign table has
 * no relevant triggers.

After your fix, now tupleid is invalid which is expected, but seems
like we need to do something more. As per the comments seems like it
is expected to get the oldtuple from planSlot.  But I don't see any
code for handling that part.

-- 
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com

  3. Re: Bug in ExecModifyTable function and trigger issues for foreign tables

    Dilip Kumar <dilipbalaut@gmail.com> — 2017-05-15T05:04:58Z

    On Sun, May 14, 2017 at 9:54 PM, Dilip Kumar <dilipbalaut@gmail.com> wrote:
> After your fix, now tupleid is invalid which is expected, but seems
> like we need to do something more. As per the comments seems like it
> is expected to get the oldtuple from planSlot.  But I don't see any
> code for handling that part.

Maybe we should do something like attached patch.

-- 
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com

  4. Re: Bug in ExecModifyTable function and trigger issues for foreign tables

    Michael Paquier <michael.paquier@gmail.com> — 2017-05-15T05:48:53Z

    On Mon, May 15, 2017 at 2:04 PM, Dilip Kumar <dilipbalaut@gmail.com> wrote:
> On Sun, May 14, 2017 at 9:54 PM, Dilip Kumar <dilipbalaut@gmail.com> wrote:
>> After your fix, now tupleid is invalid which is expected, but seems
>> like we need to do something more. As per the comments seems like it
>> is expected to get the oldtuple from planSlot.  But I don't see any
>> code for handling that part.
>
> Maybe we should do something like attached patch.

As a deficiency, shouldn't this try as well to improve regression test
coverage for FDW triggers with inheritance trees? Those tests are in
postgres_fdw. You may find other issues on the way..
-- 
Michael

  5. Re: Bug in ExecModifyTable function and trigger issues for foreign tables

    Ildus Kurbangaliev <i.kurbangaliev@postgrespro.ru> — 2017-05-15T09:16:38Z

    On Mon, 15 May 2017 10:34:58 +0530
Dilip Kumar <dilipbalaut@gmail.com> wrote:

> On Sun, May 14, 2017 at 9:54 PM, Dilip Kumar <dilipbalaut@gmail.com>
> wrote:
> > After your fix, now tupleid is invalid which is expected, but seems
> > like we need to do something more. As per the comments seems like it
> > is expected to get the oldtuple from planSlot.  But I don't see any
> > code for handling that part.  
> 
> Maybe we should do something like attached patch.
> 

Hi,
planSlot contains already projected tuple, you can't use it as oldtuple.
I think problem is that `rewriteTargetListUD` called only for parent
relation, so there is no `wholerow` attribute for foreign tables.

-- 
---
Ildus Kurbangaliev
Postgres Professional: http://www.postgrespro.com
Russian Postgres Company

  6. Re: Bug in ExecModifyTable function and trigger issues for foreign tables

    Dilip Kumar <dilipbalaut@gmail.com> — 2017-05-15T10:02:31Z

    On Mon, May 15, 2017 at 2:46 PM, Ildus Kurbangaliev
<i.kurbangaliev@postgrespro.ru> wrote:
> Hi,
> planSlot contains already projected tuple, you can't use it as oldtuple.
> I think problem is that `rewriteTargetListUD` called only for parent
> relation, so there is no `wholerow` attribute for foreign tables.

Oh, right!

-- 
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com

  7. Re: Bug in ExecModifyTable function and trigger issues for foreign tables

    Ashutosh Bapat <ashutosh.bapat@enterprisedb.com> — 2017-05-15T12:13:52Z

    On Mon, May 15, 2017 at 2:46 PM, Ildus Kurbangaliev
<i.kurbangaliev@postgrespro.ru> wrote:
> On Mon, 15 May 2017 10:34:58 +0530
> Dilip Kumar <dilipbalaut@gmail.com> wrote:
>
>> On Sun, May 14, 2017 at 9:54 PM, Dilip Kumar <dilipbalaut@gmail.com>
>> wrote:
>> > After your fix, now tupleid is invalid which is expected, but seems
>> > like we need to do something more. As per the comments seems like it
>> > is expected to get the oldtuple from planSlot.  But I don't see any
>> > code for handling that part.
>>
>> Maybe we should do something like attached patch.
>>
>
> Hi,
> planSlot contains already projected tuple, you can't use it as oldtuple.
> I think problem is that `rewriteTargetListUD` called only for parent
> relation, so there is no `wholerow` attribute for foreign tables.

Yes. postgresAddForeignUpdateTargets() which is called by
rewriteTargetListUD injects "ctid". "wholerow" is always there. Not
for postgres_fdw but for other wrappers it might be a bad news. ctid,
whole row obtained from the remote postgres server will fit the tuple
descriptor of parent, but for other FDWs the column injected by
rewriteTargetListUD() may make the child tuple look different from
that of the parent, so we may not pass that column down to the child.

-- 
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company

  8. Re: Bug in ExecModifyTable function and trigger issues for foreign tables

    Dilip Kumar <dilipbalaut@gmail.com> — 2017-05-15T12:34:39Z

    On Mon, May 15, 2017 at 5:43 PM, Ashutosh Bapat
<ashutosh.bapat@enterprisedb.com> wrote:
> Yes. postgresAddForeignUpdateTargets() which is called by
> rewriteTargetListUD injects "ctid". "wholerow" is always there. Not
> for postgres_fdw but for other wrappers it might be a bad news. ctid,
> whole row obtained from the remote postgres server will fit the tuple
> descriptor of parent, but for other FDWs the column injected by
> rewriteTargetListUD() may make the child tuple look different from
> that of the parent, so we may not pass that column down to the child.

But, can't we call rewriteTargetListUD for all the inheritors if the
inheritor is a foreign table which will intern call the
postgresAddForeignUpdateTargets?

-- 
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com

  9. Re: Bug in ExecModifyTable function and trigger issues for foreign tables

    Ashutosh Bapat <ashutosh.bapat@enterprisedb.com> — 2017-05-15T12:50:39Z

    On Mon, May 15, 2017 at 6:04 PM, Dilip Kumar <dilipbalaut@gmail.com> wrote:
> On Mon, May 15, 2017 at 5:43 PM, Ashutosh Bapat
> <ashutosh.bapat@enterprisedb.com> wrote:
>> Yes. postgresAddForeignUpdateTargets() which is called by
>> rewriteTargetListUD injects "ctid". "wholerow" is always there. Not
>> for postgres_fdw but for other wrappers it might be a bad news. ctid,
>> whole row obtained from the remote postgres server will fit the tuple
>> descriptor of parent, but for other FDWs the column injected by
>> rewriteTargetListUD() may make the child tuple look different from
>> that of the parent, so we may not pass that column down to the child.
>
> But, can't we call rewriteTargetListUD for all the inheritors if the
> inheritor is a foreign table which will intern call the
> postgresAddForeignUpdateTargets?
>
Yes. But it's not necessary to have all children use same FDW.


-- 
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company

  10. Re: Bug in ExecModifyTable function and trigger issues for foreign tables

    Ildus Kurbangaliev <i.kurbangaliev@postgrespro.ru> — 2017-05-15T13:54:55Z

    On Mon, 15 May 2017 17:43:52 +0530
Ashutosh Bapat <ashutosh.bapat@enterprisedb.com> wrote:

> On Mon, May 15, 2017 at 2:46 PM, Ildus Kurbangaliev
> <i.kurbangaliev@postgrespro.ru> wrote:
> > On Mon, 15 May 2017 10:34:58 +0530
> > Dilip Kumar <dilipbalaut@gmail.com> wrote:
> >  
> >> On Sun, May 14, 2017 at 9:54 PM, Dilip Kumar
> >> <dilipbalaut@gmail.com> wrote:  
> >> > After your fix, now tupleid is invalid which is expected, but
> >> > seems like we need to do something more. As per the comments
> >> > seems like it is expected to get the oldtuple from planSlot.
> >> > But I don't see any code for handling that part.  
> >>
> >> Maybe we should do something like attached patch.
> >>  
> >
> > Hi,
> > planSlot contains already projected tuple, you can't use it as
> > oldtuple. I think problem is that `rewriteTargetListUD` called only
> > for parent relation, so there is no `wholerow` attribute for
> > foreign tables.  
> 
> Yes. postgresAddForeignUpdateTargets() which is called by
> rewriteTargetListUD injects "ctid". "wholerow" is always there. Not
> for postgres_fdw but for other wrappers it might be a bad news. ctid,
> whole row obtained from the remote postgres server will fit the tuple
> descriptor of parent, but for other FDWs the column injected by
> rewriteTargetListUD() may make the child tuple look different from
> that of the parent, so we may not pass that column down to the child.
> 

I'm trying to say that when we have a regular table as parent, and
foreign table as child, in rewriteTargetListUD `wholerow` won't be
added, because rewriteTargetListUD will be called only for parent
relation. You can see that by running the script i provided in the first
message of this thread.

-- 
---
Ildus Kurbangaliev
Postgres Professional: http://www.postgrespro.com
Russian Postgres Company

  11. Re: Bug in ExecModifyTable function and trigger issues for foreign tables

    Ashutosh Bapat <ashutosh.bapat@enterprisedb.com> — 2017-05-16T09:51:27Z

    On Mon, May 15, 2017 at 7:24 PM, Ildus Kurbangaliev
<i.kurbangaliev@postgrespro.ru> wrote:
> On Mon, 15 May 2017 17:43:52 +0530
> Ashutosh Bapat <ashutosh.bapat@enterprisedb.com> wrote:
>
>> On Mon, May 15, 2017 at 2:46 PM, Ildus Kurbangaliev
>> <i.kurbangaliev@postgrespro.ru> wrote:
>> > On Mon, 15 May 2017 10:34:58 +0530
>> > Dilip Kumar <dilipbalaut@gmail.com> wrote:
>> >
>> >> On Sun, May 14, 2017 at 9:54 PM, Dilip Kumar
>> >> <dilipbalaut@gmail.com> wrote:
>> >> > After your fix, now tupleid is invalid which is expected, but
>> >> > seems like we need to do something more. As per the comments
>> >> > seems like it is expected to get the oldtuple from planSlot.
>> >> > But I don't see any code for handling that part.
>> >>
>> >> Maybe we should do something like attached patch.
>> >>
>> >
>> > Hi,
>> > planSlot contains already projected tuple, you can't use it as
>> > oldtuple. I think problem is that `rewriteTargetListUD` called only
>> > for parent relation, so there is no `wholerow` attribute for
>> > foreign tables.
>>
>> Yes. postgresAddForeignUpdateTargets() which is called by
>> rewriteTargetListUD injects "ctid". "wholerow" is always there. Not
>> for postgres_fdw but for other wrappers it might be a bad news. ctid,
>> whole row obtained from the remote postgres server will fit the tuple
>> descriptor of parent, but for other FDWs the column injected by
>> rewriteTargetListUD() may make the child tuple look different from
>> that of the parent, so we may not pass that column down to the child.
>>
>
> I'm trying to say that when we have a regular table as parent, and
> foreign table as child, in rewriteTargetListUD `wholerow` won't be
> added, because rewriteTargetListUD will be called only for parent
> relation. You can see that by running the script i provided in the first
> message of this thread.


You are right.

explain verbose update parent set val = random();
                                  QUERY PLAN
-------------------------------------------------------------------------------
 Update on public.parent  (cost=0.00..258.63 rows=5535 width=10)
   Update on public.parent
   Update on public.child1
   Foreign Update on public.ftable
     Remote SQL: UPDATE public.child1 SET val = $2 WHERE ctid = $1
   ->  Seq Scan on public.parent  (cost=0.00..4.83 rows=255 width=10)
         Output: random(), parent.ctid
   ->  Seq Scan on public.child1  (cost=0.00..48.25 rows=2550 width=10)
         Output: random(), child1.ctid
   ->  Foreign Scan on public.ftable  (cost=100.00..205.55 rows=2730 width=10)
         Output: random(), ftable.ctid
         Remote SQL: SELECT ctid FROM public.child1 FOR UPDATE
(12 rows)

explain verbose update ftable set val = random();
                                  QUERY PLAN
-------------------------------------------------------------------------------
 Update on public.ftable  (cost=100.00..159.42 rows=1412 width=38)
   Remote SQL: UPDATE public.child1 SET val = $2 WHERE ctid = $1
   ->  Foreign Scan on public.ftable  (cost=100.00..159.42 rows=1412 width=38)
         Output: random(), ctid, ftable.*
         Remote SQL: SELECT val, ctid FROM public.child1 FOR UPDATE
(5 rows)

Anyway, the problem I am stating, i.e. we have a bigger problem to fix
when there are FDWs other than postgres_fdw involved seems to be still
valid.
-- 
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company

  12. Re: Bug in ExecModifyTable function and trigger issues for foreign tables

    Ildus Kurbangaliev <i.kurbangaliev@postgrespro.ru> — 2017-05-16T12:05:16Z

    On Tue, 16 May 2017 15:21:27 +0530
Ashutosh Bapat <ashutosh.bapat@enterprisedb.com> wrote:

> On Mon, May 15, 2017 at 7:24 PM, Ildus Kurbangaliev
> <i.kurbangaliev@postgrespro.ru> wrote:
> > On Mon, 15 May 2017 17:43:52 +0530
> > Ashutosh Bapat <ashutosh.bapat@enterprisedb.com> wrote:
> >  
> >> On Mon, May 15, 2017 at 2:46 PM, Ildus Kurbangaliev
> >> <i.kurbangaliev@postgrespro.ru> wrote:  
> >> > On Mon, 15 May 2017 10:34:58 +0530
> >> > Dilip Kumar <dilipbalaut@gmail.com> wrote:
> >> >  
> >> >> On Sun, May 14, 2017 at 9:54 PM, Dilip Kumar
> >> >> <dilipbalaut@gmail.com> wrote:  
> >> >> > After your fix, now tupleid is invalid which is expected, but
> >> >> > seems like we need to do something more. As per the comments
> >> >> > seems like it is expected to get the oldtuple from planSlot.
> >> >> > But I don't see any code for handling that part.  
> >> >>
> >> >> Maybe we should do something like attached patch.
> >> >>  
> >> >
> >> > Hi,
> >> > planSlot contains already projected tuple, you can't use it as
> >> > oldtuple. I think problem is that `rewriteTargetListUD` called
> >> > only for parent relation, so there is no `wholerow` attribute for
> >> > foreign tables.  
> >>
> >> Yes. postgresAddForeignUpdateTargets() which is called by
> >> rewriteTargetListUD injects "ctid". "wholerow" is always there. Not
> >> for postgres_fdw but for other wrappers it might be a bad news.
> >> ctid, whole row obtained from the remote postgres server will fit
> >> the tuple descriptor of parent, but for other FDWs the column
> >> injected by rewriteTargetListUD() may make the child tuple look
> >> different from that of the parent, so we may not pass that column
> >> down to the child. 
> >
> > I'm trying to say that when we have a regular table as parent, and
> > foreign table as child, in rewriteTargetListUD `wholerow` won't be
> > added, because rewriteTargetListUD will be called only for parent
> > relation. You can see that by running the script i provided in the
> > first message of this thread.  
> 
> 
> You are right.
> 
> explain verbose update parent set val = random();
>                                   QUERY PLAN
> -------------------------------------------------------------------------------
>  Update on public.parent  (cost=0.00..258.63 rows=5535 width=10)
>    Update on public.parent
>    Update on public.child1
>    Foreign Update on public.ftable
>      Remote SQL: UPDATE public.child1 SET val = $2 WHERE ctid = $1
>    ->  Seq Scan on public.parent  (cost=0.00..4.83 rows=255
> width=10) Output: random(), parent.ctid
>    ->  Seq Scan on public.child1  (cost=0.00..48.25 rows=2550
> width=10) Output: random(), child1.ctid
>    ->  Foreign Scan on public.ftable  (cost=100.00..205.55 rows=2730
> width=10) Output: random(), ftable.ctid
>          Remote SQL: SELECT ctid FROM public.child1 FOR UPDATE
> (12 rows)
> 
> explain verbose update ftable set val = random();
>                                   QUERY PLAN
> -------------------------------------------------------------------------------
>  Update on public.ftable  (cost=100.00..159.42 rows=1412 width=38)
>    Remote SQL: UPDATE public.child1 SET val = $2 WHERE ctid = $1
>    ->  Foreign Scan on public.ftable  (cost=100.00..159.42 rows=1412
> width=38) Output: random(), ctid, ftable.*
>          Remote SQL: SELECT val, ctid FROM public.child1 FOR UPDATE
> (5 rows)
> 
> Anyway, the problem I am stating, i.e. we have a bigger problem to fix
> when there are FDWs other than postgres_fdw involved seems to be still
> valid.

I agree. Maybe this issue should be added to Postgresql Open Items? 
I think there should be some complex solution that fixes not only
triggers for foreign tables at table partitioning, but covers other
possible not working cases.

-- 
---
Ildus Kurbangaliev
Postgres Professional: http://www.postgrespro.com
Russian Postgres Company

  13. Re: Bug in ExecModifyTable function and trigger issues for foreign tables

    Ashutosh Bapat <ashutosh.bapat@enterprisedb.com> — 2017-05-16T12:11:44Z

    On Tue, May 16, 2017 at 5:35 PM, Ildus Kurbangaliev
<i.kurbangaliev@postgrespro.ru> wrote:

>
> I agree. Maybe this issue should be added to Postgresql Open Items?
> I think there should be some complex solution that fixes not only
> triggers for foreign tables at table partitioning, but covers other
> possible not working cases.
>

I doubt if this is an open item, since DMLs on foreign tables are
supported since 9.3 and support to add foreign tables to inheritance
was added back in 9.5.

-- 
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company

  14. Re: Bug in ExecModifyTable function and trigger issues for foreign tables

    Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp> — 2017-05-16T12:36:11Z

    On 2017/05/16 21:11, Ashutosh Bapat wrote:
> On Tue, May 16, 2017 at 5:35 PM, Ildus Kurbangaliev
> <i.kurbangaliev@postgrespro.ru> wrote:

>> I agree. Maybe this issue should be added to Postgresql Open Items?
>> I think there should be some complex solution that fixes not only
>> triggers for foreign tables at table partitioning, but covers other
>> possible not working cases.

> I doubt if this is an open item, since DMLs on foreign tables are
> supported since 9.3 and support to add foreign tables to inheritance
> was added back in 9.5.

I think this issue was introduced by the latter, so that was my fault.

One approach I came up with to fix this issue is to rewrite the 
targetList entries of an inherited UPDATE/DELETE properly using 
rewriteTargetListUD, when generating a plan for each child table in 
inheritance_planner.  Attached is a WIP patch for that.  Maybe I am 
missing something, though.

Best regards,
Etsuro Fujita

  15. Re: Bug in ExecModifyTable function and trigger issues for foreign tables

    Ildus Kurbangaliev <i.kurbangaliev@postgrespro.ru> — 2017-05-16T14:26:00Z

    On Tue, 16 May 2017 21:36:11 +0900
Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp> wrote:

> On 2017/05/16 21:11, Ashutosh Bapat wrote:
> > On Tue, May 16, 2017 at 5:35 PM, Ildus Kurbangaliev
> > <i.kurbangaliev@postgrespro.ru> wrote:  
> 
> >> I agree. Maybe this issue should be added to Postgresql Open Items?
> >> I think there should be some complex solution that fixes not only
> >> triggers for foreign tables at table partitioning, but covers other
> >> possible not working cases.  
> 
> > I doubt if this is an open item, since DMLs on foreign tables are
> > supported since 9.3 and support to add foreign tables to inheritance
> > was added back in 9.5.  
> 
> I think this issue was introduced by the latter, so that was my fault.
> 
> One approach I came up with to fix this issue is to rewrite the 
> targetList entries of an inherited UPDATE/DELETE properly using 
> rewriteTargetListUD, when generating a plan for each child table in 
> inheritance_planner.  Attached is a WIP patch for that.  Maybe I am 
> missing something, though.
> 
> Best regards,
> Etsuro Fujita

I tested the patch, looks good.

-- 
---
Ildus Kurbangaliev
Postgres Professional: http://www.postgrespro.com
Russian Postgres Company

  16. Re: Bug in ExecModifyTable function and trigger issues for foreign tables

    Michael Paquier <michael.paquier@gmail.com> — 2017-05-17T06:28:24Z

    On Tue, May 16, 2017 at 11:26 PM, Ildus Kurbangaliev
<i.kurbangaliev@postgrespro.ru> wrote:
> On Tue, 16 May 2017 21:36:11 +0900
> Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp> wrote:
>> On 2017/05/16 21:11, Ashutosh Bapat wrote:
>> > On Tue, May 16, 2017 at 5:35 PM, Ildus Kurbangaliev
>> > <i.kurbangaliev@postgrespro.ru> wrote:
>>
>> >> I agree. Maybe this issue should be added to Postgresql Open Items?
>> >> I think there should be some complex solution that fixes not only
>> >> triggers for foreign tables at table partitioning, but covers other
>> >> possible not working cases.
>>
>> > I doubt if this is an open item, since DMLs on foreign tables are
>> > supported since 9.3 and support to add foreign tables to inheritance
>> > was added back in 9.5.
>>
>> I think this issue was introduced by the latter, so that was my fault.
>>
>> One approach I came up with to fix this issue is to rewrite the
>> targetList entries of an inherited UPDATE/DELETE properly using
>> rewriteTargetListUD, when generating a plan for each child table in
>> inheritance_planner.  Attached is a WIP patch for that.  Maybe I am
>> missing something, though.

Could this patch include some regression tests to see at what extent
it has been tested? We surely don't want to see that broken again in
the future as well. (Nit: I did not look at the patch in details yet)

> I tested the patch, looks good.

What kind of tests did you do?

                  junkfilter = resultRelInfo->ri_junkFilter;
+                 tupleid = NULL;
                  estate->es_result_relation_info = resultRelInfo;
Er, what is that?
-- 
Michael

  17. Re: Bug in ExecModifyTable function and trigger issues for foreign tables

    Ildus Kurbangaliev <i.kurbangaliev@postgrespro.ru> — 2017-05-17T08:54:37Z

    On Wed, 17 May 2017 15:28:24 +0900
Michael Paquier <michael.paquier@gmail.com> wrote:

> On Tue, May 16, 2017 at 11:26 PM, Ildus Kurbangaliev
> <i.kurbangaliev@postgrespro.ru> wrote:
> > On Tue, 16 May 2017 21:36:11 +0900
> > Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp> wrote:  
> >> On 2017/05/16 21:11, Ashutosh Bapat wrote:  
> >> > On Tue, May 16, 2017 at 5:35 PM, Ildus Kurbangaliev
> >> > <i.kurbangaliev@postgrespro.ru> wrote:  
> >>  
> >> >> I agree. Maybe this issue should be added to Postgresql Open
> >> >> Items? I think there should be some complex solution that fixes
> >> >> not only triggers for foreign tables at table partitioning, but
> >> >> covers other possible not working cases.  
> >>  
> >> > I doubt if this is an open item, since DMLs on foreign tables are
> >> > supported since 9.3 and support to add foreign tables to
> >> > inheritance was added back in 9.5.  
> >>
> >> I think this issue was introduced by the latter, so that was my
> >> fault.
> >>
> >> One approach I came up with to fix this issue is to rewrite the
> >> targetList entries of an inherited UPDATE/DELETE properly using
> >> rewriteTargetListUD, when generating a plan for each child table in
> >> inheritance_planner.  Attached is a WIP patch for that.  Maybe I am
> >> missing something, though.  
> 
> Could this patch include some regression tests to see at what extent
> it has been tested? We surely don't want to see that broken again in
> the future as well. (Nit: I did not look at the patch in details yet)
> 
> > I tested the patch, looks good.  
> 
> What kind of tests did you do?

I tested update triggers for foreign table when regular table is a
parent and foreign table is a child. Case like this:

explain verbose update parent set val = random();
                                  QUERY PLAN
-------------------------------------------------------------------------------
 Update on public.parent  (cost=0.00..258.63 rows=5535 width=10)
   Update on public.parent
   Update on public.child1
   Foreign Update on public.ftable // we have triggers on ftable here

> 
>                   junkfilter = resultRelInfo->ri_junkFilter;
> +                 tupleid = NULL;
>                   estate->es_result_relation_info = resultRelInfo;
> Er, what is that?

That fixes the bug when tupleid from regular tuple is used to get
oldtuple for triggers of foreign table.

-- 
---
Ildus Kurbangaliev
Postgres Professional: http://www.postgrespro.com
Russian Postgres Company

  18. Re: Bug in ExecModifyTable function and trigger issues for foreign tables

    Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp> — 2017-05-17T09:06:29Z

    On 2017/05/17 17:54, Ildus Kurbangaliev wrote:
> On Wed, 17 May 2017 15:28:24 +0900
> Michael Paquier <michael.paquier@gmail.com> wrote:
>
>> On Tue, May 16, 2017 at 11:26 PM, Ildus Kurbangaliev
>> <i.kurbangaliev@postgrespro.ru> wrote:
>>> On Tue, 16 May 2017 21:36:11 +0900
>>> Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp> wrote:
>>>> On 2017/05/16 21:11, Ashutosh Bapat wrote:
>>>>> On Tue, May 16, 2017 at 5:35 PM, Ildus Kurbangaliev
>>>>> <i.kurbangaliev@postgrespro.ru> wrote:
>>>>
>>>>>> I agree. Maybe this issue should be added to Postgresql Open
>>>>>> Items? I think there should be some complex solution that fixes
>>>>>> not only triggers for foreign tables at table partitioning, but
>>>>>> covers other possible not working cases.
>>>>
>>>>> I doubt if this is an open item, since DMLs on foreign tables are
>>>>> supported since 9.3 and support to add foreign tables to
>>>>> inheritance was added back in 9.5.
>>>>
>>>> I think this issue was introduced by the latter, so that was my
>>>> fault.
>>>>
>>>> One approach I came up with to fix this issue is to rewrite the
>>>> targetList entries of an inherited UPDATE/DELETE properly using
>>>> rewriteTargetListUD, when generating a plan for each child table in
>>>> inheritance_planner.  Attached is a WIP patch for that.  Maybe I am
>>>> missing something, though.
>>
>> Could this patch include some regression tests to see at what extent
>> it has been tested? We surely don't want to see that broken again in
>> the future as well. (Nit: I did not look at the patch in details yet)

OK, I'll include regression tests in the next version of the patch.

>>> I tested the patch, looks good.
>>
>> What kind of tests did you do?
>
> I tested update triggers for foreign table when regular table is a
> parent and foreign table is a child. Case like this:
>
> explain verbose update parent set val = random();
>                                   QUERY PLAN
> -------------------------------------------------------------------------------
>  Update on public.parent  (cost=0.00..258.63 rows=5535 width=10)
>    Update on public.parent
>    Update on public.child1
>    Foreign Update on public.ftable // we have triggers on ftable here
>
>>
>>                   junkfilter = resultRelInfo->ri_junkFilter;
>> +                 tupleid = NULL;
>>                   estate->es_result_relation_info = resultRelInfo;
>> Er, what is that?
>
> That fixes the bug when tupleid from regular tuple is used to get
> oldtuple for triggers of foreign table.

That's right.  Let me explain in more detail.  Currently, tupleid is 
only initialized at the top of ExecModifyTable, so if we just loop 
within the for(;;) code in that function (without returning RETURNING to 
caller), tupleid won't be initialized even when advancing to next 
subplan in case of inherited UPDATE/DELETE.  This would cause a problem. 
  Assume that the current subplan is for the parent, which is a plain 
table, that the next subplan is for the child, which is a foreign table, 
and that the foreign table has a BEFORE trigger, as tested by Ildus.  In 
that case the current subplan would set tupleid to ctid for each row 
from the plain table, and after advancing to the next subplan, the 
subplan would set oldtuple to wholerow for the first row from the 
foreign table, *without initializing tupleid to NULL*, and then call 
ExecBRUpdateTriggers/ExecBRDeleteTriggers during ExecUpdate/ExecDelete, 
which would cause an assertion error for 
Assert(HeapTupleIsValid(fdw_trigtuple) ^ ItemPointerIsValid(tupleid)) in 
those trigger functions, because oldtuple and tupleid are both valid. 
So, tupleid should be initialized at least when advancing to next 
subplan.  It might be better to initialize that at each iteration of the 
for(;;) code, like oldtuple, though.

Best regards,
Etsuro Fujita

  19. Re: Bug in ExecModifyTable function and trigger issues for foreign tables

    Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp> — 2017-06-02T09:10:27Z

    On 2017/05/16 21:36, Etsuro Fujita wrote:
> One approach I came up with to fix this issue is to rewrite the 
> targetList entries of an inherited UPDATE/DELETE properly using 
> rewriteTargetListUD, when generating a plan for each child table in 
> inheritance_planner.  Attached is a WIP patch for that.  Maybe I am 
> missing something, though.

While updating the patch, I noticed the patch rewrites the UPDATE 
targetList incorrectly in some cases; rewrite_inherited_tlist I added to 
adjust_appendrel_attrs (1) removes all junk items from the targetList 
and (2) adds junk items for the child table using rewriteTargetListUD, 
but it's wrong to drop all junk items in cases where there are junk 
items for some other reasons than rewriteTargetListUD.  Consider junk 
items containing MULTIEXPR SubLink.  One way I came up with to fix this 
is to change (1) to only remove junk items with resname; since junk 
items added by rewriteTargetListUD should have resname (note: we would 
need resname to call ExecFindJunkAttributeInTlist at execution time!) 
while other junk items wouldn't have resname (see 
transformUpdateTargetList), we could correctly replace junk items added 
by rewriteTargetListUD for the parent with ones for the child, by that 
change.  I might be missing something, though.  Comments welcome.

Best regards,
Etsuro Fujita

  20. Re: Bug in ExecModifyTable function and trigger issues for foreign tables

    Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp> — 2017-06-05T08:25:27Z

    On 2017/06/02 18:10, Etsuro Fujita wrote:
> On 2017/05/16 21:36, Etsuro Fujita wrote:
>> One approach I came up with to fix this issue is to rewrite the 
>> targetList entries of an inherited UPDATE/DELETE properly using 
>> rewriteTargetListUD, when generating a plan for each child table in 
>> inheritance_planner.  Attached is a WIP patch for that.  Maybe I am 
>> missing something, though.
> 
> While updating the patch, I noticed the patch rewrites the UPDATE 
> targetList incorrectly in some cases; rewrite_inherited_tlist I added to 
> adjust_appendrel_attrs (1) removes all junk items from the targetList 
> and (2) adds junk items for the child table using rewriteTargetListUD, 
> but it's wrong to drop all junk items in cases where there are junk 
> items for some other reasons than rewriteTargetListUD.  Consider junk 
> items containing MULTIEXPR SubLink.  One way I came up with to fix this 
> is to change (1) to only remove junk items with resname; since junk 
> items added by rewriteTargetListUD should have resname (note: we would 
> need resname to call ExecFindJunkAttributeInTlist at execution time!) 
> while other junk items wouldn't have resname (see 
> transformUpdateTargetList), we could correctly replace junk items added 
> by rewriteTargetListUD for the parent with ones for the child, by that 
> change.  I might be missing something, though.  Comments welcome.

I updated the patch that way.  Please find attached an updated version.

Other changes:
* Moved the initialization for "tupleid" I added in ExecModifyTable as 
discussed before, which I think is essentially the same as proposed by 
Ildus in [1], since I think that would be more consistent with "oldtuple".
* Added regression tests.

Anyway I'll add this to the next commitfest.

Best regards,
Etsuro Fujita

[1] 
https://www.postgresql.org/message-id/20170514150525.0346ba72%40postgrespro.ru

  21. Re: Bug in ExecModifyTable function and trigger issues for foreign tables

    Ashutosh Bapat <ashutosh.bapat@enterprisedb.com> — 2017-06-05T08:39:53Z

    On Fri, Jun 2, 2017 at 2:40 PM, Etsuro Fujita
<fujita.etsuro@lab.ntt.co.jp> wrote:
> On 2017/05/16 21:36, Etsuro Fujita wrote:
>>
>> One approach I came up with to fix this issue is to rewrite the targetList
>> entries of an inherited UPDATE/DELETE properly using rewriteTargetListUD,
>> when generating a plan for each child table in inheritance_planner.
>> Attached is a WIP patch for that.  Maybe I am missing something, though.
>
>
> While updating the patch, I noticed the patch rewrites the UPDATE targetList
> incorrectly in some cases; rewrite_inherited_tlist I added to
> adjust_appendrel_attrs (1) removes all junk items from the targetList and
> (2) adds junk items for the child table using rewriteTargetListUD, but it's
> wrong to drop all junk items in cases where there are junk items for some
> other reasons than rewriteTargetListUD.  Consider junk items containing
> MULTIEXPR SubLink.  One way I came up with to fix this is to change (1) to
> only remove junk items with resname; since junk items added by
> rewriteTargetListUD should have resname (note: we would need resname to call
> ExecFindJunkAttributeInTlist at execution time!) while other junk items
> wouldn't have resname (see transformUpdateTargetList), we could correctly
> replace junk items added by rewriteTargetListUD for the parent with ones for
> the child, by that change.  I might be missing something, though.  Comments
> welcome.

I haven't looked at the patch, but that doesn't look right. In future
some code path other than rewriteTargetListUD() may add junk items
with resname and this fix will remove those junk items as well.

-- 
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company

  22. Re: Bug in ExecModifyTable function and trigger issues for foreign tables

    Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp> — 2017-06-05T09:25:26Z

    On 2017/06/05 17:39, Ashutosh Bapat wrote:
> On Fri, Jun 2, 2017 at 2:40 PM, Etsuro Fujita

>> While updating the patch, I noticed the patch rewrites the UPDATE targetList
>> incorrectly in some cases; rewrite_inherited_tlist I added to
>> adjust_appendrel_attrs (1) removes all junk items from the targetList and
>> (2) adds junk items for the child table using rewriteTargetListUD, but it's
>> wrong to drop all junk items in cases where there are junk items for some
>> other reasons than rewriteTargetListUD.  Consider junk items containing
>> MULTIEXPR SubLink.  One way I came up with to fix this is to change (1) to
>> only remove junk items with resname; since junk items added by
>> rewriteTargetListUD should have resname (note: we would need resname to call
>> ExecFindJunkAttributeInTlist at execution time!) while other junk items
>> wouldn't have resname (see transformUpdateTargetList), we could correctly
>> replace junk items added by rewriteTargetListUD for the parent with ones for
>> the child, by that change.  I might be missing something, though.  Comments
>> welcome.
> 
> I haven't looked at the patch, but that doesn't look right. In future
> some code path other than rewriteTargetListUD() may add junk items
> with resname and this fix will remove those junk items as well.

Yeah, I thought that too.  I think we could replace junk tlist entries 
added by rewriteTargetListUD() more safely, by adding a lot more code, 
but I'm not sure it's worth complicating the code at the current stage.

Best regards,
Etsuro Fujita

  23. Re: Bug in ExecModifyTable function and trigger issues for foreign tables

    Ildus Kurbangaliev <i.kurbangaliev@postgrespro.ru> — 2017-06-15T15:05:48Z

    On Mon, 5 Jun 2017 17:25:27 +0900
Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp> wrote:

> On 2017/06/02 18:10, Etsuro Fujita wrote:
> > On 2017/05/16 21:36, Etsuro Fujita wrote:  
> >> One approach I came up with to fix this issue is to rewrite the 
> >> targetList entries of an inherited UPDATE/DELETE properly using 
> >> rewriteTargetListUD, when generating a plan for each child table
> >> in inheritance_planner.  Attached is a WIP patch for that.  Maybe
> >> I am missing something, though.  
> > 
> > While updating the patch, I noticed the patch rewrites the UPDATE 
> > targetList incorrectly in some cases; rewrite_inherited_tlist I
> > added to adjust_appendrel_attrs (1) removes all junk items from the
> > targetList and (2) adds junk items for the child table using
> > rewriteTargetListUD, but it's wrong to drop all junk items in cases
> > where there are junk items for some other reasons than
> > rewriteTargetListUD.  Consider junk items containing MULTIEXPR
> > SubLink.  One way I came up with to fix this is to change (1) to
> > only remove junk items with resname; since junk items added by
> > rewriteTargetListUD should have resname (note: we would need
> > resname to call ExecFindJunkAttributeInTlist at execution time!)
> > while other junk items wouldn't have resname (see
> > transformUpdateTargetList), we could correctly replace junk items
> > added by rewriteTargetListUD for the parent with ones for the
> > child, by that change.  I might be missing something, though.
> > Comments welcome.  
> 
> I updated the patch that way.  Please find attached an updated
> version.
> 
> Other changes:
> * Moved the initialization for "tupleid" I added in ExecModifyTable
> as discussed before, which I think is essentially the same as
> proposed by Ildus in [1], since I think that would be more consistent
> with "oldtuple".
> * Added regression tests.
> 
> Anyway I'll add this to the next commitfest.
> 
> Best regards,
> Etsuro Fujita
> 
> [1] 
> https://www.postgresql.org/message-id/20170514150525.0346ba72%40postgrespro.ru

Checked the latest patch. Looks good.
Shouldn't this patch be backported to 9.6 and 10beta? The bug
affects them too.

-- 
---
Ildus Kurbangaliev
Postgres Professional: http://www.postgrespro.com
Russian Postgres Company

  24. Re: Bug in ExecModifyTable function and trigger issues for foreign tables

    Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp> — 2017-06-16T10:05:47Z

    On 2017/06/16 0:05, Ildus Kurbangaliev wrote:

I wrote:
>>>> One approach I came up with to fix this issue is to rewrite the
>>>> targetList entries of an inherited UPDATE/DELETE properly using
>>>> rewriteTargetListUD, when generating a plan for each child table
>>>> in inheritance_planner.  Attached is a WIP patch for that.  Maybe
>>>> I am missing something, though.
>>>
>>> While updating the patch, I noticed the patch rewrites the UPDATE
>>> targetList incorrectly in some cases; rewrite_inherited_tlist I
>>> added to adjust_appendrel_attrs (1) removes all junk items from the
>>> targetList and (2) adds junk items for the child table using
>>> rewriteTargetListUD, but it's wrong to drop all junk items in cases
>>> where there are junk items for some other reasons than
>>> rewriteTargetListUD.  Consider junk items containing MULTIEXPR
>>> SubLink.  One way I came up with to fix this is to change (1) to
>>> only remove junk items with resname; since junk items added by
>>> rewriteTargetListUD should have resname (note: we would need
>>> resname to call ExecFindJunkAttributeInTlist at execution time!)
>>> while other junk items wouldn't have resname (see
>>> transformUpdateTargetList), we could correctly replace junk items
>>> added by rewriteTargetListUD for the parent with ones for the
>>> child, by that change.  I might be missing something, though.
>>> Comments welcome.
>>
>> I updated the patch that way.  Please find attached an updated
>> version.
>>
>> Other changes:
>> * Moved the initialization for "tupleid" I added in ExecModifyTable
>> as discussed before, which I think is essentially the same as
>> proposed by Ildus in [1], since I think that would be more consistent
>> with "oldtuple".
>> * Added regression tests.
>>
>> Anyway I'll add this to the next commitfest.

> Checked the latest patch. Looks good.
> Shouldn't this patch be backported to 9.6 and 10beta? The bug
> affects them too.

Thank you for the review!

The bug is in foreign table inheritance, which was supported in 9.5, so 
I think this patch should be backported to 9.5.

Ashutosh mentioned his concern about what I proposed above before [2], 
but I'm not sure we should address that.  And there have been no 
opinions from him (or anyone else) since then.  So, I'd like to leave 
that for committer (ie, +1 for Ready for Committer).

Attached is a slightly-updated version; I renamed some variables used in 
rewrite_inherited_tlist() to match other existing code in prepunion.c 
and revised some comments a bit.  I didn't make any functional changes, 
so I'll keep this Ready for Committer.

Best regards,
Etsuro Fujita

[2] 
https://www.postgresql.org/message-id/CAFjFpRfTpamoUz6fNyk6gPh%3DecfBJjbUHJNKbDxscpyPJ3FfjQ%40mail.gmail.com

  25. Re: Bug in ExecModifyTable function and trigger issues for foreign tables

    Ashutosh Bapat <ashutosh.bapat@enterprisedb.com> — 2017-06-16T10:26:13Z

    On Fri, Jun 16, 2017 at 3:35 PM, Etsuro Fujita
<fujita.etsuro@lab.ntt.co.jp> wrote:
> On 2017/06/16 0:05, Ildus Kurbangaliev wrote:
>
>
> I wrote:
>>>>>
>>>>> One approach I came up with to fix this issue is to rewrite the
>>>>> targetList entries of an inherited UPDATE/DELETE properly using
>>>>> rewriteTargetListUD, when generating a plan for each child table
>>>>> in inheritance_planner.  Attached is a WIP patch for that.  Maybe
>>>>> I am missing something, though.
>>>>
>>>>
>>>> While updating the patch, I noticed the patch rewrites the UPDATE
>>>> targetList incorrectly in some cases; rewrite_inherited_tlist I
>>>> added to adjust_appendrel_attrs (1) removes all junk items from the
>>>> targetList and (2) adds junk items for the child table using
>>>> rewriteTargetListUD, but it's wrong to drop all junk items in cases
>>>> where there are junk items for some other reasons than
>>>> rewriteTargetListUD.  Consider junk items containing MULTIEXPR
>>>> SubLink.  One way I came up with to fix this is to change (1) to
>>>> only remove junk items with resname; since junk items added by
>>>> rewriteTargetListUD should have resname (note: we would need
>>>> resname to call ExecFindJunkAttributeInTlist at execution time!)
>>>> while other junk items wouldn't have resname (see
>>>> transformUpdateTargetList), we could correctly replace junk items
>>>> added by rewriteTargetListUD for the parent with ones for the
>>>> child, by that change.  I might be missing something, though.
>>>> Comments welcome.
>>>
>>>
>>> I updated the patch that way.  Please find attached an updated
>>> version.
>>>
>>> Other changes:
>>> * Moved the initialization for "tupleid" I added in ExecModifyTable
>>> as discussed before, which I think is essentially the same as
>>> proposed by Ildus in [1], since I think that would be more consistent
>>> with "oldtuple".
>>> * Added regression tests.
>>>
>>> Anyway I'll add this to the next commitfest.
>
>
>> Checked the latest patch. Looks good.
>> Shouldn't this patch be backported to 9.6 and 10beta? The bug
>> affects them too.
>
>
> Thank you for the review!
>
> The bug is in foreign table inheritance, which was supported in 9.5, so I
> think this patch should be backported to 9.5.
>
> Ashutosh mentioned his concern about what I proposed above before [2], but
> I'm not sure we should address that.  And there have been no opinions from
> him (or anyone else) since then.  So, I'd like to leave that for committer
> (ie, +1 for Ready for Committer).

That issue has not been addressed. The reason stated was that it would
make code complicated. But I have not had chance to look at how
complicated would be and assess myself whether that's worth the
trouble. There was another issue

Also, I don't see any discussion about my concern [3] about a parent
with child from multiple foreign servers with different FDWs. So, I am
not sure whether the patch really fixes the problem in its entirety.

[3] https://www.postgresql.org/message-id/CAFjFpRfQ1pkCjNQFVOP_BPJfc7OR3596nqVVFBgAiDEZqB4Azg@mail.gmail.com

-- 
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company

  26. Re: Bug in ExecModifyTable function and trigger issues for foreign tables

    Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp> — 2017-06-16T10:45:24Z

    On 2017/06/16 19:26, Ashutosh Bapat wrote:
> Also, I don't see any discussion about my concern [3] about a parent
> with child from multiple foreign servers with different FDWs. So, I am
> not sure whether the patch really fixes the problem in its entirety.

The patch would allow child tables to have different foreign servers 
with different FDWs since it applies rewriteTargetListUD to each child 
table when generating a modified query with that child table with the 
target in inheritance_planner.  I didn't any regression tests for that, 
though.

Best regards,
Etsuro Fujita

  27. Re: Bug in ExecModifyTable function and trigger issues for foreign tables

    Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp> — 2017-06-16T12:29:17Z

    On 2017/06/16 19:26, Ashutosh Bapat wrote:
> On Fri, Jun 16, 2017 at 3:35 PM, Etsuro Fujita
>> Ashutosh mentioned his concern about what I proposed above before [2], but
>> I'm not sure we should address that.  And there have been no opinions from
>> him (or anyone else) since then.  So, I'd like to leave that for committer
>> (ie, +1 for Ready for Committer).
> 
> That issue has not been addressed. The reason stated was that it would
> make code complicated. But I have not had chance to look at how
> complicated would be and assess myself whether that's worth the
> trouble.
I have to admit that what I proposed upthread is a quick-and-dirty 
kluge.  One thing I thought to address your concern was to move 
rewriteTargetListUD entirely from the rewriter to the planner when doing 
inherited UPDATE/DELETE, but I'm not sure that's a good idea, because at 
least I think that would need a lot more changes to the rewriter.

Best regards,
Etsuro Fujita

  28. Re: Bug in ExecModifyTable function and trigger issues for foreign tables

    Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp> — 2017-06-30T09:44:02Z

    On 2017/06/16 21:29, Etsuro Fujita wrote:
> On 2017/06/16 19:26, Ashutosh Bapat wrote:

>> That issue has not been addressed. The reason stated was that it would
>> make code complicated. But I have not had chance to look at how
>> complicated would be and assess myself whether that's worth the
>> trouble.
> I have to admit that what I proposed upthread is a quick-and-dirty 
> kluge.  One thing I thought to address your concern was to move 
> rewriteTargetListUD entirely from the rewriter to the planner when doing 
> inherited UPDATE/DELETE, but I'm not sure that's a good idea, because at 
> least I think that would need a lot more changes to the rewriter.

I'll have second thought about this, so I'll mark this as waiting on author.

Best regards,
Etsuro Fujita

  29. Re: Bug in ExecModifyTable function and trigger issues for foreign tables

    Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp> — 2017-07-13T12:10:20Z

    On 2017/06/30 18:44, Etsuro Fujita wrote:
> On 2017/06/16 21:29, Etsuro Fujita wrote:
> I'll have second thought about this, so I'll mark this as waiting on 
> author.

I spent quite a bit of time on this and came up with a solution for 
addressing the concern mentioned by Ashutosh [1].  The basic idea is, as 
I said before, to move rewriteTargetListUD from the rewriter to the 
planner (whether the update or delete is inherited or not), except for 
the view case.  In case of inherited UPDATE/DELETE, the planner adds a 
necessary junk column needed for the update or delete for each child 
relation, without the assumption I made before about junk tlist entries, 
so I think this would be more robust for future changes as mentioned in 
[1].  It wouldn't work well that the planner does the same thing for the 
view case (ie, add a whole-row reference to the given target relation), 
unlike other cases, because what we need to do for that case is to add a 
whole-row reference to the view as the source for an INSTEAD OF trigger, 
not the target.  So, ISTM that it's reasonable to handle that case in 
the rewriter as-is, not in the planner, but one thing I'd like to 
propose to simplify the code in rewriteHandler.c is to move the code for 
the view case in rewriteTargetListUD to ApplyRetrieveRule.  By that 
change, we won't add a whole-row reference to the view in RewriteQuery, 
so we don't need this annoying thing in rewriteTargetView any more:

     /*
      * For UPDATE/DELETE, rewriteTargetListUD will have added a 
wholerow junk
      * TLE for the view to the end of the targetlist, which we no 
longer need.
      * Remove it to avoid unnecessary work when we process the targetlist.
      * Note that when we recurse through rewriteQuery a new junk TLE 
will be
      * added to allow the executor to find the proper row in the new target
      * relation.  (So, if we failed to do this, we might have multiple junk
      * TLEs with the same name, which would be disastrous.)
      */
     if (parsetree->commandType != CMD_INSERT)
     {
         TargetEntry *tle = (TargetEntry *) llast(parsetree->targetList);

         Assert(tle->resjunk);
         Assert(IsA(tle->expr, Var) &&
                ((Var *) tle->expr)->varno == parsetree->resultRelation &&
                ((Var *) tle->expr)->varattno == 0);
         parsetree->targetList = list_delete_ptr(parsetree->targetList, 
tle);
     }

Attached is an updated version of the patch.  Comments are welcome!

Best regards,
Etsuro Fujita

[1] 
https://www.postgresql.org/message-id/CAFjFpRfTpamoUz6fNyk6gPh%3DecfBJjbUHJNKbDxscpyPJ3FfjQ%40mail.gmail.com

  30. Re: Bug in ExecModifyTable function and trigger issues for foreign tables

    Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp> — 2017-07-19T10:21:04Z

    On 2017/07/13 21:10, Etsuro Fujita wrote:
> Attached is an updated version of the patch.
Here is an updated version of the patch.  Changes are:

* Modified rewrite_targetlist(), which is a new function added to 
preptlist.c, so that we do const-simplification to junk TLEs that 
AddForeignUpdateTargets() added, as that API allows the FDW to add junk 
TLEs containing non-Var expressions to the query's targetlist.
* Updated docs in fdwhandler.sgml.

Best regards,
Etsuro Fujita

  31. Re: Bug in ExecModifyTable function and trigger issues for foreign tables

    Tom Lane <tgl@sss.pgh.pa.us> — 2017-07-19T14:36:51Z

    Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp> writes:
> * Modified rewrite_targetlist(), which is a new function added to 
> preptlist.c, so that we do const-simplification to junk TLEs that 
> AddForeignUpdateTargets() added, as that API allows the FDW to add junk 
> TLEs containing non-Var expressions to the query's targetlist.

This does not seem like a good idea to me.  eval_const_expressions is not
a cheap thing, and for most use-cases those cycles will be wasted, and it
has never been the responsibility of preprocess_targetlist to do this sort
of thing.

Please put the responsibility of doing const-expression simplification
in these cases somewhere closer to where the problem is being created.

			regards, tom lane

  32. Re: Bug in ExecModifyTable function and trigger issues for foreign tables

    Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp> — 2017-07-20T02:21:57Z

    On 2017/07/19 23:36, Tom Lane wrote:
> Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp> writes:
>> * Modified rewrite_targetlist(), which is a new function added to
>> preptlist.c, so that we do const-simplification to junk TLEs that
>> AddForeignUpdateTargets() added, as that API allows the FDW to add junk
>> TLEs containing non-Var expressions to the query's targetlist.
> 
> This does not seem like a good idea to me.  eval_const_expressions is not
> a cheap thing, and for most use-cases those cycles will be wasted, and it
> has never been the responsibility of preprocess_targetlist to do this sort
> of thing.

Hm, I added that const-simplification to that function so that the 
existing FDWs that append junk TLEs that need const-simplification, 
which I don't know really exist, would work well for this fix, without 
any changes, but I agree on that point.

> Please put the responsibility of doing const-expression simplification
> in these cases somewhere closer to where the problem is being created.

It would be reasonable that it's the FDW's responsibility to do that 
const-simplification if necessary?

Best regards,
Etsuro Fujita

  33. Re: Bug in ExecModifyTable function and trigger issues for foreign tables

    Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp> — 2017-07-21T10:16:13Z

    On 2017/07/20 11:21, Etsuro Fujita wrote:
> On 2017/07/19 23:36, Tom Lane wrote:
>> Please put the responsibility of doing const-expression simplification
>> in these cases somewhere closer to where the problem is being created.
> 
> It would be reasonable that it's the FDW's responsibility to do that 
> const-simplification if necessary?
There seems to be no objections, so I removed the const-expression 
simplification from the patch and I added the note to the docs for 
AddForeignUpdateTargets.

Attached is an updated version of the patch.

Best regards,
Etsuro Fujita

  34. Re: Bug in ExecModifyTable function and trigger issues for foreign tables

    Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp> — 2017-07-24T02:59:13Z

    On 2017/07/21 19:16, Etsuro Fujita wrote:
> On 2017/07/20 11:21, Etsuro Fujita wrote:
>> On 2017/07/19 23:36, Tom Lane wrote:
>>> Please put the responsibility of doing const-expression simplification
>>> in these cases somewhere closer to where the problem is being created.
>>
>> It would be reasonable that it's the FDW's responsibility to do that 
>> const-simplification if necessary?
> There seems to be no objections, so I removed the const-expression 
> simplification from the patch and I added the note to the docs for 
> AddForeignUpdateTargets.
> 
> Attached is an updated version of the patch.

I cleaned up the patch a bit.  PFA a new version of the patch.

Best regards,
Etsuro Fujita

  35. Re: Bug in ExecModifyTable function and trigger issues for foreign tables

    Ildus Kurbangaliev <i.kurbangaliev@postgrespro.ru> — 2017-09-12T15:26:22Z

    On Mon, 24 Jul 2017 11:59:13 +0900
Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp> wrote:

> On 2017/07/21 19:16, Etsuro Fujita wrote:
> > On 2017/07/20 11:21, Etsuro Fujita wrote:  
> >> On 2017/07/19 23:36, Tom Lane wrote:  
> >>> Please put the responsibility of doing const-expression
> >>> simplification in these cases somewhere closer to where the
> >>> problem is being created.  
> >>
> >> It would be reasonable that it's the FDW's responsibility to do
> >> that const-simplification if necessary?  
> > There seems to be no objections, so I removed the const-expression 
> > simplification from the patch and I added the note to the docs for 
> > AddForeignUpdateTargets.
> > 
> > Attached is an updated version of the patch.  
> 
> I cleaned up the patch a bit.  PFA a new version of the patch.
> 
> Best regards,
> Etsuro Fujita

Checked, looks good to me. Changed status to 'Ready for Commiter'.

-- 
---
Ildus Kurbangaliev
Postgres Professional: http://www.postgrespro.com
Russian Postgres Company

  36. Re: [HACKERS] Bug in ExecModifyTable function and trigger issues for foreign tables

    Tom Lane <tgl@sss.pgh.pa.us> — 2017-11-26T22:56:32Z

    Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp> writes:
> [ fix-rewrite-tlist-v4.patch ]

I started reviewing this patch.  I did not much like the fact that it
effectively moved rewriteTargetListUD to a different file and renamed it.
That seems like unnecessary code churn, plus it breaks the analogy with
rewriteTargetListIU, plus it will make back-patching harder (since that
code isn't exactly the same in back branches).  I see little reason why
we can't leave it where it is and just make it non-static.  It's not like
there's no other parts of the rewriter that the planner calls.

I revised the patch along that line, and while at it, refactored
preptlist.c a bit to eliminate repeated heap_opens of the target
relation.  I've not really reviewed any other aspects of the patch
yet, but in the meantime, does anyone object to proceeding this way?

			regards, tom lane

  37. Re: [HACKERS] Bug in ExecModifyTable function and trigger issues for foreign tables

    Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp> — 2017-11-27T05:38:12Z

    (2017/11/27 7:56), Tom Lane wrote:
> Etsuro Fujita<fujita.etsuro@lab.ntt.co.jp>  writes:
>> [ fix-rewrite-tlist-v4.patch ]
>
> I started reviewing this patch.

Great!

> I did not much like the fact that it
> effectively moved rewriteTargetListUD to a different file and renamed it.
> That seems like unnecessary code churn, plus it breaks the analogy with
> rewriteTargetListIU, plus it will make back-patching harder (since that
> code isn't exactly the same in back branches).  I see little reason why
> we can't leave it where it is and just make it non-static.  It's not like
> there's no other parts of the rewriter that the planner calls.

Agreed.

Best regards,
Etsuro Fujita

  38. Re: [HACKERS] Bug in ExecModifyTable function and trigger issues for foreign tables

    Dean Rasheed <dean.a.rasheed@gmail.com> — 2017-11-27T11:19:31Z

    On 26 November 2017 at 22:56, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp> writes:
>> [ fix-rewrite-tlist-v4.patch ]
>
> I started reviewing this patch.  I did not much like the fact that it
> effectively moved rewriteTargetListUD to a different file and renamed it.
> That seems like unnecessary code churn, plus it breaks the analogy with
> rewriteTargetListIU, plus it will make back-patching harder (since that
> code isn't exactly the same in back branches).  I see little reason why
> we can't leave it where it is and just make it non-static.  It's not like
> there's no other parts of the rewriter that the planner calls.
>

I wonder if, years from now, it might look a bit odd that
rewriteTargetListUD() is doing part of work of preptlist.c, is only
called from there, and yet is located in the rewriter.

Aside from having a similar name to rewriteTargetListIU(), what
rewriteTargetListUD() does seems more like what
preprocess_targetlist() does for rowmarks. The fact that
rewriteTargetListIU() intentionally only applies to the parent,
whereas preprocess_targetlist() and now rewriteTargetListUD() apply to
each child, further destroys any similarity between
rewriteTargetListUD() and rewriteTargetListIU().

The point about back-patching is a reasonable one though, so I won't
mind either way.

A separate point -- it might be marginally more efficient to have the
work of rewriteTargetListUD() done after expand_targetlist() to avoid
the possible renumbering of the resjunk entries.

Regards,
Dean

  39. Re: [HACKERS] Bug in ExecModifyTable function and trigger issues for foreign tables

    Tom Lane <tgl@sss.pgh.pa.us> — 2017-11-27T15:49:32Z

    Dean Rasheed <dean.a.rasheed@gmail.com> writes:
> I wonder if, years from now, it might look a bit odd that
> rewriteTargetListUD() is doing part of work of preptlist.c, is only
> called from there, and yet is located in the rewriter.

Yeah, I probably wouldn't have done it like this in a green field,
but maintaining traceability to the existing code is valuable IMO.

> A separate point -- it might be marginally more efficient to have the
> work of rewriteTargetListUD() done after expand_targetlist() to avoid
> the possible renumbering of the resjunk entries.

Hm.  It wouldn't save a lot, but yeah, doing it in this order seems
a bit silly when you put it like that.

			regards, tom lane

  40. Re: [HACKERS] Bug in ExecModifyTable function and trigger issues for foreign tables

    Tom Lane <tgl@sss.pgh.pa.us> — 2017-11-27T16:35:36Z

    I wrote:
> Dean Rasheed <dean.a.rasheed@gmail.com> writes:
>> A separate point -- it might be marginally more efficient to have the
>> work of rewriteTargetListUD() done after expand_targetlist() to avoid
>> the possible renumbering of the resjunk entries.

> Hm.  It wouldn't save a lot, but yeah, doing it in this order seems
> a bit silly when you put it like that.

On looking closer, the reason it's like that in Fujita-san's patch
is to minimize the API churn seen by FDW AddForeignUpdateTargets
functions, specifically whether they see a tlist that's before or
after what expand_targetlist() does.  I'm doubtful that the
potential savings is worth taking risks there.  In particular,
it seems like a good thing that expand_targetlist() verifies the
correct tlist ordering *after* the FDW function has acted.
So now my inclination is to leave this alone.

			regards, tom lane

  41. Re: [HACKERS] Bug in ExecModifyTable function and trigger issues for foreign tables

    Dean Rasheed <dean.a.rasheed@gmail.com> — 2017-11-27T17:22:00Z

    On 27 November 2017 at 16:35, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I wrote:
>> Dean Rasheed <dean.a.rasheed@gmail.com> writes:
>>> A separate point -- it might be marginally more efficient to have the
>>> work of rewriteTargetListUD() done after expand_targetlist() to avoid
>>> the possible renumbering of the resjunk entries.
>
>> Hm.  It wouldn't save a lot, but yeah, doing it in this order seems
>> a bit silly when you put it like that.
>
> On looking closer, the reason it's like that in Fujita-san's patch
> is to minimize the API churn seen by FDW AddForeignUpdateTargets
> functions, specifically whether they see a tlist that's before or
> after what expand_targetlist() does.  I'm doubtful that the
> potential savings is worth taking risks there.  In particular,
> it seems like a good thing that expand_targetlist() verifies the
> correct tlist ordering *after* the FDW function has acted.
> So now my inclination is to leave this alone.
>

Ah yes, that seems like a worthwhile check to keep. Never mind then.

Regards,
Dean

  42. Re: [HACKERS] Bug in ExecModifyTable function and trigger issues for foreign tables

    Tom Lane <tgl@sss.pgh.pa.us> — 2017-11-27T22:58:23Z

    Dean Rasheed <dean.a.rasheed@gmail.com> writes:
> On 27 November 2017 at 16:35, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> On looking closer, the reason it's like that in Fujita-san's patch
>> is to minimize the API churn seen by FDW AddForeignUpdateTargets
>> functions, specifically whether they see a tlist that's before or
>> after what expand_targetlist() does.  I'm doubtful that the
>> potential savings is worth taking risks there.  In particular,
>> it seems like a good thing that expand_targetlist() verifies the
>> correct tlist ordering *after* the FDW function has acted.
>> So now my inclination is to leave this alone.

> Ah yes, that seems like a worthwhile check to keep. Never mind then.

Pushed with that and some cosmetic fiddling with comments and docs.
Thanks for the discussion!

			regards, tom lane

  43. Re: [HACKERS] Bug in ExecModifyTable function and trigger issues for foreign tables

    Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp> — 2017-11-28T01:55:37Z

    (2017/11/28 7:58), Tom Lane wrote:
> Pushed with that and some cosmetic fiddling with comments and docs.

Thank you.

Best regards,
Etsuro Fujita