Postgresql: type of parameter does not match that when preparing the plan postgresql-12

Question

Hoping to get some help with a service I'm working on, which interacts with a Postgres-12 database. I've created a trigger that fires whenever there's an update/insert/delete to the client table, creating a payload out of the row that was affected.

CREATE OR REPLACE FUNCTION notify_trigger() RETURNS trigger AS $trigger$
DECLARE
  rec RECORD;
  dat RECORD;
  payload TEXT;
BEGIN

  -- Set record row depending on operation
  CASE TG_OP
  WHEN 'UPDATE' THEN
     rec := NEW;
     dat := OLD;
  WHEN 'INSERT' THEN
     rec := NEW;
  WHEN 'DELETE' THEN
     rec := OLD;
  ELSE
     RAISE EXCEPTION 'Unknown TG_OP: "%". Should not occur!', TG_OP;
  END CASE;

  -- Build the payload
  payload := json_build_object('timestamp',CURRENT_TIMESTAMP,'action',LOWER(TG_OP),'db_schema',TG_TABLE_SCHEMA,'table',TG_TABLE_NAME,'record',row_to_json(rec), 'old',row_to_json(dat));

  -- Notify the channel
  PERFORM pg_notify('db_event', payload);

  RETURN rec;
END;
$trigger$ LANGUAGE plpgsql;

CREATE TRIGGER clients_notify AFTER INSERT OR UPDATE OR DELETE ON clients
FOR EACH ROW EXECUTE PROCEDURE notify_trigger();

I can verify through psql the trigger works most of the time, but on some updates to the client table, I encounter the following error.

DBProgrammingError: type of parameter 15 (clients) does not match that when preparing the plan (record)
CONTEXT:  PL/pgSQL function notify_trigger() line 22 at assignment

The SQL query that results in the error:

db.executeCommands(
    [
        """
        UPDATE clients
        SET pos = %(pos)s,
            cos = %(cos)s,
            rpl = %(rpl)s,
            obv = %(obv)s,
            oav = %(oav)s,
            wth = %(wth)s
        WHERE id = %(id)s
        """
    ],
    (
        {
            "id": client["id"],
            "pos": client["pos"],
            "cos": client["cos"],
            "rpl": client["rpl"],
            "obv": client["obv"],
            "oav": client["oav"],
            "wth": client["wth"],
        },
    ),
)

And the schema for the client table:

CREATE TABLE clients (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    datetime timestamp DEFAULT now(),
    first VARCHAR(16) NOT NULL,
    last VARCHAR(16) NOT NULL,
    alias VARCHAR(32) NOT NULL,
    pos integer DEFAULT 0,
    cos numeric(1000,4) DEFAULT 0.0,
    rpl numeric(1000,4) DEFAULT 0.0,
    obv integer DEFAULT 0,
    oav integer DEFAULT 0,
    wth numeric(1000,4) DEFAULT 0.0
);

The curious thing is that I do this update in two different places in the service, one close to the api and the other in a separate thread; the former results in error above, while the latter executes successfully.

I've checked out this answer that was asked a while ago, but I'm not sure how it applies to the situation here, as casting the payload to text (PERFORM pg_notify('db_event', payload::text);) didn't help. My knowledge of PL/pgSQL is quite weak, so if anyone understands the Postgres Listen/Notify deeply, would really appreciate some help.

How can I get the parameter (clients) to match the plan (record)?

Solution

As Laurenz pointed out before his edit, the client type did not match somehow with the record type I was using to represent it. While I couldn't get coalesce to work as pointed out above, I decided to create a separate trigger handler for each data type I needed to represent. It's a little verbose but gets the job done. Better solutions welcome.

CREATE OR REPLACE FUNCTION notify_trigger() RETURNS trigger AS $trigger$
DECLARE
-  rec RECORD;
-  dat RECORD;
+  rec clients;
+  dat clients;
  payload TEXT;
BEGIN

  -- Set record row depending on operation
  CASE TG_OP
  WHEN 'UPDATE' THEN
     rec := NEW;
     dat := OLD;
  WHEN 'INSERT' THEN
     rec := NEW;
  WHEN 'DELETE' THEN
     rec := OLD;
  ELSE
     RAISE EXCEPTION 'Unknown TG_OP: "%". Should not occur!', TG_OP;
  END CASE;

  -- Build the payload
  payload := json_build_object('timestamp',CURRENT_TIMESTAMP,'action',LOWER(TG_OP),'db_schema',TG_TABLE_SCHEMA,'table',TG_TABLE_NAME,'record',row_to_json(rec), 'old',row_to_json(dat));

  -- Notify the channel
  PERFORM pg_notify('db_event', payload);

  RETURN rec;
END;
$trigger$ LANGUAGE plpgsql;

CREATE TRIGGER clients_notify AFTER INSERT OR UPDATE OR DELETE ON clients
FOR EACH ROW EXECUTE PROCEDURE notify_trigger();

Answer 1

You are returning a value of type record, but it should be client.

The simple, safe and generic RETURN statement would be

RETURN coalesce(NEW, OLD);