The Update That Recalculated Nothing

There is one demo in V8 I never get tired of running: an UPDATE that changes real data and causes zero downstream work. You edit a sale's PaymentMethod, the trigger fires, it consults the dependency map, finds that nothing depends on PaymentMethod, and recalculates nothing. Under every version before V8, that same edit would have rebuilt eighteen measures to produce eighteen unchanged numbers. This post is the trigger that makes zero-work possible — and, because I keep promising honesty about V8's rough edges, a tautological bug sitting right next to it.

The selective trigger

The heart of stage three is ProcessIntelligentDimensionChange, wired to SalesDim as TR_IntelligentDimensionChange. Its logic is a direct expression of the thesis: figure out what the change affects, and only act if it affects something.

CREATE OR REPLACE FUNCTION ProcessIntelligentDimensionChange()
RETURNS TRIGGER AS $$
DECLARE
    affected_measures SMALLINT[];
BEGIN
    -- ask the dependency map what this dimension drives
    affected_measures := (
        SELECT array_agg(MeasureTypeId)
        FROM DetermineRecalculationNeeds(NEW.SalesReferenceId, NEW.DimTypeId)
    );

    -- stamp what we found onto the dimension row
    UPDATE SalesDim SET AffectedMeasures = affected_measures
    WHERE DimSurrogateKey = NEW.DimSurrogateKey;

    -- ONLY recalculate if something actually depends on this dimension
    IF array_length(affected_measures, 1) > 0 THEN
        PERFORM RecalculateSpecificMeasures(
            NEW.SalesReferenceId, affected_measures, 'Dimension change');
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

The whole intelligence is in that IF array_length(affected_measures, 1) > 0. DetermineRecalculationNeeds returns the measures the dependency map says are affected. If that array is empty — nothing depends on the changed dimension — the IF is false and RecalculateSpecificMeasures is never called. No expiry, no rebuild, no fact writes. The trigger does its lookup and returns, having done no measure work at all. That guard is the difference between V8 and every version before it: the old triggers always rebuilt; this one asks first.

The PaymentMethod payoff

The demonstration is a dimension chosen precisely because nothing depends on it. PaymentMethod (dimension 10 in V8's expanded set) is recorded on a sale but feeds no measure — it is analytical metadata, not a calculation input. So the map has no outgoing edges from it, and:

-- change PaymentMethod on sale 3
UPDATE SalesDim SET DimValue = 'Debit Card'
WHERE SalesReferenceId = 3 AND DimTypeId = 10 AND IsCurrent = TRUE;

-- ask what PaymentMethod affects: no rows
SELECT * FROM GetAffectedMeasures(10);

GetAffectedMeasures(10) returns nothing, DetermineRecalculationNeeds returns an empty array, the IF guard is false, and the update recalculates 0 of 18 measures. A genuine data change with genuinely zero downstream cost. Compare it to a ProductPrice change:

SELECT 'ProductPrice' AS dim, COUNT(*) FROM GetAffectedMeasures(2)
UNION ALL
SELECT 'ShippingCost', COUNT(*) FROM GetAffectedMeasures(8);

ProductPrice lights up eleven measures; ShippingCost lights up seven; PaymentMethod lights up none. The work is proportional to the blast radius, exactly as promised — and the most convincing point on that spectrum is the zero, because zero is impossible under rebuild-everything. You can also drive a targeted recalculation by hand when you want to:

SELECT RecalculateSpecificMeasures(4, ARRAY[1,2,3,8,9], 'Manual batch recalculation');

What gets logged

When recalculation does happen, it is recorded. RecalculateSpecificMeasures expires the old fact, inserts the new one with a fresh version hash and JSONB context, and writes a MeasureRecalculationLog row capturing what triggered the work and which measures it touched:

-- MeasureRecalculationLog carries the affected set as an array
AffectedMeasureTypeIds SMALLINT[]   -- e.g. {1,2,3,7,8}

Storing the affected set as an array on the log row is what makes V8's efficiency measurable rather than asserted. The RecalculationEfficiency views read these logs to report how selective the system is actually being — and, as part 5 gets into, that measurement is exactly what exposes where the selectivity holds and where it quietly doesn't.

The bug beside it

Now the honesty. Right inside this trigger's follow-up bookkeeping is a classic PL/pgSQL tautology. After recalculating, the code tries to stamp the triggering dimension onto the log rows, and writes:

UPDATE MeasureRecalculationLog
SET TriggeredByDimTypeId = ...
WHERE BatchId = BatchId;   -- both sides resolve to the column: always true

BatchId on both sides of that predicate resolves to the column, so BatchId = BatchId is true for every row (except nulls) — the update stamps every log row, not just the one the trigger just wrote. It is the same variable/column shadowing trap that bit CompleteBatch in V7, in a new spot. There is a related smell nearby: the log's TriggeredByDimTypeId is initially seeded with a placeholder (SELECT DimTypeId FROM DimTypeLookup WHERE DimTypeName = 'Unknown') and a comment saying it “will be updated by trigger” — by the very update that turns out to be a tautology.

I did not catch it because the selective recalculation itself — the part that matters, the IF guard and RecalculateSpecificMeasures — works correctly. The bug is in the log-annotation afterthought, so the measures come out right and the demo is genuinely impressive; it is the audit trail that gets over-stamped. That is the insidious thing about these shadowing bugs: they hide in the bookkeeping around correct core logic, exactly where a passing demo will never surface them. It is one more argument for the rule this whole series runs on — read the code, verify the invariant, do not trust that a working headline means working details.

The real achievement

The tautology is a blemish on an genuinely good idea. The achievement of V8 is that “change something that matters, pay for it; change something that doesn't, pay nothing” is now true at the row level, and demonstrably so. The PaymentMethod zero is the cleanest possible proof that the dependency map is being consulted and obeyed. After eight versions of rebuilding more than necessary, an update that correctly does nothing is a small, precise victory.

Getting a live V7 database to V8 — dropping the old always-rebuild triggers and installing this selective one without losing data — is its own careful operation, and unlike V7's chaotic refactor it ships an actual verification function. Migrating a live star from V7 to V8 is next.