Re: partitions vs indexes

On 2 Oct 2019, at 22:09, Enrico Thierbach wrote:

> 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

and, errm, forgot to mention thatI am on postgresql 11.3. Sorry for that
omission.

Best,
eno

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