AS OF queries - Mailing list pgsql-hackers
From | Konstantin Knizhnik |
---|---|
Subject | AS OF queries |
Date | |
Msg-id | 78aadf6b-86d4-21b9-9c2a-51f1efb8a499@postgrespro.ru Whole thread Raw |
Responses |
Re: AS OF queries
Re: AS OF queries Re: AS OF queries Re: AS OF queries |
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
Attachment
pgsql-hackers by date: