Subject: Re: Proposal: Local indexes for partitioned table
To: Alvaro Herrera <alvherre@alvh.no-ip.org>,
 Pg Hackers <pgsql-hackers@postgresql.org>
Cc: Maksim Milyutin <m.milyutin@postgrespro.ru>
References: <20171006163715.oulhmymknz3m5mqm@alvherre.pgsql>
From: Maksim Milyutin <milyutinma@gmail.com>
Message-ID: <4df81bc5-538e-bd4e-1e9f-5ef249aff02c@gmail.com>
Date: Fri, 6 Oct 2017 21:18:14 +0300
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101
 Thunderbird/52.3.0
MIME-Version: 1.0
In-Reply-To: <20171006163715.oulhmymknz3m5mqm@alvherre.pgsql>
Content-Type: multipart/alternative;
 boundary="------------2CF9301CE3BE5EF80909515A"
Content-Language: en-US
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

This is a multi-part message in MIME format.
--------------2CF9301CE3BE5EF80909515A
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit

Hi!


On 06.10.2017 19:37, Alvaro Herrera wrote:
> As you propose, IMO this new feature would use the standard index
> creation syntax:
>     CREATE [UNIQUE] INDEX someindex ON parted_table (a, b);
>
> This command currently throws an error.  We'd have it do two things:
>
> 1. create catalog rows in pg_class and pg_index for the main table,
>     indicating the existance of this partitioned index.  These would not
>     point to an actual index (since the main table itself is empty), but
>     instead to an "abstract" index.  This abstract index can be used by
>     various operations; see below.

Robert Haas proposed[1] to use the name "partitioned index" (instead of 
abstract) that have to be reflected in 'relkind' field of pg_class as 
the RELKIND_PARTITIONED_INDEX value.

> I propose that an index declared UNIQUE throws an error for now.  We can
> implement uniqueness (for the case where the indexed columns match the
> partitioning key) later, once we sort out all the issues here first.  I
> think unique indexes would be very useful even with that limitation, but
> let's have it as a separate project.

Yes, global uniqueness through local unique indexes causes further work 
related with foreign keys on partitioned table, full support of INSERT 
OF CONFLICT, etc. It make sense to implement after the current stage of 
work.

> I think using pg_depend as the mechanism to link partition indexes to
> parent is a bad idea.  How about pg_inherits instead?  Seems more
> appropriate.

Greg Stark proposed[2] to use new pg_index field of oid type that refers 
to the parent pg_index item. AFAIC pg_inherits also makes sense but 
semantically it deals with inheriting tables. IMHO the using of this 
catalog table to define relation between partitioned table and 
partitions looks like a hack to make use of constraint exclusion logic 
for partition pruning.

> Creating hierachy-wide indexes for existing partitioned tables is likely
> to take a long time, so we must include CONCURRENTLY as an option.  This
> will need some transaction machinery support in order to ensure that
> each partition gets its index created at some point in a long chain of
> transactions, and that the whole thing is marked valid only at the end.
> Also, if the creation is interrupted (because of a crash or a regular
> shutdown), it'll be useful to be able to continue rather than being
> forced to start from scratch.

This option was very difficult for me. I would be interested to see the 
implementation.

> During ALTER TABLE ... ATTACH PARTITION, we check that an indexing
> satisfying the abstract index exist (and we create a pg_inherit link).
> If not, the command is aborted.

We could create necessary index for partition, not abort ALTER TABLE ... 
ATTACH PARTITION statement.

> We need to come up with some way to generate names for each partition
> index.

I think the calling 'ChooseIndexName(RelationGetRelationName(childrel), 
namespaceId, indexColNames, ...)' resolves this problem.

> I am going to work on this now.

It will be great! I think this project is difficult for me on the part 
of integration described above functionality with the legacy postgres 
code. Also IMO this project is very important because it opens the way 
for such feature as global uniqueness of fields of partitioned tables. 
And any protraction in implementation is bad.

I would like to review and test your intermediate results.


1. 
https://www.postgresql.org/message-id/CA%2BTgmoY5UOUnW%3DMcwT7xUB_2W5dAkvOg5kD20Spx5gF-Ad47cA%40mail.gmail.com
2. 
https://www.postgresql.org/message-id/CAM-w4HOVftuv5RVi3a%2BsRV6nBpg204w7%3DL8MwPXVvYBFo1uM1Q%40mail.gmail.com

-- 
Regards,
Maksim Milyutin


--------------2CF9301CE3BE5EF80909515A
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 7bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
  </head>
  <body text="#000000" bgcolor="#FFFFFF">
    <p>Hi!<br>
    </p>
    <br>
    <div class="moz-cite-prefix">On 06.10.2017 19:37, Alvaro Herrera
      wrote:
    </div>
    <blockquote type="cite"
      cite="mid:20171006163715.oulhmymknz3m5mqm@alvherre.pgsql">
      <pre wrap="">As you propose, IMO this new feature would use the standard index
creation syntax:
   CREATE [UNIQUE] INDEX someindex ON parted_table (a, b);

This command currently throws an error.  We'd have it do two things:

1. create catalog rows in pg_class and pg_index for the main table,
   indicating the existance of this partitioned index.  These would not
   point to an actual index (since the main table itself is empty), but
   instead to an "abstract" index.  This abstract index can be used by
   various operations; see below.</pre>
    </blockquote>
    <br>
    Robert Haas proposed[1] to use the name "partitioned index" (instead
    of abstract) that have to be reflected in 'relkind' field of
    pg_class as the RELKIND_PARTITIONED_INDEX value.<br>
    <br>
    <blockquote type="cite"
      cite="mid:20171006163715.oulhmymknz3m5mqm@alvherre.pgsql">
      <pre wrap="">I propose that an index declared UNIQUE throws an error for now.  We can
implement uniqueness (for the case where the indexed columns match the
partitioning key) later, once we sort out all the issues here first.  I
think unique indexes would be very useful even with that limitation, but
let's have it as a separate project.</pre>
    </blockquote>
    <br>
    Yes, global uniqueness through local unique indexes causes further
    work related with foreign keys on partitioned table, full support of
    INSERT OF CONFLICT, etc. It make sense to implement after the
    current stage of work.<br>
    <br>
    <blockquote type="cite"
      cite="mid:20171006163715.oulhmymknz3m5mqm@alvherre.pgsql">
      <pre wrap="">I think using pg_depend as the mechanism to link partition indexes to
parent is a bad idea.  How about pg_inherits instead?  Seems more
appropriate.
</pre>
    </blockquote>
    <br>
    Greg Stark proposed[2] to use new pg_index field of oid type that
    refers to the parent pg_index item. AFAIC pg_inherits also makes
    sense but semantically it deals with inheriting tables. IMHO the
    using of this catalog table to define relation between partitioned
    table and partitions looks like a hack to make use of constraint
    exclusion logic for partition pruning.<br>
    <br>
    <blockquote type="cite"
      cite="mid:20171006163715.oulhmymknz3m5mqm@alvherre.pgsql">
      <pre wrap="">Creating hierachy-wide indexes for existing partitioned tables is likely
to take a long time, so we must include CONCURRENTLY as an option.  This
will need some transaction machinery support in order to ensure that
each partition gets its index created at some point in a long chain of
transactions, and that the whole thing is marked valid only at the end.
Also, if the creation is interrupted (because of a crash or a regular
shutdown), it'll be useful to be able to continue rather than being
forced to start from scratch.</pre>
    </blockquote>
    <br>
    This option was very difficult for me. <span id="result_box"
      class="short_text" lang="en"><span class="">I would be interested
        to see the implementation.</span></span><br>
    <br>
    <blockquote type="cite"
      cite="mid:20171006163715.oulhmymknz3m5mqm@alvherre.pgsql">
      <pre wrap="">During ALTER TABLE ... ATTACH PARTITION, we check that an indexing
satisfying the abstract index exist (and we create a pg_inherit link).
If not, the command is aborted.</pre>
    </blockquote>
    <br>
    We could create necessary index for partition, not abort ALTER TABLE
    ... ATTACH PARTITION statement.<br>
    <br>
    <blockquote type="cite"
      cite="mid:20171006163715.oulhmymknz3m5mqm@alvherre.pgsql">
      <pre wrap="">We need to come up with some way to generate names for each partition
index.</pre>
    </blockquote>
    <br>
    I think the calling
    'ChooseIndexName(RelationGetRelationName(childrel), namespaceId,
    indexColNames, ...)' resolves this problem.<br>
    <br>
    <blockquote type="cite"
      cite="mid:20171006163715.oulhmymknz3m5mqm@alvherre.pgsql">
      <pre wrap="">I am going to work on this now.</pre>
    </blockquote>
    <br>
    It will be great! I think this project is difficult for me on the
    part of integration described above functionality with the legacy
    postgres code. Also IMO this project is very important because it
    opens the way for such feature as global uniqueness of fields of
    partitioned tables. And any protraction in implementation is bad.<br>
    <br>
    I would like to review and test your intermediate results.<br>
    <br>
    <br>
    1.
<a class="moz-txt-link-freetext" href="https://www.postgresql.org/message-id/CA%2BTgmoY5UOUnW%3DMcwT7xUB_2W5dAkvOg5kD20Spx5gF-Ad47cA%40mail.gmail.com">https://www.postgresql.org/message-id/CA%2BTgmoY5UOUnW%3DMcwT7xUB_2W5dAkvOg5kD20Spx5gF-Ad47cA%40mail.gmail.com</a><br>
    2.
<a class="moz-txt-link-freetext" href="https://www.postgresql.org/message-id/CAM-w4HOVftuv5RVi3a%2BsRV6nBpg204w7%3DL8MwPXVvYBFo1uM1Q%40mail.gmail.com">https://www.postgresql.org/message-id/CAM-w4HOVftuv5RVi3a%2BsRV6nBpg204w7%3DL8MwPXVvYBFo1uM1Q%40mail.gmail.com</a><br>
    <pre class="moz-signature" cols="72">-- 
Regards,
Maksim Milyutin</pre>
  </body>
</html>

--------------2CF9301CE3BE5EF80909515A--