Re: Support NOT VALID / VALIDATE constraint options for named NOT NULL constraints

On 2025-Mar-24, Robert Haas wrote:

> I mean, maybe there's an argument that some changes are more
> disruptive than others. For instance, if removing attndims would force
> drivers to run extra more complicated queries to learn whether a
> certain type is an array type, one could argue that taking it away
> without providing some alternative is breaking the drivers in some
> fundamental way. I'm not sure whether that's a real problem, but there
> is no such argument to be made here. There's no proposal on the table
> to entirely remove any information from pg_attribute -- only to turn
> something that is presently two-valued into something three-valued.
> So, it will still be possible for it to learn the same facts that it
> can learn today, it will just perhaps need to be adjusted in terms of
> exactly how it does that. But if that is prohibited then what made it
> OK all the other times we've done it?

I think use of attnotnull is much more prevalent than that of fields
we've removed or changed previously. In pg_attribute we recently
removed attcacheoff, which pretty much nobody uses; going back to 2016 I
couldn't find any other removal or type change. In pg_class, I can see
that we got rid of reltoastidxid in 2013, but that one was quite
esoteric and I doubt anybody would be interested in that. One commit
that I see removed a lot of columns is 739adf32eecf, but that was in
2002.

One we removed not so long ago was protransform, which was turned into
prosupport by commit 1fb57af92069 in 2019. But I'm sure that
protransform was much less used by external code, because it's very
specialized, so there weren't so many complaints. We removed
proisagg/proiswindow to turn them into prokind with commit fd1a421fe661
(2018), and you can find plenty of user complaints about that.

Use of attnotnull is very widespread.

> Again, I'm not 100% positive that changing the Boolean column to a
> three-valued column is the right way forward, but it does have the
> advantage of saving a byte, and the width of system catalog tables has
> been a periodic concern.

In this case, as I already said, the new boolean column would go in
what's currently padding space, so there's no widening taking place.

> Also, relpersistence is an example of a seemingly very similar kind of
> change - turning a Boolean column into a char column so it can support
> three values. Do we with the benefit of hindsight consider that to
> have been a mistake?

Are you talking about 5f7b58fad8f4 "Generalize concept of temporary
relations to "relation persistence"." from 2010? That was 15 years ago,
and maybe it was not a mistake because the ecosystem around Postgres was
different then, but also relistemp/relpersistence is not as widely used
as attnotnull. There were complaints regarding that change though:
https://www.postgresql.org/message-id/flat/F0ADACAC-15A3-4E5A-A27E-6C9EE090589C(at)kineticode(dot)com

> I don't, because I think that PostgreSQL development will be paralyzed
> if we prohibit such changes, but opinions might vary.

It's very rare that we need to make catalog changes like this, so I
think you're being overly dramatic. This is not going to paralyze the
development. But I agree that we should still allow ourselves to change
system schema, even if we'd break a few things here and there, as long
as it's not the world.

Please don't think that I'm in favor of doing it the difficult way for
no reason. Previous versions of the patch (changing attnotnull to
char), from the Postgres core code perspective, were pretty much ready
-- we're having this discussion only to avoid the breakage. I could
save us a bunch of time here and just push those patches, but I think
the responsible thing here (the one we're less likely to regret) is not
to.

--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/