Thread: hstore for audit logging: Finding differences between two hstore values
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/
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/
Re: hstore for audit logging: Finding differences between two hstore values
From
Sergey Konoplev
Date:
On Fri, Jul 13, 2012 at 9:51 AM, Craig Ringer <craig@postnewspapers.com.au> wrote: > ... but it feels like there must be a smarter way. Well, may be kind of (h1 - h2), (h2 - h1) and derivatives will make sense? -- Sergey Konoplev a database architect, software developer at PostgreSQL-Consulting.com http://www.postgresql-consulting.com Jabber: gray.ru@gmail.com Skype: gray-hemp Phone: +79160686204
On 07/13/2012 02:15 PM, Sergey Konoplev wrote: > On Fri, Jul 13, 2012 at 9:51 AM, Craig Ringer > <craig@postnewspapers.com.au> wrote: >> ... but it feels like there must be a smarter way. > Well, may be kind of (h1 - h2), (h2 - h1) and derivatives will make sense? > > I apparently have the reading skills of a stoned mole, as I completely missed the subtraction operator in hstore's operators table. "hstore - hstore | delete matching pairs from left operand" I was thinking in terms of union/intersection/difference, didn't find matching terms in the hstore docs, and managed to repeatedly overlook the -(hstore,hstore) operator in the table. Thanks. On the upside I've come up with the a truly insanely verbose and slow way to implement the -(hstore,hstore) operator. Yay? -- 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/