DSL support for arbitary tablesample sampling methods

[Steffo99]

I was looking for a way to take advantage of the tsm_system_rows extension with Diesel, but it seems that when using the query builder I'm limited to using only the .tablesample_system and .tablesample_bernoulli methods.

What I've tried

Since Tablesample::new is private, I can't create a new sampling method myself to use.

[weiznich]

Your observations are correct, in so far that the tablesample implementation in diesel is restricted to these two methods, as that were the only ones that I was aware of at the time of writing. There is also no way to extend this outside of diesel. Given that it seems like there exists at least one other method we might want to change that. The question is what exactly can and should change.
From my point of view that depends on how many other tablesample methods exists other than the now 3 known variants. Can they defined by arbitrary custom extensions or is it just a somewhat fixed list of methods provided by postgresql?

[Steffo99]

No idea; found out about TABLESAMPLE last night...

I later also found out about tsm_system_time, so that's four, I guess.

[weiznich]

I did a quick search and it seems like you can write your own extension to provide a custom table sample method: https://www.postgresql.org/docs/current/tablesample-method.html

That indicates that we might need a solution to add support for these methods outside of diesel.

In addition the docs of tsm_system_time indicate that the REPEATABLE clause is not supported, so we might need some changes to disallow that clause for certain table sample methods.

From looking at the relevant code in diesel, the best way forward seems to be:

• Make this trait public
• Define 2 new public zero sized types:
  • SupportRepeatableClause
  • NoRepeatableClause
• Add an associated type SupportsRepeatableClause to the trait linked above and set it to SupportRepeatableClause for both built-in impls
• Restrict this method to only exist if T: TablesampleMethod<SupportsRepeatableClause = SupportRepeatableClause>
• Add a fn tablesample<TM>(self, portion: i16) -> Tablesample<Self, TM> method here
• Write some documentation for the exposed trait to show how to implement support for a custom table sample method in diesel.

PR's for this are welcome.