On Wed, 24 Jan 2024 at 08:15, Alvaro Herrera <alvhe...@alvh.no-ip.org> wrote: > (Similarly, allowing GROUP BY to ignore columns not in the GROUP BY, > when a UNIQUE constraint exists and all columns are NOT NULL; currently > we allow that for PRIMARY KEY, but if you have the NOT NULL constraint > OIDs to cue the plan invalidation would let that case to be implemented > as well.)
I recall some discussion about the GROUP BY case. I think at the time there might have been some confusion with plan cache invalidation and invalidating views that have been created with columns in the target list which are functionally dependent on columns in the GROUP BY. i.e, given: create table ab (a int primary key, b int not null unique); the following works: create view v_ab1 as select a,b from ab group by a; -- works but this one does not: create view v_ab2 as select a,b from ab group by b; -- does not work ERROR: column "ab.a" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: create view v_ab2 as select a,b from ab group by b; I think thanks to your work on adding pg_constraint records for NOT NULL conditions, the latter case could now be made to work. As for the plan optimisation, I agree with Tom about the relcache invalidation triggering a replan. Maybe it's worth adding a test to ensure the replan is done after a ALTER TABLE ... DROP NOT NULL, however. David
