sql/pg_catalog: emit a NOT NULL row in pg_constraint for each NOT NULL column (PG18 compat)

[rafiss]

Describe the problem

PostgreSQL 18 changed pg_catalog.pg_constraint to expose a row with
contype = 'n' for every column-level NOT NULL constraint. CockroachDB
still follows pre-18 behavior and emits no row in pg_constraint for
column NOT NULL constraints, only for PRIMARY KEY, UNIQUE, FOREIGN KEY, and CHECK.

This breaks tools, ORMs, and applications that target PG18 and expect to
discover NOT NULL constraints by querying pg_constraint.

This is the catalog-visibility piece of the broader PG18 NOT-NULL rework
tracked in #168988 (which also covers naming, NOT VALID, NO INHERIT,
and the attnullability field). This issue is scoped to just the
pg_catalog.pg_constraint surface — the change most directly visible to
client tooling.

To Reproduce

CREATE TABLE notnull_test (
  id INT PRIMARY KEY,
  required_col TEXT NOT NULL,
  nullable_col TEXT,
  another_required INT NOT NULL CHECK (another_required > 0)
);

SELECT conname, contype, conkey, pg_get_constraintdef(oid) AS def
FROM pg_constraint
WHERE conrelid = 'notnull_test'::regclass
ORDER BY contype, conname;

PostgreSQL 18.3:

                conname                 | contype | conkey |              def
----------------------------------------+---------+--------+--------------------------------
 notnull_test_another_required_check    | c       | {4}    | CHECK ((another_required > 0))
 notnull_test_another_required_not_null | n       | {4}    | NOT NULL another_required
 notnull_test_id_not_null               | n       | {1}    | NOT NULL id
 notnull_test_required_col_not_null     | n       | {2}    | NOT NULL required_col
 notnull_test_pkey                      | p       | {1}    | PRIMARY KEY (id)

CockroachDB (master, v26.3.0-alpha):

        conname         | contype | conkey |              def
------------------------+---------+--------+--------------------------------
 check_another_required | c       | {4}    | CHECK ((another_required > 0))
 notnull_test_pkey      | p       | {1}    | PRIMARY KEY (id ASC)

Expected behavior

For each non-nullable column, pg_constraint should contain a row with:

• contype = 'n'
• conname = '<table>_<column>_not_null' (when no explicit name has been given)
• conkey = {<column ordinal>}
• pg_get_constraintdef(oid) = 'NOT NULL <column>'
• conrelid set to the table OID, contypid = 0

Code references

• populateTableConstraints iterates table.AllConstraints() but emits no
  row for NOT NULL column constraints:
  pkg/sql/pg_catalog.go:1188-1401
• The conTypeNotNull = "n" constant already exists and is used today only
  for domain-level NOT NULL:
  pkg/sql/pg_catalog.go:1153,
  pkg/sql/pg_catalog.go:1466
• information_schema.table_constraints already synthesizes these rows
  (as CHECK, matching PG's information_schema mapping) by walking
  table.PublicColumns(). That walk is a useful template for the
  pg_constraint change:
  pkg/sql/information_schema.go:1503-1516
• pg_get_constraintdef will need a corresponding case for 'n':
  pkg/sql/sem/builtins/pg_builtins.go:861

Notes

• Scope here is only the read-side catalog surface. Named NOT NULL
  constraints, NOT VALID, NO INHERIT, the attnullability rework,
  and ALTER TABLE … ALTER CONSTRAINT … [NO] INHERIT syntax remain
  tracked in sql/schema: track NOT NULL as proper table constraints (PG18 compatibility) #168988.
• A stable OID scheme is needed for synthesized NOT NULL constraint
  rows. oidHasher already has helpers for other constraint kinds; a
  new NotNullConstraintOid(db, schema, table, column) helper would fit
  the existing pattern.
• Cluster-version gating is likely needed since clients in mixed-version
  clusters may not expect the new rows.

Environment:

• CockroachDB version: master (v26.3.0-alpha)
• Verified PG behavior: PostgreSQL 18.3

Additional context

PG release notes: "Store column NOT NULL specifications in pg_constraint".
PG commit: a379061a22a8.

Related: #168988 (broader PG18 NOT-NULL rework).

Epic CRDB-39727

Jira issue: CRDB-63721

[blathers-crl]

Hi @rafiss, please add a branch-* label to identify the earliest affected branch for this C-bug

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is dev-inf.}