Timescaledb: PRIMARY KEYs and UNIQUE constraints not handled correctly by ALTER TABLE

[andrew-blake]

I'm observing an error when trying to create a new hypertable from an existing table - following the instructions at http://docs.timescale.com/getting-started/setup/migrate-from-postgresql#same-db

CREATE TABLE my_new_table (LIKE my_old_table INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES);

SELECT create_hypertable('my_new_table', 'my_timestamptz');

ERROR:  Cannot create a unique index without the column: my_timestamptz (used in partitioning)
CONTEXT:  PL/pgSQL function _timescaledb_internal.get_general_index_definition(regclass,regclass,_timescaledb_catalog.hypertable) line 33 at RAISE
SQL statement "SELECT 1
    FROM pg_index,
    LATERAL _timescaledb_internal.add_index(
        hypertable_row.id,
        hypertable_row.schema_name,
        (SELECT relname FROM pg_class WHERE oid = indexrelid::regclass),
        _timescaledb_internal.get_general_index_definition(indexrelid, indrelid, hypertable_row)
    )
    WHERE indrelid = main_table"
PL/pgSQL function public.create_hypertable(regclass,name,name,integer,name,name,bigint,boolean,boolean) line 87 at PERFORM

However the hypertable creation succeeds if I omit the constraints and indexes in the create table like statement:

CREATE TABLE my_new_table (LIKE my_old_table INCLUDING DEFAULTS EXCLUDING CONSTRAINTS EXCLUDING INDEXES);

Is this expected behaviour ? The docs suggest it is possible to convert an empty table with constraints and indexes.

[cevian]

Hi, So this error is a result of the fact that we currently do not support unique indexes (or primary keys) that do not include partitioning columns (in this case my_timestamptz). So what's happening is that my_old_table has either a primary key or unique index without my_timestamptz as one of the columns. This index is copied over because of INCLUDING INDEXES and then the hypertable cannot be created. If you provide the output of \d+ my_old_table I can tell you which index is the offending one.

[andrew-blake]

Removing my primary key before creating the hypertable worked.

Indexes:
    "my_old_table_pkey" PRIMARY KEY, btree (id)
    "my_old_table_my_timestamptz" btree ("timestamp")

Our system uses Django and the ID is generated using a sequence. I think my issues is related to https://github.com/timescale/timescaledb/issues/79.

I can understand why you state you don't support non time based primary keys as Postgres doesn't support global unique constraints across partitions. This limitation is something we currently observe and accept using pg_partman.

However, I don't understand why can I successfully add the primary key to my_new_table after creating the hypertable with no errors, even though the primary key doesn't propagate to the _hyper_chunk partition tables. That is inconsistent.

I would therefore recommend not raising an error when creating the partition with a primary key, but instead warn / document that the PK will not propagate to the _hyper_chunk partition tables.

The end result would be the same, and it would be a more convenient a development experience.

Btw, I'm really impressed with the performance so far!

[cevian]

Yeah you should not be able to add a primary key after hypertable creation. That's a bug. I'll look at it.

[rtconner]

I didn't see any documentation on this.
Am I allowed to add a unique constraint after the table is created?
ALTER TABLE my_table ADD CONSTRAINT my_id_constraint_unique UNIQUE (id);

[mfreed]

Hi @rtconner sorry for the delay.

We have a few PRs in flight that will be released as part of 0.5 (which should go out in the next 2-3 weeks) that will add support for UNIQUE constraints like this.

That said, we'll only support UNIQUE constraints that are defined on the time/space partitioning keys -- we don't maintain global UNIQUEness over non-partitioning columns.

So if your table is:

CREATE TABLE example (
    time timestamp;
    id int;
    name TEXT;
);

And you create the hypertable via create_hypertable('example', 'time');, then you'll be able to use either UNIQUE(time) or UNIQUE(time, id), but not UNIQUE(id).

[mfreed]

Fixed in https://github.com/timescale/timescaledb/releases/tag/0.5.0 release. See #198.

[markande]

I installed timeScaledb on Ubuntu (https://docs.timescale.com/v0.9/getting-started/installation/linux/installation-apt-ubuntu) today & I still see this issue. I am assuming that apt would have downloaded the latest version of timescaledb.
I removed all indexes & primary key from the table and the create_hypertable() function was successful. However while creating the primary key, post the hypertable creation it errors out. (ERROR: cannot create a unique index without the column "updated_at" (used in partitioning))

[ayZagen]

This issue stilll occurs. cannot migrate from postgresql !

[soccerdroid]

I am using Django 1.8. I am trying to alter a postgres table created from a Django model, but when I try to create the hypertable, I get the same error: cannot create a unique index without the column "datetime" (used in partitioning).
The datetime field is a Django.models.DateTimeField, and it is not a unique or primary key, as Django works with is generated sequential id column. How can I solve this?

[mathiasose]

Django models come out of the box with a field id = models.AutoField(primary_key=True). You can add primary_key=True to a different field in the model definition to override this behavior. So for TimescaleDB you'd want to make a DateTimeField the primary key.

It's very simple to create a new Django model with whatever PK you want, but a bit complicated to change the PK of an existing model. My suggested solution is backing up the data from that table, removing the existing model/table with a Django migration and creating a new model with the new PK and importing the data from the backup. Make sure you don't lose any related data when you remove the table because of foreign keys and cascading deletes.

(this comment is partially conjecture. I have had to migrate a Django model from integer PKs to UUID PKs before, but never from integer PKs to datetime PKs. I assume it will work just the same, but I haven't tried it)