tl;dr

NEW.updated_at := NOW(); is not working, where updated_at stores a timestamptz value.

I have this trigger function, where the error seems to happen in the first line. The updated_at field stores a timestamptz value, which NOW() should be, but doesn't seem to work (throws error). I have tried both CURRENT_TIMESTAMP and NOW() and neither seems to work. Does anyone know what might be causing this issue?

BEGIN
  NEW.updated_at := NOW();
  UPDATE public.projects 
  SET updated_at = NEW.updated_at 
  WHERE id = NEW.project_id;
  INSERT INTO public.document_contributor (document_id, contributor)
  VALUES (NEW.id, NEW.created_by)
  ON CONFLICT
  DO NOTHING;
  INSERT INTO public.commits (message, created_by, project_id, document_id, created_at, previous_content, current_content)
  VALUES (NEW.note, NEW.updated_by, NEW.project_id, NEW.id, NEW.updated_at, OLD.content, NEW.content);
  RETURN NEW;
END

Error

Event message

invalid input syntax for type timestamp with time zone: "Invalid Date"

I've noticed that the query being made looks like this.

{
  "query" : "\n-- source: dashboard\n-- user: 201e7b7c-bb29-409f-9e01-65ca849999e6\n-- date: 2022-10-09T01:44:28.100Z\n\nupdate public.documents set (created_at,updated_at,note,data) = (select created_at,updated_at,note,data from json_populate_record(null::public.documents, '{\"created_at\":\"Invalid Date\",\"updated_at\":\"Invalid Date\",\"note\":null,\"data\":[{\"type\":\"title\",\"children\":[{\"text\":\"Untitled\"}]},{\"type\":\"paragraph\",\"children\":[{\"text\":\"Pool 입니다.\"}]}]}')) where id = 'c5e2348b-9da7-4db3-8d5e-9d669cfbd7cb' returning *;\n"
}

CodePudding user response：

Turns out the "time" of the trigger function was the issue.

In the official document, the operation happens BEFORE.

CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
    FOR EACH ROW EXECUTE FUNCTION emp_stamp();

But I have done the opposite, which was AFTER an update happens.

SO when I recreated the function changing from AFTER to BEFORE, it works fine.

The reason? Well I am guessing that updating the record by RETURN NEW, AFTER the operation finishes violates some rule. (But I am not sure)

CodePudding user response：

BEGIN
  NEW.updated_at = NOW();
  UPDATE public.projects 
  SET updated_at = NEW.updated_at 
  WHERE id = NEW.project_id;
  INSERT INTO public.document_contributor (document_id, contributor)
  VALUES (NEW.id, NEW.created_by)
  ON CONFLICT
  DO NOTHING;
  INSERT INTO public.commits (message, created_by, project_id, document_id, created_at, previous_content, current_content)
  VALUES (NEW.note, NEW.updated_by, NEW.project_id, NEW.id, NEW.updated_at, OLD.content, NEW.content);
  RETURN NEW;
END