Maandfacturatie — een PL/pgSQL-procedure onder de loep
Een batchprocedure met rijvergrendeling, idempotente inserts en een exception-handler per iteratie. Er zitten twee fouten in die pas na maanden zichtbaar worden.
Deze procedure draait elke nacht en maakt facturen aan voor alle abonnementen waarvan de factuurdatum verstreken is. Hij is geschreven om naast zichzelf te kunnen draaien: meerdere workers tegelijk, zonder dat iemand dubbel gefactureerd wordt.
Dat lukt grotendeels. Maar er zitten twee constructies in die er correct uitzien en dat niet zijn —
één ervan merk je pas als een klant in februari belt. Kijk goed naar de datumberekening en naar wat
de EXCEPTION-tak allemaal opvangt.
CREATE OR REPLACE PROCEDURE facturatie.verwerk_maandfacturen(
p_peildatum date DEFAULT current_date
)
LANGUAGE plpgsql
AS $$
DECLARE
r_abo record;
v_factuur_id bigint;
v_bedrag numeric(12,2);
v_verwerkt int := 0;
v_overgeslagen int := 0;
BEGIN
FOR r_abo IN
SELECT a.id, a.klant_id, a.prijs_per_maand, a.korting_pct, a.valuta
FROM facturatie.abonnementen a
WHERE a.status = 'actief'
AND a.volgende_factuurdatum <= p_peildatum
ORDER BY a.id
FOR UPDATE SKIP LOCKED
LOOP
BEGIN
v_bedrag := ROUND(
r_abo.prijs_per_maand * (1 - COALESCE(r_abo.korting_pct, 0) / 100.0), 2
);
IF v_bedrag <= 0 THEN
v_overgeslagen := v_overgeslagen + 1;
CONTINUE;
END IF;
INSERT INTO facturatie.facturen
(abonnement_id, klant_id, bedrag, valuta, periode, status)
VALUES (r_abo.id, r_abo.klant_id, v_bedrag, r_abo.valuta,
date_trunc('month', p_peildatum)::date, 'open')
ON CONFLICT (abonnement_id, periode) DO NOTHING
RETURNING id INTO v_factuur_id;
IF v_factuur_id IS NULL THEN
v_overgeslagen := v_overgeslagen + 1;
CONTINUE;
END IF;
UPDATE facturatie.abonnementen
SET volgende_factuurdatum = volgende_factuurdatum + interval '1 month',
laatst_gefactureerd_op = p_peildatum
WHERE id = r_abo.id;
INSERT INTO facturatie.factuurregels
(factuur_id, omschrijving, aantal, stukprijs)
VALUES (v_factuur_id,
'Abonnement ' || to_char(p_peildatum, 'YYYY-MM'),
1, v_bedrag);
v_verwerkt := v_verwerkt + 1;
EXCEPTION WHEN OTHERS THEN
v_overgeslagen := v_overgeslagen + 1;
INSERT INTO facturatie.verwerkingsfouten
(abonnement_id, foutmelding, opgetreden_op)
VALUES (r_abo.id, SQLERRM, clock_timestamp());
END;
END LOOP;
RAISE NOTICE 'Facturatie klaar: % verwerkt, % overgeslagen', v_verwerkt, v_overgeslagen;
END;
$$;
Wat doet FOR UPDATE SKIP LOCKED, en waarom staat het hier?
v_factuur_id wordt buiten de loop gedeclareerd en nooit expliciet leeggemaakt. Toch is de check
IF v_factuur_id IS NULL betrouwbaar. Waarom?
Wat doet het BEGIN ... EXCEPTION ... END-blok binnen de loop met de transactie? En waarom overleeft
de rij in verwerkingsfouten de fout wél?
Een abonnement heeft volgende_factuurdatum = 2026-01-31. Wat gebeurt er de komende maanden met die
datum?
Een abonnement staat drie maanden achter: volgende_factuurdatum is 15 mei, en de procedure draait op
15 augustus. Wordt die achterstand ingehaald?
EXCEPTION WHEN OTHERS THEN vangt alles op. Welk probleem levert dat op in een systeem dat met
meerdere workers tegelijk draait?
De CONTINUE staat binnen het BEGIN ... EXCEPTION-blok, dat zelf in de FOR-loop zit. Waar gaat de
uitvoering naartoe, en wat gebeurt er met de rijvergrendeling van het overgeslagen abonnement?