Re: WITH clause in CREATE STATISTICS

2017-04-22 11:30 GMT+02:00 Simon Riggs <simon(at)2ndquadrant(dot)com>:

> On 21 April 2017 at 01:21, Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com>
> wrote:
> > On 04/21/2017 12:13 AM, Tom Lane wrote:
> >>
> >> Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> writes:
> >>>
> >>> Simon just pointed out that having the WITH clause appear in the middle
> >>> of the CREATE STATISTICS command looks odd; apparently somebody else
> >>> already complained on list about the same. Other commands put the WITH
> >>> clause at the end, so perhaps we should do likewise in the new command.
> >>
> >>
> >>> Here's a patch to implement that. I verified that if I change
> >>> qualified_name to qualified_name_list, bison does not complain about
> >>> conflicts, so this new syntax should support extension to multiple
> >>> relations without a problem.
> >>
> >>
> >> Yeah, WITH is fully reserved, so as long as the clause looks like
> >> WITH ( stuff... ) you're pretty much gonna be able to drop it
> >> wherever you want.
> >>
> >>> Discuss.
> >>
> >>
> >> +1 for WITH at the end; the existing syntax looks weird to me too.
> >>
> >
> > -1 from me
> >
> > I like the current syntax more, and WHERE ... WITH seems a bit weird to
> me.
> > But more importantly, one thing Dean probably considered when proposing
> the
> > current syntax was that we may add support for partial statistics, pretty
> > much like partial indexes. And we don't allow WITH at the end (after
> WHERE)
> > for indexes:
> >
> > test=# create index on t (a) where a < 100 with (fillfactor=10);
> > ERROR: syntax error at or near "with"
> > LINE 1: create index on t (a) where a < 100 with (fillfactor=10);
> > ^
> > test=# create index on t (a) with (fillfactor=10) where a < 100;
>
> OK, didn't know about WHERE clause; makes sense.
>
> Currently WITH is supported in two places, which feels definitely
> wrong. The WITH clause is used elsewhere to provide optional
> parameters, and if there are none present it is optional.
>
> OK, so lets try...
>
> 1.
> No keyword at all, just list of statistics we store (i.e. just lose the
> WITH)
> CREATE STATISTICS s1 (dependencies, ndistinct) ON (a, b) FROM t1 WHERE
> partial-stuff;
>
> and if there are options, use the WITH for the optional parameters like
> this
> CREATE STATISTICS s1 (dependencies, ndistinct) WITH (options) ON (a,
> b) FROM t1 WHERE partial-stuff;
>
> 2.
> USING keyword, no brackets
> CREATE STATISTICS s1 USING (dependencies, ndistinct) ON (a, b) FROM t1
> WHERE partial-stuff;
>
> and if there are options, use the WITH for the optional parameters like
> this
> CREATE STATISTICS s1 USING (dependencies, ndistinct) WITH (options) ON
> (a, b) FROM t1 WHERE partial-stuff;
>
>
> I think I like (2)
>

+1

Regards

Pavel

>
> --
> Simon Riggs http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>