"memory access out of bounds" or "null function or function signature mismatch" with plpgsql; but works in Postgres 13/14.5/15.1

[andymitchell]

@samwillis Pglite has been a fantastic transformation for testing, and the speed is incredible compared to Pgmock.

Sadly, I've written a valid plpgsql function that triggers errors in Pglite:

RuntimeError: memory access out of bounds RuntimeError: null function or function signature mismatch

Replicating code

async function main() {

// SETUP
const dbPglite = new PGlite();

// DEFINE SQL TO SET UP SCHEMA AND FUNCTIONS

const sqlSetup = `

CREATE SCHEMA IF NOT EXISTS "error_demo_schema";

CREATE OR REPLACE FUNCTION "error_demo_schema".add_job( 
    p_queue_name TEXT,
    p_payload jsonb,
    p_optional TEXT DEFAULT NULL
) 
RETURNS VOID
SECURITY DEFINER
SET search_path = "error_demo_schema"
AS $$
BEGIN

    IF p_queue_name IS NULL OR p_payload IS NULL OR p_optional IS NULL THEN 
        RAISE EXCEPTION 'No NULLS allowed';
    END IF;

END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION "error_demo_schema".testhelper_throws_ok(query TEXT, expected_exception TEXT DEFAULT NULL, description TEXT DEFAULT '')
RETURNS VOID AS $$
DECLARE
    exception_occurred BOOLEAN := FALSE;
BEGIN

    BEGIN
        EXECUTE query;
    EXCEPTION WHEN OTHERS THEN
        exception_occurred := TRUE;
        IF expected_exception IS NOT NULL AND SQLERRM !~ expected_exception THEN
            RAISE EXCEPTION 'Test failed: % - Unexpected exception: %', description, SQLERRM;
        END IF;
    END;

    IF exception_occurred THEN
        RAISE NOTICE 'Test passed: %', description;
    ELSE
        RAISE EXCEPTION 'Test failed: % - No exception was raised', description;
    END IF;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION "error_demo_schema".test_the_test_for_throws_ok()
RETURNS boolean
LANGUAGE plpgsql
AS $$
BEGIN
    -- Test the add_job function fails with NULLs 
    PERFORM "error_demo_schema".testhelper_throws_ok(
        $q$ SELECT "error_demo_schema".add_job('randomq_b', '{"opinion":"x"}', NULL) $q$,
        NULL,
        'function throw an error with NULL values'
    );

    RETURN true;
END;
$$;

`.trim();

// DEFINE SQL TO CALL 'testhelper_throws_ok'

// Pglite will error with "RuntimeError: memory access out of bounds". (Works fine in Postgres 13 and 14.5 and 15.1).
const sqlQueryDirect = `

SELECT "error_demo_schema".testhelper_throws_ok(
    $q$ SELECT "error_demo_schema".add_job('randomq_b', '{"opinion":"x"}', NULL) $q$,
    NULL,
    'function throw an error with NULL values'
);

`.trim();

// Pglite will error with "null function or function signature mismatch". (Works fine in Postgres 13 and 14.5 and 15.1).
const sqlQueryViaWrapper = `

SELECT "error_demo_schema".test_the_test_for_throws_ok();

`.trim()

// EXECUTE IT
await dbPglite.exec(sqlSetup);
await dbPglite.query(sqlQueryDirect);
await dbPglite.query(sqlQueryViaWrapper);
}
main();

What the function testhelper_throws_ok is doing

It accepts a line of SQL that it expects to fail. If the SQL errors, the function catches it and does nothing. If the SQL runs cleanly, the function throws an exception.

(It's an alternate implementation of pgtap's throws_ok)

A few observations

• Afaik, there's only one function causing the error: testhelper_throws_ok. But the exact error varies depending on how it's invoked (see the two examples in the code below).
• It doesn't appear to be a native Postgres error... a) because it runs fine in Postgres, b) googling the errors doesn't return any postgres specific results. Possibly wasm runtime errors?
• This is the only time I've found plpgsql functions to fail in Pglite. Other functions run fine, including ones similar to this.

[AntonOfTheWoods]

I've had plenty of these kinds of errors. The RuntimeError: memory access out of bounds errors typically go away with a reload and the RuntimeError: null function or function signature mismatch can be due to actual errors, for which this is not a very good error message. If memory serves I was getting the latter from a VIEW that was trying to do something illegal in the context of a ::json coercion on certain values in certain rows. I have regularly also been getting error messages about illegal nodes.

[andymitchell]

@AntonOfTheWoods Sadly in this case, the RuntimeError: memory access out of bounds is happening consistently. I'm executing the code in Jest, so it's restarting Pglite on each run.

[AntonOfTheWoods]

@AntonOfTheWoods Sadly in this case, the RuntimeError: memory access out of bounds is happening consistently. I'm executing the code in Jest, so it's restarting Pglite on each run.

@andymitchell it is becoming clear that any sort of error being raised (incorrectly spelt column name in a select, etc.) cause the db to enter an invalid/broken state, and you can definitely expect errors of various kinds, including this one. My understanding of the code above is that you are testing whether errors get managed properly... so you are generating an error. It looks like that is a sure way to break things currently, so your code would be a welcome addition to the pglite test suite :-) !

[samwillis]

Hey, thanks for the report.

We are working on a new version of PGlite that should solve the bug where it ends up in an invalid state after throwing an error. I'm hopeful we will be able to get a version out in about two weeks time. (I'm away next week)

[maurotrigo]

After quite some time debugging the init migrations file, I found that INT index creation statements are causing the "memory access out of bounds" error in my case. I commented out lines like the following from the file generated by Drizzle:

CREATE INDEX IF NOT EXISTS "Company_primaryContactId_idx" ON "Company" USING btree ("primaryContactId" int4_ops);--> statement-breakpoint

Hope this helps!

Loving PGLite so far btw!