Re: WIP: Covering + unique indexes. - Mailing list pgsql-hackers

02.03.2016 08:50, Michael Paquier:
> On Wed, Mar 2, 2016 at 2:10 AM, Anastasia Lubennikova
> <a.lubennikova@postgrespro.ru> wrote:
>> 01.03.2016 19:55, Anastasia Lubennikova:
>>> It is not the final version, because it breaks pg_dump for previous
>>> versions. I need some help from hackers here.
>>> pgdump. line 5466
>>> if (fout->remoteVersion >= 90400)
>>>
>>> What does 'remoteVersion' mean? And what is the right way to change it? Or
>>> it changes between releases?
>>> I guess that 90400 is for 9.4 and 80200 is for 8.2 but is it really so?
>>> That is totally new to me.
> Yes, you got it. That's basically PG_VERSION_NUM as compiled on the
> server that has been queried, in this case the server from which a
> dump is taken. If you are changing the system catalog layer, you would
> need to provide a query at least equivalent to what has been done
> until now for your patch, the modify pg_dump as follows:
> if (fout->remoteVersion >= 90600)
> {
>      query = my_new_query;
> }
> else if (fout->remoteVersion >= 90400)
> {
>      query = the existing 9.4 query
> }
> etc.
>
> In short you just need to add a new block so as remote servers newer
> than 9.6 will be able to dump objects correctly. pg_upgrade is a good
> way to check the validity of pg_dump actually, this explains why some
> objects are not dropped in the regression tests. Perhaps you'd want to
> do the same with your patch if the current test coverage of pg_dump is
> not enough. I have not looked at your patch so I cannot say for sure.

Thank you for the explanation.
New version of the patch implements pg_dump well.
Documentation related to constraints is updated.

I hope, that patch is in a good shape now. Brief overview for reviewers:

This patch allows unique indexes to be defined on one set of columns
and include another set of column in the INCLUDING clause, on which
the uniqueness is not enforced upon. It allows more queries to benefit
from using index-only scan. Currently, only the B-tree access method
supports this feature.

Syntax example:
CREATE TABLE tbl (c1 int, c2 int, c3 box);
CREATE INDEX idx ON TABLE tbl (c1) INCLUDING (c2, c3);

In opposite to key columns (c1),  included columns (c2,c3) are not used
in index scankeys neither in "search" scankeys nor in "insertion" scankeys.
Included columns are stored only in leaf pages and it can help to slightly
reduce index size. Hence, included columns do not require any opclass
for btree access method. As you can see from example above, it's possible
to add into index columns of "box" type.

The most common use-case for this feature is combination of UNIQUE or
PRIMARY KEY constraint on columns (a,b) and covering index on columns
(a,b,c).
So, there is a new syntax for constraints.

CREATE TABLE tblu (c1 int, c2 int, c3 box, UNIQUE (c1,c2) INCLUDING (c3));
Index, created for this constraint contains three columns.
"tblu_c1_c2_c3_key" UNIQUE CONSTRAINT, btree (c1, c2) INCLUDING (c3)

CREATE TABLE tblpk (c1 int, c2 int, c3 box, PRIMARY KEY (c1) INCLUDING
(c3));
Index, created for this constraint contains two columns. Note that NOT NULL
constraint is applied only to key column(s) as well as unique constraint.

postgres=# \d tblpk
      Table "public.tblpk"
  Column |  Type   | Modifiers
--------+---------+-----------
  c1     | integer | not null
  c2     | integer |
  c3     | box     |
Indexes:
     "tblpk_pkey" PRIMARY KEY, btree (c1) INCLUDING (c3)

Same for ALTER TABLE statements:
CREATE TABLE tblpka (c1 int, c2 int, c3 box);
ALTER TABLE tblpka ADD PRIMARY KEY (c1) INCLUDING (c3);

pg_dump is updated and seems to work fine with this kind of indexes.

I see only one problem left (maybe I've mentioned it before).
Queries like this [1] must be rewritten, because after catalog changes,
i.indkey contains both key and included attrs.
One more thing to do is some refactoring of names, since "indkey"
looks really confusing to me. But it could be done as a separate patch [2].


[1] https://wiki.postgresql.org/wiki/Retrieve_primary_key_columns
[2] http://www.postgresql.org/message-id/56BB7788.30808@postgrespro.ru

--
Anastasia Lubennikova
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company