Skip to content

auditing changes to tables with hstore columns is not working #29

@zam6ak

Description

@zam6ak

If a table you wish to audit has an hstore column the changes recorded in changed_fields is not tracking properly.

consider a table with hstore column hprops with these initial values

-- add some data
INSERT INTO audit_test VALUES (default, 'apples', 'red', true, 123456789, 2.99, inet_client_addr(), hstore('"x"=>"30", "y"=>"c"'), json_object('{a, 1, b, "def", c, 3.5}'), jsonb_object('{zz, 2, xx, "geh", yy, 99.5}'));

Consider following 3 scenarios

-- case 1) update hstore column (works!)
-- "changed_fields":   ""hprops"=>"\"x\"=>\"30\", \"y\"=>\"fred\"""    -- Works if entire column changed 
UPDATE audit_test SET hprops = hstore('"x"=>"30", "y"=>"fred"')  WHERE id = 1   

-- case 2) add a key or update existing key in hstore column (does not work)
-- "changed_fields":   ""hprops"=>"\"x\"=>\"30\", \"y\"=>\"bob\"""
UPDATE audit_test SET hprops = hprops || hstore('"y"=>"bob"')  WHERE id = 1     

-- case 3) delete a key in hstore  (does not work)
-- "changed_fields":   ""hprops"=>"\"x\"=>\"30\"""
UPDATE audit_test SET hprops = delete(hprops, 'y')  WHERE id = 1  
  • In scenario 2, changed_fields should not contain 'x' key as it has not changed
  • In scenario 3, changed key should be 'y'

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type
    No fields configured for issues without a type.

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions