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

    XMLWordPrintable

    Details

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

      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

              Activity

                People

                Assignee:
                sekjal Ian Walls
                Reporter:
                jemiller Jon Miller
                Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                  Dates

                  Created:
                  Updated:

                    TestRail: Runs

                      TestRail: Cases