Re: TABLESAMPLE patch

On 06/04/15 15:07, Amit Kapila wrote:
> On Mon, Apr 6, 2015 at 5:56 PM, Petr Jelinek <petr@2ndquadrant.com
> <mailto:petr@2ndquadrant.com>> wrote:
>  >
>  > On 06/04/15 12:33, Amit Kapila wrote:
>  >>
>  >>
>  >> But I think the Update on target table with sample scan is
>  >> supported via views which doesn't seem to be the right thing
>  >> in case you just want to support it via FROM/USING, example
>  >>
>  >> postgres=# create view vw_test As select * from test_tablesample
>  >> TABLESAMPLE sys
>  >> tem(30);
>  >> postgres=# explain update vw_test set id = 4;
>  >>                                  QUERY PLAN
>  >>
> ---------------------------------------------------------------------------
>  >>   Update on test_tablesample  (cost=0.00..4.04 rows=4 width=210)
>  >>     ->  Sample Scan on test_tablesample  (cost=0.00..4.04 rows=4
> width=210)
>  >> (2 rows)
>  >>
>  >
>  > Right, I'll make those views not auto-updatable.
>  >
>  >>
>  >>  > Standard is somewhat useless for UPDATE and DELETE as it only defines
>  >> quite limited syntax there. From what I've seen when doing research
>  >> MSSQL also only supports it in their equivalent of FROM/USING list,
>  >> Oracle does not seem to support their SAMPLING clause outside of SELECTs
>  >> at all and if I got the cryptic DB2 manual correctly I think they don't
>  >> support it outside of (sub)SELECTs either.
>  >>  >
>  >>
>  >> By the way, what is the usecase to support sample scan in
>  >> Update or Delete statement?
>  >>
>  >
>  > Well for the USING/FROM part the use-case is same as for SELECT -
> providing sample of the data for the query (it can be useful also for
> getting pseudo random rows fast). And if we didn't support it, it could
> still be done using sub-select so why not have it directly.
>  >
>
> I can understand why someone wants to read sample data via
> SELECT, but not clearly able to understand, why some one wants
> to Update or Delete random data in table and if there is a valid
> case, then why just based on sub-selects used in where clause
> or table reference in FROM/USING list.  Can't we keep it simple
> such that either we support to Update/Delete based on Tablesample
> clause or prohibit it in all cases?
>

Well, I don't understand why would somebody do it either, but then again 
during research of this feature I've found questions on stack overflow 
and similar sites about how to do it, so people must have use-cases.

And in any case as you say sub-select would work there so there is no 
reason to explicitly disable it. Plus there is already difference 
between what can be the target table in DELETE/UPDATE versus what can be 
in the FROM/USING clause and I think the TABLESAMPLE behavior follows 
that separation nicely - it's well demonstrated by the fact that we 
would have to add explicit exception to some places in code to disallow it.

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