Re: AS OF queries

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 using quasi SQL-standard syntax for a nonstandard
interpretation? I think we could very well have a variety of standard
and nonstandard AS OF variants, including by commit timestamp, xid,
explicit range columns, etc. But I'd like to see a discussion on that,
perhaps in a documentation update, which this patch is missing.

I have questions about corner cases. What happens when multiple tables
are queried with different AS OF clauses? Can there be apparent RI
violations? What happens when the time_travel_period is changed during
a session? How can we check how much old data is available, and how can
we check how much space it uses? What happens if no old data for the
selected AS OF is available? How does this interact with catalog
changes, such as changes to row-level security settings? (Do we apply
the current or the past settings?)

This patch should probably include a bunch of tests to cover these and
other scenarios.

(Maybe "period" isn't the best name, because it implies a start and an
end. How about something with "age"?)

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