Uploaded image for project: 'mod-users'
  1. mod-users
  2. MODUSERS-185

set_users_md_json_trigger or set_users_md_trigger is removing metadata.createdDate timezone info/offset



    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: TBD
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Labels:
    • Template:


      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.

      input text;   
      createdDate timestamp; 
          input = NEW.jsonb->'metadata'->>'createdDate';   
          IF input IS NULL THEN
             RETURN NEW;   
          END IF;   
          IF (input::timestamp::timestamptz = input::timestamptz) THEN
              createdDate = input::timestamp;
              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; 
          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}';   
              NEW.jsonb = jsonb_set(NEW.jsonb, '{metadata,createdByUserId}', to_jsonb(NEW.created_by));   
          end if;
          RETURN NEW; 

      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


            Issue Links



                sekjal Ian Walls
                jemiller Jon Miller
                0 Vote for this issue
                3 Start watching this issue



                    TestRail: Runs

                      TestRail: Cases