On 20.12.2017 14:45, Konstantin Knizhnik wrote: > I wonder if Postgres community is interested in supporting time travel > queries in PostgreSQL (something like AS OF queries in Oracle: > https://docs.oracle.com/cd/B14117_01/appdev.101/b10795/adfns_fl.htm). > As far as I know something similar is now developed for MariaDB. > > It seems to me that it will be not so difficult to implement them in > Postgres - we already have versions of tuples. > Looks like we only need to do three things: > 1. Disable autovacuum (autovacuum = off) In the design for original University Postgres ( which was a full history database geared towards WORM drives ) it was the task of vacuum to move old tuples to "an archive" from where the AS OF queries would then fetch them as needed.
This might also be a good place to do Commit LSN to Commit Timestamp translation Hannu > 2. Enable commit timestamp (track_commit_timestamp = on) > 3. Add asofTimestamp to snapshot and patch XidInMVCCSnapshot to > compare commit timestamps when it is specified in snapshot. > > > Attached please find my prototype implementation of it. > Most of the efforts are needed to support asof timestamp in grammar > and add it to query plan. > I failed to support AS OF clause (as in Oracle) because of > shift-reduce conflicts with aliases, > so I have to introduce new ASOF keyword. May be yacc experts can > propose how to solve this conflict without introducing new keyword... > > Please notice that now ASOF timestamp is used only for data snapshot, > not for catalog snapshot. > I am not sure that it is possible (and useful) to travel through > database schema history... > > Below is an example of how it works: > > postgres=# create table foo(pk serial primary key, ts timestamp > default now(), val text); > CREATE TABLE > postgres=# insert into foo (val) values ('insert'); > INSERT 0 1 > postgres=# insert into foo (val) values ('insert'); > INSERT 0 1 > postgres=# insert into foo (val) values ('insert'); > INSERT 0 1 > postgres=# select * from foo; > pk | ts | val > ----+----------------------------+-------- > 1 | 2017-12-20 14:59:17.715453 | insert > 2 | 2017-12-20 14:59:22.933753 | insert > 3 | 2017-12-20 14:59:27.87712 | insert > (3 rows) > > postgres=# select * from foo asof timestamp '2017-12-20 14:59:25'; > pk | ts | val > ----+----------------------------+-------- > 1 | 2017-12-20 14:59:17.715453 | insert > 2 | 2017-12-20 14:59:22.933753 | insert > (2 rows) > > postgres=# select * from foo asof timestamp '2017-12-20 14:59:20'; > pk | ts | val > ----+----------------------------+-------- > 1 | 2017-12-20 14:59:17.715453 | insert > (1 row) > > postgres=# update foo set val='upd',ts=now() where pk=1; > UPDATE 1 > postgres=# select * from foo asof timestamp '2017-12-20 14:59:20'; > pk | ts | val > ----+----------------------------+-------- > 1 | 2017-12-20 14:59:17.715453 | insert > (1 row) > > postgres=# select * from foo; > pk | ts | val > ----+----------------------------+-------- > 2 | 2017-12-20 14:59:22.933753 | insert > 3 | 2017-12-20 14:59:27.87712 | insert > 1 | 2017-12-20 15:09:17.046047 | upd > (3 rows) > > postgres=# update foo set val='upd2',ts=now() where pk=1; > UPDATE 1 > postgres=# select * from foo asof timestamp '2017-12-20 15:10'; > pk | ts | val > ----+----------------------------+-------- > 2 | 2017-12-20 14:59:22.933753 | insert > 3 | 2017-12-20 14:59:27.87712 | insert > 1 | 2017-12-20 15:09:17.046047 | upd > (3 rows) > > > Comments and feedback are welcome:) > -- Hannu Krosing PostgreSQL Consultant Performance, Scalability and High Availability https://2ndquadrant.com/