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
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
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
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
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
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...
>
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
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
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
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
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" [
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
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
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' ] [ [
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
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
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:
>
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
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
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
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
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'
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
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
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
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
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
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
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
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
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
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
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
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
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_
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
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
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
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
> 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
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
> 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
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
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
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
45 matches
Mail list logo