Repository navigation
sql-839: Report SQL string 'NULL' for table column defaults in mz_columns again - #40316
Merged
SangJunBak merged 4 commits intoOct 29, 2026
Conversation
mz_columns.default reads as the string NULL for a table column without a default, as it did in every release through v26.48. #40001 made it SQL NULL, which the Terraform provider (every release through v0.11.12) compares against its declared default of "NULL": it then replaces the table on every apply and drops its rows (SQL-839). Builtin tables read the string too, so the contract holds for every table and not only for user tables. The PostgreSQL-compatible views keep the behavior #40001 gave them by mapping the string to no default: pg_attribute.atthasdef is false, pg_attrdef has no row and information_schema.columns.column_default is NULL for such a column. Closes: SQL-839 Co-Authored-By: Claude Fable 5.1 <[email protected]>
A table column without a default reads as the string NULL in mz_columns and as no default in pg_attribute, pg_attrdef and information_schema (SQL-839). Co-Authored-By: Claude Fable 5.1 <[email protected]>
aljoscha
approved these changes
Oct 29, 2026
ggevay
approved these changes
Oct 29, 2026
| item_columns_initial_mv 2 b true text <null> text -1 | ||
| item_columns_initial_mv 3 c1 true numeric <null> numeric -1 | ||
| item_columns_initial_table 1 a false integer <null> int4 -1 | ||
| item_columns_initial_table 1 a false integer NULL int4 -1 |
Contributor
There was a problem hiding this comment.
The two version branches now expect the same rows, and the comment above them ("From 26.49 on ... no default reads as SQL NULL") no longer holds. The guard and comment can go, leaving one query.
| .map_or(serde_json::Value::Null, |expr| { | ||
| json!(expr.to_ast_string_stable()) | ||
| }) | ||
| // A column without a default reads as the string NULL, see the |
Contributor
There was a problem hiding this comment.
Worth saying why here, so the next cleanup doesn't undo it: clients compare against this string, and the Terraform provider replaces a table whose column default reads as SQL NULL (SQL-839). Maybe also TODO(DEX-197): switch to SQL NULL once released providers read it as no default.
- Say at the decision point why a missing default reads as the string NULL - Drop the 26.49 version guard in the item_columns platform check, since both sides now expect the same rows. Co-Authored-By: Claude Opus 5.5 <[email protected]>
SangJunBak
force-pushed
the
jun/sql-839-terraform-apply-drops-and-recreates-tables-after-the
branch
from
October 29, 2026 13:52
8d8f551 to
4e7e4aa
Compare
SangJunBak
deleted the
jun/sql-839-terraform-apply-drops-and-recreates-tables-after-the
branch
October 29, 2026 14:35
def-
added a commit
that referenced
this pull request
Nov 6, 2026
Undo the string "NULL" default from #40316 now that terraform-provider-materialize v0.11.13 reads a SQL NULL column default as no default ([DEX-197](https://linear.app/materializeinc/issue/DEX-197)). Table columns again match every other relation in `mz_columns.default`, and the PostgreSQL-compatible views go back to plain `IS NOT NULL` checks. `information_schema.columns`, `pg_attribute`, and `pg_attrdef` report the same results as before. `materialize_table` is the only provider resource that reads `mz_columns.default`. ### Release notes This release will report SQL `NULL` instead of the string `'NULL'` in `mz_catalog.mz_columns.default` for table columns without a default, matching sources, views, and materialized views. If you manage tables with the Materialize Terraform provider, upgrade the provider to v0.11.13 or later before you upgrade Materialize. Co-authored-by: Claude Opus 5.5 <[email protected]>
leedqin
added a commit
to leedqin/materialize
that referenced
this pull request
Nov 6, 2026
Generated with the fixed `yarn gen:types`. Adds mz_builtin_columns and mz_builtin_index_columns. Builtin columns lose their Generated<> wrappers, since MaterializeInc#40316 reports no default for them in pg_attribute. Keeps the two hand-added TODO markers for mz >= 26.34.
leedqin
added a commit
that referenced
this pull request
Nov 6, 2026
### Motivation `yarn gen:types` fails with `function "pg_get_serial_sequence" does not exist`. kysely-codegen runs kysely's Postgres introspector for its table query. Since the bump to kysely 0.28 (#35669), that query calls `pg_get_serial_sequence`, which Materialize lacks. kysely 0.29 still does. The query also lists only relkinds `r`, `v` and `p`, so the catalog's builtin materialized views (`m`) never reached the types under kysely-codegen 0.17. #40044's types were generated with both patched by hand in `node_modules`. This also bumps kysely to 0.29 and kysely-codegen to 0.20, superseding #39686. ### Description - `bin/kysely-codegen.ts` runs kysely-codegen programmatically with an introspector that overrides only the table query. kysely-codegen's materialized view query, enum handling and serializer still apply. - The script writes the copyright header to `types/materialize.d.ts`. The codegen output lacks it, and the copyright lint fails without it. - `bin/generate-types` exits on errors, pulls the requested image (a cached `latest` went stale), and stops the container on exit. Before, each run left a container bound to port 6875, and the next run generated types from that old container. - kysely 0.28.17 → 0.29.8 and kysely-codegen 0.17 → 0.20.0: - 0.29 rejects `limit(sql.raw(...))`. `rawLimit` and the lag history limit now call `query.limit(sql.lit(n))`, which still inlines the limit as a literal, as Materialize requires. - `rawLimit` used to append its limit with `modifyEnd`. In 0.29, compiling end modifiers calls `Array.prototype.toSorted`, which Chrome 109 lacks. No console query uses end modifiers now. Neither `rawLimit` caller has an OFFSET or another end modifier, so the compiled SQL is unchanged. - kysely 0.29 is ESM-only and kysely-codegen 0.20 `require`s it, so `yarn gen:types` needs Node 22.12 or later. CI and the Volta pin run Node 26, and #39683 updates `engines.node`. - `types/materialize.d.ts` is regenerated against v26.49.1. kysely-codegen 0.17 and 0.20 produce identical output. - Adds `mz_builtin_columns` and `mz_builtin_index_columns`. - Builtin columns lose their `Generated<>` wrappers, because #40316 changed `pg_attribute.atthasdef` to report no default for them. The console only selects from these relations, where `Generated<T>` and `T` read the same. - Drops the two hand-added `TODO` markers from #39992. The generator now emits `mz_cluster_auto_scaling_strategies` and `mz_cluster_reconfigurations`, and `AUTO_SCALING_MIN_VERSION` still gates them at runtime. The file is now exactly the generator output. Worth checking on the preview, since these run the changed limits: - the Query History list; - a sink's Errors tab; - the freshness lag charts on the environment overview and the cluster pages. 🤖 Generated with [Claude Code](https://claude.com/claude-code)
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Add this suggestion to a batch that can be applied as a single commit.This suggestion is invalid because no changes were made to the code.Suggestions cannot be applied while the pull request is closed.Suggestions cannot be applied while viewing a subset of changes.Only one suggestion per line can be applied in a batch.Add this suggestion to a batch that can be applied as a single commit.Applying suggestions on deleted lines is not supported.You must change the existing code in this line in order to create a valid suggestion.Outdated suggestions cannot be applied.This suggestion has been applied or marked resolved.Suggestions cannot be applied from pending reviews.Suggestions cannot be applied on multi-line comments.Suggestions cannot be applied while the pull request is queued to merge.Suggestion cannot be applied right now. Please check back later.
Before, tables whose columns' defaults were undefined we'd store their
mz_columnsdefault value as the SQL string 'NULL' but would store it as NULL for other relation objects. In #40001 , we decided to unify the behavior and store it as NULL for tables too. However, this created a regression in our terraform provider where it'd drop and create tables with no defaults on their columns because of a schema mismatch. Thus we change it back to the SQL stringNULL.Motivation
Closes: SQL-839
Verification
We still get pg compatibility by just changing the definition of the information_schema / pg views. This is why we don't need to update any of the SLT tests related to those views.