A Batch Has a Name, a Log, and a Deadline

Once you turn off the triggers and move derivation into batches, a batch stops being an implementation detail and becomes a thing — something you name, log, time, and hold accountable. V7 gives every batch run an identity: a generated id, a BatchProcessingLog row, a count of what it processed, and a deadline it must finish inside. This post is that machinery, the orchestrator that runs a batch end to end, and — because I promised honesty about V7's scars — a variable-shadows-column bug sitting right in the middle of it.

A batch gets a name

Every batch begins with CreateBatch, which mints an id and opens a log row:

CREATE OR REPLACE FUNCTION CreateBatch(
    p_BatchType VARCHAR DEFAULT 'Analytical',
    p_CreatedBy VARCHAR DEFAULT 'SYSTEM'
)
RETURNS VARCHAR AS $$
DECLARE
    v_BatchId VARCHAR;
BEGIN
    v_BatchId := p_BatchType || '_' || to_char(now(), 'YYYY_MM_DD_HH24_MI_SS')
                 || '_' || extract(epoch from now())::bigint;

    INSERT INTO BatchProcessingLog (BatchId, BatchType, StartTime, Status, CreatedBy)
    VALUES (v_BatchId, p_BatchType, now(), 'Running', p_CreatedBy);

    RETURN v_BatchId;
END;
$$ LANGUAGE plpgsql;

The id is human-legible on purpose — Analytical_2023_05_07_14_30_00_1683467400 tells you at a glance what kind of batch it was and exactly when it started, with an epoch suffix guaranteeing uniqueness. That id then threads through everything the batch touches: the BatchId stamped on every sale it processes, on every fact it derives, on the log row that records it. A batch is traceable end to end because its name is written onto its work.

A batch gets a log and a deadline

BatchProcessingLog is the batch's permanent record:

CREATE TABLE BatchProcessingLog (
    BatchId VARCHAR(50) PRIMARY KEY,
    BatchType VARCHAR(50),
    StartTime TIMESTAMP,
    EndTime TIMESTAMP,
    Status VARCHAR(20) DEFAULT 'Running',   -- Running → Completed / Failed
    RecordsProcessed INT,
    RecordsFailed INT,
    ProcessingTimeMs INT,
    ErrorMessage TEXT,
    CreatedBy VARCHAR(50) DEFAULT 'SYSTEM'
);

CompleteBatch closes it out — computes the elapsed ProcessingTimeMs, sets Status to Completed or Failed, records how many records were processed and failed, and logs a performance metric. So every batch leaves a row you can audit: when it ran, how long it took, how much it did, whether it succeeded. Run a hundred batches and you have a hundred rows of operational history, which is exactly what you need to answer “is the pipeline keeping up?”

The deadline lives in the orchestrator. Batches are bounded not just by size but by time: a batch is given a MaxProcessingTime interval and is expected to stop when it hits it, leaving the rest for the next run. That time budget is what keeps a batch from running away — a surge of unprocessed sales does not produce one enormous hours-long batch, it produces a series of bounded ones.

The orchestrator

ProcessBatchAnalyticalUpdates ties it together. It is the function that actually runs a batch end to end:

CREATE OR REPLACE FUNCTION ProcessBatchAnalyticalUpdates(
    p_BatchSize INT DEFAULT 1000,
    p_MaxProcessingTime INTERVAL DEFAULT '30 seconds'
)
RETURNS TABLE (ProcessedSales INT, ProcessingTime INTERVAL, Status TEXT, BatchId VARCHAR) AS $$
DECLARE
    v_BatchId VARCHAR := CreateBatch('Analytical');
BEGIN
    BEGIN
        -- 1. pull the unprocessed sales (with the 5-minute age gate)
        -- 2. mark them processed under v_BatchId
        -- 3. derive distinct EmployeeIds → OfficeIds → RegionIds → OrgIds
        -- 4. PopulateAnalyticalDimensionsBatch / PopulateAnalyticalFactsBatch per level
        -- 5. CompleteBatch(v_BatchId, processed, failed, NULL)
    EXCEPTION WHEN OTHERS THEN
        PERFORM CompleteBatch(v_BatchId, 0, 0, SQLERRM);   -- isolate the failure
    END;
    RETURN QUERY SELECT ...;
END;
$$ LANGUAGE plpgsql;

Two things earn their place. The flow walks up the org hierarchy — it takes the batch's sales, finds the distinct employees, then their offices, regions and organisations, and rebuilds the analytical star level by level. And the whole body is wrapped in BEGIN ... EXCEPTION WHEN OTHERS, so a batch that hits an error does not crash the caller — it marks its own log row Failed with the error message and returns cleanly. Per-batch exception isolation means one poisoned batch is a logged failure, not a pipeline outage. There is also an emergency path, ProcessAllUnprocessedSales, that loops thousand-row batches until it drains the backlog or hits its own ten-minute ceiling — the catch-up tool for when the pipeline has fallen behind.

The bug I shipped

I promised to show V7's scars, and here is one, sitting in CompleteBatch. To compute elapsed time it needs the batch's start time, and the code does this:

DECLARE StartTime TIMESTAMP;
...
SELECT StartTime INTO StartTime
FROM BatchProcessingLog
WHERE BatchId = p_BatchId;

Look at it. The local variable is named StartTime, and so is the column. SELECT StartTime INTO StartTime is self-referential — PostgreSQL's name resolution here reaches for the variable on the column side in some contexts, and the statement is at best ambiguous and at worst reads the uninitialised variable into itself. It is a classic PL/pgSQL trap: a local variable sharing a name with a column it queries. The elapsed-time calculation downstream is only as trustworthy as this assignment, and this assignment is a coin flip.

The same shadowing shows up elsewhere in V7 and V8 — GetMonitoringConfig does SELECT ConfigValue INTO ConfigValue, and a batch population function has a WHERE ReferenceId = ReferenceId that, with a same-named variable, is a tautology matching every row. I did not catch these because the happy path looks like it works — a shadowed variable often still produces a plausible number. They are exactly the kind of bug that a type system cannot see and a casual test cannot catch, and they are why I lean so hard, everywhere else in this series, on the rule that you read the code, not the docs. The fix is trivial — rename the variable, or qualify the column — but the lesson is that a batch pipeline you cannot see into is a batch pipeline whose bugs hide in plain sight.

Why identity matters

Giving a batch a name, a log and a deadline is not ceremony. It is what turns “the analytics eventually catch up” into an operable system. When something goes wrong at three hundred sales a second, you do not want to debug an anonymous background process — you want to query BatchProcessingLog, find the Failed row, read its ErrorMessage, see exactly which BatchId stamped which sales, and re-run precisely that work. Identity is what makes a batch pipeline observable, and observability is the whole point of moving off the invisible per-row triggers.

That observability wants to be tunable at runtime, too — batch size, age gates, whether partitioning is even on. V7 makes all of that configurable without a deploy, through feature flags that live inside the database. That is next.