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) THENIF 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$;Best Answer
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 UNSAFEif theywrite to the database, access sequences, change the transaction stateeven temporarily (e.g., a PL/pgSQL function that establishes anEXCEPTIONblock to catch errors), or make persistent changes tosettings.
Bold emphasis mine.
Related:
- When to mark functions as PARALLEL RESTRICTED vs PARALLEL SAFE?
Better function
While being at it, consider this rewrite:
CREATE FUNCTION verification_request_email(in_email_address text,in_submitted_ip integer,OUT out_submitted_at integer)LANGUAGE plpgsql AS -- default PARALLEL UNSAFE$func$BEGINIF EXISTS ( -- simpler, cheaperSELECT FROM user_email ueJOIN user u ON u.id = ue.user_idWHERE ue.address = in_email_addressAND u.status = B'0') THEN-- User account suspended.RAISE EXCEPTION 'ERR(2)';END IF;out_submitted_at := extract(epoch FROM now() AT TIME ZONE 'utc');INSERT INTO verification_email (submitted_ip, submitted_at) -- target column list!SELECT in_submitted_ip, in_submitted_atWHERE NOT EXISTS (SELECT FROM verification_email vWHERE v.submitted_ip = in_submitted_ipAND v.submitted_at > (out_submitted_at - 60) -- minus, not plus!);IF NOT FOUND 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;END$func$;A single SELECT and a single INSERT should be much cheaper.
Especially important if you expect many concurrent calls like indicated in your comment.