Обсуждение: Re: pgsql: Add better handling of redundant IS [NOT] NULL quals

On 2024-Jan-23, David Rowley wrote:

> Add better handling of redundant IS [NOT] NULL quals
> 
> Until now PostgreSQL has not been very smart about optimizing away IS
> NOT NULL base quals on columns defined as NOT NULL.

Hmm, what happens if a NOT NULL constraint is dropped and you have such
a plan in plancache?  As I recall, lack of a mechanism to invalidate
such plans was the main reason for Postgres not to have this.  One of
the motivations for adding catalogued NOT NULL constraints was precisely
to have an OID that you could use to cause plancache to invalidate such
a plan.  Does this new code add something like that?

Admittedly I didn't read the threads or the patch, just skimmed for some
clues, so I may have failed to notice it.  But in the tests you added I
don't see any ALTER TABLE DROP CONSTRAINT.


(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.)

-- 
Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.com/
"Por suerte hoy explotó el califont porque si no me habría muerto
 de aburrido"  (Papelucho)

Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> On 2024-Jan-23, David Rowley wrote:
>> Until now PostgreSQL has not been very smart about optimizing away IS
>> NOT NULL base quals on columns defined as NOT NULL.

> Hmm, what happens if a NOT NULL constraint is dropped and you have such
> a plan in plancache?  As I recall, lack of a mechanism to invalidate
> such plans was the main reason for Postgres not to have this.

IIRC, we realized that that concern was bogus.  Removal of such
constraints would cause pg_attribute.attnotnull to change, leading
to a relcache invalidation on the table, forcing replan.  If anyone
tried to get rid of attnotnull or make it incompletely reliable,
then we'd have problems; but AFAIK that's not being contemplated.

            regards, tom lane

On Wed, 24 Jan 2024 at 08:15, Alvaro Herrera <alvherre@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