Re: Proposal : REINDEX xxx VERBOSE

Hello, I had a look on gram.y and found other syntaxes using WITH
option clause.

At Wed, 11 Feb 2015 14:34:17 -0600, Jim Nasby <Jim(dot)Nasby(at)BlueTreble(dot)com> wrote in <54DBBCC9(dot)1020206(at)BlueTreble(dot)com>
> I suspect at least some of this stems from how command line programs
> tend to process options before arguments. I tend to agree with you
> Tom, but I think what's more important is that we're consistent. COPY
> is already a bit of an oddball because it uses WITH, but both EXPLAIN
> and VACUUM use parenthesis immediately after the first
> verb. Introducing a parenthesis version that goes at the end instead
> of the beginning is just going to make this worse.
>
> If we're going to take a stand on this, we need to do it NOW, before
> we have even more commands that use ().
>
> I know you were worried about accepting options anywhere because it
> leads to reserved words, but perhaps we could support it just for
> EXPLAIN and VACUUM, and then switch to trailing options if people
> think that would be better.

I agree with the direction, but I see two issues here; how many
syntax variants we are allowed to have for one command at a time,
and how far we should/may extend the unified options syntax on
other commands.

Let me put the issues aside for now, VACUUM can have trailing
options naturally but it seems to change, but, IMHO, EXPLAIN
should have the target statement at the tail end. Are you
thinking of syntaxes like following?

VACUUM [FULL] [FREEZE] ... [ANALYZE] [tname [(cname, ...)]
| VACUUM [({FULL [bool]|FREEZE [bool]|...}[,...])] [tname [(cname, ...)]
| VACUUM [tname [(cname, ...)] [[WITH ]({FULL [bool]|FREEZE [bool]|...})]

REINDEX [{INDEX|TABLE|...}] name [[WITH] (VERBOSE [bool]|...)]

EXPLAIN [[WITH] ({ANALYZE [bool]|VERBOSE [bool]|... [,...]})] <statement>

For concrete examples, the lines prefixed by asterisk are in new
syntax.

VACUUM FULL table1;
VACUUM ANALYZE table1 (col1);
VACUUM (ANALYZE, VERBOSE) table1 (col1);
*VACUUM table1 WITH (FREEZE on)
*VACUUM table1 (cola) WITH (ANALYZE)
*VACUUM table1 WITH (ANALYZE)
*VACUUM table1 (FREEZE on)

The fifth example looks quite odd.

REINDEX INDEX index1 FORCE;
*REINDEX TABLE table1 WITH (VERBOSE on);
*REINDEX TABLE table1 (VERBOSE on, FORCE on);

EXPLAIN (ANALYZE) SELECT 1;
*EXPLAIN WITH (ANALYZE) SELECT 1;

The WITH looks a bit uneasy..

COPY table1 FROM 'file.txt' WITH (FORMAT csv);

Returning to the second issue, the following statements have
option list (or single option) preceded (or not preceded) by the
word WITH. The prefixing dollar sign indicates that the syntax is
of SQL standard according to the PostgreSQL
Documentation. Asterisk indicates that the line shows the syntax
if the new policy is applied. Other few statements like DECLARE
looks using WITH as a part of an idiom.

CREATE MATERIALIZED VIEW mv1 WITH (storageparam, ...) AS qry WITH DATA;

This is similar to EXPLAIN in the sense that a query follows
it, but this syntax can have the second WITH follows by DATA.

CREATE EXTENSION ext1 WITH SCHEMA s1 VERSION v1 FROM over;
*CREATE EXTENSION ext1 WITH (SCHEMA s1, VERSION v1, FROM over);

This seems to fit the unification.

CREATE ROLE role WITH LOGIN;
CREATE ROLE role SUPERUSER, LOGIN;
$CREATE ROLE role WITH ADMIN admin;
*CREATE ROLE role WITH (SUPERUSER, LOGIN);
*CREATE ROLE role (SUPERUSER, LOGIN);

This seems meaninglessly too complecated.

GRANT .... WITH GRANT OPTION;
*GRANT .... WITH (GRANT on);

Mmm. Seems no reasoning...

CREATE VIEW v1 AS qry WITH CASCADED CHECK OPTION;
*CREATE VIEW v1 AS qry WITH (CASCADED_CHECK);

Wired syntax?

ALTER DATABASE db1 WITH CONNECTION LIMIT 50;
*ALTER DATABASE db1 WITH (CONNECTION_LIMIT 50);

Hardly looks reasonable..

$DECLARE c1 INSENSITIVE SCROLL CURSOR WITH HOLD;

This cannot have another style.

Mmm... I'm at a loss what is desirable..

regards,

--
Kyotaro Horiguchi
NTT Open Source Software Center