Re: TABLESAMPLE doesn't actually satisfy the SQL spec, does it?

On 2015-07-16 16:22, Tom Lane wrote:
> Petr Jelinek <petr(at)2ndquadrant(dot)com> writes:
>> On 2015-07-12 18:02, Tom Lane wrote:
>>> A possible way around this problem is to redefine the sampling rule so
>>> that it is not history-dependent but depends only on the tuple TIDs.
>>> For instance, one could hash the TID of a candidate tuple, xor that with
>>> a hash of the seed being used for the current query, and then select the
>>> tuple if (hash/MAXINT) < P.
>
>> That would work for bernoulli for physical tuples, yes. Only thing that
>> worries me is future extensibility for data sources that only provide
>> virtual tuples.
>
> Well, repeatability of a TABLESAMPLE attached to a join seems like an
> unsolved and possibly unsolvable problem anyway. I don't think we should
> assume that the API we define today will cope with that.
>

Ok, It's true that the implementations I've seen in other databases so
far only concern themselves by sampling physical relations and ignore
the rest.

> But that is another reason why the current API is inadequate: there's no
> provision for specifying whether or how a tablesample method can be
> applied to non-base-table RTEs. (I re-read the thread and noted that
> Peter E. complained about that some time ago, but nothing was done about
> it. I'm fine with not supporting the case right now, but nonetheless
> it's another reason why we'd better make the API more easily extensible.)

Nothing in terms of implementation yes, I did write my idea on how this
could be done via extending the current API in the future. I won't try
to pretend that I am absolutely sure that the API might not need some
breaking change to do that though.

--
Petr Jelinek http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services