Ability for the history table to INHERITS the table containing the current state

[simonexmachina]

I'd much prefer to say that CREATE TABLE employees_history () INHERITS (employees), but this produces unexpected results. Is there any reason why this doesn't work as expected?

For example:

DROP TABLE IF EXISTS "test_history";
DROP TABLE IF EXISTS "test";
DROP TRIGGER IF EXISTS "versioning_trigger" ON "test";

CREATE TABLE "test" (
  seq INT,
  validity tstzrange
);
CREATE TABLE "test_history" () INHERITS ("test");
CREATE TRIGGER "versioning_trigger"
  BEFORE INSERT OR UPDATE OR DELETE ON "test"
  FOR EACH ROW EXECUTE PROCEDURE versioning('validity', 'test_history', true);

INSERT INTO "test" VALUES (1);
UPDATE "test" SET seq = 2;
UPDATE "test" SET seq = 3;
SELECT * FROM "test_history";
 seq |                             validity
-----+-------------------------------------------------------------------
   1 | ["2015-04-15 11:18:44.971061+10","2015-04-15 11:18:44.987991+10")
   2 | ["2015-04-15 11:18:44.987991+10","2015-04-15 11:18:44.999616+10")
   1 | ["2015-04-15 11:24:12.907224+10","2015-04-15 11:24:12.908114+10")
   2 | ["2015-04-15 11:24:12.908114+10","2015-04-15 11:24:12.909926+10")
(4 rows)

If I change this to CREATE TABLE "test" () INHERITS ("test_history") then this works as expected:

SELECT * FROM "test_history";
 seq |                             validity
-----+-------------------------------------------------------------------
   1 | ["2015-04-15 11:29:12.386408+10","2015-04-15 11:29:12.387281+10")
   2 | ["2015-04-15 11:29:12.387281+10","2015-04-15 11:29:12.389392+10")
   3 | ["2015-04-15 11:29:12.389392+10",)
(3 rows)

[simonexmachina]

We're adding _history to tables that already exist and I'm concerned about some gotchas if we have data INHERITS data_history. Take the following example:

CREATE TABLE "data_history" (LIKE "data");
ALTER TABLE "data" INHERIT "data_history";

• This means that the columns are defined both in data and data_history, and I can't see a way to remove the columns from the data table ONLY
• Developers need to remember to ALTER TABLE data_history, rather than ALTER TABLE data
• ALTER TABLE data_history DROP foo will still leave the foo column in the data table

[arkhipov]

You are getting duplicate results for SELECT * FROM test_history because queries to the parent table operate on the parent table and all child tables. You can avoid it by rewriting the query using ONLY keyword: SELECT * FROM ONLY test_history. Also, you have to rewrite UPDATE queries using this keyword: UPDATE ONLY test SET seq = 2. That is how inheritance works in Postgres.

Of course, it is not an option if you are using a framework like Hibernate. If so, you have to keep both tables in sync manually.

You may well find this tutorial helpful.

[simonexmachina]

Yes, you're right. I was sure I tried that, but obviously did something wrong. Thanks again!