AS OF queries - Mailing list pgsql-hackers

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

-- 
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company