Re: Proposal: Local indexes for partitioned table

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