Timescaledb: Cannot create a unique index without using the partitioning key

[pregenie]

Thank you in advance for helping on this. We have created a unique index on occurred_at, our timestamp column. We have been getting this message when trying to create the hypertable using the following command:

SELECT create_hypertable('event_data', 'occurred_at', 'device_id', 1);

ERROR:  Cannot create a unique index without the column: occurred_at (used in partitioning)
CONTEXT:  SQL statement "SELECT _timescaledb_internal.verify_hypertable_indexes(main_table)"
PL/pgSQL function _timescaledb_internal.create_hypertable(regclass,name,name,name,name,integer,name,name,bigint,name,boolean,regproc) line 63 at PERFORM
SQL statement "SELECT *
                                    FROM  _timescaledb_internal.create_hypertable(
            main_table,
            schema_name,

Here is the table structure:

-- PostgreSQL database dump
--

-- Dumped from database version 10.2 (Ubuntu 10.2-1.pgdg16.04+1)
-- Dumped by pg_dump version 10.2 (Ubuntu 10.2-1.pgdg16.04+1)

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;

SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: event_data; Type: TABLE; Schema: public; Owner: dba
--

CREATE TABLE event_data (
    occurred_at timestamp(6) without time zone NOT NULL,
    device_id integer NOT NULL,
    mac_address character varying(17) NOT NULL,
    application_id integer NOT NULL,
    user_id integer NOT NULL,
    event_id integer NOT NULL,
    seq_id bigint NOT NULL,
    parent_seq_id bigint,
    latitude double precision,
    longitude double precision,
    altitude double precision,
    created_on timestamp(6) without time zone,
    created_by integer,
    modified_on timestamp(6) without time zone,
    modified_by integer
);


ALTER TABLE event_data OWNER TO dba;

--
-- Data for Name: event_data; Type: TABLE DATA; Schema: public; Owner: dba
--

COPY event_data (occurred_at, device_id, mac_address, application_id, user_id, event_id, seq_id, parent_seq_id, latitude, longitu$
\.


--
-- Name: event_data event_data_new_pkey; Type: CONSTRAINT; Schema: public; Owner: dba
--

ALTER TABLE ONLY event_data
    ADD CONSTRAINT event_data_new_pkey PRIMARY KEY (occurred_at, device_id);


--
-- Name: event_data event_data_seq_id_unique; Type: CONSTRAINT; Schema: public; Owner: dba
--

ALTER TABLE ONLY event_data
    ADD CONSTRAINT event_data_seq_id_unique UNIQUE (seq_id);


--
-- Name: event_data_application_id_key; Type: INDEX; Schema: public; Owner: dba
--

CREATE INDEX event_data_application_id_key ON event_data USING btree (application_id);


--
-- Name: event_data_device_id_key; Type: INDEX; Schema: public; Owner: dba
--

CREATE INDEX event_data_device_id_key ON event_data USING btree (device_id);


--
-- Name: event_data_event_id_key; Type: INDEX; Schema: public; Owner: dba
--

CREATE INDEX event_data_event_id_key ON event_data USING btree (event_id);


--
-- Name: event_data_mac_address_key; Type: INDEX; Schema: public; Owner: dba
--

CREATE INDEX event_data_mac_address_key ON event_data USING btree (mac_address);


--
-- Name: event_data_occurred_at_key; Type: INDEX; Schema: public; Owner: dba
--

CREATE INDEX event_data_occurred_at_key ON event_data USING btree (occurred_at);


--
-- Name: event_data_seq_id_key; Type: INDEX; Schema: public; Owner: dba
--

CREATE INDEX event_data_seq_id_key ON event_data USING btree (seq_id);


--
-- Name: event_data_user_id_key; Type: INDEX; Schema: public; Owner: dba
--

CREATE INDEX event_data_user_id_key ON event_data USING btree (user_id);


--
-- Name: event_data event_data_application_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: dba
--

ALTER TABLE ONLY event_data
    ADD CONSTRAINT event_data_application_id_fkey FOREIGN KEY (application_id) REFERENCES application(id) ON DELETE CASCADE;


--
-- Name: event_data event_data_created_by_fkey; Type: FK CONSTRAINT; Schema: public; Owner: dba
--

ALTER TABLE ONLY event_data
    ADD CONSTRAINT event_data_created_by_fkey FOREIGN KEY (created_by) REFERENCES auth_user(id);


--
-- Name: event_data event_data_device_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: dba
--

ALTER TABLE ONLY event_data
    ADD CONSTRAINT event_data_device_id_fkey FOREIGN KEY (device_id) REFERENCES application_device(id) ON DELETE CASCADE;

TRIED THIS TO SEE IF COMBINED INDEXES HELPED THE PROBLEM

-- PostgreSQL database dump
--

-- Dumped from database version 10.2 (Ubuntu 10.2-1.pgdg16.04+1)
-- Dumped by pg_dump version 10.2 (Ubuntu 10.2-1.pgdg16.04+1)

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;

SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: event_data; Type: TABLE; Schema: public; Owner: dba
--

CREATE TABLE event_data (
    occurred_at timestamp(6) without time zone NOT NULL,
    device_id integer NOT NULL,
    mac_address character varying(17) NOT NULL,
    application_id integer NOT NULL,
    user_id integer NOT NULL,
    event_id integer NOT NULL,
    seq_id bigint NOT NULL,
    parent_seq_id bigint,
    latitude double precision,
    longitude double precision,
    altitude double precision,
    created_on timestamp(6) without time zone,
    created_by integer,
    modified_on timestamp(6) without time zone,
    modified_by integer
);


ALTER TABLE event_data OWNER TO dba;

--
-- Data for Name: event_data; Type: TABLE DATA; Schema: public; Owner: dba
--

COPY event_data (occurred_at, device_id, mac_address, application_id, user_id, event_id, seq_id, parent_seq_id, latitude, longitude,$
\.


--
-- Name: event_data event_data_new_pkey; Type: CONSTRAINT; Schema: public; Owner: dba
--

ALTER TABLE ONLY event_data
    ADD CONSTRAINT event_data_new_pkey PRIMARY KEY (occurred_at, device_id);


--
-- Name: event_data event_data_seq_id_unique; Type: CONSTRAINT; Schema: public; Owner: dba
--

ALTER TABLE ONLY event_data
    ADD CONSTRAINT event_data_seq_id_unique UNIQUE (seq_id);


--
-- Name: event_data_device_id_occurred_at_idx; Type: INDEX; Schema: public; Owner: dba
--

CREATE INDEX event_data_device_id_occurred_at_idx ON event_data USING btree (device_id, occurred_at DESC);


--
-- Name: event_data_occurred_at_application_id_idx; Type: INDEX; Schema: public; Owner: dba
--

CREATE INDEX event_data_occurred_at_application_id_idx ON event_data USING btree (occurred_at DESC, application_id);


--
-- Name: event_data_occurred_at_event_id_idx; Type: INDEX; Schema: public; Owner: dba
--

CREATE INDEX event_data_occurred_at_event_id_idx ON event_data USING btree (occurred_at DESC, event_id);


--
-- Name: event_data_occurred_at_mac_address_idx; Type: INDEX; Schema: public; Owner: dba
--

CREATE INDEX event_data_occurred_at_mac_address_idx ON event_data USING btree (occurred_at DESC, mac_address);


--
-- Name: event_data_occurred_at_parent_seq_id_idx; Type: INDEX; Schema: public; Owner: dba
--

CREATE INDEX event_data_occurred_at_parent_seq_id_idx ON event_data USING btree (occurred_at DESC, parent_seq_id);


--
-- Name: event_data_occurred_at_seq_id_idx; Type: INDEX; Schema: public; Owner: dba
--

CREATE INDEX event_data_occurred_at_seq_id_idx ON event_data USING btree (occurred_at DESC, seq_id);


--
-- Name: event_data event_data_application_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: dba
--

ALTER TABLE ONLY event_data
    ADD CONSTRAINT event_data_application_id_fkey FOREIGN KEY (application_id) REFERENCES application(id) ON DELETE CASCADE;


--
-- Name: event_data event_data_created_by_fkey; Type: FK CONSTRAINT; Schema: public; Owner: dba
--

PROBLEM TURNED OUT TO BE THE ADDITIONAL CONSTRAINT ON SEQ_ID

-- Name: event_data event_data_seq_id_unique; Type: CONSTRAINT; Schema: public; Owner: dba
--

ALTER TABLE ONLY event_data
    ADD CONSTRAINT event_data_seq_id_unique UNIQUE (seq_id);

[mfreed]

Hi @pregenie if the problem is occurring with your UNIQUE constraint on seq_id, that makes total sense: TimescaleDB requires that all UNIQUE constraints include their partitioning fields as their prefix. Technical explanation: We do not build global indexes/lookup data structures ACROSS all partitions, which would then need to be updated/checked on any write.

Instead, all of our indexes are local to be chunk. Therefore, by requiring that unique constraints use the partitioning keys as prefix, this insures that we only need to check the individual chunk (defined by the partitioning keys) when performing a check constraint.

See discussion: http://docs.timescale.com/v0.8/using-timescaledb/schema-management#indexing

That said, I'm not totally sure I understand why the error message mentions occurred_at, rather than something on event_data_seq_id_unique. That said, I don't see the create_hypertable() command in the log you dumped. Typically people execute right after you create the table and before any DDL changes (via ALTER). When are you calling it?

[mfreed]

Hi @pregenie, following up for more information?

[beeing]

I am having the same problem, here's my SQL

CREATE TABLE ttt (
    id uuid DEFAULT uuid_generate_v4() PRIMARY KEY,
    tdate date DEFAULT now() NOT NULL,
    locationid uuid
);

SELECT create_hypertable('ttt', 'tdate', 'locationid');

[pregenie]

Haven't run into this but will test.

On Wed, Apr 25, 2018, 6:27 PM Bee Ing [e-mail] wrote:

I am having the same problem, here's my SQL

CREATE TABLE ttt (
id uuid DEFAULT uuid_generate_v4() PRIMARY KEY,
tdate date DEFAULT now() NOT NULL,
locationid uuid
);
SELECT create_hypertable('ttt', 'tdate', 'locationid');

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/timescale/timescaledb/issues/447#issuecomment-384483709,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AAjf_XwTC4ymvwenZOwtkN0dBh_SeKnjks5tsSKdgaJpZM4SXJfr
.

[beeing]

Just FYI, using PostgreSQL 10.3 with timescaledb 0.9.1

[ayZagen]

I use PostgreSQL 9.6.7 with timescaledb 0.9.1 and have this issue too.

[pregenie]

Thank you. Figured this out on my own. Love your product.

On Wed, Feb 28, 2018, 12:26 PM Mike Freedman [e-mail]
wrote:

Hi @pregenie https://github.com/pregenie if the problem is occurring
with your UNIQUE constraint on seq_id, that makes total sense: TimescaleDB
requires that all UNIQUE constraints include their partitioning fields as
their prefix. Technical explanation: We do not build global indexes/lookup
data structures ACROSS all partitions, which would then need to be
updated/checked on any write.

Instead, all of our indexes are local to be chunk. Therefore, by
requiring that unique constraints use the partitioning keys as prefix, this
insures that we only need to check the individual chunk (defined by the
partitioning keys) when performing a check constraint.

See discussion:
http://docs.timescale.com/v0.8/using-timescaledb/schema-management#indexing

That said, I'm not totally sure I understand why the error message
mentions occurred_at, rather than something on event_data_seq_id_unique.
That said, I don't see the create_hypertable() command in the log you
dumped. Typically people execute right after you create the table and
before any DDL changes (via ALTER). When are you calling it?

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/timescale/timescaledb/issues/447#issuecomment-369371441,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AAjf_aJ34xwf1snl9vHQIZNqna7Z0Tokks5tZbaAgaJpZM4SXJfr
.

[RobAtticus]

Hey @beeing and @ayZagen --

The problem stems from the fact that for TimescaleDB, the PRIMARY KEY must include all dimensions you are partitioning by:
http://docs.timescale.com/v0.9/using-timescaledb/schema-management#indexing-best-practices
(scroll down to the tip)

[cljk]

To clarify: You are saying that a hypertable cannot have a "primary key" definition for a single column?

Okay... that´s .... problematic I think.