Lab 05 · Cross-engine conversion sprint with parity proof

Shruti's team is decommissioning an Oracle estate, and the migration vendor's slide says "95% automated conversion." The uncomfortable truth of cross-engine work is that the 5% is not syntax — syntax errors announce themselves at compile time. The dangerous residue is semantics: code that compiles, runs, and returns different answers. This lab is the discipline for that residue: convert, then prove parity row-by-row against expected outputs, and keep an honest log of everything the AI got wrong.

Time & tier

~120 minutes hands-on · Core tier (maps to module M2 · Schema, Change & Migration with Safety Gates).

Setup

Local Postgres with dblink (the standard image ships it), plus an AI assistant. Real converters (ora2pg, AWS SCT) are worth knowing; here the transpiler-first pass is simulated by the AI so the lab stays at $0 — the verification method is identical either way.

docker exec -it tuning-lab psql -U postgres -c "create database parity"
docker exec -it tuning-lab psql -U postgres -d parity -c "create extension dblink"

The source, pkg_billing.sql — Oracle dialect, reference only, it will not run anywhere in this lab:

create or replace package body pkg_billing as

  function late_fee(p_days number, p_notes varchar2) return varchar2 is
    v_notes varchar2(100);
  begin
    v_notes := nvl(p_notes, 'NONE');           -- '' is NULL in Oracle
    if p_days <= 0 then return '0.00|' || v_notes; end if;
    return to_char(round(p_days * 12.50, 2)) || '|' || v_notes;
  end;

  function due_date(p_invoice date, p_terms_months number) return date is
  begin
    return add_months(p_invoice, p_terms_months);   -- EOM-aware in Oracle
  end;

  procedure log_attempt(p_inv number, p_msg varchar2) is
    pragma autonomous_transaction;               -- survives caller rollback
  begin
    insert into billing_audit(invoice_id, msg, at_ts)
    values (p_inv, p_msg, sysdate);
    commit;
  end;

  procedure process_invoice(p_inv number) is
    cursor c_lines is
      select line_id, amount from invoice_lines
       where invoice_id = p_inv order by line_id;
    v_total number := 0;
  begin
    log_attempt(p_inv, 'START');
    for r in c_lines loop
      if r.amount < 0 then
        raise_application_error(-20001, 'negative line ' || r.line_id);
      end if;
      v_total := v_total + r.amount;
    end loop;
    update invoices set total = v_total where invoice_id = p_inv;
  exception
    when others then
      log_attempt(p_inv, 'FAIL: ' || sqlerrm);
      raise;
  end;
end pkg_billing;

Target tables and the parity fixtures (authored from Oracle's actual behavior — treat them as ground truth):

create table invoices (invoice_id int primary key, total numeric(12,2));
create table invoice_lines (invoice_id int, line_id int, amount numeric(12,2));
create table billing_audit (invoice_id int, msg text, at_ts timestamptz);
insert into invoices values (101, 0), (102, 0);
insert into invoice_lines values
  (101,1,250.00),(101,2,175.50),(101,3,99.99),
  (102,1,500.00),(102,2,-40.00);          -- 102 must fail, audited

create table parity_expected (
  test_id text primary key, expected text);
insert into parity_expected values
  ('fee_null',   '0.00|NONE'),      -- late_fee(0, NULL)
  ('fee_empty',  '0.00|NONE'),      -- late_fee(0, '')      << trap 1
  ('fee_late',   '62.5|urgent'),    -- late_fee(5, 'urgent')
  ('due_mid',    '2026-04-15'),     -- due_date(2026-01-15, 3)
  ('due_eom',    '2026-03-31'),     -- due_date(2026-02-28, 1)  << trap 2
  ('due_jan31',  '2026-02-28'),     -- due_date(2026-01-31, 1)
  ('inv_ok',     '525.49'),         -- process_invoice(101) then totals
  ('inv_fail_audit', '2');          -- rows in billing_audit for 102  << trap 3

Guided steps

1. 1.

   Run the conversion pass. Give the assistant pkg_billing.sql with: "Convert to PostgreSQL 16 PL/pgSQL. Preserve exact behavior. List every Oracle-specific construct you translated and any whose semantics differ between engines." Save the output as converted_raw.sql and its self-reported caveat list as the start of ai-failure-log.md. Do not fix anything yet — the raw conversion is a specimen.

2. 2.

   Install and smoke-test the raw conversion. Load it into parity (adjust obvious compile errors only — package-to-schema flattening, varchar2→text, number→numeric; log each edit). Expected: it compiles and select late_fee(5,'urgent') returns something plausible. Plausible is the enemy; proceed to parity.

3. 3.

   Build the parity runner. parity_run.sql — one row per test comparing actual to expected:

   Copy

   create or replace function parity_report() returns table(test_id text, expected text, actual text, ok boolean)
   language plpgsql as $$
   begin
     return query
     with actuals(t, a) as (values
       ('fee_null',  late_fee(0, null)),
       ('fee_empty', late_fee(0, '')),
       ('fee_late',  late_fee(5, 'urgent')),
       ('due_mid',   due_date(date '2026-01-15', 3)::text),
       ('due_eom',   due_date(date '2026-02-28', 1)::text),
       ('due_jan31', due_date(date '2026-01-31', 1)::text))
     select e.test_id, e.expected, a.a, (e.expected = a.a)
     from parity_expected e join actuals a on a.t = e.test_id;
   end $$;
   select * from parity_report();
   

   Run it against the raw conversion. Expected: fee_empty and due_eom fail. Record actual-vs-expected for each failure in the parity report.

4. 4.

   Diagnose trap 1 — the empty string. Oracle treats '' as NULL, so nvl('', 'NONE') returns 'NONE'; in Postgres '' is a real value and coalesce('', 'NONE') returns ''. The naive conversion silently changes every empty-string code path. Fix explicitly — nullif(p_notes, '') inside the coalesce — and add a line to the failure log: what the AI produced, why it was wrong, which test caught it.

5. 5.

   Diagnose trap 2 — month arithmetic. Oracle's add_months(date '2026-02-28', 1) returns 2026-03-31 (last-day-in maps to last-day-out); Postgres + interval '1 month' returns 2026-03-28. Most AI conversions emit the interval form and pass the easy cases (due_jan31 works by coincidence of clamping). Fix with an explicit EOM-aware implementation:

   Copy

   create or replace function due_date(p_invoice date, p_terms int)
   returns date language sql as $$
     select case
       when p_invoice = (date_trunc('month', p_invoice)
                         + interval '1 month - 1 day')::date
       then (date_trunc('month', p_invoice) + (p_terms + 1) * interval '1 month'
             - interval '1 day')::date
       else (p_invoice + p_terms * interval '1 month')::date end $$;
   

   Re-run parity: all six scalar tests must pass, including due_jan31 (clamping) and due_eom (EOM promotion). Log the failure.

6. 6.

   Diagnose trap 3 — the autonomous transaction. Run the procedural tests: select process_invoice(101); select total from invoices where invoice_id=101; → expect 525.49. Then select process_invoice(102); — it raises (negative line, correctly). Now check select count(*) from billing_audit where invoice_id = 102;. Naive conversion: 0 — Postgres has no autonomous transactions, so the START and FAIL audit rows died with the caller's rollback. Oracle ground truth says 2. Fix log_attempt to write through a separate connection via dblink:

   Copy

   create or replace procedure log_attempt(p_inv int, p_msg text)
   language plpgsql as $$
   begin
     perform dblink('dbname=parity user=postgres',
       format('insert into billing_audit values (%s, %L, now())', p_inv, p_msg));
   end $$;
   

   Re-run the 102 case (reset audit rows first); expect count = 2 even though the transaction rolled back. Log the miss — and note the honest caveat that dblink audit writes are their own operational surface (connection cost, no shared transaction), which belongs in the migration risk register.

7. 7.

   Full parity run and report. Reset all tables to seed state, run every test in order, and produce parity-report.md: 8 tests, expected, actual, pass/fail — all green — plus the before-fix failure snapshot from steps 3–6.

8. 8.

   Finalize the AI-failure log. For each of the three traps: the raw AI output (quoted), the wrong behavior it produced, the test that caught it, the fix, and one sentence on how you would catch this class of error at scale (grep patterns for '' comparisons, an add_months inventory, a pragma inventory — the pre-conversion census that makes trap-hunting systematic).

Verify

• •
  parity-report.md shows all 8 tests passing after fixes, with the pre-fix failures preserved as evidence.
• •
  ai-failure-log.md has all three traps with quoted AI output and the catching test named.
• •
  billing_audit retains 2 rows for invoice 102 after its failed run; invoice 101's total is 525.49.
• •
  Every manual edit to the raw conversion is logged — no silent fixes.

Self-grading rubric (pass = 8/10): raw conversion preserved as specimen with edits logged (0–1); parity runner built and executed before any fixing (0–2); trap 1 caught, fixed, explained (0–2); trap 2 caught with the EOM distinction articulated (0–2); trap 3 caught with a working dblink fix and its caveat noted (0–2); failure log includes the at-scale census idea (0–1).

Stuck?

• dblink extension missingit ships with the standard Postgres image's contrib package; run create extension dblink inside the parity database, not postgres.
• process_invoice will not compile as a functionPostgres procedures and functions differ on transaction control; a plain function ... returns void works for every test here.
• fee_late returns 62.50|urgent instead of 62.5|urgentOracle's to_char(number) trims trailing zeros; match it with round(...)::text semantics or trim explicitly, and log the formatting difference either way.