Re: temporal support patch - Mailing list pgsql-hackers

Hello

what is conformance of your solution with temporal extension in ANSI SQL 2011

http://www.slideshare.net/CraigBaumunk/temporal-extensions-tosql20112012010438

Regards

Pavel Stehule

2012/5/16 Miroslav Šimulčík <simulcik.miro@gmail.com>:

> Hi all,
>
> as a part of my master's thesis I have created temporal support patch for
> PostgreSQL. It enables the creation of special temporal tables with entries
> versioning. Modifying operations (UPDATE, DELETE, TRUNCATE) on these tables
> don't cause permanent changes to entries, but create new versions of them.
> Thus user can easily get to the past states of the table.
>
> Basic information on temporal databases can be found
> on http://en.wikipedia.org/wiki/Temporal_database
>
> In field of temporal databases, there are only proprietary solution
> available. During the analysis I found these:
>     - IBM DB2 10 for z/OS
>     - Oracle 11g Workspace Manager
>     - Teradata Database 13.10
>
> Primary goal of my work was the creation of opensource solution, that is
> easy to use and is backward compatible with existing applications, so that
> the change of the original tables to temporal ones, does not require changes
> to applications that work with them. This patch is built on standard
> SQL/Temporal with some minor modifications inspired by commercial temporal
> database systems. Currently it only deals with transaction time support.
>
> Here is simple description on how it works:
>
> 1. user can create transaction time table using modified CREATE TABLE
> command:
>
>     CREATE TABLE person(name varchar(50)) AS TRANSACTIONTIME;
>
>     This command automatically creates all objects required for transaction
> time support:
>
>                   List of relations
>       Schema |         Name         |   Type   |  Owner
>      --------+----------------------+----------+----------
>       public | person               | table    | tester
>       public | person__entry_id_seq | sequence | tester
>       public | person_hist          | table    | postgres
>
>
>                                                   Table "public.person"
>         Column   |            Type             |
>      Modifiers
>
>    ------------+-----------------------------+------------------------------------------------------------------------------
>       name       | character varying(50)       |
>       _entry_id  | bigint                      | not null default
> nextval('person__entry_id_seq'::regclass)
>       _sys_start | timestamp without time zone | not null default
> clock_timestamp()
>       _sys_end   | timestamp without time zone | not null default
> '294276-12-31 23:59:59.999999'::timestamp without time zone
>      Indexes:
>          "person__entry_id_idx" btree (_entry_id)
>          "person__sys_start__sys_end_idx" btree (_sys_start, _sys_end)
>
>
>               Table "public.person_hist"
>         Column   |            Type             | Modifiers
>      ------------+-----------------------------+-----------
>       name       | character varying(50)       |
>       _entry_id  | bigint                      | not null
>       _sys_start | timestamp without time zone | not null
>       _sys_end   | timestamp without time zone | not null
>      Indexes:
>          "person_hist__entry_id_idx" btree (_entry_id)
>          "person_hist__sys_start__sys_end_idx" btree (_sys_start, _sys_end)
>
>
>
>
>     Table person stores current versions of entries. 3 additional columns
> are added:
>         _entry_id - id of entry. It groups together different versions of
> entry.
>         _sys_start - beginning of the version validity period (version
> creation timestamp).
>         _sys_end - end of the version validity period.
>
>     Table person_hist stores historical versions of entries. It has the same
> structure and indexes as the person table, but without any constraints and
> default values.
>
> 2. another way of creating transaction time table is adding transaction time
> support to existing standard table using ALTER command.
>
>     CREATE TABLE person(name varchar(50));
>     ALTER TABLE person ADD TRANSACTIONTIME;
>
> 3. INSERT entry
>
>     INSERT INTO person VALUES('Jack');
>
>     SELECT *, _entry_id, _sys_start, _sys_end FROM person;
>
>      name | _entry_id |         _sys_start         |           _sys_end
>
> ------+-----------+----------------------------+------------------------------
>      Jack |         1 | 2012-05-16 22:11:39.856916 | 294276-12-31
> 23:59:59.999999
>
> 4. UPDATE entry
>
>     UPDATE person SET name = 'Tom';
>
>     SELECT *, _entry_id, _sys_start, _sys_end FROM person;
>
>      name | _entry_id |         _sys_start         |           _sys_end
>
> ------+-----------+----------------------------+------------------------------
>      Tom  |         1 | 2012-05-16 22:11:44.736195 | 294276-12-31
> 23:59:59.999999
>
>     SELECT * FROM person_hist;
>
>      name | _entry_id |         _sys_start         |          _sys_end
>
> ------+-----------+----------------------------+----------------------------
>      Jack |         1 | 2012-05-16 22:11:39.856916 | 2012-05-16
> 22:11:44.736194
>
> 5. DELETE entry
>
>     DELETE FROM person;
>
>     SELECT *, _entry_id, _sys_start, _sys_end FROM person;
>
>      name | _entry_id | _sys_start | _sys_end
>     ------+-----------+------------+----------
>
>     SELECT * FROM person_hist;
>
>      name | _entry_id |         _sys_start         |          _sys_end
>
> ------+-----------+----------------------------+----------------------------
>      Jack |         1 | 2012-05-16 22:11:39.856916 | 2012-05-16
> 22:11:44.736194
>      Tom  |         1 | 2012-05-16 22:11:44.736195 | 2012-05-16
> 22:14:33.875869
>
> 6. selecting entries
>
>     INSERT INTO person VALUES('Mike');
>     INSERT INTO person VALUES('Mike');
>
>     --standard SELECT - operates only with current versions of entries
>     SELECT * FROM person;
>
>      name
>     ------
>      Mike
>      Mike
>
>     --special temporal SELECT which operates with all versions
>     NONSEQUENCED TRANSACTIONTIME SELECT *, _entry_id, _sys_start, _sys_end
> FROM person;
>
>      name | _entry_id |         _sys_start         |           _sys_end
>
> ------+-----------+----------------------------+------------------------------
>      Mike |         3 | 2012-05-16 22:20:55.055671 | 294276-12-31
> 23:59:59.999999
>      Mike |         2 | 2012-05-16 22:20:51.619475 | 294276-12-31
> 23:59:59.999999
>      Jack |         1 | 2012-05-16 22:11:39.856916 | 2012-05-16
> 22:11:44.736194
>      Tom  |         1 | 2012-05-16 22:11:44.736195 | 2012-05-16
> 22:14:33.875869
>
>     --special temporal SELECT which operates with versions valid in
> specified time
>     TRANSACTIONTIME AS OF '2012-05-16 22:11:39.856916' SELECT *, _entry_id,
> _sys_start, _sys_end FROM person;
>
>      name | _entry_id |         _sys_start         |          _sys_end
>
> ------+-----------+----------------------------+----------------------------
>      Jack |         1 | 2012-05-16 22:11:39.856916 | 2012-05-16
> 22:11:44.736194
>
>     --it is also possible to set timestamp globally for session. All
> subsequent SELECTs without any temporal modifier will operate with versions
> valid in this time,
>     SET history_timestamp TO '2012-05-16 22:11:39.856916';
>
>     SELECT * FROM person;
>
>      name
>     ------
>      Jack
>
>     --to select only current versions when history_tiumestamp is set,
> CURRENT TRANSACTIONTIME have to be used with SELECT
>     CURRENT TRANSACTIONTIME SELECT * FROM person;
>
>      name
>     ------
>      Mike
>      Mike
>
>
>
> This is only a illustration of main functionality. Later I can create a
> document about the design and implementation details, but first I need to
> know if such temporal features as described here, could be added to future
> versions of PostgreSQL, after meeting all the requirements of a new patch.
>
> Regards
>
> Miroslav Simulcik