Alvaro Herrera <alvhe...@alvh.no-ip.org> 于2024年4月10日周三 21:58写道：
> It turns out that trying to close all holes that lead to columns marked > not-null without a pg_constraint row is not possible within the ALTER > TABLE framework, because it can happen outside it also. Consider this > > CREATE DOMAIN dom1 AS integer; > CREATE TABLE notnull_tbl (a dom1, b int, PRIMARY KEY (a, b)); > DROP DOMAIN dom1 CASCADE; > > In this case you'll end up with b having attnotnull=true and no > constraint; and no amount of messing with tablecmds.c will fix it. > I try above case on my v4 patch[1], and it seems no result as what you said. But, anyway, I now don't like updating other catalog in RemoveConstraintById(). Because it will not be friendly for others who call RemoveConstraintById() want only to remove pg_constraint tuple, but actually it do more works stealthily. > So I propose to instead allow those constraints, and treat them as > second-class citizens. We allow dropping them with ALTER TABLE DROP NOT > NULL, and we allow to create a backing full-fledged constraint with SET > NOT NULL or ADD CONSTRAINT. So here's a partial crude initial patch to > do that. > Hmm, the patch looks like the patch in my first email in this thread. But my v1 patch seem a poc at most. > > > One thing missing here is pg_dump support. If you just dump this table, > it'll end up with no constraint at all. That's obviously bad, so I > propose we have pg_dump add a regular NOT NULL constraint for those, to > avoid perpetuating the weird situation further. > > Another thing I wonder if whether I should use the existing > set_attnotnull() instead of adding drop_orphaned_notnull(). Or we could > just inline the code in ATExecDropNotNull, since it's small and > self-contained. > I like just inline the code in ATExecDropNotNull, as you said, it's small and self-contained. in ATExecDropNotNull(), we had open the pg_attributed table and hold RowExclusiveLock, the tuple we also get. What we do is set attnotnull = false, and call CatalogTupleUpdate. -- > Álvaro Herrera 48°01'N 7°57'E — > https://www.EnterpriseDB.com/ > "Postgres is bloatware by design: it was built to house > PhD theses." (Joey Hellerstein, SIGMOD annual conference 2002) > [1] https://www.postgresql.org/message-id/CAHewXNn_So7LUCxxxyDNfdvCQp1TnD3gTVECBZX2bT_nbPgraQ%40mail.gmail.com -- Tender Wang OpenPie: https://en.openpie.com/
