Details
-
Type:
Bug
-
Status: Open (View Workflow)
-
Priority:
TBD
-
Resolution: Unresolved
-
Affects Version/s: None
-
Fix Version/s: None
-
Labels:None
-
Template:customfield_11100 33121
Description
The diku_mod_users.users has two triggers named set_users_md_json_trigger and set_users_md_trigger that call functions set_users_md_json() or users_set_md(). The definitions for the functions are the following.
--users_set_md DECLARE input text; createdDate timestamp; BEGIN input = NEW.jsonb->'metadata'->>'createdDate'; IF input IS NULL THEN RETURN NEW; END IF; IF (input::timestamp::timestamptz = input::timestamptz) THEN createdDate = input::timestamp; ELSE createdDate = input::timestamptz AT TIME ZONE '+00'; END IF; NEW.jsonb = jsonb_set(NEW.jsonb, '{metadata,createdDate}', to_jsonb(createdDate)); NEW.creation_date = createdDate; NEW.created_by = NEW.jsonb->'metadata'->>'createdByUserId'; RETURN NEW; END; --set_users_md_json BEGIN if NEW.creation_date IS NULL then RETURN NEW; end if; NEW.jsonb = jsonb_set(NEW.jsonb, '{metadata,createdDate}', to_jsonb(NEW.creation_date)); if NEW.created_by IS NULL then NEW.jsonb = NEW.jsonb #- '{metadata,createdByUserId}'; else NEW.jsonb = jsonb_set(NEW.jsonb, '{metadata,createdByUserId}', to_jsonb(NEW.created_by)); end if; RETURN NEW; END;
There is a problem with the metadata.createdDate property value. The problem is that the timezone info/offset is being removed by the trigger.
The following example demonstrates the problem (though it demonstrates the problem for groups which also has the problem). All the groups were created at 10:51 AM local time. As you can see in column 4, the updatedDate value is correct. You can see that the offset "+0000" is present in column 2 for the updatedDate values. You can see that it is missing from the createdDate value. Casting updatedDate to a PostgreSQL timestamptz data type results, in the date/time value being displayed in local time which is easy to read. This won't work for the createdDate value because it is missing the offset/timezone info. Also, the value in creation_date is wrong. I'm thinking maybe the creation_date data type should be timestamptz (timestamp with time zone), not timestamp (timestamp without time zone) as it is now.
I'm thinking maybe the following line from above is the problem. Why is it casting the value to a timestamp without the offset?
IF (input::timestamp::timestamptz = input::timestamptz) THEN createdDate = input::timestamp;
For that matter, why is any of this even necessary? I don't think it should be copying the value from creation_date. I can see going the other way around maybe, but, I don't see what the point of copying in both directions is. Are these triggers even necessary?
Also, I think this may be an RMB problem. Triggers like these are used across the board for all the tables I think. The problem definitely happens for the users and groups tables, but, I'm assuming it's a problem for any table that uses RMB.
TestRail: Results
Attachments
Issue Links
- is cloned by
-
MODUSERS-186 User record "Record last updated" "Record created" in wrong time zone
-
- Closed
-