temporal support patch

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

On May 16, 2012, at 5:14 PM, Miroslav Šimulčík wrote:

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

Hello!

I currently use the table_log extension:
http://pgfoundry.org/projects/tablelog/

Other than the built-in syntax, how does your implementation differ
feature-wise?

Cheers,
M

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

Miroslav Šimulcík wrote:

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.

Most of that is something you could handle with triggers (not TRUNCATE of
course).

What does your proposed feature do that a set of triggers wouldn't?

Yours,
Laurenz Albe

Regards
Miroslav Simulcik

2012/5/17 Pavel Stehule <pavel.stehule@gmail.com>

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

Hello

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

Hello.

SQL 2011 standard wasn't available in time I started this project so I

built

my implementation on older standards TSQL2 and SQL/Temporal, that were

only

available. None of these were accepted by ANSI/ISO commissions however.

There is different syntax in SQL 2011 and it looks like one that IBM DB2

had

been using even before this standard were published.

So my implementation differs in syntax, but features are same as stated

in

"system versioned tables" part of slideshow.

I would to see temporal functionality in pg, but only in SQL 2011
syntax. Using syntax from deprecated proposals has no sense. I am not
sure so history table concept is best from performance view - it is
simpler for implementation, but you duplicate all indexes - there will
be lot of redundant fields in history table. A important query is
difference in cost for some non trivial query for actual data and same
query for historic data.

Regards

Pavel Stehule

Regards
Miroslav Simulcik

2012/5/17 Pavel Stehule <pavel.stehule@gmail.com>

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

Regards,
Jeff Davis

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers