PARALLEL label for a function with SELECT and INSERT

Question

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$;

Erwin Brandstetter · Accepted Answer · 2022-02-05 02:38:27Z

2

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.

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$
BEGIN
   IF EXISTS (                                   -- simpler, cheaper
      SELECT FROM user_email ue
      JOIN   user u ON u.id = ue.user_id
      WHERE  ue.address = in_email_address
      AND    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_at
   WHERE  NOT EXISTS (
      SELECT FROM verification_email v
      WHERE  v.submitted_ip = in_submitted_ip
      AND    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.

edited Feb 5, 2022 at 2:38

answered Feb 4, 2022 at 18:23

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

HelloMachine Over a year ago

So if 10000 clients wants to call this function in same time, there will no problem in waiting time ??!!!!

Erwin Brandstetter Over a year ago

@HelloMachine: 10000 function calls at the same time will be a (performance) problem for pretty much any existing database system. (Luckily, that's hardly ever going to happen at exactly the same time.) But that's unrelated to the correct PARALLEL label of the function. The correct label keeps Postgres from generating possibly conflicting (or needlessly cautious) query plans.

Collectives™ on Stack Overflow

PARALLEL label for a function with SELECT and INSERT

1 Answer 1

Better function

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Better function

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related