Re: AS OF queries

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 this asof_timestamp value.
>
>
> latest_table_ddl being found with
>
> SELECT greatest( max(pg_xact_commit_timestamp( rel.xmin )),
> max(pg_xact_commit_timestamp( att.xmin ))) as latest_table_ddl
> FROM pg_catalog.pg_attribute att
> INNER JOIN pg_catalog.pg_class rel
> ON att.attrelid = rel.oid WHERE rel.relname = '<asof_tablename>' and
> rel.relowner= ...
>
> (tested with add/alter/drop column and drop/create/truncate table)

Well, it can be done.
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.
The obvious solution is to calculate this latest_table_ddl timestamp
once and store it it somewhere (in ScanState?)
But I am not sure that this check is actually needed.
If table is changed in some incompatible way, then we will get error in
any case.
If table change is not critical for this query (for example some column
was added or removed which is not used in this query),
then should we really throw error in this case?

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