Re: AS OF queries

On 21/12/17 15:00, Konstantin Knizhnik wrote:
>
>
> On 20.12.2017 19:26, Tom Lane wrote:
>> Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com> writes:
>>> On 12/20/17 10:29, Tom Lane wrote:
>>>> Please say that's just an Oracle-ism and not SQL standard, because
>>>> it's
>>>> formally ambiguous.
>>> The SQL standard syntax appears to be something like
>>> "tablename" [ AS OF SYSTEM TIME 'something' ] [ [ AS ] "alias" ]
>>> That's not going to be fun to parse.
>> Bleah.  In principle we could look two tokens ahead so as to recognize
>> "AS OF SYSTEM", but base_yylex is already a horrid mess with one-token
>> lookahead; I don't much want to try to extend it to that.
>>
>> Possibly the most workable compromise is to use lookahead to convert
>> "AS OF" to "AS_LA OF", and then we could either just break using OF
>> as an alias, or add an extra production that allows "AS_LA OF" to
>> be treated as "AS alias" if it's not followed by the appropriate
>> stuff.
>>
>> It's a shame that the SQL committee appears to be so ignorant of
>> standard parsing technology.
>>
>>             regards, tom lane
>
> Thank you for suggestion with AS_LA: it really works.
> Actually instead of AS_LA I just return ASOF token if next token after
> AS is OF.
> So now it is possible to write query in this way:
>
>     select * from foo as of timestamp '2017-12-21 14:12:15.1867';
>
> There is still one significant difference of my prototype
> implementation with SQL standard: it associates timestamp with select
> statement, not with particular table.
> It seems to be more difficult to support and I am not sure that
> joining tables from different timelines has much sense.
> But certainly it also can be fixed.

    If the standard is "AS OF SYSTEM TIME" and we're going to deviate
and go for "AS OF TIMESTAMP", I'd recommend then, if possible, to:

- Make "TIMESTAMP" optional, i.e., "AS OF [TIMESTAMP] <timestamp>"

- Augment the syntax to support also a transaction id, similar to
Oracle's "AS OF SCN <scn>": "AS OF TRANSACTION <txid>".

    Merry Christmas,

    Álvaro

--

Alvaro Hernandez

-----------
OnGres