Re: Can't find not null constraint, but \d+ shows that

Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> 于2024年4月11日周四 22:48写道：

> On 2024-Apr-11, Alvaro Herrera wrote:
>
> > Well, I think you were right that we should try to handle the situation
> > of unmarking attnotnull as much as possible, to decrease the chances
> > that the problematic situation occurs. That means, we can use the
> > earlier code to handle DROP COLUMN when it causes a PK to be dropped --
> > even though we still need to handle the situation of an attnotnull flag
> > set with no pg_constraint row. I mean, we still have to handle DROP
> > DOMAIN correctly (and there might be other cases that I haven't thought
> > about) ... but surely this is a much less common situation than the one
> > you reported. So I'll merge everything and post an updated patch.
>
> Here's roughly what I'm thinking. If we drop a constraint, we can still
> reset attnotnull in RemoveConstraintById(), but only after checking that
> it's not a generated column or a replica identity. If they are, we
> don't error out -- just skip the attnotnull update.
>
> Now, about the code to allow ALTER TABLE DROP NOT NULL in case there's
> no pg_constraint row, I think at this point it's mostly dead code,
> because it can only happen when you have a replica identity or generated
> column ... and the DROP NOT NULL should still prevent you from dropping
> the flag anyway. But the case can still arise, if you change the
> replica identity or ALTER TABLE ALTER COLUMN DROP DEFAULT, respectively.
>
> I'm still not ready with this -- still not convinced about the new AT
> pass.

Yeah, at first, I was also hesitant. Two reasons make me convinced.
in ATPostAlterTypeParse()
-----
else if (cmd->subtype == AT_SetAttNotNull)
{
/*
* The parser will create
AT_AttSetNotNull subcommands for
* columns of PRIMARY KEY
indexes/constraints, but we need
* not do anything with them here,
because the columns'
* NOT NULL marks will already have
been propagated into
* the new table definition.
*/
}
-------
The new table difinition continues to use old column not-null, so here does
nothing.
If we reset NOT NULL marks in RemoveConstrainById() when dropping PK
indirectly,
we need to do something here or somewhere else.

Except AT_SetAttNotNull type, other types add a AT pass to tab->subcmds.
Because
not-null should be added before re-adding index, there is no right AT pass
in current AlterTablePass.
So a new AT pass ahead AT_PASS_OLD_INDEX is needed.

Another reason is that it can use ALTER TABLE frame to set not-null.
This way looks simpler and better than hardcode to re-install not-null in
some funciton.

--
Tender Wang
OpenPie: https://en.openpie.com/