PARALLEL label for a function with SELECT and INSERT

February 4, 2022

All of my database activities (PostgreSQL) are based on my own functions. For example, to submit verification request data, I use a function with some SELECT and INSERT operations. What is the correct PARALLEL label for this function? SAFE or UNSAFE?

I think I have to use SAFE. I read if a function changes a database or creates a new one, it must be UNSAFE. But I’m not changing the database! I just SELECT from a table and INSERT …

CREATE FUNCTION "verification_request_email"(
    IN  "in_email_address" text,
    IN  "in_submitted_ip" integer,
    OUT "out_submitted_at" integer
) RETURNS integer LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
    "uid" integer;
BEGIN
    "out_submitted_at":=extract(epoch FROM now() AT TIME ZONE 'utc');
    IF EXISTS(SELECT 1 FROM "verification_email" WHERE "submitted_ip"="in_submitted_ip"
                AND "submitted_at" > ("out_submitted_at" + 60)) THEN
        -- The last email address verification request for this IP address (in_submitted_ip) was
        -- less than a minute ago, user must wait for a minute.
        RAISE EXCEPTION 'ERR(1)';
    END IF;
    SELECT "user_id" INTO "uid" FROM "user_email" WHERE "address"="in_email_address" LIMIT 1;
    IF("user_id" IS NOT NULL) THEN
        IF EXISTS(SELECT 1 FROM "user" WHERE "id"="user_id" AND "status"=B'0' LIMIT 1) THEN
            -- User account suspended.
            RAISE EXCEPTION 'ERR(2)';
        END IF;
    END IF;
    INSERT INTO "verification_email" VALUES ("in_submitted_ip", "in_submitted_at");
END;
$BODY$;

>Solution :

Leave the function at its default, which is PARALLEL UNSAFE. A function that writes to the database is never PARALLEL SAFE.

The manual:

Functions and aggregates must be marked PARALLEL UNSAFE if they
write to the database, access sequences, change the transaction state
even temporarily (e.g., a PL/pgSQL function that establishes an
EXCEPTION block to catch errors), or make persistent changes to
settings.

Bold emphasis mine.