Cannot delete record in PostgreSQL table due to trigger

Question

I would like to delete a record inside a table which have a trigger like this:

CREATE OR REPLACE FUNCTION public.notify_trigger()
  RETURNS trigger AS
$BODY$
DECLARE
BEGIN
  PERFORM pg_notify('watchers', 
    '{' ||
      '"table":"'    || TG_TABLE_NAME || '",' ||
      '"operation":"'|| TG_OP         || '",' ||
      '"row":'       || (select row_to_json(row)::varchar from (SELECT NEW.*) row) ||
    '}'
  );
  RETURN NEW;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION public.notify_trigger()
  OWNER TO postgres;

When I tried to delete a record using delete from test_notify where id = 1277, it returns an error like this:

ERROR:  record "new" is not assigned yet
DETAIL:  The tuple structure of a not-yet-assigned record is indeterminate.
CONTEXT:  SQL statement "SELECT pg_notify('watchers', 
    '{' ||
      '"table":"'    || TG_TABLE_NAME || '",' ||
      '"operation":"'|| TG_OP         || '",' ||
      '"row":'       || (select row_to_json(row)::varchar from (SELECT NEW.*) row) ||
    '}'
  )"
PL/pgSQL function notify_trigger() line 4 at PERFORM

********** Error **********

On the test_notify table I have a trigger like this:

CREATE TRIGGER watched_table_trigger
  AFTER INSERT OR UPDATE OR DELETE
  ON public.test_notify
  FOR EACH ROW
  EXECUTE PROCEDURE public.notify_trigger();

Laurenz Albe · Accepted Answer · 2020-01-08 07:04:07Z

1

Whoever wrote that trigger obviously made a mistake: NEW is not defined in ON DELETE triggers.

The best solution is to drop the trigger and re-create it with ON INSERT OR UPDATE.

To temporarily keep triggers from firing, you can set session_replication_role to replica. Be warned that that also disables foreign key constraints and therefore can only be changed by superusers.

answered Jan 8, 2020 at 7:04

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

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

Collectives™ on Stack Overflow

Cannot delete record in PostgreSQL table due to trigger

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related