Testing Stored Procedures in Postgres — Why PL/pgSQL Only Fails at Call Time, and What a Test Has to Build First

Published Nov 8, 2026
by

A click on “Duplicate” in the project list, a red notice “The project could not be duplicated”, nothing else. The server log has the reason: a procedure reads a column that has not existed for five months. During those five months, the deploy ran green on four environments, production included. The convention check had nothing to complain about, and more than a thousand unit tests were green. None of these nets had ever called the procedure. PL/pgSQL resolves the column names in a body only at call time, and in an empty throwaway database nothing calls the procedure.

This article shows why there is no cheap test layer for the behavior of stored procedures in Postgres, and how to test Postgres stored procedures without installing a tool for it. The means are probes, integration tests against the real database in testing vocabulary: small SQL scripts that build an initial state from synthetic data in the gate’s throwaway database, call the real procedure, and check each requirement individually.

The key points up front:

  • Three nets, one gap: A convention check checks the form, the deploy checks that everything can be created, and unit tests stop before the database. None of them checks the behavior of a procedure.
  • A probe is a SQL script that creates test data, calls the real procedure, and writes every result to a table. The checking happens outside SQL, and when it goes red, the stored state comes first, not a guess.
  • The test consists mostly of setup: In the DI² project, 85 percent of the probe code is the SQL files that create test data and call the procedure, 7,266 lines across 22 probes. The checks for them take 1,328 lines of shell script.
  • A probe counts only once it has gone red against the broken state. Performance requirements need volume via generate_series, and a time gate in CI is still a bad idea.
  • Transparency: The project’s script, fixtures, and checks come from Claude Code sessions. The maintainer reported the bugs or requested the checks, verified the negative probes, and made the decisions this article is about.

Prerequisite: Postgres and psql, a CI run with a throwaway database as described in GitHub Actions for Postgres Deploys, and basic knowledge of PL/pgSQL. All code blocks were executed against PostgreSQL 17.11 in a throwaway Docker container, including the negative probe and the measurement. The examples use a small domain of their own made of customers and orders, and the figures from the DI² project are as of November 5, 2026.

Contents

Three Nets, One Gap

In the DI² project, three automated nets stand in front of every merge. A convention check reads every SQL file line by line and checks the form: banners, naming rules, forbidden constructs. What it can and cannot do is in Why sqlfluff Can’t Lint Our SQL Conventions. The deploy smoke applies the complete schema to a fresh throwaway database, and it does so twice, so that the second run on an existing state is proven as well. That gate is described in GitHub Actions for Postgres Deploys. The application’s unit tests check TypeScript, and by an explicit rule only code without database access.

Each of these nets has a job. But no net checks the behavior of a procedure. How big the gap is was measured by a Claude Code session in September 2026, by deliberately removing a working safeguard and then checking whether any net would fire. First it removed from a procedure the block that truncates an overlong text to the column width before the insert, and ran all gates. That block was one half of a fix, the other half lived in the TypeScript. There, a function writes a batch of messages in one transaction. Since the fix, each insert step sits in its own savepoint, so that a failed message rolls back only itself and not the whole batch. The session deliberately removed those savepoints too, ran all gates again, and restored both files afterwards. The question was not whether the safeguard should go, but whether anyone would notice it was gone.

InterventionConvention checkDeploy smokeUnit tests
Truncation block removed from the procedure0 findingsgreennot in scope
Savepoint isolation removed from the TypeScriptnot in scopenot in scope1,101 of 1,101 green

For the SQL half, the reason is easy to name. The convention check sees form, not behavior. The deploy smoke at the time ran against an empty database, and in an empty database an overlong text never occurs. Both nets were green although the safeguard was gone. For the TypeScript half, it is shorter still: the function accesses the database, and such functions have no unit test by rule. There was nothing that could have gone red.

The case of the vanished column sits one level deeper still, because there not even a call with the right data would have been needed. Any call would have done. The example below reproduces it in the small domain. A procedure copies a customer along with their orders and takes a column legacy_state with it. Three days after this procedure, a schema script removes the column. The next deploy creates the procedure again:

  1: CREATE TABLE shop.customer
  2: (
  3:     id              bigint          GENERATED ALWAYS AS IDENTITY PRIMARY KEY
  4:    ,name            varchar(100)    NOT NULL
  5:    ,legacy_state    varchar(20)         NULL
  6: );
  7:
  8: CREATE TABLE shop.sales_order
  9: (
 10:     id              bigint          GENERATED ALWAYS AS IDENTITY PRIMARY KEY
 11:    ,customer_id     bigint          NOT NULL REFERENCES shop.customer (id)
 12:    ,amount          numeric(12, 2)  NOT NULL
 13:    ,approved_on     timestamptz         NULL
 14: );
 15:
 16: CREATE OR REPLACE PROCEDURE shop.sp_dup_customer
 17: (
 18:     IN    p_customer_id        bigint
 19:    ,INOUT p_new_customer_id    bigint
 20: )
 21: LANGUAGE plpgsql
 22: AS $procedure$
 23: BEGIN
 24:    INSERT INTO shop.customer (name, legacy_state)
 25:    SELECT
 26:        name || ' (copy)'
 27:       ,legacy_state
 28:    FROM
 29:       shop.customer
 30:    WHERE
 31:       id = p_customer_id
 32:    RETURNING id INTO p_new_customer_id;
 33:
 34:    INSERT INTO shop.sales_order (customer_id, amount)
 35:    SELECT
 36:        p_new_customer_id
 37:       ,amount
 38:    FROM
 39:       shop.sales_order
 40:    WHERE
 41:       customer_id = p_customer_id;
 42: END;
 43: $procedure$;
 44:
 45: -- Three days later, a schema script removes the column.
 46: ALTER TABLE shop.customer DROP COLUMN legacy_state;
 47:
 48: -- The next deploy creates the same procedure again, unchanged (abridged here,
 49: -- it is lines 16 to 43 once more). Postgres answers as it did the first time:
 50: -- CREATE PROCEDURE
 51:
 52: -- Only the call resolves the body, even with empty tables.
 53: CALL shop.sp_dup_customer(1, NULL);
 54: -- ERROR:  column "legacy_state" of relation "customer" does not exist
 55: -- CONTEXT:  PL/pgSQL function shop.sp_dup_customer(bigint,bigint) line 3 at SQL statement

Lines 48 to 50 are the point. The repeated CREATE OR REPLACE PROCEDURE goes through although the body reads a column that no longer exists. When creating a PL/pgSQL routine, Postgres checks the syntax and the declarations. The identifiers in the body’s SQL statements are resolved only when the statement is executed for the first time, in the example at the first call. A statement in a branch that only a later call passes through is resolved only then. That is how the Postgres documentation describes it in the chapter PL/pgSQL under the Hood, and that is what the context of the error message in line 55 shows: line 3 at SQL statement, not CREATE PROCEDURE. With check_function_bodies switched off, even this check at creation time is skipped, and syntax and declarations are then also checked only at the first call: a body with a typo in the SELECT or an unknown type in DECLARE can be created, and only the CALL reports the error. For the same reason, the schema script in line 46 was allowed to remove the column at all: for the tables and columns that the SQL statements of a PL/pgSQL body use, Postgres registers no dependency, so DROP COLUMN finds no object hanging on the column. A SQL function with a BEGIN ATOMIC body does register them, and there the DROP COLUMN already fails with a hint to the dependent function.

For a CI gate, this means: a deploy that creates a procedure proves that it can be created. What it does, and whether it runs at all, it does not prove.

What Requirement a Probe Checks, and Where It Is Written

A requirement here is a sentence about behavior that the code has to satisfy. “The copy takes over the customer and all orders, but no approval.” “An overlong text is truncated to the column width before the insert, without two different texts becoming identical in the process.” “No path via the parent table deletes the foreign key of an approved child table.” Two of these sentences speak of an approval, a business state of the data. In the DI² project, the owner approves a fully configured table. From that moment on, its configuration is locked: columns can no longer be deselected, the table can no longer be deactivated, and the procedures that try reject the call. In the small domain of this article, an order is approved once the customer has inspected and released the delivery. In the schema, the timestamp approved_on stands for that. A copy starts without approval: what was released was the original, not the copy.

Each probe in the project checks one or a few such requirements, and they are written verbatim in the header of the fixture file, before the first line of SQL. Fixture is the testing term for the prepared initial state a test needs before it can check anything. In this article it means the SQL file of a probe. It creates the test data, calls the real procedure, and writes every result to the result table.

The rule for this is short and came out of the measurement above: SQL behavior belongs in a probe of the deploy smoke. A check is cheap there, because the throwaway database is up anyway and the schema has just been applied. The rule has a counterpart that is just as important. Not every requirement gets a probe. The savepoint half from above, for example, lives in a TypeScript function with database access, and such functions are exempt from unit tests in the project by rule. For every requirement that deliberately stays without a net in this way, the project keeps a row in an inventory table. Its four columns record which requirement it is, why no net checks it, what a refactoring would silently break, and which planned change will close the gap later. Without this entry, a deliberately unchecked requirement would look exactly like one that simply nobody thought of. In both cases there is no test that could go red, and CI is green.

The Initial State Before the Test

What data a single requirement needs is shown by the third one above. “No path via the parent table deletes the foreign key of an approved child table” is one sentence. The probe for it needs two parent tables and two child tables, two foreign keys, a column snapshot of the first parent table, an approval on exactly one child table, and after that seven named cases: deactivating the parent is rejected, deselecting a parent column is rejected, deselecting a different column goes through, the second parent without an approved child may be deactivated, plus three diagnostic rows that record the state afterwards. That is 183 lines of SQL for one sentence.

That is not an outlier, it is the rule. The project’s 22 probes consist of 7,266 lines of fixture SQL, and the checks for them of 1,328 lines of shell script. So 85 percent of the probe code is in the SQL files, and in those files only around 15 percent of the lines belong to calling the procedures and recording the results. The rest build the initial state. The three largest probes have 1,030, 944, and 923 lines, and most of that is setup: accounts, projects, memberships, connections, tables, columns, rules, foreign keys, approvals, in the right order and with the right roles.

This is the real complexity of testing database behavior, and it does not lie in the check. The check, in the end, is a counter or a comparison. The work lies in producing a state in which the requirement has any meaning at all. A procedure that protects a foreign key needs a foreign key, an approval, and a caller with the matching role, otherwise there is nothing to protect. And because the probe is supposed to check the behavior against the real database, each of these building blocks has to be real. A stand-in would replace exactly the part that matters. That is where a probe differs from a UI test, which may replace its server with a stand-in that returns a prepared response: a UI test consists of a lot of checking and little setup. A probe consists of a lot of setup and little checking.

The Probe Pattern

The pattern can be shown in full on the small domain. First the corrected procedure, with the requirement in the comment:

  1: -- 155002: The corrected procedure. The requirement on it: the copy takes over the
  2: -- customer and all orders, but no approval.
  3: -- --------------------------------------------------------------------------------
  4: -- Parameter
  5: -- --------------------------------------------------------------------------------
  6: --    p_customer_id        bigint
  7: --       Customer to be copied
  8: --    p_new_customer_id    bigint
  9: --       Id of the copy (returned)
 10: -- --------------------------------------------------------------------------------
 11: CREATE OR REPLACE PROCEDURE shop.sp_dup_customer
 12: (
 13:     IN    p_customer_id        bigint
 14:    ,INOUT p_new_customer_id    bigint
 15: )
 16: LANGUAGE plpgsql
 17: AS $procedure$
 18: BEGIN
 19:    INSERT INTO shop.customer (name)
 20:    SELECT
 21:       name || ' (copy)'
 22:    FROM
 23:       shop.customer
 24:    WHERE
 25:       id = p_customer_id
 26:    RETURNING id INTO p_new_customer_id;
 27:
 28:    -- Copy the orders, deliberately not the approvals (approved_on stays NULL).
 29:    INSERT INTO shop.sales_order (customer_id, amount)
 30:    SELECT
 31:        p_new_customer_id
 32:       ,amount
 33:    FROM
 34:       shop.sales_order
 35:    WHERE
 36:       customer_id = p_customer_id;
 37: END;
 38: $procedure$;

Then the probe. It consists of four parts: a result table, the initial state, the cases, and a check that runs outside SQL.

  1: -- 155003: The probe. Builds an initial state, calls the real procedure
  2: -- and writes every result to a table. The checking happens outside SQL.
  3: -- For throwaway databases only.
  4: SET client_min_messages = warning;
  5:
  6: DROP TABLE IF EXISTS shop._probe_dup_customer;
  7: CREATE TABLE shop._probe_dup_customer
  8: (
  9:     label       varchar(40)     PRIMARY KEY
 10:    ,sqlstate    varchar(5)          NULL
 11:    ,message     text                NULL
 12:    ,value       text                NULL
 13: );
 14:
 15: BEGIN;
 16:
 17: -- --------------------------------------------------------------------------------
 18: -- Initial state: one customer, two orders, one of them approved
 19: -- --------------------------------------------------------------------------------
 20: INSERT INTO shop.customer (name) VALUES ('_probe_customer');
 21:
 22: INSERT INTO shop.sales_order (customer_id, amount, approved_on)
 23: SELECT
 24:     T01.id
 25:    ,T02.amount
 26:    ,T02.approved_on
 27: FROM
 28:    shop.customer T01
 29:    CROSS JOIN (VALUES (100.00, NULL::timestamptz), (250.00, now())) AS T02 (amount, approved_on)
 30: WHERE
 31:    T01.name = '_probe_customer';
 32:
 33: -- --------------------------------------------------------------------------------
 34: -- Cases: an error is caught and recorded, not passed through
 35: -- --------------------------------------------------------------------------------
 36: DO $do$
 37: DECLARE
 38:    l_source      bigint;
 39:    l_copy        bigint;
 40:    l_count       int;
 41:    l_approved    int;
 42: BEGIN
 43:    SELECT id INTO l_source FROM shop.customer WHERE name = '_probe_customer';
 44:
 45:    -- Fixture self-test: two orders, exactly one of them approved, otherwise abort.
 46:    SELECT count(*), count(approved_on) INTO l_count, l_approved
 47:    FROM shop.sales_order WHERE customer_id = l_source;
 48:    IF l_count <> 2 OR l_approved <> 1 THEN
 49:       RAISE EXCEPTION 'Fixture incomplete: % orders, % approved', l_count, l_approved;
 50:    END IF;
 51:
 52:    -- Case 1: The procedure runs through (expected: sqlstate empty).
 53:    BEGIN
 54:       CALL shop.sp_dup_customer(l_source, l_copy);
 55:       INSERT INTO shop._probe_dup_customer (label, value)
 56:       VALUES ('call_ok', l_copy::text);
 57:    EXCEPTION WHEN OTHERS THEN
 58:       INSERT INTO shop._probe_dup_customer (label, sqlstate, message)
 59:       VALUES ('call_ok', SQLSTATE, SQLERRM);
 60:    END;
 61:
 62:    -- Case 2: Both orders are copied (expected: 2).
 63:    SELECT count(*) INTO l_count FROM shop.sales_order WHERE customer_id = l_copy;
 64:    INSERT INTO shop._probe_dup_customer (label, value)
 65:    VALUES ('order_count', l_count::text);
 66:
 67:    -- Case 3: No approval travels along (expected: 0).
 68:    SELECT count(*) INTO l_count
 69:    FROM shop.sales_order
 70:    WHERE customer_id = l_copy AND approved_on IS NOT NULL;
 71:    INSERT INTO shop._probe_dup_customer (label, value)
 72:    VALUES ('approved_copied', l_count::text);
 73:
 74:    -- Case 4: The copy's amounts match the original (expected: 0).
 75:    SELECT count(*) INTO l_count
 76:    FROM
 77:    (
 78:       (SELECT amount FROM shop.sales_order WHERE customer_id = l_source
 79:        EXCEPT ALL
 80:        SELECT amount FROM shop.sales_order WHERE customer_id = l_copy)
 81:       UNION ALL
 82:       (SELECT amount FROM shop.sales_order WHERE customer_id = l_copy
 83:        EXCEPT ALL
 84:        SELECT amount FROM shop.sales_order WHERE customer_id = l_source)
 85:    ) T01;
 86:    INSERT INTO shop._probe_dup_customer (label, value)
 87:    VALUES ('amount_diff', l_count::text);
 88: END;
 89: $do$;
 90:
 91: COMMIT;

The result table in lines 7 to 13 is the core of the pattern. Each case writes one row with a descriptive label, plus either a value or, if an error occurred, SQLSTATE and message. The label is the primary key, so a probe that writes the same label twice aborts on its own. The probe decides nothing, it records. There are two reasons for that. A RAISE EXCEPTION at the first finding would hide the remaining cases, and an error message that guesses the cause would regularly be wrong, because several requirements can be violated at the same time.

The initial state in lines 17 to 31 is as small as possible and as large as necessary: one customer, two orders, one of them approved. Whether it came out that way is checked by the self-test in lines 45 to 50 before the first case runs: two orders, exactly one of them approved, otherwise the probe aborts.

The cases in lines 36 to 89 call the real procedure. The call in line 54 sits in its own BEGIN block with an EXCEPTION branch. An error is caught and recorded with its SQLSTATE, it does not tear down the probe. This frame assumes that the procedure can run inside the probe’s transaction. A procedure that itself executes COMMIT or ROLLBACK needs a different call, more on that in the section on the limits of the pattern. Which error can be caught in the call is shown by the first block: with the old procedure, 42703 would appear here, because the body reads the column legacy_state that no longer exists, and the message would only come at the CALL. A second example sits in the copy itself: the name gets the suffix “(copy)”, and for an original with more than 93 characters it no longer fits into varchar(100), error code 22001. Without the EXCEPTION branch, each of these errors would abort the DO block, cases 2 and 3 would never run, and the check would find an empty table with no hint at the cause. If the probe aborts anyway, the error does not come from the call, because that is caught. Then something is wrong with the probe itself, such as a wrong table name in the setup or a typo in a counting query. Such an abort says something about the probe, not about the procedure. The two counters after it check the two halves of the requirement separately: line 63 counts the copied orders, line 68 counts the approvals on the copy. If the requirement is violated, the label shows which half it is. The counter in line 63 checks only the number of orders, not their content, and a procedure that creates two orders with wrong amounts would stay green with it. That is why case 4, from line 74 on, compares the amounts themselves: EXCEPT ALL in both directions returns every order that occurs on only one side, duplicates included, and the target is zero deviations.

The check reads the table from outside:

  1: #!/bin/bash
  2: # 155004: The check. Reads the probe's result table, shows the stored
  3: # rows first when red, and then aborts.
  4: set -u
  5: PSQL=(psql -v ON_ERROR_STOP=1 -tA -d "${PGDATABASE:-probe}")
  6:
  7: fail() {
  8:   echo "--- Probe red. Stored rows (label | sqlstate | message | value): ---" >&2
  9:   "${PSQL[@]}" -c "SELECT label || ' | ' || coalesce(sqlstate, '') || ' | ' || coalesce(message, '') || ' | ' || coalesce(value, '') FROM shop._probe_dup_customer ORDER BY label;" >&2
 10:   echo "ERROR: $1" >&2
 11:   exit 1
 12: }
 13:
 14: # expect <label> <expected> <message>: exactly one row with this label, without SQLSTATE,
 15: # with value = expected. The expected value "*" requires only the row without SQLSTATE.
 16: # If psql cannot read the table, that is red as well.
 17: expect() {
 18:   local row
 19:   row="$("${PSQL[@]}" -c "SELECT count(*) || '|' || coalesce(max(sqlstate), '') || '|' || coalesce(max(value), '') FROM shop._probe_dup_customer WHERE label = '$1';")" \
 20:     || fail "psql could not read the result table."
 21:   [[ "$row" == "1||"$2 ]] || fail "$3"
 22: }
 23:
 24: expect call_ok         "*" "The call aborted or left no row behind."
 25: expect order_count     "2" "Expected 2 copied orders."
 26: expect approved_copied "0" "An approval was copied along."
 27: expect amount_diff     "0" "The copy's amounts differ from the original."
 28:
 29: echo "Probe green: 4 of 4 checks passed."

It runs in the shell, because the gate is a shell script anyway and needs an exit code. The function fail in lines 7 to 12 prints the stored rows first when red and only then aborts. Whoever reads the run in the CI log sees the actual state and does not have to reconstruct it from a message. The function expect in lines 17 to 22 requires exactly one row without SQLSTATE for each label and compares the value with the expected one. A missing row is thus red and not green, and so is a psql that cannot read the table. The four lines 24 to 27 are the requirement, broken down into four checks.

The values of a probe are chosen so that each violated requirement stands out individually. The probe for the customer therefore creates two orders instead of one, so that “copies all orders” can be told apart from “copies one”. One of the two is approved, the other is not. A procedure that copies approvals along thus shows up in case 3, one that skips approved orders in case 2, and one that changes an amount while copying in case 4. None of these defects hides behind another, because the label says which check is red.

The brief for such a probe is short, because the pattern is in place. The following prompt is reconstructed from the bug report and the fixture file, not quoted verbatim from the session:

The procedure for duplicating a project ran green through the deploy for
five months and was broken all the same, because nothing called it.
Write a probe for the deploy smoke following the pattern of the existing
probes: its own fixture file, a result table with label, sqlstate and
message, checks in the script. The requirement it checks: duplicating
takes over everything that makes up the working state, but no approvals.
The original must contain at least one record from every area.
Acceptance criterion: against the state before the fix the probe goes red,
afterwards green, and the CI run stays green.

From this came the project’s largest probe with 1,030 lines, and almost all of them build the original, which contains at least one record from every area. The checks at the end are counters.

When a Probe Counts: Negative Probe and Fixture Self-Test

The last sentence of the prompt is the most important one, and it is the one most often left out. A probe that is green has not proven anything yet. It can be green because the requirement is met, or because it never produces the case that would violate the requirement. Without the approved order in the initial state, case 3 would always be 0, with any procedure. Only the negative probe shows the difference: the probe runs against the broken state and has to go red. In the small domain, that means the procedure copies approved_on along by mistake. The probe then reports:

--- Probe red. Stored rows (label | sqlstate | message | value): ---
amount_diff |  |  | 0
approved_copied |  |  | 1
call_ok |  |  | 2
order_count |  |  | 2
ERROR: An approval was copied along.

Three of the four checks pass, the one on the approval does not, and the dump says with which value. The negative probe thus proves that the probe reacts to this one defect. That it would find every other defect of the procedure, it does not prove. Whoever wants to know that introduces further defects one after another, the way mutation tests do systematically. In the project, this probe is part of the deal before a new probe counts. More than forty bug reports record that the new probe went red against the state before the fix, before the fix counted as done. That is the same principle as the counter-check for automated tests in The Green Test That Proves Nothing: a test that does not react to the code does not check it.

For the initial state there is a second trap that the negative probe does not see. A fixture can become ineffective without anything going red. If the approved order in the customer probe were created without approved_on through a typo, case 3 would stay green forever, and nobody would notice. That is why a fixture checks for itself, before the call, whether it has produced the state it is supposed to produce. In the probe above, those are lines 45 to 50: two orders, exactly one of them approved, otherwise the block aborts with an error message, and the check finds no row call_ok. In the project, the fixtures that are applied between the two deploy runs include such a fixture self-test, and all of them answer the same question: does this test still check anything?

Volume Instead of a Minimal State

A minimal initial state proves behavior. For a performance requirement it is worthless, because a customer with two orders is copied quickly in any implementation. The requirement in the project was that a project of a certain size is duplicated in at most ten seconds. For that, the probe needs a project of that size, and it generates one with generate_series: two connections, 200 tables, 10,000 columns, 500 rules, and 300 foreign keys. In the small domain, the same setup looks like this:

  1: -- 155005: Volume fixture and measurement run. 200 customers with 50 orders each,
  2: -- every customer is copied once. Completeness is checked, the duration is printed.
  3: -- For throwaway databases only.
  4: INSERT INTO shop.customer (name)
  5: SELECT
  6:    'perf_' || lpad(n::text, 3, '0')
  7: FROM
  8:    generate_series(1, 200) n;
  9:
 10: INSERT INTO shop.sales_order (customer_id, amount, approved_on)
 11: SELECT
 12:     T01.id
 13:    ,(n * 1.5)::numeric(12, 2)
 14:    ,CASE WHEN n % 10 = 0 THEN now() END
 15: FROM
 16:    shop.customer T01
 17:    CROSS JOIN generate_series(1, 50) n
 18: WHERE
 19:    T01.name LIKE 'perf\_%';
 20:
 21: DO $do$
 22: DECLARE
 23:    l_started     timestamptz;
 24:    l_source      record;
 25:    l_copy        bigint;
 26:    l_expected    bigint;
 27:    l_actual      bigint := 0;
 28: BEGIN
 29:    SELECT count(*) INTO l_expected
 30:    FROM
 31:       shop.sales_order T01
 32:       INNER JOIN shop.customer T02
 33:       ON
 34:         T02.id = T01.customer_id
 35:    WHERE
 36:       T02.name LIKE 'perf\_%';
 37:
 38:    -- The clock starts only before the loop: measured is the duration of all 200 calls together.
 39:    l_started := clock_timestamp();
 40:    FOR l_source IN
 41:       SELECT id FROM shop.customer WHERE name LIKE 'perf\_%' ORDER BY id
 42:    LOOP
 43:       CALL shop.sp_dup_customer(l_source.id, l_copy);
 44:       SELECT l_actual + count(*) INTO l_actual
 45:       FROM shop.sales_order
 46:       WHERE customer_id = l_copy;
 47:    END LOOP;
 48:
 49:    RAISE NOTICE 'Copied: % of % orders in % ms'
 50:       ,l_actual
 51:       ,l_expected
 52:       ,round(extract(epoch FROM clock_timestamp() - l_started) * 1000);
 53:
 54:    -- The gate is completeness, not time.
 55:    IF l_actual <> l_expected THEN
 56:       RAISE EXCEPTION 'Copy incomplete: % of % orders', l_actual, l_expected;
 57:    END IF;
 58: END;
 59: $do$;
 60: -- NOTICE:  Copied: 10000 of 10000 orders in 174 ms

Lines 4 to 19 create 200 customers with 50 orders each, every tenth one approved. The measurement run from line 21 on first determines the expected number, starts the clock in line 39, and then copies every customer once. It records two things: the duration of the whole loop, that is 200 calls including the count after each, and the number of copied orders. Line 55 is the actual check, and it checks completeness, not time. It does not check the content of the copies either, the probe above did that with its minimal initial state. In the container, three runs with the small domain on one day took between 2.2 and 2.9 seconds, ten more on the following days on the same machine between 0.2 and 1.0 seconds. The probe prints this duration but does not decide on it.

That time is not a gate was a deliberate decision in the project, with two reasons. The first is the CI runner, which fluctuates. Before an optimization, the copy under the application role took eleven and twenty-three times the reference, afterwards still seven times, and none of these values works as a threshold. The second reason is more important: the time was a symptom, the cause was the shape of the execution plan. The gate has sat on that plan shape ever since, the duration is only printed, and the ten seconds are measured during the manual check on the development environment.

The most important finding of this probe, however, only came with the third measurement. The first version measured as superuser, because that is how the smoke runs. In operation, the procedure runs under the application role, with row-level permissions and an actor. The actor is the logged-in user that the application passes to the database session, and the row-level permissions refer to them. Only the measurement under this role showed 144 seconds, in one case almost five minutes. As superuser, none of that was visible, because the row-level permissions cost the superuser nothing. Since then, the probe measures three times and requires completeness for each of the three copies. The brief for that, reconstructed:

The performance probe measures the duplication as superuser. That is not
how the procedure runs in the application: there, the application role
with row-level permissions and an actor apply. Please measure the copy
three times, as superuser for reference, under the application role with
an actor and without one, and print all three times. No hard time gate,
the CI runner fluctuates. The only check is that each of the three copies
is complete.

The lesson from this applies to every volume probe: the role is part of the initial state. A measurement under the wrong role measures a different system.

Fixtures Between the Deploy Runs: Backfill and Drift

GitHub Actions for Postgres Deploys explains why the gate applies the schema twice: the second run proves that every file is repeatable on an existing state. It proves this, however, only for the state the first run left behind, and that is an empty database with a fresh schema. Two error classes it therefore does not see, and the project closes both with fixtures that are applied between the two runs.

A backfill is a deploy script that brings the existing data up to date after a schema change. In the project, it loops over all active tables and calls, for each one, a procedure that derives new check rules. In the empty throwaway database there is no active table. The loop runs zero times, the call never happens, and the deploy is green. The call runs under the role of the schema owner, and that role once lacked a privilege. On the first environment with data, the deploy would have been red, and in the gate nobody saw it. The backfill fixture therefore creates three records before the second run: a project, a connection, an active table. With that, the loop runs at least once, the call happens, and a missing privilege turns the second run red. How such scripts run exactly once is in Tracking Schema Changes Without a Framework.

The drift fixture simulates an existing database that lags behind the repository: it sets eight check constraints to an old state and creates a row that is valid only under the old constraint. The second run has to bring every definition back to the target state and migrate the row. Without the fixture, there would never be an old constraint for the run to work against.

This is the answer to the open point that “GitHub Actions for Postgres Deploys” ends on. There it says that data-dependent effects stay invisible in the gate, and that the common complement is a nightly run against an anonymized production dump. For requirements on behavior, the synthetic initial state in the same gate is the better answer: it runs on every pull request, it is versioned, it contains exactly the constellation that matters, and it needs no real data. The dump keeps its place where it is about data distribution, lock durations, and runtimes on real volumes. Those are different questions, and they do not belong in the PR gate.

pgTAP, plpgsql_check, and the Limits of the Pattern

Anyone who tests Postgres routines knows pgTAP: an extension that provides assertion functions such as ok(), is(), and results_eq() in SQL, outputs results in TAP format, and fits into any test runner via pg_prove. In the DI² project, pgTAP was never evaluated. The probes grew out of the existing deploy smoke, bug by bug, and there was no tool decision. The difference therefore lies in the packaging, not in the idea. A pgTAP file builds the same initial state and checks the same requirements, only the check is written in SQL instead of the shell. For that it needs an extension in the database and a second runner. The shell variant needs only psql and runs in the same run as the deploy. Whoever starts from scratch and plans many probes anyway should look at pgTAP. Whoever has a deploy gate gets by with the pattern here without a new dependency.

For the error class of the vanished column there is, in addition, a cheaper net than any probe. The extension plpgsql_check checks the body of a routine statically, resolving the identifiers as it goes, and reports the removed column without anyone calling the procedure. Against the broken procedure from the first example, version 2.10 in the container returns:

SELECT * FROM plpgsql_check_function('shop.sp_dup_customer(bigint, bigint)');

error:42703:3:SQL statement:column "legacy_state" of relation "customer" does not exist
Query: INSERT INTO shop.customer (name, legacy_state)
--                                      ^

After the fix, the same query returns zero rows. A call of plpgsql_check_function over all routines of a schema therefore belongs at the end of every deploy run as an additional net, in the gate too. It catches orphaned columns and tables at deploy time, in seconds, without test data. What it cannot see is SQL whose text depends on parameters or data only at runtime and runs via EXECUTE. An EXECUTE string that it can assemble from constants in the body, including via format() or a variable, it does check. Conversely, it reports a temporary table that the body itself creates as missing, although the call goes through. Every finding of the check therefore needs a look into the body before it counts as an error. And what it fundamentally does not catch is everything this article is about: whether the copy takes the approval along, whether the truncation keeps two different texts apart, whether the foreign key survives the detour via the parent table. Those are requirements on behavior, and behavior needs a call with data.

The pattern has limits. Three of them are already visible in the project, a fourth is set by Postgres itself. Probes couple themselves to details: the probe for the text truncation knows the width of the column, and whoever widens the column has to touch two files. The runtime of the gate grows with every probe, and 22 probes today run one after another in a single run. And a probe checks only what someone has written down as a requirement. What nobody has written down is either in the inventory or is left to the manual click. The fourth limit concerns procedures that control their own transaction. Postgres allows COMMIT and ROLLBACK in a procedure only if the CALL sits at the top level in autocommit mode. The call in the probe, by contrast, sits between BEGIN and COMMIT, that is in an explicitly opened transaction block, and additionally in a block with an EXCEPTION branch, which internally opens a subtransaction. Either of the two is enough for the error 2D000, and the probe reports red although the procedure runs flawlessly in the application. For such a procedure, the call needs its own psql invocation without a BEGIN before it, and the check then reads its exit code and the state of the tables afterwards.

Summary

  • Creating a PL/pgSQL routine checks syntax and declarations. The columns and tables in the body are resolved by Postgres only at the first call. A deploy gate therefore proves that a procedure can be created, not that it runs.
  • A probe builds an initial state from synthetic data, calls the real procedure, catches errors, and writes every result to a table. The check runs outside SQL and, when red, shows the state first.
  • The test consists mostly of setup. In the DI² project, 85 percent of the probe code is in the SQL files, and in them only around 15 percent of the lines belong to call and recording. The initial state also includes the role under which the procedure runs in the application.
  • A probe counts only after the negative probe, a fixture only after its self-test.
  • For performance requirements, generate_series generates the volume. The gate is completeness, the time is printed.

For practice, as a decision aid:

Starting pointRecommended route
A routine reads columns or tables that may changeplpgsql_check over all routines at the end of the deploy run
A requirement on behavior: copies X but not Y, rejects ZProbe with a minimal initial state in the gate, validated by a negative probe
A performance requirementVolume fixture via generate_series, completeness as gate, duration as output, measurement under the application role
A deploy script depends on existing dataFixture between the two deploy runs, with self-test
Data distribution, lock durations, runtimes on real volumesRun against an anonymized dump, outside the PR gate

FAQ

How do you test a stored procedure in Postgres without pgTAP?

With a SQL script that creates the necessary tables and rows in a throwaway database, calls the procedure, and writes every result with a label to a table. Errors are caught in a BEGIN block with an EXCEPTION branch and recorded with their SQLSTATE. A shell script reads the table via psql -tAc, compares the values with the expected ones, and exits with a non-zero exit code on any deviation. In CI, the script runs right after the deploy against the same throwaway database.

Why is the double deploy from the CI gate not enough?

The second deploy run proves that every object file is repeatable on an existing state. But it calls no procedure and works against a database without data. A procedure that reads a removed column can be created any number of times, because Postgres resolves the column names in the body only at call time. And a procedure that copies something wrong only does so once there is something to copy. The first needs any call, the second a call with suitable data. A probe delivers both, and a static check with plpgsql_check delivers the first as well.

Can Postgres check a stored procedure for errors without executing it?

Partly, with the extension plpgsql_check. It analyzes the body of a routine statically, resolves table and column names as it goes, and reports, for example, a column that no longer exists, without anyone calling the procedure. It does not check behavior: whether the procedure copies, rejects, or truncates the right thing only shows in a call with data, that is, in a probe.

How do you know that a probe really checks something?

Through the negative probe. The probe runs once against the broken state, that is against the procedure before the fix or against a version from which the checked line was deliberately removed, and has to go red in the process. If it stays green, it does not reach the requirement. For fixtures, a self-test is added that proves the fixture really produced the state, for example through a counter on the created rows.

How do you test a stored procedure that issues COMMIT or ROLLBACK itself?

Not in the probe frame from above, but in a psql invocation of its own. Postgres allows COMMIT and ROLLBACK in a procedure only if the CALL sits at the top level in autocommit mode. Between BEGIN and COMMIT, or in a block with an EXCEPTION branch, the call aborts with the error 2D000 (invalid transaction termination), although the procedure runs flawlessly in the application. The probe therefore calls such a procedure without a BEGIN before it, and the check reads the exit code of psql and then the state of the tables.

May you use real production data as test data?

For probes you do not need it, and for the PR gate it is the wrong place. A probe checks a named requirement, and for that a synthetic constellation of a few rows that touches exactly this requirement is enough. Production data contains personal information that has to be anonymized before any use outside production, and it is large. Where it is about data distribution, lock durations, or runtimes on real volumes, an anonymized dump is the right means, as a separate run outside the gate.

Upstream:

Siblings in the cluster:

Cross-cluster: