Failure to create composite indices on distributed tables that are partitioned
- Dominant language
- C
- Stars
- 12.8k
- Forks
- 794
- Avg merge
- 2d 14h
- Merged PRs (30d)
- 31
Description
# Issue
Creating composite indices on a distributed table fails when then target table is partitioned.
**Postgres version**:
```
PostgreSQL 11.10 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.12) 5.4.0 20160609, 64-bit
```
**Platform**:
```
Azure Database for PostgreSQL - Hyperscale
```
**Citus version**:
```
Citus Enterprise 9.4.2 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.12) 5.4.0 20160609, 64-bit gitref: v9.4.2
```
## Steps to reproduce
We have a reference table as follows:
```sql
CREATE TABLE IF NOT EXISTS metadata
(
id SERIAL NOT NULL PRIMARY KEY,
last_update TIMESTAMP WITH TIME ZONE NOT NULL,
metadata TEXT DEFAULT ''
);
SELECT create_reference_table('metadata');
```
and a distributed table that is also partitioned, as follows:
```sql
CREATE TABLE IF NOT EXISTS sample_dist
(
distribution_id VARCHAR(26) NOT NULL,
partition_id VARCHAR(26) NOT NULL,
metadata_id INT NOT NULL,
date TIMESTAMP WITH TIME ZONE,
payload JSONB DEFAULT '{"value": null}',
CONSTRAINT fk_sample_dist_metadata
FOREIGN KEY ( metadata_id )
REFERENCES metadata ( id )
ON UPDATE CASCADE
ON DELETE CASCADE,
PRIMARY KEY (metadata_id, distribution_id, partition_id)
)
PARTITION BY LIST ( partition_id );
SELECT create_distributed_table('sample_dist', 'distribution_id');
```
We then create the first partition in our table:
```sql
CREATE TABLE IF NOT EXISTS sample_dist_p_first_partition
PARTITION OF sample_dist ( partition_id )
FOR VALUES IN ('partition_a', 'partition_b');
```
Now, if we attempt to create a simple index as follows, the operation will succeed:
```sql
CREATE INDEX IF NOT EXISTS idx_sample_dist_payload
ON sample_dist USING GIN (payload jsonb_path_ops);
```
However, if we attempt to implement a composite index, which would naturally use more than one column, the operation will fail regardless of which columns are used or what the index type is:
For instance, the following operation will fail:
```sql
CREATE INDEX IF NOT EXISTS idx_sample_dist
ON sample_dist
USING BTREE (partition_id, date, metadata_id);
```
The failure yields the following error message:
```
[57014] ERROR: canceling statement due to user request
```
## Highlights
It may be noteworthy that the operation *would succeed* if the composite index is created *before* the first partition is created.
---
Hope this helps find and resolve the issue.
Thank you for the excellent product.
Contributor guide
Assessment
This issue has not been assessed yet.