temporal support patch

                  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 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

4. UPDATE entry

    UPDATE person SET name = 'Tom';

    SELECT *, _entry_id, _sys_start, _sys_end FROM person;

   

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