partitions vs indexes

Hello list,

I run into some trouble with partitions:

I would like to convert a table with a primary key into a partitioned
setup by a column which is not part of the primary key. Also, a column
might hold a referenece to a parent row. So this is my current table
setup, slimmed down:

CREATE TYPE statuses AS ENUM ('ready', ‘processing’,
‘done’);

CREATE TABLE mytable (
id BIGSERIAL PRIMARY KEY NOT NULL,
parent_id BIGINT REFERENCES mytable(id) ON DELETE CASCADE,
status statuses DEFAULT 'ready'
);

Since entries in the table are often frequented when status is not
‘done’ I would like to partition by state. However, if I want to do
that:

CREATE TABLE mytable (
id BIGSERIAL NOT NULL,
parent_id BIGINT REFERENCES mytable(id) ON DELETE CASCADE,
status statuses DEFAULT 'ready'

-- UNIQUE(id, status) -- doesn’t work: can’t reference
parent
-- UNIQUE(id) -- doesn’t work: can’t partition
) PARTITION BY LIST(status);

I need to add status to the primary key or another unique constraint. In
that case, however, I can no longer have the foreign key constraint on
parent_id.

Weirdly enough partitioning works fine if there is no unique constraint
on that table:

CREATE TABLE mytable (
id BIGSERIAL NOT NULL,
status statuses DEFAULT 'ready'
) PARTITION BY LIST(status);

So partitioning seems to require the column being in a unique constraint
if and only if a unique constraint exist on the table. Also I cannot
create multiple unique constraints on the table.

Here comes my question:

- Do I miss something?
- ThI don’t understand the requirement the partition value to be part
of a unique constraint if such a constraint exists, since partitioning
seems to work fine if the table has no unique constraints at all. Can
someone shed some light on that? Is that maybe an artificial limitation
that will go away on the future?
- Any suggestions how I could proceed?

Thank you for any suggestion!

Best,
Eno

--
me on github: http://github.com/radiospiel