pg.ddx.io  pgsql-committers@postgresql.org mailing list archive  
help / color / mirror / Atom feed
pgsql: Fix problems when a plain-inheritance parent table is excluded.
4+ messages / 1 participants
[nested] [flat]

* pgsql: Fix problems when a plain-inheritance parent table is excluded.
@ 2023-10-24 18:48  Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 4+ messages in thread

From: Tom Lane @ 2023-10-24 18:48 UTC (permalink / raw)
  To: pgsql-committers@lists.postgresql.org

Fix problems when a plain-inheritance parent table is excluded.

When an UPDATE/DELETE/MERGE's target table is an old-style
inheritance tree, it's possible for the parent to get excluded
from the plan while some children are not.  (I believe this is
only possible if we can prove that a CHECK ... NO INHERIT
constraint on the parent contradicts the query WHERE clause,
so it's a very unusual case.)  In such a case, ExecInitModifyTable
mistakenly concluded that the first surviving child is the target
table, leading to at least two bugs:

1. The wrong table's statement-level triggers would get fired.

2. In v16 and up, it was possible to fail with "invalid perminfoindex
0 in RTE with relid nnnn" due to the child RTE not having permissions
data included in the query plan.  This was hard to reproduce reliably
because it did not occur unless the update triggered some non-HOT
index updates.

In v14 and up, this is easy to fix by defining ModifyTable.rootRelation
to be the parent RTE in plain inheritance as well as partitioned cases.

While the wrong-triggers bug also appears in older branches, the
relevant code in both the planner and executor is quite a bit
different, so it would take a good deal of effort to develop and
test a suitable patch.  Given the lack of field complaints about the
trigger issue, I'll desist for now.  (Patching v11 for this seems
unwise anyway, given that it will have no more releases after next
month.)

Per bug #18147 from Hans Buschmann.

Amit Langote and Tom Lane

Discussion: https://postgr.es/m/18147-6fc796538913ee88@postgresql.org

Branch
------
REL_15_STABLE

Details
-------
https://git.postgresql.org/pg/commitdiff/1268e7378123a4fccc73f709fb0cb0021a2e8d5a

Modified Files
--------------
src/backend/executor/nodeModifyTable.c |  9 +++++----
src/backend/optimizer/plan/planner.c   | 14 ++++----------
src/backend/optimizer/util/pathnode.c  |  2 +-
src/include/nodes/pathnodes.h          |  2 +-
src/include/nodes/plannodes.h          | 13 +++++++------
src/test/regress/expected/inherit.out  | 27 +++++++++++++++++++++++++++
src/test/regress/sql/inherit.sql       | 19 +++++++++++++++++++
7 files changed, 64 insertions(+), 22 deletions(-)



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* pgsql: Fix problems when a plain-inheritance parent table is excluded.
@ 2023-10-24 18:48  Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 4+ messages in thread

From: Tom Lane @ 2023-10-24 18:48 UTC (permalink / raw)
  To: pgsql-committers@lists.postgresql.org

Fix problems when a plain-inheritance parent table is excluded.

When an UPDATE/DELETE/MERGE's target table is an old-style
inheritance tree, it's possible for the parent to get excluded
from the plan while some children are not.  (I believe this is
only possible if we can prove that a CHECK ... NO INHERIT
constraint on the parent contradicts the query WHERE clause,
so it's a very unusual case.)  In such a case, ExecInitModifyTable
mistakenly concluded that the first surviving child is the target
table, leading to at least two bugs:

1. The wrong table's statement-level triggers would get fired.

2. In v16 and up, it was possible to fail with "invalid perminfoindex
0 in RTE with relid nnnn" due to the child RTE not having permissions
data included in the query plan.  This was hard to reproduce reliably
because it did not occur unless the update triggered some non-HOT
index updates.

In v14 and up, this is easy to fix by defining ModifyTable.rootRelation
to be the parent RTE in plain inheritance as well as partitioned cases.

While the wrong-triggers bug also appears in older branches, the
relevant code in both the planner and executor is quite a bit
different, so it would take a good deal of effort to develop and
test a suitable patch.  Given the lack of field complaints about the
trigger issue, I'll desist for now.  (Patching v11 for this seems
unwise anyway, given that it will have no more releases after next
month.)

Per bug #18147 from Hans Buschmann.

Amit Langote and Tom Lane

Discussion: https://postgr.es/m/18147-6fc796538913ee88@postgresql.org

Branch
------
REL_16_STABLE

Details
-------
https://git.postgresql.org/pg/commitdiff/b1444a09dcb5dc3c2898ee5f35a4093710dc6be6

Modified Files
--------------
src/backend/executor/nodeModifyTable.c |  9 +++++----
src/backend/optimizer/plan/planner.c   | 14 ++++----------
src/backend/optimizer/util/pathnode.c  |  2 +-
src/include/nodes/pathnodes.h          |  2 +-
src/include/nodes/plannodes.h          | 13 +++++++------
src/test/regress/expected/inherit.out  | 27 +++++++++++++++++++++++++++
src/test/regress/sql/inherit.sql       | 19 +++++++++++++++++++
7 files changed, 64 insertions(+), 22 deletions(-)



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* pgsql: Fix problems when a plain-inheritance parent table is excluded.
@ 2023-10-24 18:48  Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 4+ messages in thread

From: Tom Lane @ 2023-10-24 18:48 UTC (permalink / raw)
  To: pgsql-committers@lists.postgresql.org

Fix problems when a plain-inheritance parent table is excluded.

When an UPDATE/DELETE/MERGE's target table is an old-style
inheritance tree, it's possible for the parent to get excluded
from the plan while some children are not.  (I believe this is
only possible if we can prove that a CHECK ... NO INHERIT
constraint on the parent contradicts the query WHERE clause,
so it's a very unusual case.)  In such a case, ExecInitModifyTable
mistakenly concluded that the first surviving child is the target
table, leading to at least two bugs:

1. The wrong table's statement-level triggers would get fired.

2. In v16 and up, it was possible to fail with "invalid perminfoindex
0 in RTE with relid nnnn" due to the child RTE not having permissions
data included in the query plan.  This was hard to reproduce reliably
because it did not occur unless the update triggered some non-HOT
index updates.

In v14 and up, this is easy to fix by defining ModifyTable.rootRelation
to be the parent RTE in plain inheritance as well as partitioned cases.

While the wrong-triggers bug also appears in older branches, the
relevant code in both the planner and executor is quite a bit
different, so it would take a good deal of effort to develop and
test a suitable patch.  Given the lack of field complaints about the
trigger issue, I'll desist for now.  (Patching v11 for this seems
unwise anyway, given that it will have no more releases after next
month.)

Per bug #18147 from Hans Buschmann.

Amit Langote and Tom Lane

Discussion: https://postgr.es/m/18147-6fc796538913ee88@postgresql.org

Branch
------
master

Details
-------
https://git.postgresql.org/pg/commitdiff/387f9ed0a0832760a709d27abd630002903e3459

Modified Files
--------------
src/backend/executor/nodeModifyTable.c |  9 +++++----
src/backend/optimizer/plan/planner.c   | 14 ++++----------
src/backend/optimizer/util/pathnode.c  |  2 +-
src/include/nodes/pathnodes.h          |  2 +-
src/include/nodes/plannodes.h          | 13 +++++++------
src/test/regress/expected/inherit.out  | 27 +++++++++++++++++++++++++++
src/test/regress/sql/inherit.sql       | 19 +++++++++++++++++++
7 files changed, 64 insertions(+), 22 deletions(-)



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* pgsql: Fix problems when a plain-inheritance parent table is excluded.
@ 2023-10-24 18:48  Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 4+ messages in thread

From: Tom Lane @ 2023-10-24 18:48 UTC (permalink / raw)
  To: pgsql-committers@lists.postgresql.org

Fix problems when a plain-inheritance parent table is excluded.

When an UPDATE/DELETE/MERGE's target table is an old-style
inheritance tree, it's possible for the parent to get excluded
from the plan while some children are not.  (I believe this is
only possible if we can prove that a CHECK ... NO INHERIT
constraint on the parent contradicts the query WHERE clause,
so it's a very unusual case.)  In such a case, ExecInitModifyTable
mistakenly concluded that the first surviving child is the target
table, leading to at least two bugs:

1. The wrong table's statement-level triggers would get fired.

2. In v16 and up, it was possible to fail with "invalid perminfoindex
0 in RTE with relid nnnn" due to the child RTE not having permissions
data included in the query plan.  This was hard to reproduce reliably
because it did not occur unless the update triggered some non-HOT
index updates.

In v14 and up, this is easy to fix by defining ModifyTable.rootRelation
to be the parent RTE in plain inheritance as well as partitioned cases.

While the wrong-triggers bug also appears in older branches, the
relevant code in both the planner and executor is quite a bit
different, so it would take a good deal of effort to develop and
test a suitable patch.  Given the lack of field complaints about the
trigger issue, I'll desist for now.  (Patching v11 for this seems
unwise anyway, given that it will have no more releases after next
month.)

Per bug #18147 from Hans Buschmann.

Amit Langote and Tom Lane

Discussion: https://postgr.es/m/18147-6fc796538913ee88@postgresql.org

Branch
------
REL_14_STABLE

Details
-------
https://git.postgresql.org/pg/commitdiff/8f4a6b9e4f5b6959575ec4fb86cc0f5d5a608f06

Modified Files
--------------
src/backend/executor/nodeModifyTable.c |  9 +++++----
src/backend/optimizer/plan/planner.c   | 14 ++++----------
src/backend/optimizer/util/pathnode.c  |  2 +-
src/include/nodes/pathnodes.h          |  2 +-
src/include/nodes/plannodes.h          | 13 +++++++------
src/test/regress/expected/inherit.out  | 27 +++++++++++++++++++++++++++
src/test/regress/sql/inherit.sql       | 19 +++++++++++++++++++
7 files changed, 64 insertions(+), 22 deletions(-)



^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2023-10-24 18:48 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-10-24 18:48 pgsql: Fix problems when a plain-inheritance parent table is excluded. Tom Lane <tgl@sss.pgh.pa.us>
2023-10-24 18:48 pgsql: Fix problems when a plain-inheritance parent table is excluded. Tom Lane <tgl@sss.pgh.pa.us>
2023-10-24 18:48 pgsql: Fix problems when a plain-inheritance parent table is excluded. Tom Lane <tgl@sss.pgh.pa.us>
2023-10-24 18:48 pgsql: Fix problems when a plain-inheritance parent table is excluded. Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox