pgsql: doc: add examples of creative use of unique expression indexes

Bruce Momjian <bruce@momjian.us> writes:

doc: add examples of creative use of unique expression indexes
https://git.postgresql.org/pg/commitdiff/a9760d0f3cb523336b5fdd9d6c5985e39a8588a1

We had a complaint [1]/messages/by-id/158648685043.655.3074746555320970574@wrigleys.postgresql.org that this dropped an example into the middle of
two related paragraphs. I agree with that objection, and also notice
that the extra example broke subsequent references to the "first example"
and "second example". I'm also unhappy that the other addition that this
commit made was dropped inside Example 11.3; if we're going to use
<example> markup at all, each one ought to be a coherent entity.

On top of that, I don't find that either example actually adds anything
to the discussion, as the same points are being made in the existing
text. Therefore, I don't think it's worth trying to fix these problems,
and propose just reverting this patch.

regards, tom lane

[1]: /messages/by-id/158648685043.655.3074746555320970574@wrigleys.postgresql.org

On Fri, Apr 10, 2020 at 11:30:34AM -0400, Tom Lane wrote:

Bruce Momjian <bruce@momjian.us> writes:

doc: add examples of creative use of unique expression indexes
https://git.postgresql.org/pg/commitdiff/a9760d0f3cb523336b5fdd9d6c5985e39a8588a1

We had a complaint [1] that this dropped an example into the middle of
two related paragraphs. I agree with that objection, and also notice
that the extra example broke subsequent references to the "first example"
and "second example". I'm also unhappy that the other addition that this
commit made was dropped inside Example 11.3; if we're going to use
<example> markup at all, each one ought to be a coherent entity.

On top of that, I don't find that either example actually adds anything
to the discussion, as the same points are being made in the existing
text. Therefore, I don't think it's worth trying to fix these problems,
and propose just reverting this patch.

regards, tom lane

[1] /messages/by-id/158648685043.655.3074746555320970574@wrigleys.postgresql.org

I agree with your analysis. I still want to have some mention that
partial indexes can be used to create single-NULL columns, which might
be required for compatibility with other databases. Attached is an
updated patch which removes the previous commit but adds a mention of
this.

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

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

Bruce Momjian <bruce@momjian.us> writes:

I agree with your analysis. I still want to have some mention that
partial indexes can be used to create single-NULL columns, which might
be required for compatibility with other databases. Attached is an
updated patch which removes the previous commit but adds a mention of
this.

The single-null thing is probably a useful example, but please make
it an actual separate example, or at least its own para outside the
existing <example> sections.

Also, the existing example demonstrating that seems overcomplicated;
why not just

create unique index ... (1) where (foo is null);

regards, tom lane

On Fri, Apr 10, 2020 at 07:21:29PM -0400, Tom Lane wrote:

Bruce Momjian <bruce@momjian.us> writes:

I agree with your analysis. I still want to have some mention that
partial indexes can be used to create single-NULL columns, which might
be required for compatibility with other databases. Attached is an
updated patch which removes the previous commit but adds a mention of
this.

The single-null thing is probably a useful example, but please make
it an actual separate example, or at least its own para outside the
existing <example> sections.

Also, the existing example demonstrating that seems overcomplicated;
why not just

create unique index ... (1) where (foo is null);

I ended up using "true" since that is ony one byte; patch attached.

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

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

On Fri, Apr 10, 2020 at 08:17:09PM -0400, Bruce Momjian wrote:

On Fri, Apr 10, 2020 at 07:21:29PM -0400, Tom Lane wrote:

Bruce Momjian <bruce@momjian.us> writes:

I agree with your analysis. I still want to have some mention that
partial indexes can be used to create single-NULL columns, which might
be required for compatibility with other databases. Attached is an
updated patch which removes the previous commit but adds a mention of
this.

The single-null thing is probably a useful example, but please make
it an actual separate example, or at least its own para outside the
existing <example> sections.

Also, the existing example demonstrating that seems overcomplicated;
why not just

create unique index ... (1) where (foo is null);

I ended up using "true" since that is ony one byte; patch attached.

[ thread moved to docs]

I now remember that I wrote the first IS NULL in:

CREATE UNIQUE INDEX tests_target_one_null ON tests ((target IS NULL)) WHERE target IS NULL;

in hope that if someone is looking for the null value in the column, the
IS NULL would allow the index to be used to find it, while 1 or true
would not.

Also, I think the most popular use for this ability would be for
multi-column indexes where you want only one NULL value for a
combination of columns, e.g.:

CREATE UNIQUE INDEX tests_target_one_null ON test (x, (y IS NULL)) WHERE y IS NULL;

I have added that. It also hows the use of columns and expressions in
the same index. Proposed patch attached.

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

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

[ sorry, I'd lost track of this thread ]

Bruce Momjian <bruce@momjian.us> writes:

I now remember that I wrote the first IS NULL in:
CREATE UNIQUE INDEX tests_target_one_null ON tests ((target IS NULL)) WHERE target IS NULL;
in hope that if someone is looking for the null value in the column, the
IS NULL would allow the index to be used to find it, while 1 or true
would not.

Well, that's not the case:

regression=# create index tenk1_null_index on tenk1((1)) where ten is null;
CREATE INDEX
regression=# explain select * from tenk1 where ten is null;
QUERY PLAN
--------------------------------------------------------------------------------
Index Scan using tenk1_null_index on tenk1 (cost=0.12..8.14 rows=1 width=244)
(1 row)

(Maybe it was true at some time in the past, but not any more.)

Also, it complicates the example, and since you didn't explain the
reason for the complication, I think it's pretty confusing.

But really I still don't see the need for these additional examples
at all. It's especially weird that what you want to do is have
some examples on that page have <example> markup and others not.

regards, tom lane

On Mon, Apr 20, 2020 at 04:21:32PM -0400, Tom Lane wrote:

[ sorry, I'd lost track of this thread ]

Bruce Momjian <bruce@momjian.us> writes:

I now remember that I wrote the first IS NULL in:
CREATE UNIQUE INDEX tests_target_one_null ON tests ((target IS NULL)) WHERE target IS NULL;
in hope that if someone is looking for the null value in the column, the
IS NULL would allow the index to be used to find it, while 1 or true
would not.

Well, that's not the case:

regression=# create index tenk1_null_index on tenk1((1)) where ten is null;
CREATE INDEX
regression=# explain select * from tenk1 where ten is null;
QUERY PLAN
--------------------------------------------------------------------------------
Index Scan using tenk1_null_index on tenk1 (cost=0.12..8.14 rows=1 width=244)
(1 row)

(Maybe it was true at some time in the past, but not any more.)

Also, it complicates the example, and since you didn't explain the
reason for the complication, I think it's pretty confusing.

But really I still don't see the need for these additional examples
at all. It's especially weird that what you want to do is have
some examples on that page have <example> markup and others not.

OK, seems like only you and I care about this issue, which I take to
mean that we should minimize what we are adding here. What the attached
patch does is to remove the previous commit, and just add a sentence to
the last example to mention the ability restrict a column to a single
NULL.

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

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

On Mon, Apr 20, 2020 at 08:31:29PM -0400, Bruce Momjian wrote:

On Mon, Apr 20, 2020 at 04:21:32PM -0400, Tom Lane wrote:

[ sorry, I'd lost track of this thread ]

Bruce Momjian <bruce@momjian.us> writes:

I now remember that I wrote the first IS NULL in:
CREATE UNIQUE INDEX tests_target_one_null ON tests ((target IS NULL)) WHERE target IS NULL;
in hope that if someone is looking for the null value in the column, the
IS NULL would allow the index to be used to find it, while 1 or true
would not.

Well, that's not the case:

regression=# create index tenk1_null_index on tenk1((1)) where ten is null;
CREATE INDEX
regression=# explain select * from tenk1 where ten is null;
QUERY PLAN
--------------------------------------------------------------------------------
Index Scan using tenk1_null_index on tenk1 (cost=0.12..8.14 rows=1 width=244)
(1 row)

(Maybe it was true at some time in the past, but not any more.)

Also, it complicates the example, and since you didn't explain the
reason for the complication, I think it's pretty confusing.

But really I still don't see the need for these additional examples
at all. It's especially weird that what you want to do is have
some examples on that page have <example> markup and others not.

OK, seems like only you and I care about this issue, which I take to
mean that we should minimize what we are adding here. What the attached
patch does is to remove the previous commit, and just add a sentence to
the last example to mention the ability restrict a column to a single
NULL.

Patch applied.

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

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +