citusdata / citusdata/citus

Failure to create composite indices on distributed tables that are partitioned

Open
#4,845 5 comments 0 reactions 0 assignees View on GitHub
bug
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.