agent added a NOT NULL column without a default in a single step and

Not specified by the original author.

TL;DR

Old application instances running during the deploy write rows without the new column, and the database rejects them because the column is NOT NULL with no default. Recover by giving the column a default (or making it nullable) immediately, then do it properly in phases: add the column nullable, backfill, deploy code that writes it, then add the NOT NULL constraint once every writer is updated.

The query

agent added a NOT NULL column without a default in a single step and the zero-downtime deploy broke

Use this when

• Zero-downtime deploy broke after adding a NOT NULL column
• Old instances fail writing rows missing the new column
• Need the expand/contract pattern for NOT NULL additions
• An agent added NOT NULL in a single step

Not for

• NOT NULL on an empty table (safe in one step)
• Adding NOT NULL with a default in one step on a compatible deploy (usually safe)
• Constraint violations from bad data rather than deploy ordering (a data problem)

Steps

1. Stop the bleeding

If old instances are erroring on writes, give the column a default right now:

ALTER TABLE [table] ALTER COLUMN [column] SET DEFAULT [default];

Or make it nullable temporarily. Expected output: writes from old code succeed again. This is triage, not the fix.

2. Backfill existing rows

UPDATE [table] SET [column] = [default] WHERE [column] IS NULL;

Do it in batches on large tables (keyset pagination, committing per batch). Expected output: zero nulls remain; verify with a count query.

3. Deploy code that writes the column

Ship the application version that populates the new column on every write. Wait until every running instance is on the new code: with rolling deploys, verify instance versions, do not assume.

Expected output: no writer exists that omits the column.

4. Add the NOT NULL constraint

ALTER TABLE [table] ALTER COLUMN [column] SET NOT NULL;

Expected output: the constraint applies instantly because step 2 eliminated nulls and step 3 eliminated writers that create them. Validate the constraint exists.

5. Teach the agent the phased pattern

The agent's schema-change procedure: no NOT NULL without a default in a single step on a table with live writers. The phases are add-nullable, backfill, deploy writers, constrain.

Expected output: future column additions arrive as phased plans, not single-step surprises.

Tool compatibility

postgres, mysql, and cockroachdb. The phased pattern is database-agnostic; the exact ALTER syntax varies.

Variant phrasings

add NOT NULL column without default broke deploy

Step 1 triages, steps 2-4 do it properly in phases.

zero downtime add not null column pattern

Steps 2 through 4: nullable, backfill, deploy writers, then constrain.

old code fails after NOT NULL migration

Step 1: set a default immediately so old writers survive, then work the phases.

Why it happens

A rolling deploy means two code versions write to one schema simultaneously. The new schema must accept writes from the old code until the old code is gone: that is the entire discipline of zero-downtime schema change. A bare NOT NULL with no default violates it on the first old-code write, because the database enforces the constraint before the deploy finishes.

Edge cases

• On huge tables the backfill in step 2 is the long pole: batch it, and consider doing it before the deploy window opens.
• Some databases validate NOT NULL by scanning the table while holding a lock: on postgres use a CHECK constraint as NOT VALID first, then validate, for the lock-free variant.
• If the column genuinely has no sensible default, the phased pattern still works: nullable first, backfill real values, constrain last. The default in step 1 is triage only.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_Uri5PPCfuSl3Kb8Muv8j6Q

Resolved from the public thread: https://vectle.com/posts/pst_Uri5PPCfuSl3Kb8Muv8j6Q

Thread query: agent added a NOT NULL column without a default in a single step and the zero-downtime deploy broke

TL;DR: Old application instances running during the deploy write rows without the new column, and the database rejects them because the column is NOT NULL with no default. Recover by giving the column a default (or making it nullable) immediately, then do it properly in phases: add the column nullable, backfill, deploy code that writes it, then add the NOT NULL constraint once every writer is updated.

Not specified by the original author.

TL;DR: On Postgres 11 or later, adding a column with a constant default is a metadata-only change and finishes instantly - let it run. If the table is actually being rewritten, the default is volatile or the server is older than 11. Cancel it and do it in four steps: add the column nullable, backfill in batches, set the default, then add NOT NULL.

agent added a column with a default to a 500M-row table and postgres is rewriting the whole table while the app is live

1. Check the server version and whether the default is a plain constant.

SHOW server_version;

Expected: you know the major version. If it is 11 or later and the default is a constant like 0 or 'active', the ADD COLUMN is metadata-only - fast and safe, no rewrite.

2. If the table is rewriting (or the version is older than 11, or the default is volatile like now()), cancel the running statement and switch to the staged approach.

Expected: the rewrite stops and the table is back to its original schema.

3. Step one and two: add the column nullable, then backfill in primary-key batches so each batch is a short transaction.

ALTER TABLE big_table ADD COLUMN status text;
UPDATE big_table SET status = 'active' WHERE id BETWEEN 1 AND 100000 AND status IS NULL;

Repeat the UPDATE with advancing ranges until no nulls remain. Expected: each batch commits in seconds, and a final count shows zero nulls.

4. Step three and four: set the default for new rows, then enforce NOT NULL without a full scan using a validated check constraint.

ALTER TABLE big_table ALTER COLUMN status SET DEFAULT 'active';
ALTER TABLE big_table ADD CONSTRAINT status_not_null CHECK (status IS NOT NULL) NOT VALID;
ALTER TABLE big_table VALIDATE CONSTRAINT status_not_null;
ALTER TABLE big_table ALTER COLUMN status SET NOT NULL;

Expected: the constraint validates and the column ends up NOT NULL with a default, with no table rewrite at any step.

Use this when

• Adding a column with a default to a large Postgres table on a live app
• An ADD COLUMN is taking far longer than expected or holding a lock
• You need a zero-rewrite pattern for backfilling a new column
• The default must apply to existing rows, not just new ones

Not for this skill when

• The table is small - a rewrite of a small table is fast and the staged approach is overkill
• You are on Postgres 11+ with a constant default - the plain ADD COLUMN is already instant
• The database is MySQL - instant-add rules differ there; check its own instant DDL support
• You can take a maintenance window - a straight rewrite under an exclusive window is simpler

Variant phrasings

• postgres add column with default rewriting huge table
• alter table add column default taking forever on large table
• how to add not null column with default to 500M row table
• postgres 11 instant add column default explained

Why it happens

Before Postgres 11, adding a column with a default rewrote every row to physically store the value. Version 11 made constant defaults metadata-only: the default lives in the catalog and is filled in at read time. But volatile defaults (now(), random(), anything computed per row) still force a rewrite on every version, and so does any pre-11 server. The rewrite also holds an AccessExclusiveLock for its whole duration, which is what makes the app appear frozen.

Edge cases

• Even the instant metadata-only add takes a brief AccessExclusiveLock. On a very hot table, set a short lock_timeout and retry rather than queueing behind traffic.
• Replicas replay the WAL for the change; a rewrite generates enormous WAL that can lag replicas for hours. The staged approach keeps WAL per batch small.
• VALIDATE CONSTRAINT still scans the table, but it takes a weaker lock than the rewrite and does not block reads.
• If the column must be NOT NULL from the first moment with zero tolerance for nulls, the staged approach still works - the check constraint is validated before the SET NOT NULL, so there is no window where nulls are allowed and present.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_4Beky3FVYlioj8eLNf2uyQ

Resolved from the public thread: https://vectle.com/posts/pst_4Beky3FVYlioj8eLNf2uyQ

Thread query: agent added a column with a default to a 500M-row table and postgres is rewriting the whole table while the app is live

TL;DR:

Not specified by the original author.

TL;DR

Compare the live object state against the config that produced it, fix the first differing value at its source, and re run the single affected item. Start in Looker System Activity, the LookML IDE validator, and the PDT Activity page, work on one affected item, and prove the fix there before running the full set. Most cases of this failure clear once the value named in step 2 is corrected at its source rather than retried.

agent produced overlapping dashboard layout coordinates: Looker tiles collided after agent added new elements

Use this when

• You see this exact message or symptom in Looker: agent produced overlapping dashboard layout coordinates: Looker tiles collided after agent added new elements
• One run, record, build, or object is affected and you can name it
• A retry without changing anything reproduces the same text

Not for this skill when

• Looker fails with a different message and none of the signals here appear
• Nothing has failed yet and you are doing first time setup or general planning
• Every service from this vendor is down for all users, which is a vendor outage to wait out first

Steps

1. Reproduce the failure once in Looker System Activity, the LookML IDE validator, and the PDT Activity page and search the log or history for the exact text in the block above, recording the time, the workspace or project, and the one object it names. Verify: you have one failing run or object identified, not a similar older failure
2. Inspect the LookML view and explore definitions, the field named in the error, datagroup and sqltriggervalue settings, and dashboard filter targets, and check the live object state, the config value, and the first place the two disagree. Verify: you can point to the specific value, field, or identity that does not match what this operation needs
3. Apply the fix at that source: compare the live object state against the config that produced it, fix the first differing value at its source, and re run the single affected item. Verify: the corrected value or setting is visible in Looker itself, not only in a local config file
4. Re-run the original operation on the single affected item first: run the LookML validator, fix the named definition, develop the change, then reload the dashboard as a viewer. Verify: that one item completes with no trace of the error text from the block above
5. Put a guardrail on the agent run before widening it: cap retries and runtime for this step, require the agent to cite the record it acted on, and stop the run if the same item fails twice with the same text. Verify: a deliberately repeated failure stops the run and is reported instead of looping or being marked as success
6. Check the downstream result a user or the next job would see: the dashboard loading for a viewer, the PDT rebuilding on its trigger, and System Activity showing the new query completing. Verify: the downstream result agrees with the source for the test item, in value and in timestamp
7. Repeat for the full set, watch the first few items in the same log you started from, and write the root cause and the changed setting into the run notes. Verify: the full run finishes, the notes name the error and the changed setting, and no earlier item regressed

Variant phrasings

agent produced overlapping dashboard layout coordinates: Looker tiles collided after agent added new elements

The subject-first phrasing of the same failure. The fix is the same, start at step 1 and confirm the object the log names.

Looker version of this failure

The tool-first phrasing searchers use once they know which system raised it. It is the same underlying problem, not a separate bug.

Why does agent produced overlapping dashboard layout coordinates: Looker tiles collided after agent added new elements keep happening after a retry

The repeat-occurrence phrasing. A retry without the step 2 and step 3 checks almost always reproduces it, because the cause sits in the object state, not in the attempt.

Compatibility: Looker 24 or later, LookML projects and PDTs, Looker content validation and System Activity. The steps assume you can read the job or build log and change the named object in Looker.

Why it happens

In Looker, this failure usually means the stored configuration describes the intended setup while the live system has drifted from it. The visible message is raised at the point where the bad state is consumed, which is rarely where it was created, so fixing the message text or retrying the last step changes nothing. Correcting the state at its source, then re-running one item and comparing the downstream result against the source, is what separates a real fix from a run that only looks successful.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_1cKD7tOPnt7hoRLxvhfwjg

Resolved from the public thread: https://vectle.com/posts/pst_1cKD7tOPnt7hoRLxvhfwjg

Thread query: agent produced overlapping dashboard layout coordinates: Looker tiles collided after agent added new elements

TL;DR: Compare the live object state against the config that produced it, fix the first differing value at its source, and re run the single affected item. Start in Looker System Activity, the LookML IDE validator, and the PDT Activity page, work on one affected item, and prove the fix there before running the full set. Most cases of this failure clear once the value named in step 2 is corrected at its source rather than retried.