Re: WITH clause in CREATE STATISTICS

On 05/03/2017 04:42 PM, Tom Lane wrote:
> Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> writes:
>> Yawn. So, we have not achieved the stated goal which was to get rid of
>> the ugly clause in the middle of the command; moreover we have gained
>> *yet another* clause in the middle of the command. Is this really an
>> improvement? We're trading this
>> CREATE STATISTICS s1 WITH (dependencies, ndistinct, options) ON (a, b) FROM t1 WHERE partial-stuff;
>> for this:
>> CREATE STATISTICS s1 USING (dependencies, ndistinct) WITH (options) ON (a, b) FROM t1 WHERE partial-stuff;
>> Can we decide by a show of hands, please, whether we're really on board
>> with this change?
> That seems totally messy :-(
>
> I can't see any good reason why "WITH (options)" can't be at the end of
> the query. WITH is a fully reserved word, there is not going to be any
> danger of a parse problem from having it follow the FROM or WHERE clauses.
> And the end is where we have other instances of "WITH (options)".
>
> It also seems like we don't need to have *both* fully-reserved keywords
> introducing each clause *and* parentheses around the lists. Maybe
> dropping the parens around the stats-types list and the column-names
> list would help to declutter? (But I'd keep parens around the WITH
> options, for consistency with other statements.)
>
> One other point is that as long as we've got reserved keywords introducing
> each clause, there isn't actually an implementation reason why we couldn't
> accept the clauses in any order. Not sure I want to document it that way,
> but it might not be a bad thing if the grammar was forgiving about whether
> you write the USING or ON part first ...
>
>

+1 for allowing arbitrary order of clauses. I would document it with the
USING clause at the end, and have that be what psql supports and pg_dump
produces. Since there are no WITH options now we should leave that out
until it's required.

cheers

andrew

--
Andrew Dunstan https://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services