Re: cataloguing NOT NULL constraints

On 2024-May-09, Robert Haas wrote:

> Yeah, I have to admit that the ongoing bug fixing here has started to
> make me a bit nervous, but I also can't totally follow everything
> that's under discussion, so I don't want to rush to judgement.

I have found two more problems that I think are going to require some
more work to fix, so I've decided to cut my losses now and revert the
whole. I'll come back again in 18 with these problems fixed.

Specifically, the problem is that I mentioned that we could restrict the
NOT NULL NO INHERIT addition in pg_dump for primary keys to occur only
in pg_upgrade; but it turns this is not correct. In normal
dump/restore, there's an additional table scan to check for nulls when
the constraints is not there, so the PK creation would become measurably
slower. (In a table with a million single-int rows, PK creation goes
from 2000ms to 2300ms due to the second scan to check for nulls).

The addition of NOT NULL NO INHERIT constraints for this purpose
collides with addition of constraints for other reasons, and it forces
us to do unpleasant things such as altering an existing constraint to go
from NO INHERIT to INHERIT. If this happens only during pg_upgrade,
that would be okay IMV; but if we're forced to allow in normal operation
(and in some cases we are), it could cause inconsistencies, so I don't
want to do that. I see a way to fix this (adding another query in
pg_dump that detects which columns descend from ones used in PKs in
ancestor tables), but that's definitely too much additional mechanism to
be adding this late in the cycle.

--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/