Search Postgresql Archives

hstore for audit logging: Finding differences between two hstore values

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



 Hi all

I'm using Pg 9.1, working on enhancing my audit logging so it can record not only the old and new values of a record when it changes, but what exactly changed.

It's easy to produce hstore values for the old and new records, and I've been happily logging those. I was about to start testing a switch to 'json' instead (for possible storage space savings and better composite type support in exchange for poorer searchability) - when I found myself wondering how I could log only the fields that changed using hstore.

A quick look at the hstore documentation ( http://www.postgresql.org/docs/9.1/static/hstore.html) doesn't reveal any obvious key/value pairwise set operations like union/intersection/difference. Am I missing something obvious?

Is there some clever way to get a hstore with only the _differing_ keys of two input hstores? Or will I need to break them down into arrays and manually find the differences?

For example, given:

regres=# select hstore('"x"=>"30", "y"=>"c"'), hstore('"x"=>"30", "y"=>"fred"');
       hstore        |         hstore        
---------------------+------------------------
 "x"=>"30", "y"=>"c" | "x"=>"30", "y"=>"fred"
(1 row)

is there any straightforward operation between the two above hstores I can use to produce a result like:

  hstore('"y"=>"fred"');

, omitting the identical "x"=>"30" ?

I suspect I'd land up having to use each(...) to do a SQL set-oriented difference using UNION/INTERSECT/EXCEPT, which will be "fun" in performance terms. Please tell me there's a clever way I've missed.

I can do this:

regress=# select a.key, a.value from each(hstore('"x"=>"30", "y"=>"c"')) AS a EXCEPT SELECT b.* FROM each(hstore('"x"=>"30", "y"=>"fred"')) b;
 key | value
-----+-------
 y   | c

and aggregate it back into a hstore like this:

regress=# SELECT hstore( array_agg(diff.key), array_agg(diff.value) ) FROM (SELECT a.key, a.value from each(hstore('"x"=>"30", "y"=>"c"')) AS a EXCEPT SELECT b.* FROM each(hstore('"x"=>"30", "y"=>"fred"')) b) AS diff;
  hstore 
----------
 "y"=>"c"
(1 row)

... but it feels like there must be a smarter way.

 PostgreSQL 9.1.4 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 4.7.0 20120507 (Red Hat 4.7.0-5), 64-bit
 Fedora 17

--
Craig Ringer

POST Newspapers 276 Onslow Rd, Shenton Park Ph: 08 9381 3088 Fax: 08 9388 2258 ABN: 50 008 917 717 http://www.postnewspapers.com.au/

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux