[FIXED] Trigger is not updating current record PostgresSQL when record is inserted from hibernate

Issue

I have one table which stores interview slots with start and end times below is the table:

CREATE TABLE INTERVIEW_SLOT (
    ID SERIAL PRIMARY KEY NOT NULL,
    INTERVIEWER INTEGER REFERENCES USERS(ID) NOT NULL,
    START_TIME TIMESTAMP NOT NULL, -- start time of interview
    END_TIME TIMESTAMP NOT NULL, -- end time of interview
    -- more columns are not necessary for this question
);

I have created a trigger which will truncate start and end time to minutes below is the trigger:

CREATE OR REPLACE FUNCTION
iv_slot_ai() returns trigger AS
$BODY$
BEGIN
raise warning 'cleaning start and end time for iv slot for id: %', new.id;
    update interview_slot set end_time = TO_TIMESTAMP(end_time::text, 'YYYY-MM-DD HH24:MI');
    update interview_slot set start_time = TO_TIMESTAMP(start_time::text, 'YYYY-MM-DD HH24:MI');
    return new;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;

CREATE TRIGGER IV_SLOT_AI AFTER INSERT ON INTERVIEW_SLOT
FOR EACH ROW
EXECUTE PROCEDURE iv_slot_ai();

When I insert a record from psql terminal manually trigger gets hit and updates the inserted record properly.

 INSERT INTO public.interview_slot(
interviewer, start_time, end_time, is_booked, created_on, inform_email_timestamp)
VALUES (388, '2022-08-22 13:00:10.589', '2022-08-22 13:30:09.589', 'F', current_date, current_date);
WARNING:  cleaning start and end time for iv slot for id: 72
INSERT 0 1

select * from interview_slot order by id desc limit 1;
 id | interviewer |       start_time        |        end_time         |
----+-------------+-------------------------+-------------------------+
 72 |         388 | 2022-08-22 13:00:00     | 2022-08-22 13:30:00     |

I have a backend application in spring boot with hibernate ORM. When I insert the record from API call, it gets triggered(i have checked in Postgres logs) but the inserted record does not get updated.

Actually, method which saves records is being called from another method that has this @Transactional() annotation.

I have also tried BEFORE trigger but it was also not working.

Can anyone explain why this is happening and what is the solution?

Is it because of transactional annotation?

Solution

The OMR might be updating. Use BEFORE INSERT OR UPDATE trigger with this somewhat simpler function (w/o the message) using date_trunc:

create or replace function iv_slot_ai() returns trigger language plpgsql as
$body$
begin
  new.start_time := date_trunc('minute', new.start_time);
  new.end_time := date_trunc('minute', new.end_time);
  return new;
end;
$body$;

Answered By - Stefanov.sm
Answer Checked By - Willingham (JavaFixing Volunteer)