CODEWITHSUNDEEP
postgresqlparallel-processing
HelloMachine
HelloMachine

Reputation: 353

PARALLEL label for a function with SELECT and INSERT

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

Upvotes: 2

Views: 2899

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656952

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.

Related:

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.

Upvotes: 2

Related Questions