Re: AS OF queries

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.

>>  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?

>>  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.

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services