cockroachdb / cockroachdb/movr

Schema of vehicle_location_histories is not suitable for archival partitioning

Open
#75 13 comments 0 reactions 0 assignees View on GitHub
Dominant language
Python
Stars
37
Forks
14
PR merge metrics
No merged PRs in 30d

Description

I've been trying out archival partitioning, and when I try to partition the `vehicle_location_histories` table based on `timestamp`, I get this error:

```
root@10.142.0.78:26257/movr> ALTER TABLE vehicle_location_histories PARTITION BY RANGE (timestamp) (PARTITION archived_locations VALUES FROM (MINVALUE) TO ('2019-03-16 02:12:50.759879+00:00'), PARTITION archived_locations VALUES FROM ('2019-03-16 02:12:50.759879+00:00') TO (MAXVALUE));
pq: declared partition columns (timestamp) do not match first 1 columns in index being partitioned (city)
```

Looking at the table schema, the primary key includes `city`, then `ride_id`, then `timestamp`:

```
root@10.142.0.78:26257/movr> show create table vehicle_location_histories;
table_name | create_statement
+----------------------------+-------------------------------------------------------------------------------------------+
vehicle_location_histories | CREATE TABLE vehicle_location_histories (
| city VARCHAR NOT NULL,
| ride_id UUID NOT NULL,
| "timestamp" TIMESTAMP NOT NULL,
| lat FLOAT8 NULL,
| long FLOAT8 NULL,
| CONSTRAINT "primary" PRIMARY KEY (city ASC, ride_id ASC, "timestamp" ASC),
| CONSTRAINT fk_city_ref_rides FOREIGN KEY (city, ride_id) REFERENCES rides (city, id),
| FAMILY "primary" (city, ride_id, "timestamp", lat, long)
| )
(1 row)
```

I don't understand why `city` and `ride_id` need to be in the primary key. If it were just `timestamp`, I think this would work fine. @nstewart, let me know what you were thinking.

Contributor guide

No contributing guide indexed for this repository

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.