Re: AS OF queries

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 connected by a foreign key, you can expect a
> certain shape of result, for example at least one match per PK row. But
> if you select from each table "as of" a different timestamp, then that
> won't hold. That could also throw off any optimizations we might come
> up with in that area, such as cross-table statistics. Not saying it
> can't or shouldn't be done, but there might be some questions.

Now I understand your statement. Yes, combining different timelines in
the same query can violate integrity constraint.
In theory there can be some query plans which will be executed
incorrectly  because of this constraint violation.
I do not know concrete examples of such plans right now, but I can not
prove that such problem can  not happen.

>
>>> What happens if no old data for the
>>> selected AS OF is available?
>> It will just return the version closest to the specified timestamp.
> That seems strange. Shouldn't that be an error?

I will add an option raising error in this case.
I do not want to always throw error, because Postgres is very
conservative in reclaiming old space. And the fact that version is not
used by any snapshot doesn't mean that it will be immediately deleted.
So there is still chance to peek-up old data although it is out of the
specified time travel period.

>
>>> How does this interact with catalog
>>> changes, such as changes to row-level security settings? (Do we apply
>>> the current or the past settings?)
>> Catalog changes are not currently supported.
>> And I do not have good understanding how to support it if query involves
>> two different timeslice with different versions of the table.
>> Too much places in parser/optimizer have to be change to support such
>> "historical collisions".
> Right, it's probably very hard to do. But I think it somehow should be
> recognized that catalog changes took place between the selected
> timestamp(s) and now and an error or notice should be produced.
>
There is one challenge: right now AS OF timestamps are not required to
be constants: them can be calculated dynamically during query execution.
So at the time of query compilation it is not possible to check whether
specified timestamps observe catalog changes or not.

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