Thread: [HACKERS] Bug in ExecModifyTable function and trigger issues for foreigntables
[HACKERS] Bug in ExecModifyTable function and trigger issues for foreigntables
From
Ildus Kurbangaliev
Date:
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Attachment
Re: [HACKERS] Bug in ExecModifyTable function and trigger issues forforeign tables
From
Dilip Kumar
Date:
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
theplanSlot. 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
Re: [HACKERS] Bug in ExecModifyTable function and trigger issues forforeign tables
From
Dilip Kumar
Date:
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Attachment
Re: [HACKERS] Bug in ExecModifyTable function and trigger issues forforeign tables
From
Michael Paquier
Date:
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
Re: [HACKERS] Bug in ExecModifyTable function and trigger issuesfor foreign tables
From
Ildus Kurbangaliev
Date:
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
Re: [HACKERS] Bug in ExecModifyTable function and trigger issues forforeign tables
From
Dilip Kumar
Date:
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
Re: [HACKERS] Bug in ExecModifyTable function and trigger issues forforeign tables
From
Ashutosh Bapat
Date:
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
Re: [HACKERS] Bug in ExecModifyTable function and trigger issues forforeign tables
From
Dilip Kumar
Date:
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
Re: [HACKERS] Bug in ExecModifyTable function and trigger issues forforeign tables
From
Ashutosh Bapat
Date:
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
Re: [HACKERS] Bug in ExecModifyTable function and trigger issuesfor foreign tables
From
Ildus Kurbangaliev
Date:
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
Re: [HACKERS] Bug in ExecModifyTable function and trigger issues forforeign tables
From
Ashutosh Bapat
Date:
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.63rows=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.83rows=255 width=10) Output: random(), parent.ctid -> Seq Scan on public.child1 (cost=0.00..48.25rows=2550 width=10) Output: random(), child1.ctid -> Foreign Scan on public.ftable (cost=100.00..205.55rows=2730 width=10) Output: random(), ftable.ctid Remote SQL: SELECT ctid FROM public.child1FOR UPDATE (12 rows) explain verbose update ftable set val = random(); QUERY PLAN -------------------------------------------------------------------------------Update on public.ftable (cost=100.00..159.42rows=1412 width=38) Remote SQL: UPDATE public.child1 SET val = $2 WHERE ctid = $1 -> Foreign Scanon 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
Re: [HACKERS] Bug in ExecModifyTable function and trigger issuesfor foreign tables
From
Ildus Kurbangaliev
Date:
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
Re: [HACKERS] Bug in ExecModifyTable function and trigger issues forforeign tables
From
Ashutosh Bapat
Date:
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
Re: [HACKERS] Bug in ExecModifyTable function and trigger issues forforeign tables
From
Etsuro Fujita
Date:
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Attachment
Re: [HACKERS] Bug in ExecModifyTable function and trigger issuesfor foreign tables
From
Ildus Kurbangaliev
Date:
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
Re: [HACKERS] Bug in ExecModifyTable function and trigger issues forforeign tables
From
Michael Paquier
Date:
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
Re: [HACKERS] Bug in ExecModifyTable function and trigger issuesfor foreign tables
From
Ildus Kurbangaliev
Date:
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.63rows=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
Re: [HACKERS] Bug in ExecModifyTable function and trigger issues forforeign tables
From
Etsuro Fujita
Date:
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
Re: [HACKERS] Bug in ExecModifyTable function and trigger issues forforeign tables
From
Etsuro Fujita
Date:
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
Re: [HACKERS] Bug in ExecModifyTable function and trigger issues forforeign tables
From
Etsuro Fujita
Date:
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Attachment
Re: [HACKERS] Bug in ExecModifyTable function and trigger issues forforeign tables
From
Ashutosh Bapat
Date:
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
Re: [HACKERS] Bug in ExecModifyTable function and trigger issues forforeign tables
From
Etsuro Fujita
Date:
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
Re: [HACKERS] Bug in ExecModifyTable function and trigger issuesfor foreign tables
From
Ildus Kurbangaliev
Date:
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
Re: [HACKERS] Bug in ExecModifyTable function and trigger issues forforeign tables
From
Etsuro Fujita
Date:
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Attachment
Re: [HACKERS] Bug in ExecModifyTable function and trigger issues forforeign tables
From
Ashutosh Bapat
Date:
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
Re: [HACKERS] Bug in ExecModifyTable function and trigger issues forforeign tables
From
Etsuro Fujita
Date:
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
Re: [HACKERS] Bug in ExecModifyTable function and trigger issues forforeign tables
From
Etsuro Fujita
Date:
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
Re: [HACKERS] Bug in ExecModifyTable function and trigger issues forforeign tables
From
Etsuro Fujita
Date:
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
Re: [HACKERS] Bug in ExecModifyTable function and trigger issues forforeign tables
From
Etsuro Fujita
Date:
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Attachment
Re: [HACKERS] Bug in ExecModifyTable function and trigger issues forforeign tables
From
Etsuro Fujita
Date:
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Attachment
Re: [HACKERS] Bug in ExecModifyTable function and trigger issues for foreign tables
From
Tom Lane
Date:
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
Re: [HACKERS] Bug in ExecModifyTable function and trigger issues forforeign tables
From
Etsuro Fujita
Date:
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
Re: [HACKERS] Bug in ExecModifyTable function and trigger issues forforeign tables
From
Etsuro Fujita
Date:
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Attachment
Re: [HACKERS] Bug in ExecModifyTable function and trigger issues forforeign tables
From
Etsuro Fujita
Date:
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Attachment
Re: [HACKERS] Bug in ExecModifyTable function and trigger issuesfor foreign tables
From
Ildus Kurbangaliev
Date:
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Bug in ExecModifyTable function and trigger issues for foreign tables
From
Tom Lane
Date:
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
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 4339bbf..f90a6cf 100644
*** a/contrib/postgres_fdw/expected/postgres_fdw.out
--- b/contrib/postgres_fdw/expected/postgres_fdw.out
*************** update bar set f2 = f2 + 100 returning *
*** 7022,7027 ****
--- 7022,7086 ----
7 | 277
(6 rows)
+ -- Test that UPDATE/DELETE with inherited target works with row-level triggers
+ CREATE TRIGGER trig_row_before
+ BEFORE UPDATE OR DELETE ON bar2
+ FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
+ CREATE TRIGGER trig_row_after
+ AFTER UPDATE OR DELETE ON bar2
+ FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
+ explain (verbose, costs off)
+ update bar set f2 = f2 + 100;
+ QUERY PLAN
+ --------------------------------------------------------------------------------------
+ Update on public.bar
+ Update on public.bar
+ Foreign Update on public.bar2
+ Remote SQL: UPDATE public.loct2 SET f2 = $2 WHERE ctid = $1 RETURNING f1, f2, f3
+ -> Seq Scan on public.bar
+ Output: bar.f1, (bar.f2 + 100), bar.ctid
+ -> Foreign Scan on public.bar2
+ Output: bar2.f1, (bar2.f2 + 100), bar2.f3, bar2.ctid, bar2.*
+ Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR UPDATE
+ (9 rows)
+
+ update bar set f2 = f2 + 100;
+ NOTICE: trig_row_before(23, skidoo) BEFORE ROW UPDATE ON bar2
+ NOTICE: OLD: (3,333,33),NEW: (3,433,33)
+ NOTICE: trig_row_before(23, skidoo) BEFORE ROW UPDATE ON bar2
+ NOTICE: OLD: (4,344,44),NEW: (4,444,44)
+ NOTICE: trig_row_before(23, skidoo) BEFORE ROW UPDATE ON bar2
+ NOTICE: OLD: (7,277,77),NEW: (7,377,77)
+ NOTICE: trig_row_after(23, skidoo) AFTER ROW UPDATE ON bar2
+ NOTICE: OLD: (3,333,33),NEW: (3,433,33)
+ NOTICE: trig_row_after(23, skidoo) AFTER ROW UPDATE ON bar2
+ NOTICE: OLD: (4,344,44),NEW: (4,444,44)
+ NOTICE: trig_row_after(23, skidoo) AFTER ROW UPDATE ON bar2
+ NOTICE: OLD: (7,277,77),NEW: (7,377,77)
+ explain (verbose, costs off)
+ delete from bar where f2 < 400;
+ QUERY PLAN
+ ---------------------------------------------------------------------------------------------
+ Delete on public.bar
+ Delete on public.bar
+ Foreign Delete on public.bar2
+ Remote SQL: DELETE FROM public.loct2 WHERE ctid = $1 RETURNING f1, f2, f3
+ -> Seq Scan on public.bar
+ Output: bar.ctid
+ Filter: (bar.f2 < 400)
+ -> Foreign Scan on public.bar2
+ Output: bar2.ctid, bar2.*
+ Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 WHERE ((f2 < 400)) FOR UPDATE
+ (10 rows)
+
+ delete from bar where f2 < 400;
+ NOTICE: trig_row_before(23, skidoo) BEFORE ROW DELETE ON bar2
+ NOTICE: OLD: (7,377,77)
+ NOTICE: trig_row_after(23, skidoo) AFTER ROW DELETE ON bar2
+ NOTICE: OLD: (7,377,77)
+ -- cleanup
+ DROP TRIGGER trig_row_before ON bar2;
+ DROP TRIGGER trig_row_after ON bar2;
drop table foo cascade;
NOTICE: drop cascades to foreign table foo2
drop table bar cascade;
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index ddfec79..8e1bd89 100644
*** a/contrib/postgres_fdw/sql/postgres_fdw.sql
--- b/contrib/postgres_fdw/sql/postgres_fdw.sql
*************** explain (verbose, costs off)
*** 1656,1661 ****
--- 1656,1681 ----
update bar set f2 = f2 + 100 returning *;
update bar set f2 = f2 + 100 returning *;
+ -- Test that UPDATE/DELETE with inherited target works with row-level triggers
+ CREATE TRIGGER trig_row_before
+ BEFORE UPDATE OR DELETE ON bar2
+ FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
+
+ CREATE TRIGGER trig_row_after
+ AFTER UPDATE OR DELETE ON bar2
+ FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
+
+ explain (verbose, costs off)
+ update bar set f2 = f2 + 100;
+ update bar set f2 = f2 + 100;
+
+ explain (verbose, costs off)
+ delete from bar where f2 < 400;
+ delete from bar where f2 < 400;
+
+ -- cleanup
+ DROP TRIGGER trig_row_before ON bar2;
+ DROP TRIGGER trig_row_after ON bar2;
drop table foo cascade;
drop table bar cascade;
drop table loct1;
diff --git a/doc/src/sgml/fdwhandler.sgml b/doc/src/sgml/fdwhandler.sgml
index a2f8137..e610fa0 100644
*** a/doc/src/sgml/fdwhandler.sgml
--- b/doc/src/sgml/fdwhandler.sgml
*************** AddForeignUpdateTargets(Query *parsetree
*** 428,438 ****
Avoid using names matching <literal>ctid<replaceable>N</replaceable></literal>,
<literal>wholerow</literal>, or
<literal>wholerow<replaceable>N</replaceable></literal>, as the core system can
! generate junk columns of these names.
</para>
<para>
! This function is called in the rewriter, not the planner, so the
information available is a bit different from that available to the
planning routines.
<literal>parsetree</literal> is the parse tree for the <command>UPDATE</command> or
--- 428,440 ----
Avoid using names matching <literal>ctid<replaceable>N</replaceable></literal>,
<literal>wholerow</literal>, or
<literal>wholerow<replaceable>N</replaceable></literal>, as the core system can
! generate junk columns of these names. Also, as it assumes that those
! expressions have already been simplified enough to execute,
! apply <function>eval_const_expressions</function> if necessary.
</para>
<para>
! Although this function is called in the planner, the
information available is a bit different from that available to the
planning routines.
<literal>parsetree</literal> is the parse tree for the <command>UPDATE</command> or
diff --git a/doc/src/sgml/rules.sgml b/doc/src/sgml/rules.sgml
index 2074fcc..3372b1a 100644
*** a/doc/src/sgml/rules.sgml
--- b/doc/src/sgml/rules.sgml
***************
*** 167,178 ****
<para>
<command>DELETE</command> commands don't need a normal target list
! because they don't produce any result. Instead, the rule system
adds a special <acronym>CTID</acronym> entry to the empty target list,
to allow the executor to find the row to be deleted.
(<acronym>CTID</acronym> is added when the result relation is an ordinary
! table. If it is a view, a whole-row variable is added instead,
! as described in <xref linkend="rules-views-update"/>.)
</para>
<para>
--- 167,178 ----
<para>
<command>DELETE</command> commands don't need a normal target list
! because they don't produce any result. Instead, the planner
adds a special <acronym>CTID</acronym> entry to the empty target list,
to allow the executor to find the row to be deleted.
(<acronym>CTID</acronym> is added when the result relation is an ordinary
! table. If it is a view, a whole-row variable is added instead, by
! the rule system, as described in <xref linkend="rules-views-update"/>.)
</para>
<para>
***************
*** 194,200 ****
column = expression</literal> part of the command. The planner will
handle missing columns by inserting expressions that copy the values
from the old row into the new one. Just as for <command>DELETE</command>,
! the rule system adds a <acronym>CTID</acronym> or whole-row variable so that
the executor can identify the old row to be updated.
</para>
--- 194,200 ----
column = expression</literal> part of the command. The planner will
handle missing columns by inserting expressions that copy the values
from the old row into the new one. Just as for <command>DELETE</command>,
! a <acronym>CTID</acronym> or whole-row variable is added so that
the executor can identify the old row to be updated.
</para>
diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c
index 503b89f..1f5ef02 100644
*** a/src/backend/executor/nodeModifyTable.c
--- b/src/backend/executor/nodeModifyTable.c
*************** ExecModifyTable(PlanState *pstate)
*** 1699,1704 ****
--- 1699,1705 ----
EvalPlanQualSetSlot(&node->mt_epqstate, planSlot);
slot = planSlot;
+ tupleid = NULL;
oldtuple = NULL;
if (junkfilter != NULL)
{
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index f6b8bbf..ef2eaea 100644
*** a/src/backend/optimizer/plan/planner.c
--- b/src/backend/optimizer/plan/planner.c
*************** grouping_planner(PlannerInfo *root, bool
*** 1555,1561 ****
double tuple_fraction)
{
Query *parse = root->parse;
! List *tlist = parse->targetList;
int64 offset_est = 0;
int64 count_est = 0;
double limit_tuples = -1.0;
--- 1555,1561 ----
double tuple_fraction)
{
Query *parse = root->parse;
! List *tlist;
int64 offset_est = 0;
int64 count_est = 0;
double limit_tuples = -1.0;
*************** grouping_planner(PlannerInfo *root, bool
*** 1685,1697 ****
}
/* Preprocess targetlist */
! tlist = preprocess_targetlist(root, tlist);
!
! if (parse->onConflict)
! parse->onConflict->onConflictSet =
! preprocess_onconflict_targetlist(parse->onConflict->onConflictSet,
! parse->resultRelation,
! parse->rtable);
/*
* We are now done hacking up the query's targetlist. Most of the
--- 1685,1691 ----
}
/* Preprocess targetlist */
! tlist = preprocess_targetlist(root);
/*
* We are now done hacking up the query's targetlist. Most of the
diff --git a/src/backend/optimizer/prep/preptlist.c b/src/backend/optimizer/prep/preptlist.c
index d7db32e..44f3ae8 100644
*** a/src/backend/optimizer/prep/preptlist.c
--- b/src/backend/optimizer/prep/preptlist.c
***************
*** 4,23 ****
* Routines to preprocess the parse tree target list
*
* For INSERT and UPDATE queries, the targetlist must contain an entry for
! * each attribute of the target relation in the correct order. For all query
! * types, we may need to add junk tlist entries for Vars used in the RETURNING
! * list and row ID information needed for SELECT FOR UPDATE locking and/or
! * EvalPlanQual checking.
*
! * The rewriter's rewriteTargetListIU and rewriteTargetListUD routines
! * also do preprocessing of the targetlist. The division of labor between
! * here and there is partially historical, but it's not entirely arbitrary.
! * In particular, consider an UPDATE across an inheritance tree. What the
! * rewriter does need be done only once (because it depends only on the
! * properties of the parent relation). What's done here has to be done over
! * again for each child relation, because it depends on the column list of
! * the child, which might have more columns and/or a different column order
! * than the parent.
*
* The fact that rewriteTargetListIU sorts non-resjunk tlist entries by column
* position, which expand_targetlist depends on, violates the above comment
--- 4,25 ----
* Routines to preprocess the parse tree target list
*
* For INSERT and UPDATE queries, the targetlist must contain an entry for
! * each attribute of the target relation in the correct order. For UPDATE and
! * DELETE queries, it must also contain a junk tlist entry needed to allow the
! * executor to identify the physical locations of the rows to be updated or
! * deleted. For all query types, we may need to add junk tlist entries for
! * Vars used in the RETURNING list and row ID information needed for SELECT
! * FOR UPDATE locking and/or EvalPlanQual checking.
*
! * The rewriter's rewriteTargetListIU routine also does preprocessing of the
! * targetlist. The division of labor between here and there is partially
! * historical, but it's not entirely arbitrary. In particular, consider an
! * UPDATE across an inheritance tree. What rewriteTargetListIU does need be
! * done only once (because it depends only on the properties of the parent
! * relation). What's done here has to be done over again for each child
! * relation, because it depends on the relation type and column list of the
! * child, which might be of a different type, or have more columns and/or a
! * different column order than the parent.
*
* The fact that rewriteTargetListIU sorts non-resjunk tlist entries by column
* position, which expand_targetlist depends on, violates the above comment
***************
*** 47,57 ****
#include "optimizer/var.h"
#include "parser/parsetree.h"
#include "parser/parse_coerce.h"
#include "utils/rel.h"
static List *expand_targetlist(List *tlist, int command_type,
! Index result_relation, List *range_table);
/*
--- 49,60 ----
#include "optimizer/var.h"
#include "parser/parsetree.h"
#include "parser/parse_coerce.h"
+ #include "rewrite/rewriteHandler.h"
#include "utils/rel.h"
static List *expand_targetlist(List *tlist, int command_type,
! Index result_relation, Relation rel);
/*
*************** static List *expand_targetlist(List *tli
*** 59,94 ****
* Driver for preprocessing the parse tree targetlist.
*
* Returns the new targetlist.
*/
List *
! preprocess_targetlist(PlannerInfo *root, List *tlist)
{
Query *parse = root->parse;
int result_relation = parse->resultRelation;
List *range_table = parse->rtable;
CmdType command_type = parse->commandType;
ListCell *lc;
/*
! * Sanity check: if there is a result relation, it'd better be a real
! * relation not a subquery. Else parser or rewriter messed up.
*/
if (result_relation)
{
! RangeTblEntry *rte = rt_fetch(result_relation, range_table);
! if (rte->subquery != NULL || rte->relid == InvalidOid)
! elog(ERROR, "subquery cannot be result relation");
}
/*
* for heap_form_tuple to work, the targetlist must match the exact order
* of the attributes. We also need to fill in any missing attributes. -ay
* 10/94
*/
if (command_type == CMD_INSERT || command_type == CMD_UPDATE)
tlist = expand_targetlist(tlist, command_type,
! result_relation, range_table);
/*
* Add necessary junk columns for rowmarked rels. These values are needed
--- 62,120 ----
* Driver for preprocessing the parse tree targetlist.
*
* Returns the new targetlist.
+ *
+ * As a side effect, if there's an ON CONFLICT UPDATE clause, its targetlist
+ * is also preprocessed (and updated in-place).
*/
List *
! preprocess_targetlist(PlannerInfo *root)
{
Query *parse = root->parse;
int result_relation = parse->resultRelation;
List *range_table = parse->rtable;
CmdType command_type = parse->commandType;
+ RangeTblEntry *target_rte = NULL;
+ Relation target_relation = NULL;
+ List *tlist;
ListCell *lc;
/*
! * If there is a result relation, open it so we can look for missing
! * columns and so on. We assume that previous code already acquired at
! * least AccessShareLock on the relation, so we need no lock here.
*/
if (result_relation)
{
! target_rte = rt_fetch(result_relation, range_table);
! /*
! * Sanity check: it'd better be a real relation not, say, a subquery.
! * Else parser or rewriter messed up.
! */
! if (target_rte->rtekind != RTE_RELATION)
! elog(ERROR, "result relation must be a regular relation");
!
! target_relation = heap_open(target_rte->relid, NoLock);
}
+ else
+ Assert(command_type == CMD_SELECT);
+
+ /*
+ * For UPDATE/DELETE, add any junk column(s) needed to allow the executor
+ * to identify the rows to be updated or deleted.
+ */
+ if (command_type == CMD_UPDATE || command_type == CMD_DELETE)
+ rewriteTargetListUD(parse, target_rte, target_relation);
/*
* for heap_form_tuple to work, the targetlist must match the exact order
* of the attributes. We also need to fill in any missing attributes. -ay
* 10/94
*/
+ tlist = parse->targetList;
if (command_type == CMD_INSERT || command_type == CMD_UPDATE)
tlist = expand_targetlist(tlist, command_type,
! result_relation, target_relation);
/*
* Add necessary junk columns for rowmarked rels. These values are needed
*************** preprocess_targetlist(PlannerInfo *root,
*** 193,211 ****
list_free(vars);
}
! return tlist;
! }
! /*
! * preprocess_onconflict_targetlist
! * Process ON CONFLICT SET targetlist.
! *
! * Returns the new targetlist.
! */
! List *
! preprocess_onconflict_targetlist(List *tlist, int result_relation, List *range_table)
! {
! return expand_targetlist(tlist, CMD_UPDATE, result_relation, range_table);
}
--- 219,239 ----
list_free(vars);
}
! /*
! * If there's an ON CONFLICT UPDATE clause, preprocess its targetlist too
! * while we have the relation open.
! */
! if (parse->onConflict)
! parse->onConflict->onConflictSet =
! expand_targetlist(parse->onConflict->onConflictSet,
! CMD_UPDATE,
! result_relation,
! target_relation);
! if (target_relation)
! heap_close(target_relation, NoLock);
!
! return tlist;
}
*************** preprocess_onconflict_targetlist(List *t
*** 223,233 ****
*/
static List *
expand_targetlist(List *tlist, int command_type,
! Index result_relation, List *range_table)
{
List *new_tlist = NIL;
ListCell *tlist_item;
- Relation rel;
int attrno,
numattrs;
--- 251,260 ----
*/
static List *
expand_targetlist(List *tlist, int command_type,
! Index result_relation, Relation rel)
{
List *new_tlist = NIL;
ListCell *tlist_item;
int attrno,
numattrs;
*************** expand_targetlist(List *tlist, int comma
*** 238,249 ****
* order; but we have to insert TLEs for any missing attributes.
*
* Scan the tuple description in the relation's relcache entry to make
! * sure we have all the user attributes in the right order. We assume
! * that the rewriter already acquired at least AccessShareLock on the
! * relation, so we need no lock here.
*/
- rel = heap_open(getrelid(result_relation, range_table), NoLock);
-
numattrs = RelationGetNumberOfAttributes(rel);
for (attrno = 1; attrno <= numattrs; attrno++)
--- 265,272 ----
* order; but we have to insert TLEs for any missing attributes.
*
* Scan the tuple description in the relation's relcache entry to make
! * sure we have all the user attributes in the right order.
*/
numattrs = RelationGetNumberOfAttributes(rel);
for (attrno = 1; attrno <= numattrs; attrno++)
*************** expand_targetlist(List *tlist, int comma
*** 386,393 ****
tlist_item = lnext(tlist_item);
}
- heap_close(rel, NoLock);
-
return new_tlist;
}
--- 409,414 ----
diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c
index c2bc3ad..65f1b8e 100644
*** a/src/backend/rewrite/rewriteHandler.c
--- b/src/backend/rewrite/rewriteHandler.c
*************** static TargetEntry *process_matched_tle(
*** 72,79 ****
static Node *get_assignment_input(Node *node);
static void rewriteValuesRTE(RangeTblEntry *rte, Relation target_relation,
List *attrnos);
- static void rewriteTargetListUD(Query *parsetree, RangeTblEntry *target_rte,
- Relation target_relation);
static void markQueryForLocking(Query *qry, Node *jtnode,
LockClauseStrength strength, LockWaitPolicy waitPolicy,
bool pushedDown);
--- 72,77 ----
*************** rewriteValuesRTE(RangeTblEntry *rte, Rel
*** 1293,1306 ****
* This function adds a "junk" TLE that is needed to allow the executor to
* find the original row for the update or delete. When the target relation
* is a regular table, the junk TLE emits the ctid attribute of the original
! * row. When the target relation is a view, there is no ctid, so we instead
! * emit a whole-row Var that will contain the "old" values of the view row.
! * If it's a foreign table, we let the FDW decide what to add.
*
! * For UPDATE queries, this is applied after rewriteTargetListIU. The
! * ordering isn't actually critical at the moment.
*/
! static void
rewriteTargetListUD(Query *parsetree, RangeTblEntry *target_rte,
Relation target_relation)
{
--- 1291,1305 ----
* This function adds a "junk" TLE that is needed to allow the executor to
* find the original row for the update or delete. When the target relation
* is a regular table, the junk TLE emits the ctid attribute of the original
! * row. When the target relation is a foreign table, we let the FDW decide
! * what to add.
*
! * We used to do this during RewriteQuery(), but now that inheritance trees
! * can contain a mix of regular and foreign tables, we must postpone it till
! * planning, after the inheritance tree has been expanded. In that way we
! * can do the right thing for each child table.
*/
! void
rewriteTargetListUD(Query *parsetree, RangeTblEntry *target_rte,
Relation target_relation)
{
*************** rewriteTargetListUD(Query *parsetree, Ra
*** 1358,1376 ****
attrname = "wholerow";
}
}
- else
- {
- /*
- * Emit whole-row Var so that executor will have the "old" view row to
- * pass to the INSTEAD OF trigger.
- */
- var = makeWholeRowVar(target_rte,
- parsetree->resultRelation,
- 0,
- false);
-
- attrname = "wholerow";
- }
if (var != NULL)
{
--- 1357,1362 ----
*************** ApplyRetrieveRule(Query *parsetree,
*** 1497,1502 ****
--- 1483,1490 ----
parsetree->commandType == CMD_DELETE)
{
RangeTblEntry *newrte;
+ Var *var;
+ TargetEntry *tle;
rte = rt_fetch(rt_index, parsetree->rtable);
newrte = copyObject(rte);
*************** ApplyRetrieveRule(Query *parsetree,
*** 1527,1532 ****
--- 1515,1533 ----
ChangeVarNodes((Node *) parsetree->returningList, rt_index,
parsetree->resultRelation, 0);
+ /*
+ * To allow the executor to find the original view row to pass to
+ * the INSTEAD OF trigger, we add a whole-row reference to the
+ * original RTE to the query's targetlist.
+ */
+ var = makeWholeRowVar(rte, rt_index, 0, false);
+ tle = makeTargetEntry((Expr *) var,
+ list_length(parsetree->targetList) + 1,
+ pstrdup("wholerow"),
+ true);
+
+ parsetree->targetList = lappend(parsetree->targetList, tle);
+
/* Now, continue with expanding the original view RTE */
}
else
*************** rewriteTargetView(Query *parsetree, Rela
*** 2967,2992 ****
view_rte->securityQuals = NIL;
/*
- * 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);
- }
-
- /*
* Now update all Vars in the outer query that reference the view to
* reference the appropriate column of the base relation instead.
*/
--- 2968,2973 ----
*************** RewriteQuery(Query *parsetree, List *rew
*** 3347,3357 ****
parsetree->override,
rt_entry_relation,
parsetree->resultRelation, NULL);
- rewriteTargetListUD(parsetree, rt_entry, rt_entry_relation);
}
else if (event == CMD_DELETE)
{
! rewriteTargetListUD(parsetree, rt_entry, rt_entry_relation);
}
else
elog(ERROR, "unrecognized commandType: %d", (int) event);
--- 3328,3337 ----
parsetree->override,
rt_entry_relation,
parsetree->resultRelation, NULL);
}
else if (event == CMD_DELETE)
{
! /* Nothing to do here */
}
else
elog(ERROR, "unrecognized commandType: %d", (int) event);
diff --git a/src/include/optimizer/prep.h b/src/include/optimizer/prep.h
index 80fbfd6..804c9e3 100644
*** a/src/include/optimizer/prep.h
--- b/src/include/optimizer/prep.h
*************** extern Expr *canonicalize_qual(Expr *qua
*** 38,47 ****
/*
* prototypes for preptlist.c
*/
! extern List *preprocess_targetlist(PlannerInfo *root, List *tlist);
!
! extern List *preprocess_onconflict_targetlist(List *tlist,
! int result_relation, List *range_table);
extern PlanRowMark *get_plan_rowmark(List *rowmarks, Index rtindex);
--- 38,44 ----
/*
* prototypes for preptlist.c
*/
! extern List *preprocess_targetlist(PlannerInfo *root);
extern PlanRowMark *get_plan_rowmark(List *rowmarks, Index rtindex);
diff --git a/src/include/rewrite/rewriteHandler.h b/src/include/rewrite/rewriteHandler.h
index 494fa29..86ae571 100644
*** a/src/include/rewrite/rewriteHandler.h
--- b/src/include/rewrite/rewriteHandler.h
*************** extern void AcquireRewriteLocks(Query *p
*** 23,28 ****
--- 23,31 ----
bool forUpdatePushedDown);
extern Node *build_column_default(Relation rel, int attrno);
+ extern void rewriteTargetListUD(Query *parsetree, RangeTblEntry *target_rte,
+ Relation target_relation);
+
extern Query *get_view_query(Relation view);
extern const char *view_query_is_auto_updatable(Query *viewquery,
bool check_cols);
Re: [HACKERS] Bug in ExecModifyTable function and trigger issuesfor foreign tables
From
Etsuro Fujita
Date:
(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
Re: [HACKERS] Bug in ExecModifyTable function and trigger issues forforeign tables
From
Dean Rasheed
Date:
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
Re: [HACKERS] Bug in ExecModifyTable function and trigger issues for foreign tables
From
Tom Lane
Date:
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
Re: [HACKERS] Bug in ExecModifyTable function and trigger issues for foreign tables
From
Tom Lane
Date:
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
Re: [HACKERS] Bug in ExecModifyTable function and trigger issues forforeign tables
From
Dean Rasheed
Date:
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
Re: [HACKERS] Bug in ExecModifyTable function and trigger issues for foreign tables
From
Tom Lane
Date:
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
Re: [HACKERS] Bug in ExecModifyTable function and trigger issuesfor foreign tables
From
Etsuro Fujita
Date:
(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