EXCLUDE COLLATE in CREATE/ALTER TABLE document

Started by shihao zhongabout 2 years ago8 messages

zhong950419@gmail.com

about 2 years ago

1 attachment(s)

Hi hackers,

I hope this email finds you well.

I noticed that the CREATE/ALTER TABLE document does not mention that
EXCLUDE can accept a collation. I created a documentation fix for this
issue, and I have attached it to this email.

Please let me know if you have any questions or concerns.

Thanks,
Shihao

Attachments:

update_document_exclude_with_collate.patchapplication/octet-stream; name=update_document_exclude_with_collate.patchDownload

diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 2c4138e4e9..6f01cf9916 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -116,7 +116,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
   NOT NULL <replaceable class="parameter">column_name</replaceable> [ NO INHERIT ] |
   UNIQUE [ NULLS [ NOT ] DISTINCT ] ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> |
   PRIMARY KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> |
-  EXCLUDE [ USING <replaceable class="parameter">index_method</replaceable> ] ( <replaceable class="parameter">exclude_element</replaceable> WITH <replaceable class="parameter">operator</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> [ WHERE ( <replaceable class="parameter">predicate</replaceable> ) ] |
+  EXCLUDE [ USING <replaceable class="parameter">index_method</replaceable> ] ( <replaceable class="parameter">exclude_element</replaceable> [ COLLATE <replaceable class="parameter">collation</replaceable> ] WITH <replaceable class="parameter">operator</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> [ WHERE ( <replaceable class="parameter">predicate</replaceable> ) ] |
   FOREIGN KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) REFERENCES <replaceable class="parameter">reftable</replaceable> [ ( <replaceable class="parameter">refcolumn</replaceable> [, ... ] ) ]
     [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE <replaceable class="parameter">referential_action</replaceable> ] [ ON UPDATE <replaceable class="parameter">referential_action</replaceable> ] }
 [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index e04a0692c4..a08af85948 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -80,7 +80,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
   NOT NULL <replaceable class="parameter">column_name</replaceable> [ NO INHERIT ] |
   UNIQUE [ NULLS [ NOT ] DISTINCT ] ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> |
   PRIMARY KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> |
-  EXCLUDE [ USING <replaceable class="parameter">index_method</replaceable> ] ( <replaceable class="parameter">exclude_element</replaceable> WITH <replaceable class="parameter">operator</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> [ WHERE ( <replaceable class="parameter">predicate</replaceable> ) ] |
+  EXCLUDE [ USING <replaceable class="parameter">index_method</replaceable> ] ( <replaceable class="parameter">exclude_element</replaceable> [ COLLATE <replaceable class="parameter">collation</replaceable> ] WITH <replaceable class="parameter">operator</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> [ WHERE ( <replaceable class="parameter">predicate</replaceable> ) ] |
   FOREIGN KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) REFERENCES <replaceable class="parameter">reftable</replaceable> [ ( <replaceable class="parameter">refcolumn</replaceable> [, ... ] ) ]
     [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE <replaceable
 class="parameter">referential_action</replaceable> ] [ ON UPDATE <replaceable class="parameter">referential_action</replaceable> ] }

Tom Lane

tgl@sss.pgh.pa.us

about 2 years ago

In reply to: shihao zhong (#1)

Re: EXCLUDE COLLATE in CREATE/ALTER TABLE document

shihao zhong <zhong950419@gmail.com> writes:

I noticed that the CREATE/ALTER TABLE document does not mention that
EXCLUDE can accept a collation. I created a documentation fix for this
issue, and I have attached it to this email.

Hmm ... is this actually correct? I think that the collate
option has to come before the opclass name etc, so you'd need
to shove it into exclude_element to provide an accurate
description of the syntax.

regards, tom lane

shihao zhong

zhong950419@gmail.com

about 2 years ago

In reply to: Tom Lane (#2)

1 attachment(s)

Re: EXCLUDE COLLATE in CREATE/ALTER TABLE document

On Tue, Oct 31, 2023 at 9:07 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

shihao zhong <zhong950419@gmail.com> writes:

I noticed that the CREATE/ALTER TABLE document does not mention that
EXCLUDE can accept a collation. I created a documentation fix for this
issue, and I have attached it to this email.

Hmm ... is this actually correct? I think that the collate
option has to come before the opclass name etc, so you'd need
to shove it into exclude_element to provide an accurate
description of the syntax.

regards, tom lane

Hi Tom,
Thank you for your feedback on my previous patch. I have fixed the issue
and attached a new patch for your review. Could you please take a look for
it if you have a sec? Thanks

Also, if I understand correctly, the changes to sql_help.c will be made by
the committer, so I do not need to run create_help.pl in my patch. Can you
please confirm?

I appreciate your help and time.

Thanks,
Shihao

Attachments:

update_document_exclude_with_collate_v2.patchapplication/octet-stream; name=update_document_exclude_with_collate_v2.patchDownload

diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 2c4138e4e9..43f5f0873e 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -135,7 +135,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
 
 <phrase><replaceable class="parameter">exclude_element</replaceable> in an <literal>EXCLUDE</literal> constraint is:</phrase>
 
-{ <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ <replaceable class="parameter">opclass</replaceable> ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]
+{ <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ COLLATE <replaceable class="parameter">collation</replaceable> ] [ <replaceable class="parameter">opclass</replaceable> ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]
 
 <phrase><replaceable class="parameter">referential_action</replaceable> in a <literal>FOREIGN KEY</literal>/<literal>REFERENCES</literal> constraint is:</phrase>
 
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index e04a0692c4..da574e9804 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -105,7 +105,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
 
 <phrase><replaceable class="parameter">exclude_element</replaceable> in an <literal>EXCLUDE</literal> constraint is:</phrase>
 
-{ <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ <replaceable class="parameter">opclass</replaceable> ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]
+{ <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ COLLATE <replaceable class="parameter">collation</replaceable> ] [ <replaceable class="parameter">opclass</replaceable> ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]
 
 <phrase><replaceable class="parameter">referential_action</replaceable> in a <literal>FOREIGN KEY</literal>/<literal>REFERENCES</literal> constraint is:</phrase>

jian he

jian.universality@gmail.com

about 2 years ago

In reply to: shihao zhong (#3)

Re: EXCLUDE COLLATE in CREATE/ALTER TABLE document

On Wed, Nov 1, 2023 at 10:30 AM shihao zhong <zhong950419@gmail.com> wrote:

Thank you for your feedback on my previous patch. I have fixed the issue and attached a new patch for your review. Could you please take a look for it if you have a sec? Thanks

Your patch works fine. you can see it here:
https://cirrus-ci.com/task/6481922939944960
in an ideal world, since the doc is already built, we can probably
view it as a plain html file just click the ci test result.

in src/sgml/ref/create_table.sgml:
"Each exclude_element can optionally specify an operator class and/or
ordering options; these are described fully under CREATE INDEX."

You may need to update this sentence to reflect that exclude_element
can also optionally specify collation.

shihao zhong

zhong950419@gmail.com

about 2 years ago

In reply to: jian he (#4)

1 attachment(s)

Re: EXCLUDE COLLATE in CREATE/ALTER TABLE document

Hi Jian,

Thanks for your comments, a new version is attached.

Thanks,
Shihao

On Fri, Nov 10, 2023 at 9:59 AM jian he <jian.universality@gmail.com> wrote:

Show quoted text

On Wed, Nov 1, 2023 at 10:30 AM shihao zhong <zhong950419@gmail.com>
wrote:

Thank you for your feedback on my previous patch. I have fixed the issue

and attached a new patch for your review. Could you please take a look for
it if you have a sec? Thanks

Your patch works fine. you can see it here:
https://cirrus-ci.com/task/6481922939944960
in an ideal world, since the doc is already built, we can probably
view it as a plain html file just click the ci test result.

in src/sgml/ref/create_table.sgml:
"Each exclude_element can optionally specify an operator class and/or
ordering options; these are described fully under CREATE INDEX."

You may need to update this sentence to reflect that exclude_element
can also optionally specify collation.

Attachments:

update_document_exclude_with_collate_v3.patchapplication/octet-stream; name=update_document_exclude_with_collate_v3.patchDownload

diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 2c4138e4e9..43f5f0873e 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -135,7 +135,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
 
 <phrase><replaceable class="parameter">exclude_element</replaceable> in an <literal>EXCLUDE</literal> constraint is:</phrase>
 
-{ <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ <replaceable class="parameter">opclass</replaceable> ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]
+{ <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ COLLATE <replaceable class="parameter">collation</replaceable> ] [ <replaceable class="parameter">opclass</replaceable> ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]
 
 <phrase><replaceable class="parameter">referential_action</replaceable> in a <literal>FOREIGN KEY</literal>/<literal>REFERENCES</literal> constraint is:</phrase>
 
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index e04a0692c4..a5e768352f 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -105,7 +105,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
 
 <phrase><replaceable class="parameter">exclude_element</replaceable> in an <literal>EXCLUDE</literal> constraint is:</phrase>
 
-{ <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ <replaceable class="parameter">opclass</replaceable> ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]
+{ <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ COLLATE <replaceable class="parameter">collation</replaceable> ] [ <replaceable class="parameter">opclass</replaceable> ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]
 
 <phrase><replaceable class="parameter">referential_action</replaceable> in a <literal>FOREIGN KEY</literal>/<literal>REFERENCES</literal> constraint is:</phrase>
 
@@ -1097,8 +1097,8 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
       method <replaceable>index_method</replaceable>.
       The operators are required to be commutative.
       Each <replaceable class="parameter">exclude_element</replaceable>
-      can optionally specify an operator class and/or ordering options;
-      these are described fully under
+      can optionally specify any of the following: a collation, a
+      operator class, or ordering options; these are described fully under
       <xref linkend="sql-createindex"/>.
      </para>

Shubham Khanna

khannashubham1197@gmail.com

about 2 years ago

In reply to: shihao zhong (#5)

Re: EXCLUDE COLLATE in CREATE/ALTER TABLE document

On Fri, Nov 17, 2023 at 4:55 AM shihao zhong <zhong950419@gmail.com> wrote:

Hi Jian,

Thanks for your comments, a new version is attached.

Thanks,
Shihao

On Fri, Nov 10, 2023 at 9:59 AM jian he <jian.universality@gmail.com> wrote:

On Wed, Nov 1, 2023 at 10:30 AM shihao zhong <zhong950419@gmail.com> wrote:

Thank you for your feedback on my previous patch. I have fixed the issue and attached a new patch for your review. Could you please take a look for it if you have a sec? Thanks

Your patch works fine. you can see it here:
https://cirrus-ci.com/task/6481922939944960
in an ideal world, since the doc is already built, we can probably
view it as a plain html file just click the ci test result.

in src/sgml/ref/create_table.sgml:
"Each exclude_element can optionally specify an operator class and/or
ordering options; these are described fully under CREATE INDEX."

You may need to update this sentence to reflect that exclude_element
can also optionally specify collation.

I have reviewed the changes and it looks fine.

Thanks and Regards,
Shubham Khanna.

Daniel Verite

daniel@manitou-mail.org

about 2 years ago

In reply to: shihao zhong (#5)

1 attachment(s)

Re: EXCLUDE COLLATE in CREATE/ALTER TABLE document

shihao zhong wrote:

Thanks for your comments, a new version is attached.

In this hunk:

@@ -1097,8 +1097,8 @@ WITH ( MODULUS <replaceable
class="parameter">numeric_literal</replaceable>, REM
       method <replaceable>index_method</replaceable>.
       The operators are required to be commutative.
       Each <replaceable class="parameter">exclude_element</replaceable>
-      can optionally specify an operator class and/or ordering options;
-      these are described fully under
+      can optionally specify any of the following: a collation, a
+      operator class, or ordering options; these are described fully under
       <xref linkend="sql-createindex"/>.
      </para>

"a" should be "an" as it's followed by "operator class".

Also the use of "and/or" in the previous version conveys the fact
that operator class and ordering options are not mutually
exclusive. But when using "any of the following" in the new text,
doesn't it loose that meaning?

In case it does, I would suggest the attached diff.

Best regards,
--
Daniel Vérité
https://postgresql.verite.pro/
Twitter: @DanielVerite

Attachments:

update_document_exclude_with_collate_v3-bis.patchtext/plainDownload

diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 2c4138e4e9..43f5f0873e 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -135,7 +135,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
 
 <phrase><replaceable class="parameter">exclude_element</replaceable> in an <literal>EXCLUDE</literal> constraint is:</phrase>
 
-{ <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ <replaceable class="parameter">opclass</replaceable> ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]
+{ <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ COLLATE <replaceable class="parameter">collation</replaceable> ] [ <replaceable class="parameter">opclass</replaceable> ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]
 
 <phrase><replaceable class="parameter">referential_action</replaceable> in a <literal>FOREIGN KEY</literal>/<literal>REFERENCES</literal> constraint is:</phrase>
 
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index e04a0692c4..277d292aac 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -105,7 +105,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
 
 <phrase><replaceable class="parameter">exclude_element</replaceable> in an <literal>EXCLUDE</literal> constraint is:</phrase>
 
-{ <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ <replaceable class="parameter">opclass</replaceable> ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]
+{ <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ COLLATE <replaceable class="parameter">collation</replaceable> ] [ <replaceable class="parameter">opclass</replaceable> ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]
 
 <phrase><replaceable class="parameter">referential_action</replaceable> in a <literal>FOREIGN KEY</literal>/<literal>REFERENCES</literal> constraint is:</phrase>
 
@@ -1097,9 +1097,8 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
       method <replaceable>index_method</replaceable>.
       The operators are required to be commutative.
       Each <replaceable class="parameter">exclude_element</replaceable>
-      can optionally specify an operator class and/or ordering options;
-      these are described fully under
-      <xref linkend="sql-createindex"/>.
+      can optionally specify a collation, an operator class, and ordering options;
+      these are described fully under <xref linkend="sql-createindex"/>.
      </para>
 
      <para>

Tom Lane

tgl@sss.pgh.pa.us

almost 2 years ago

In reply to: Daniel Verite (#7)

Re: EXCLUDE COLLATE in CREATE/ALTER TABLE document

"Daniel Verite" <daniel@manitou-mail.org> writes:

Also the use of "and/or" in the previous version conveys the fact
that operator class and ordering options are not mutually
exclusive. But when using "any of the following" in the new text,
doesn't it loose that meaning?

Yeah; and/or is perfectly fine here and doesn't need to be improved
on.

There's a bigger problem though, which is that these bits
are *also* missing any reference to opclass parameters.
I fixed that and pushed it.

regards, tom lane