AS OF queries

2017-12-20 Thread Konstantin Knizhnik
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

Re: AS OF queries

2017-12-20 Thread Laurenz Albe
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 d

Re: AS OF queries

2017-12-20 Thread Konstantin Knizhnik
On 20.12.2017 16:12, Laurenz Albe wrote: 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

Re: AS OF queries

2017-12-20 Thread Joe Wildish
On 20 Dec 2017, at 13:48, Konstantin Knizhnik wrote: > > On 20.12.2017 16:12, Laurenz Albe wrote: >> Konstantin Knizhnik wrote: >>> I wonder if Postgres community is interested in supporting time travel >>> queries in PostgreSQL (something like AS OF

Re: AS OF queries

2017-12-20 Thread Laurenz Albe
Konstantin Knizhnik wrote: > Please notice that it is necessary to configure postgres in proper way in > order to be able to perform time travels. > If you do not disable autovacuum, then old versions will be just cleaned-up. > If transaction commit timestamps are not tracked, then it is not possi

Re: AS OF queries

2017-12-20 Thread Tom Lane
Laurenz Albe writes: > Konstantin Knizhnik wrote: >> 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... >

Re: AS OF queries

2017-12-20 Thread David Fetter
On Wed, Dec 20, 2017 at 03:03:50PM +0100, Laurenz Albe wrote: > Konstantin Knizhnik wrote: > > Please notice that it is necessary to configure postgres in proper > > way in order to be able to perform time travels. If you do not > > disable autovacuum, then old versions will be just cleaned-up. I

Re: AS OF queries

2017-12-20 Thread Peter Eisentraut
On 12/20/17 10:29, Tom Lane wrote: > Please say that's just an Oracle-ism and not SQL standard, because it's > formally ambiguous. This is required to work by spec: > > regression=# select x as of from (values(1)) t(x); > of > > 1 > (1 row) > > so it's not possible for us ever to suppor

Re: AS OF queries

2017-12-20 Thread Magnus Hagander
On Wed, Dec 20, 2017 at 5:17 PM, Peter Eisentraut < peter.eisentr...@2ndquadrant.com> wrote: > On 12/20/17 10:29, Tom Lane wrote: > > Please say that's just an Oracle-ism and not SQL standard, because it's > > formally ambiguous. This is required to work by spec: > > > > regression=# select x as

Re: AS OF queries

2017-12-20 Thread Tom Lane
Peter Eisentraut writes: > On 12/20/17 10:29, Tom Lane wrote: >> Please say that's just an Oracle-ism and not SQL standard, because it's >> formally ambiguous. > The SQL standard syntax appears to be something like > "tablename" [ AS OF SYSTEM TIME 'something' ] [ [ AS ] "alias" ] > That's not

Re: AS OF queries

2017-12-20 Thread Pantelis Theodosiou
On Wed, Dec 20, 2017 at 4:26 PM, Tom Lane wrote: > Peter Eisentraut writes: > > On 12/20/17 10:29, Tom Lane wrote: > >> Please say that's just an Oracle-ism and not SQL standard, because it's > >> formally ambiguous. > > > The SQL standard syntax appears to be something like > > > "tablename" [

Re: AS OF queries

2017-12-20 Thread Alvaro Hernandez
On 20/12/17 14:48, Konstantin Knizhnik wrote: On 20.12.2017 16:12, Laurenz Albe wrote: 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

Re: AS OF queries

2017-12-20 Thread Craig Ringer
On 21 December 2017 at 00:17, Peter Eisentraut < peter.eisentr...@2ndquadrant.com> wrote: > On 12/20/17 10:29, Tom Lane wrote: > > Please say that's just an Oracle-ism and not SQL standard, because it's > > formally ambiguous. This is required to work by spec: > > > > regression=# select x as of

Re: AS OF queries

2017-12-21 Thread Konstantin Knizhnik
On 20.12.2017 19:26, Tom Lane wrote: Peter Eisentraut writes: On 12/20/17 10:29, Tom Lane wrote: Please say that's just an Oracle-ism and not SQL standard, because it's formally ambiguous. The SQL standard syntax appears to be something like "tablename" [ AS OF SYSTEM TIME 'something' ] [ [

Re: AS OF queries

2017-12-21 Thread David Fetter
On Thu, Dec 21, 2017 at 05:00:35PM +0300, Konstantin Knizhnik wrote: > On 20.12.2017 19:26, Tom Lane wrote: > >Peter Eisentraut writes: > >>On 12/20/17 10:29, Tom Lane wrote: > >>>Please say that's just an Oracle-ism and not SQL standard, because it's > >>>formally ambiguous. > >>The SQL standard

Re: AS OF queries

2017-12-22 Thread Greg Stark
On 20 December 2017 at 12:45, Konstantin Knizhnik wrote: > 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) "The Wheel of Time turns, and

Re: AS OF queries

2017-12-22 Thread Michael Paquier
On Fri, Dec 22, 2017 at 11:08:02PM +, Greg Stark wrote: > On 20 December 2017 at 12:45, Konstantin Knizhnik > wrote: > > > 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: >

Re: AS OF queries

2017-12-23 Thread konstantin knizhnik
On Dec 23, 2017, at 2:08 AM, Greg Stark wrote: > On 20 December 2017 at 12:45, Konstantin Knizhnik > wrote: > >> 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 au

Re: AS OF queries

2017-12-24 Thread Alvaro Hernandez
On 21/12/17 15:00, Konstantin Knizhnik wrote: On 20.12.2017 19:26, Tom Lane wrote: Peter Eisentraut writes: On 12/20/17 10:29, Tom Lane wrote: Please say that's just an Oracle-ism and not SQL standard, because it's formally ambiguous. The SQL standard syntax appears to be something like

Re: AS OF queries

2017-12-24 Thread Craig Ringer
On 24 December 2017 at 04:53, konstantin knizhnik wrote: > > > But what if I just forbid to change recent_global_xmin? > If it is stalled at FirstNormalTransactionId and never changed? > Will it protect all versions from been deleted? > That's totally impractical, you'd have unbounded bloat and

Re: AS OF queries

2017-12-25 Thread Konstantin Knizhnik
On 25.12.2017 06:26, Craig Ringer wrote: On 24 December 2017 at 04:53, konstantin knizhnik mailto:k.knizh...@postgrespro.ru>> wrote: But what if I just forbid to change recent_global_xmin? If it is stalled at FirstNormalTransactionId and never changed? Will it protect all versio

Re: AS OF queries

2017-12-25 Thread Masahiko Sawada
me 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. > > I think that would be a good thing to have that could make > the DBA'

Re: AS OF queries

2017-12-26 Thread Jeff Janes
On Thu, Dec 21, 2017 at 6:00 AM, Konstantin Knizhnik < k.knizh...@postgrespro.ru> wrote: > There is still one significant difference of my prototype implementation > with SQL standard: it associates timestamp with select statement, not with > particular table. > It seems to be more difficult to s

Re: AS OF queries

2017-12-26 Thread legrand legrand
would actual syntax WITH old_foo AS (select * from foo as of '') select * from foo except select * from old_foo; work in replacement for select * from foo except select * from foo as old_foo as of ''; ? Regards PAscal -- Sent from: http://www.postgresql-archive.org/PostgreSQL-hackers-f19287

Re: AS OF queries

2017-12-26 Thread David Fetter
On Tue, Dec 26, 2017 at 03:43:36PM -0700, legrand legrand wrote: > would actual syntax > > WITH old_foo AS > (select * from foo as of '') > select * from foo except select * from old_foo; > > work in replacement for > > select * from foo except select * from foo as old_foo as of ''; > > ? If t

Re: AS OF queries

2017-12-26 Thread Craig Ringer
On 25 December 2017 at 15:59, Konstantin Knizhnik wrote: > > > On 25.12.2017 06:26, Craig Ringer wrote: > > On 24 December 2017 at 04:53, konstantin knizhnik < > k.knizh...@postgrespro.ru> wrote: > >> >> >> But what if I just forbid to change recent_global_xmin? >> If it is stalled at FirstNormal

Re: AS OF queries

2017-12-27 Thread Konstantin Knizhnik
On 27.12.2017 10:29, Craig Ringer wrote: On 25 December 2017 at 15:59, Konstantin Knizhnik mailto:k.knizh...@postgrespro.ru>> wrote: On 25.12.2017 06:26, Craig Ringer wrote: On 24 December 2017 at 04:53, konstantin knizhnik mailto:k.knizh...@postgrespro.ru>> wrote: B

Re: AS OF queries

2017-12-27 Thread Hannu Krosing
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 somethin

Re: AS OF queries

2017-12-27 Thread PostgreSQL - Hans-Jürgen Schönig
On 12/20/2017 01:45 PM, 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 kn

Re: AS OF queries

2017-12-27 Thread Konstantin Knizhnik
On 27.12.2017 17:14, PostgreSQL - Hans-Jürgen Schönig wrote: On 12/20/2017 01:45 PM, 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

Re: AS OF queries

2017-12-27 Thread Konstantin Knizhnik
On 27.12.2017 00:52, Jeff Janes wrote: On Thu, Dec 21, 2017 at 6:00 AM, Konstantin Knizhnik mailto:k.knizh...@postgrespro.ru>> wrote: There is still one significant difference of my prototype implementation with SQL standard: it associates timestamp with select statement, not with

Re: AS OF queries

2017-12-27 Thread Peter van Hardenberg
On Wed, Dec 27, 2017 at 7:37 AM, Konstantin Knizhnik < k.knizh...@postgrespro.ru> wrote: > > > On 27.12.2017 00:52, Jeff Janes wrote: > > On Thu, Dec 21, 2017 at 6:00 AM, Konstantin Knizhnik < > k.knizh...@postgrespro.ru> wrote: > > >> There is still one significant difference of my prototype impl

Re: AS OF queries

2017-12-28 Thread Konstantin Knizhnik
Attached please find new version of AS OF patch which allows to specify time travel period. Older versions outside this period may be reclaimed by autovacuum. This behavior is controlled by "time_travel_period" parameter. Zero value of this parameter disables time travel and postgres behaves in

Re: AS OF queries

2017-12-28 Thread Peter Eisentraut
On 12/28/17 11:36, Konstantin Knizhnik wrote: > Attached please find new version of AS OF patch which allows to specify > time travel period. > Older versions outside this period may be reclaimed by autovacuum. > This behavior is controlled by "time_travel_period" parameter. So where are we on us

Re: AS OF queries

2017-12-29 Thread Konstantin Knizhnik
On 28.12.2017 20:28, Peter Eisentraut wrote: On 12/28/17 11:36, Konstantin Knizhnik wrote: Attached please find new version of AS OF patch which allows to specify time travel period. Older versions outside this period may be reclaimed by autovacuum. This behavior is controlled by "time_travel_

Re: AS OF queries

2018-01-02 Thread Peter Eisentraut
On 12/29/17 06:28, Konstantin Knizhnik wrote: >> Can there be apparent RI >> violations? > Right now AS OF is used only in selects, not in update statements. So I > do not understand how integrity constraints can be violated. I mean, if you join tables connected by a foreign key, you can expect

Re: AS OF queries

2018-01-03 Thread legrand legrand
Maybe that a simple check of the asof_timestamp value like: asof_timestamp >= now() - time_travel_period AND asof_timestamp >= latest_table_ddl would permit to raise a warning or an error message saying that query result can not be garanteed with this asof_timestamp value. latest_table_ddl bei

Re: AS OF queries

2018-01-10 Thread Konstantin Knizhnik
On 02.01.2018 21:12, Peter Eisentraut wrote: On 12/29/17 06:28, Konstantin Knizhnik wrote: Can there be apparent RI violations? Right now AS OF is used only in selects, not in update statements. So I do not understand how integrity constraints can be violated. I mean, if you join tables c

Re: AS OF queries

2018-01-10 Thread Konstantin Knizhnik
On 03.01.2018 23:49, legrand legrand wrote: Maybe that a simple check of the asof_timestamp value like: asof_timestamp >= now() - time_travel_period AND asof_timestamp >= latest_table_ddl would permit to raise a warning or an error message saying that query result can not be garanteed with th

Re: AS OF queries

2018-01-10 Thread legrand legrand
> But performing this query on each access to the table seems to be bad > idea: in case of nested loop join it can cause significant degrade of > performance. this could be a pre-plan / pre-exec check, no more. > But I am not sure that this check is actually needed. > If table is changed in some

Re: AS OF queries

2018-01-10 Thread Konstantin Knizhnik
On 10.01.2018 16:02, legrand legrand wrote: But performing this query on each access to the table seems to be bad idea: in case of nested loop join it can cause significant degrade of performance. this could be a pre-plan / pre-exec check, no more. AS-OF timestamp can be taken from outer tab

Re: AS OF queries

2018-01-10 Thread legrand legrand
> Sorry, truncate is not compatible with AS OF. It is performed at file > level and deletes old old version. > So if you want to use time travel, you should not use truncate. As time travel doesn't support truncate, I would prefer it to be checked. If no check is performed, ASOF queries (with tim

Re: AS OF queries

2018-01-25 Thread Bruce Momjian
On Sat, Dec 23, 2017 at 11:53:19PM +0300, konstantin knizhnik wrote: > > On Dec 23, 2017, at 2:08 AM, Greg Stark wrote: > > > On 20 December 2017 at 12:45, Konstantin Knizhnik > > wrote: > > > >> It seems to me that it will be not so difficult to implement them in > >> Postgres - we already hav

Re: AS OF queries

2018-01-25 Thread Konstantin Knizhnik
On 26.01.2018 03:55, Bruce Momjian wrote: On Sat, Dec 23, 2017 at 11:53:19PM +0300, konstantin knizhnik wrote: On Dec 23, 2017, at 2:08 AM, Greg Stark wrote: On 20 December 2017 at 12:45, Konstantin Knizhnik wrote: It seems to me that it will be not so difficult to implement them in Postg

Re: AS OF queries

2018-01-26 Thread Bruce Momjian
On Fri, Jan 26, 2018 at 10:56:06AM +0300, Konstantin Knizhnik wrote: > >>Yeh, I suspected that just disabling autovacuum was not enough. > >>I heard (but do no know too much) about microvacuum and hot updates. > >>This is why I was a little bit surprised when me test didn't show lost of > >>update