Postgresql – Postgres 12: declare a function as constant or immutable ignoring parameters

postgresqltrigger

In a trigger function I want to conditionally do pg_notify() calls, depending on an application setup option. In order to do that I created a wrapper notify_conditionally(). If the application is started with the notifications disabled, a second SQL function will create this wrapper that doesn't do anything and is defined as IMMUTABLE:

CREATE OR REPLACE FUNCTION notify_conditionally(channel text, payload text) RETURNS int
LANGUAGE plpgsql IMMUTABLE AS
$inner$
BEGIN
    PERFORM 1 WHERE 1 = 0;
    RETURN 0;
END;
$inner$;

If notifications are enabled, this function is defined as a call to pg_notify() with the parameters of the function:

CREATE OR REPLACE FUNCTION notify_conditionally(channel text, payload text) RETURNS int
LANGUAGE plpgsql STABLE AS
$inner$
BEGIN
    PERFORM pg_notify(channel, payload);
    RETURN 0;
END;
$inner$;

Since the parameters to conditionally_notify() (and hence pg_notify()) change with every call, I suppose the IMMUTABLE keyword doesn't help much in terms of result value caching (in fact, it might even be counter-productive?). Therefore I am wondering is there a way that I can define this function as "constant" and have the planner be clever enough to not call this function or only call it once and reuse the first result for all other calls? This would basically be like an IMMUTABLE that ignores all parameters. This would be useful to avoid calling this function if notifications are disabled. On bigger queries this makes a difference from ~500ms in my case and ideally I could lower this in the case of disabled notifications.

While it is a possibility to also update the code that uses notify_conditionally() function dynamically based on whether notifications are enabled and in case they aren't just not use notify_conditionally(), I would prefer to not do this. The reason is that this happens in a rather large trigger function that currently should only be changed through a full migration.

Best Answer

Declare the no-op version as an SQL function then it will be in-lined as a constant value by the optimiser.

CREATE OR REPLACE FUNCTION notify_conditionally(channel text, payload text) RETURNS int
LANGUAGE sql AS
$inner$
SELECT 0;
$inner$;

Related Solutions

Postgresql – the best approach to process huge amount of data insertion efficiently

The fragment you posted so far can be simplified to:

INSERT INTO table2 (id, name, date)  -- why "date" if you insert a timestamp?
SELECT NEW.id, t1.name, NEW.timestamp
FROM   table1 t1
WHERE  ST_DWithin(NEW.position
                , ST_SetSRID(ST_MakePoint(t1.longitudedecimal, t1.latitudedecimal), 4326)
                , 0.01447534783)
AND    t1.id > 0;  -- probably redundant!

IF NOT FOUND THEN ...

It is very inefficient to run separate queries with assignments in plpgsql instead of a single query.
Basic type names like date or timestamp lead to confusing error messages and other conflicts, id and name are the worst possible column names, non-descriptive and with countless duplicates all over your tables. Revisit your naming convention ...

You probably do not need any of this. Inserting millions of rows shouldn't be handled by triggers which are fired for each row. Extremely expensive. You need a set-based solution without triggers.

Probably best to COPY to a tmeporary staging table and INSERT / UPDATE from there. But basic information is missing.

Postgresql – Trigger function using current row and current table name as variables (final part)

Yes, this should work (untested):

CREATE OR REPLACE FUNCTION trfn_tbl_log_timetypespan()  -- generic name
  RETURNS trigger AS
$func$
DECLARE
   _timetype varchar;
   _timetypespan_resume interval;
   _ct int;
BEGIN

CASE NEW.timetype
WHEN 'lap' THEN
   EXECUTE format($$
      SELECT timetype, timetypespan, age($1, timestmp)
      FROM   %s
      WHERE  fnname = $2
      AND    timetype IN ('start', 'resume')
      ORDER  BY stmtserial DESC
      LIMIT  1$$
    , TG_RELID::regclass)
   USING NEW.timestmp, NEW.fnname
   INTO  _timetype, _timetypespan_resume, NEW.timetypespan;

   CASE _timetype
   WHEN 'start' THEN  -- do nothing

   WHEN 'resume' THEN
      EXECUTE format($$
         SELECT age($1, timestmp) - _timetypespan_resume
         FROM   %s
         WHERE  fnname = $2
         AND    timetype = 'start'
         ORDER  BY stmtserial DESC
         LIMIT  1$$
       , TG_RELID::regclass)
      USING NEW.timestmp, NEW.fnname
      INTO  NEW.timetypespan;

   ELSE
      RAISE EXCEPTION 'There is no previous row.';
   END CASE;

WHEN 'resume' THEN
   EXECUTE format($$
      SELECT age($1, timestmp)
      FROM   %s
      WHERE  fnname = $2
      AND    timetype IN ('start', 'lap')
      ORDER  BY stmtserial DESC LIMIT 1$$
    , TG_RELID::regclass)
   USING NEW.timestmp, NEW.fnname
   INTO  NEW.timetypespan;

   GET DIAGNOSTICS _ct = ROW_COUNT;

   IF _ct > 0 THEN  -- do nothing
   ELSE
      RAISE EXCEPTION 'There is no previous row.';
   END IF;

WHEN 'total' THEN
   EXECUTE format($$
      SELECT COALESCE(SUM(timetypespan), $1)
      FROM (
         SELECT floor(timeidx)::int, max(timetypespan) AS timetypespan
         FROM   %1$s
         WHERE  fnname = $2
         AND    timetype = 'lap'
         AND    stmtserial > coalesce(
                 (SELECT stmtserial
                  FROM   %1$s
                  WHERE  fnname = $2
                  AND    timetype = 'total'
                  ORDER  BY stmtserial DESC
                  LIMIT  1), 0)
         GROUP  BY 1
         ) sub$$
    , TG_RELID::regclass)
   USING NEW.timetypespan, NEW.fnname
   INTO  NEW.timetypespan;

   GET DIAGNOSTICS _ct = ROW_COUNT;

   IF _ct > 0 THEN  -- do nothing
   ELSE
      RAISE EXCEPTION 'There is no previous row.';
   END IF;
END CASE;

RETURN NEW;

END
$func$  LANGUAGE plpgsql;

This is a follow-up to my previous answer to your previous question. Find explanation there:

Trigger function using current row and current table name as variables

While being at it, I simplified a couple of things. Like: the second SELECT in your first ELSIF branch was just a repetition of the SELECT one level above. I merged that for no cost.

I also removed a couple of unnecessary intermediary steps and assigned to fields of NEW directly where applicable. That's why I could remove most of your variables.

Aside: If timeidx only has positive numbers you can use the cheaper trunc(timeidx) instead of floor(timeidx).

To understand the dynamic part more easily ...

If you would implement the same just for tbl_log_a - i.e., the effectively executed code for tbl_log_a (after applying format() and EXECUTE) looks like this:

...

CASE NEW.timetype
WHEN 'lap' THEN
   SELECT timetype, timetypespan, age(NEW.timestmp, timestmp)
   FROM   tbl_log_a
   WHERE  fnname = NEW.fnname
   AND    timetype IN ('start', 'resume')
   ORDER  BY stmtserial DESC
   LIMIT  1
   INTO  _timetype, _timetypespan_resume, NEW.timetypespan;

   CASE _timetype
   WHEN 'start' THEN  -- do nothing

   WHEN 'resume' THEN
      SELECT age(NEW.timestmp, timestmp) - _timetypespan_resume
      FROM   tbl_log_a
      WHERE  fnname = NEW.fnname
      AND    timetype = 'start'
      ORDER  BY stmtserial DESC
      LIMIT  1
      INTO  NEW.timetypespan;

...

Best Answer

Related Solutions

Postgresql – the best approach to process huge amount of data insertion efficiently

Postgresql – Trigger function using current row and current table name as variables (final part)

To understand the dynamic part more easily ...

Related Question