Postgres and hstore

Posted on by

I recently started working with the hstore contrib module in Postgresql.  My impetus was  the desire to create a generic way of redirecting deletes on key tables to archive tables.  I do that now using a rule based mechanism. The problem is it is fairly fragile and prone to breakage when the parent table changes.  I had seen references to hstore and had a vague idea of what it could do. This was enough to make me dig deeper and see if I could use it for the delete process.  This is still a work in progress, though I am pleased with what I found so far, which is sketched out below.
1) The parent table:

CREATE TABLE plant1 (
p_item_no smallint DEFAULT nextval(('plant1_p_item_no_seq'::text)::regclass) NOT NULL,
common character varying(40) NOT NULL,
genus character varying(20),
species character varying(30),
variety character varying(40),
plant_type character varying(6) NOT NULL,
series character varying(20),
season character varying(9),
ts_insert timestamp(0) without time zone DEFAULT now(),
ts_update timestamp(0) without time zone,
user_update character varying(20),
user_insert character varying(20) DEFAULT "session_user"(),
color character varying(30),
category_type character varying(5) DEFAULT 'plant'::character varying NOT NULL,
category_sub_type character varying(15) DEFAULT 'normal'::character varying NOT NULL,
plant_note text
);



2) The delete table:


CREATE TABLE plant1_delete (
del_id integer NOT NULL,
record_fld public.hstore,
del_ts timestamp with time zone DEFAULT now(),
del_user character varying
);



3) The delete function and trigger:


CREATE OR REPLACE FUNCTION utility.archive_record()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
AS $BODY$
DECLARE
tbl_name text := TG_TABLE_NAME || '_delete' ;
archive_row hstore := hstore(OLD.*);
usr_name text := session_user;
BEGIN
EXECUTE 'INSERT INTO ' ||quote_ident(tbl_name)
||'(record_fld,del_ts,del_user)' ||
' VALUES('||quote_literal(archive_row)||',now(),'
||quote_literal(usr_name)||')';
RETURN OLD;
END;
$BODY$

CREATE TRIGGER p1_delete AFTER DELETE ON plant1 FOR EACH ROW EXECUTE PROCEDURE utility.archive_record();



4) The end result:


DELETE from plant1 where p_item_no=578;
DELETE 1
The raw record in the delete table:
SELECT * from plant1_delete;
-[ RECORD 1 ]------------------------------------------------------------------------------------------

del_id | 6
record_fld | "color"=>NULL, "genus"=>"selaginella", "common"=>"moss little club", "season"=>NULL, "series"=>"", "species"=>"brownii", "variety"=>"", "p_item_no"=>"578", "ts_insert"="2004-01-01 00:00:00", "ts_update"=>"2005-11-21 10:45:30", "plant_note"=>NULL, "plant_type"=>"herb", "user_insert"=>"aklaver", "user_update"=>"aklaver", "category_type"=>"plant", "category_sub_type"=>"normal"
del_ts | 2012-06-23 16:19:00.193426-07
del_user | aklaver

The hstore field parsed out, record selected using value in hstore:
SELECT (each(record_fld)).* from plant1_delete where record_fld ->'p_item_no'='578';

key | value
-------------------+---------------------
color |
genus | selaginella
common | moss little club
season |
series |
species | brownii
variety |
p_item_no | 578
ts_insert | 2004-01-01 00:00:00
ts_update | 2005-11-21 10:45:30
plant_note |
plant_type | herb
user_insert | aklaver
user_update | aklaver
category_type | plant
category_sub_type | normal



I can see this making life a lot easier. Added bonus is that psycopg2(a Python database adapter) supports hstore and will adapt from it to a Python dictionary and back again. That is subject for another blog.


 


 


 

8 thoughts on “Postgres and hstore”

  1. This is really cool.

    Especially the archive_row hstore := hstore(OLD.*); part shows once again the power of Postgres

  2. Cool. I had need of something like this for Oracle, and the best I could come up with was some ugly and non-generic XML. hstore(OLD.*) is just so elegant!

  3. Sorry, I just read it, didn’t tryied yet; does it work with 9.1?

    I’m using a XML version of this “deleted record trash”: any delete in any table goes to the trash, and I had a function to recover it from the trash by deletion ID.

    • Forgot to say, the XML version is somewhat slow, and for huge deletions I had to disable it.

    • The example shown was run on 9.0.8. My mistake for not mentioning the version.

  4. I would agree hstore(OLD.*) is really handy. It was a pleasant surprise to get so much out of such a small function.

  5. I use audit tables on several of my tables which track all inserts, deletes and updates. This could be used for that too, and for update, only record the columns with different values which would be more space efficient.

    Also, if you add a table_name column to the delete table you could create one generic delete table for all deletes regardless as to which table they came from. This would make it very easy to add this functionality to any table, and generally this isn’t going to be a performance critical path, so the big table shouldn’t be too much of a problem.

    Interesting, thank you for this post, I’m going to give this a try.

    • Yeah, I am still working out the best way to structure this. Actually, capturing the delete information is the easy part. The interesting part is going to be to coming up with a mechanism to allow users to undelete. A certain amount of time travel is involved:)

Comments are closed.