Re: AS OF queries

On Thu, Dec 21, 2017 at 05:00:35PM +0300, 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';

Thanks for your hard work so far on this! It looks really exciting.

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

I can think of a use case right offhand that I suspect would be very
common: comparing the state of a table at multiple times.

> But certainly it also can be fixed.

That would be really fantastic.

Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate