public inbox for [email protected]help / color / mirror / Atom feed
Documentation for create unique index is insuficient and (because of that) incorrect 11+ messages / 5 participants [nested] [flat]
* Documentation for create unique index is insuficient and (because of that) incorrect @ 2018-10-23 18:29 PG Doc comments form <[email protected]> 0 siblings, 1 reply; 11+ messages in thread From: PG Doc comments form @ 2018-10-23 18:29 UTC (permalink / raw) To: [email protected]; +Cc: [email protected] The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/11/static/indexes-unique.html Description: In https://www.postgresql.org/docs/11/static/indexes-unique.html there are omited clausules For example WHERE clausule is omited. In https://www.postgresql.org/docs/11/static/sql-createindex.html you can see the right sintax with all the clausules. ^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Documentation for create unique index is insuficient and (because of that) incorrect @ 2018-11-20 02:05 Bruce Momjian <[email protected]> parent: PG Doc comments form <[email protected]> 0 siblings, 1 reply; 11+ messages in thread From: Bruce Momjian @ 2018-11-20 02:05 UTC (permalink / raw) To: [email protected]; [email protected] On Tue, Oct 23, 2018 at 06:29:55PM +0000, PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/11/static/indexes-unique.html > Description: > > In https://www.postgresql.org/docs/11/static/indexes-unique.html there are > omited clausules > > For example WHERE clausule is omited. > > In https://www.postgresql.org/docs/11/static/sql-createindex.html you can > see the right sintax with all the clausules. The first URL is an example and is not intended to be the complete syntax. -- Bruce Momjian <[email protected]> http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + ^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Documentation for create unique index is insuficient and (because of that) incorrect @ 2018-11-20 14:41 Jonathan S. Katz <[email protected]> parent: Bruce Momjian <[email protected]> 0 siblings, 2 replies; 11+ messages in thread From: Jonathan S. Katz @ 2018-11-20 14:41 UTC (permalink / raw) To: Bruce Momjian <[email protected]>; [email protected]; [email protected] On 11/19/18 9:05 PM, Bruce Momjian wrote: > On Tue, Oct 23, 2018 at 06:29:55PM +0000, PG Doc comments form wrote: >> The following documentation comment has been logged on the website: >> >> Page: https://www.postgresql.org/docs/11/static/indexes-unique.html >> Description: >> >> In https://www.postgresql.org/docs/11/static/indexes-unique.html there are >> omited clausules >> >> For example WHERE clausule is omited. >> >> In https://www.postgresql.org/docs/11/static/sql-createindex.html you can >> see the right sintax with all the clausules. > > The first URL is an example and is not intended to be the complete > syntax. > In fairness, the "INCLUDE" clause was added, which is new to PostgreSQL 11, so it could raise the question as to why aren't other clauses there. Perhaps we could add some more examples? For instance, why one might want to use "INCLUDE" with a unique index. And perhaps a case of the "partial unique index" (which I have used in production systems before). Thanks! Jonathan Attachments: [application/pgp-signature] signature.asc (833B, 2-signature.asc) download ^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Documentation for create unique index is insuficient and (because of that) incorrect @ 2018-11-20 14:52 Alvaro Herrera <[email protected]> parent: Jonathan S. Katz <[email protected]> 1 sibling, 0 replies; 11+ messages in thread From: Alvaro Herrera @ 2018-11-20 14:52 UTC (permalink / raw) To: Jonathan S. Katz <[email protected]>; +Cc: Bruce Momjian <[email protected]>; [email protected]; [email protected] On 2018-Nov-20, Jonathan S. Katz wrote: > On 11/19/18 9:05 PM, Bruce Momjian wrote: > > On Tue, Oct 23, 2018 at 06:29:55PM +0000, PG Doc comments form wrote: > >> The following documentation comment has been logged on the website: > >> > >> Page: https://www.postgresql.org/docs/11/static/indexes-unique.html > >> Description: > >> > >> In https://www.postgresql.org/docs/11/static/indexes-unique.html there are > >> omited clausules > >> > >> For example WHERE clausule is omited. > >> > >> In https://www.postgresql.org/docs/11/static/sql-createindex.html you can > >> see the right sintax with all the clausules. > > > > The first URL is an example and is not intended to be the complete > > syntax. I think the OP should take a lengthier look at the whole of chapter 11 instead of looking at just one individual section within it. It's the whole chapter that should include details about other indexing features -- and indeed the WHERE clause is described in 11.8 Partial Indexes. Describing every feature within the section for every other feature would be disastrous. > In fairness, the "INCLUDE" clause was added, which is new to PostgreSQL > 11, so it could raise the question as to why aren't other clauses there. IMO that's actually a bug -- the INCLUDE clause should have gotten its own section in chapter 11 instead of being mentioned in the section about unique indexes. -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services ^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Documentation for create unique index is insuficient and (because of that) incorrect @ 2018-11-20 14:59 Tom Lane <[email protected]> parent: Jonathan S. Katz <[email protected]> 1 sibling, 1 reply; 11+ messages in thread From: Tom Lane @ 2018-11-20 14:59 UTC (permalink / raw) To: Jonathan S. Katz <[email protected]>; +Cc: Bruce Momjian <[email protected]>; [email protected]; [email protected] "Jonathan S. Katz" <[email protected]> writes: > On 11/19/18 9:05 PM, Bruce Momjian wrote: >> On Tue, Oct 23, 2018 at 06:29:55PM +0000, PG Doc comments form wrote: >>> In https://www.postgresql.org/docs/11/static/indexes-unique.html there are >>> omited clausules >> The first URL is an example and is not intended to be the complete >> syntax. > In fairness, the "INCLUDE" clause was added, which is new to PostgreSQL > 11, so it could raise the question as to why aren't other clauses there. Yes. That was a dumb idea; the correct fix is to take that out, because it's not appropriate here. There might be room for an additional section later in the chapter that discusses INCLUDE, but we shouldn't be cluttering the discussion of fundamental concepts like unique indexes with that. regards, tom lane ^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Documentation for create unique index is insuficient and (because of that) incorrect @ 2018-11-20 15:02 Jonathan S. Katz <[email protected]> parent: Tom Lane <[email protected]> 0 siblings, 1 reply; 11+ messages in thread From: Jonathan S. Katz @ 2018-11-20 15:02 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: Bruce Momjian <[email protected]>; [email protected]; [email protected] On 11/20/18 9:59 AM, Tom Lane wrote: > "Jonathan S. Katz" <[email protected]> writes: >> On 11/19/18 9:05 PM, Bruce Momjian wrote: >>> On Tue, Oct 23, 2018 at 06:29:55PM +0000, PG Doc comments form wrote: >>>> In https://www.postgresql.org/docs/11/static/indexes-unique.html there are >>>> omited clausules > >>> The first URL is an example and is not intended to be the complete >>> syntax. > >> In fairness, the "INCLUDE" clause was added, which is new to PostgreSQL >> 11, so it could raise the question as to why aren't other clauses there. > > Yes. That was a dumb idea; the correct fix is to take that out, because > it's not appropriate here. There might be room for an additional section > later in the chapter that discusses INCLUDE, but we shouldn't be > cluttering the discussion of fundamental concepts like unique indexes > with that. Shows how closely I read the docs. +1 on removing INCLUDE from UNIQUE indexes. Also +1 on having a section on covering indexes. Jonathan Attachments: [application/pgp-signature] signature.asc (833B, 2-signature.asc) download ^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Documentation for create unique index is insuficient and (because of that) incorrect @ 2018-11-20 15:16 Tom Lane <[email protected]> parent: Jonathan S. Katz <[email protected]> 0 siblings, 1 reply; 11+ messages in thread From: Tom Lane @ 2018-11-20 15:16 UTC (permalink / raw) To: Jonathan S. Katz <[email protected]>; +Cc: Bruce Momjian <[email protected]>; [email protected]; [email protected]; Alvaro Herrera <[email protected]> "Jonathan S. Katz" <[email protected]> writes: > On 11/20/18 9:59 AM, Tom Lane wrote: >> Yes. That was a dumb idea; the correct fix is to take that out, because >> it's not appropriate here. There might be room for an additional section >> later in the chapter that discusses INCLUDE, but we shouldn't be >> cluttering the discussion of fundamental concepts like unique indexes >> with that. > Shows how closely I read the docs. +1 on removing INCLUDE from UNIQUE > indexes. > > Also +1 on having a section on covering indexes. I see Alvaro is on the same page here. I'll go write something later today. regards, tom lane ^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Documentation for create unique index is insuficient and (because of that) incorrect @ 2018-11-20 17:57 Tom Lane <[email protected]> parent: Tom Lane <[email protected]> 0 siblings, 1 reply; 11+ messages in thread From: Tom Lane @ 2018-11-20 17:57 UTC (permalink / raw) To: Jonathan S. Katz <[email protected]>; +Cc: Bruce Momjian <[email protected]>; [email protected]; [email protected]; Alvaro Herrera <[email protected]> I wrote: > "Jonathan S. Katz" <[email protected]> writes: >> Also +1 on having a section on covering indexes. > I see Alvaro is on the same page here. I'll go write something > later today. So looking closer ... not only is the unique-index section not a very good fit, but there is already a section that this does fit into pretty well, which is 11.11 on index-only scans. Not only do you need to understand index-only scans to understand why you'd want INCLUDE, but there's already discussion there of making covering indexes the hard way. So what I think I should do is reformulate that discussion to talk about making covering indexes with INCLUDE, and then mention in passing that you can also do it without that as long as you don't mind the payload columns being part of the index semantics. I'm also wondering whether to move that section someplace earlier in chapter 11. Right now it's near the end because it's mostly info about an implementation detail; but it wouldn't be hard to make the argument that covering indexes are more important than, say, indexes with custom collations. Should we move it, and if so to where? regards, tom lane ^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Documentation for create unique index is insuficient and (because of that) incorrect @ 2018-11-20 18:08 Alvaro Herrera <[email protected]> parent: Tom Lane <[email protected]> 0 siblings, 2 replies; 11+ messages in thread From: Alvaro Herrera @ 2018-11-20 18:08 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: Jonathan S. Katz <[email protected]>; Bruce Momjian <[email protected]>; [email protected]; [email protected] On 2018-Nov-20, Tom Lane wrote: > So what I think I should do is reformulate that discussion to talk > about making covering indexes with INCLUDE, and then mention in > passing that you can also do it without that as long as you don't mind > the payload columns being part of the index semantics. That seems sensible. > I'm also wondering whether to move that section someplace earlier > in chapter 11. Right now it's near the end because it's mostly > info about an implementation detail; but it wouldn't be hard to > make the argument that covering indexes are more important than, > say, indexes with custom collations. Should we move it, and if > so to where? I think right next to 11.5, which currently completes the topic of how are indexes used, is a good place. -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services ^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Documentation for create unique index is insuficient and (because of that) incorrect @ 2018-11-20 18:13 Jonathan S. Katz <[email protected]> parent: Alvaro Herrera <[email protected]> 1 sibling, 0 replies; 11+ messages in thread From: Jonathan S. Katz @ 2018-11-20 18:13 UTC (permalink / raw) To: Alvaro Herrera <[email protected]>; Tom Lane <[email protected]>; +Cc: Bruce Momjian <[email protected]>; [email protected]; [email protected] On 11/20/18 1:08 PM, Alvaro Herrera wrote: > On 2018-Nov-20, Tom Lane wrote: > >> So what I think I should do is reformulate that discussion to talk >> about making covering indexes with INCLUDE, and then mention in >> passing that you can also do it without that as long as you don't mind >> the payload columns being part of the index semantics. > > That seems sensible. +1 > >> I'm also wondering whether to move that section someplace earlier >> in chapter 11. Right now it's near the end because it's mostly >> info about an implementation detail; but it wouldn't be hard to >> make the argument that covering indexes are more important than, >> say, indexes with custom collations. Should we move it, and if >> so to where? > > I think right next to 11.5, which currently completes the topic of how > are indexes used, is a good place. I would vote at least before 11.9. You could make arguments how understand how multicolumn, expression indexes, partials, etc. could be more important, or at least used for frequently in the wild (at least for now). And once we've explained those types then you could understand how to use covering indexes appropriately. Jonathan Attachments: [application/pgp-signature] signature.asc (833B, 2-signature.asc) download ^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Documentation for create unique index is insuficient and (because of that) incorrect @ 2018-11-20 18:15 Tom Lane <[email protected]> parent: Alvaro Herrera <[email protected]> 1 sibling, 0 replies; 11+ messages in thread From: Tom Lane @ 2018-11-20 18:15 UTC (permalink / raw) To: Alvaro Herrera <[email protected]>; +Cc: Jonathan S. Katz <[email protected]>; Bruce Momjian <[email protected]>; [email protected]; [email protected] Alvaro Herrera <[email protected]> writes: > On 2018-Nov-20, Tom Lane wrote: >> I'm also wondering whether to move that section someplace earlier >> in chapter 11. Right now it's near the end because it's mostly >> info about an implementation detail; but it wouldn't be hard to >> make the argument that covering indexes are more important than, >> say, indexes with custom collations. Should we move it, and if >> so to where? > I think right next to 11.5, which currently completes the topic of how > are indexes used, is a good place. Well, it has to stay after 11.6 (unique indexes), because we do need to point out that the included columns don't participate in the uniqueness constraint, and I don't want that statement to be a forward reference. Really this boils down to where you rank covering indexes vs expression indexes and partial indexes in terms of usefulness. I'd tend to put this after expression indexes, but I don't have a very strong feeling about how to rank it versus partial indexes. regards, tom lane ^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2018-11-20 18:15 UTC | newest] Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2018-10-23 18:29 Documentation for create unique index is insuficient and (because of that) incorrect PG Doc comments form <[email protected]> 2018-11-20 02:05 ` Bruce Momjian <[email protected]> 2018-11-20 14:41 ` Jonathan S. Katz <[email protected]> 2018-11-20 14:52 ` Alvaro Herrera <[email protected]> 2018-11-20 14:59 ` Tom Lane <[email protected]> 2018-11-20 15:02 ` Jonathan S. Katz <[email protected]> 2018-11-20 15:16 ` Tom Lane <[email protected]> 2018-11-20 17:57 ` Tom Lane <[email protected]> 2018-11-20 18:08 ` Alvaro Herrera <[email protected]> 2018-11-20 18:13 ` Jonathan S. Katz <[email protected]> 2018-11-20 18:15 ` Tom Lane <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox