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