Обсуждение: CREATE INDEX...USING

On Wed, Aug 17, 2022 at 12:41:03PM -0400, Jeff Janes wrote:
> This was recently added to CREATE INDEX reference page:
> 
> +        The optional <literal>USING</literal> clause specifies an index
> +        type as described in <xref linkend="indexes-types"/>.  If not
> +        specified, a default index type will be used based on the
> +        data types of the columns.
> 
> But I think this is wrong, the default type is BTREE, it does not depend on the
> data type.  Or at least, I've never witnessed the claimed behavior.  The claim
> also conflicts with what is said at https://www.postgresql.org/docs/10/
> indexes-types.html

You are correct --- parser/gram.y has:

    access_method_clause:
                USING name                              { $$ = $2; }
                | /*EMPTY*/                             { $$ = DEFAULT_INDEX_TYPE; }
    
and from include/catalog/index.h:

    #define DEFAULT_INDEX_TYPE  "btree"

Patch attached.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  Indecision is a decision.  Inaction is an action.  Mark Batterson

On Wed, Aug 17, 2022 at 05:11:23PM -0400, Jeff Janes wrote:
> That looks good to me.  But now looking over the linked pages more, it seems
> like https://www.postgresql.org/docs/current/indexes-types.html was never
> changed to admit the possibility of custom index access methods (like bloom)

Uh, bloom is in /contrib, so we wouldn't mention that in the main docs,
I think.  However, it might be nice to mention you can add others.

> and neither was the 'replaceable class="parameter">method' section of https://
> www.postgresql.org/docs/current/sql-createindex.html.

Yes, seems we should say that you can install your own methods that can
be used, e.g., bloom.

> Also, is it odd that we say essentially the same thing for literal USING as we
> say for the replaceable /method/?

Well, this is embarrassing.  Someone reported there was no mention of
USING in the CREATE INDEX docs, and I didn't see it either, so I added
it.

However, CREATE INDEX just lists the parameters, not the keywords, so it
was already there as 'method', as you mentioned above.  I will just
remove the USING section I recently added.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  Indecision is a decision.  Inaction is an action.  Mark Batterson

On Wed, Aug 17, 2022 at 11:21:58PM -0400, Bruce Momjian wrote:
> On Wed, Aug 17, 2022 at 05:11:23PM -0400, Jeff Janes wrote:
> > That looks good to me.  But now looking over the linked pages more, it seems
> > like https://www.postgresql.org/docs/current/indexes-types.html was never
> > changed to admit the possibility of custom index access methods (like bloom)
> 
> Uh, bloom is in /contrib, so we wouldn't mention that in the main docs,
> I think.  However, it might be nice to mention you can add others.
> 
> > and neither was the 'replaceable class="parameter">method' section of https://
> > www.postgresql.org/docs/current/sql-createindex.html.
> 
> Yes, seems we should say that you can install your own methods that can
> be used, e.g., bloom.
> 
> > Also, is it odd that we say essentially the same thing for literal USING as we
> > say for the replaceable /method/?
> 
> Well, this is embarrassing.  Someone reported there was no mention of
> USING in the CREATE INDEX docs, and I didn't see it either, so I added
> it.
> 
> However, CREATE INDEX just lists the parameters, not the keywords, so it
> was already there as 'method', as you mentioned above.  I will just
> remove the USING section I recently added.

I wrote the attached patch to address the issues above.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  Indecision is a decision.  Inaction is an action.  Mark Batterson

On Thu, Aug 18, 2022 at 12:32:28PM -0400, Bruce Momjian wrote:
> On Wed, Aug 17, 2022 at 11:21:58PM -0400, Bruce Momjian wrote:
> > However, CREATE INDEX just lists the parameters, not the keywords, so it
> > was already there as 'method', as you mentioned above.  I will just
> > remove the USING section I recently added.
> 
> I wrote the attached patch to address the issues above.

Patch applied back to PG 10.  Thanks for the tip.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  Indecision is a decision.  Inaction is an action.  Mark Batterson