pgsql: Don't remove surplus columns from GROUP BY for inheritance paren

From: David Rowley <drowley(at)postgresql(dot)org>
To: pgsql-committers(at)lists(dot)postgresql(dot)org
Subject: pgsql: Don't remove surplus columns from GROUP BY for inheritance paren
Date: 2019-07-03 11:48:45
Message-ID: E1hidkj-0002fx-QI@gemulon.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-committers

Don't remove surplus columns from GROUP BY for inheritance parents

d4c3a156c added code to remove columns that were not part of a table's
PRIMARY KEY constraint from the GROUP BY clause when all the primary key
columns were present in the group by. This is fine to do since we know
that there will only be one row per group coming from this relation.
However, the logic failed to consider inheritance parent relations. These
can have child relations without a primary key, but even if they did, they
could duplicate one of the parent's rows or one from another child
relation. In this case, those additional GROUP BY columns are required.

Fix this by disabling the optimization for inheritance parent tables.
In v11 and beyond, partitioned tables are fine since partitions cannot
overlap and before v11 partitioned tables could not have a primary key.

Reported-by: Manuel Rigger
Discussion: http://postgr.es/m/CA+u7OA7VLKf_vEr6kLF3MnWSA9LToJYncgpNX2tQ-oWzYCBQAw@mail.gmail.com
Backpatch-through: 9.6

Branch
------
master

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

Modified Files
--------------
src/backend/optimizer/plan/planner.c | 8 ++++++
src/test/regress/expected/aggregates.out | 45 +++++++++++++++++++++++++++++++-
src/test/regress/sql/aggregates.sql | 24 ++++++++++++++++-
3 files changed, 75 insertions(+), 2 deletions(-)

Browse pgsql-committers by date

  From Date Subject
Next Message David Rowley 2019-07-03 11:49:15 pgsql: Don't remove surplus columns from GROUP BY for inheritance paren
Previous Message Etsuro Fujita 2019-07-03 08:56:51 pgsql: postgres_fdw: Remove redundancy in postgresAcquireSampleRowsFunc