There would be no problem to make my solution compatible with SQL 2011, but the standard is not freely available. Can anybody provide me with this standard?
2012/5/20 Pavel Stehule <pavel.steh...@gmail.com> > Hello > > 2012/5/18 Miroslav Šimulčík <simulcik.m...@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.steh...@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.m...@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 > > > > >
