Re: Simple task with partitioning which I can't realize

If you are wanting to ensure uniqueness for the original oracle pk across
the partitions, you could look into adding an advisory trigger to the table.

On Wed, Mar 2, 2022, 2:28 AM Michel SALAIS <msalais(at)msym(dot)fr> wrote:

> *De :* Marc Millas <marc(dot)millas(at)mokadb(dot)com>
> *Envoyé :* mardi 1 mars 2022 19:00
> *À :* Andrew Zakharov <Andrew898(at)mail(dot)ru>
> *Cc :* pgsql-performance(at)lists(dot)postgresql(dot)org
> *Objet :* Re: Simple task with partitioning which I can't realize
>
>
>
> Andrew,
>
>
>
> contrary to Oracle, in postgres you can add the indexes and/or the
> constraints which are meaningful to you at partition level.
>
> I was not saying NOT to create keys, but I was saying to create them at
> partition level.
>
>
>
>
> Marc MILLAS
>
> Senior Architect
>
> +33607850334
>
> www.mokadb.com
>
>
>
>
>
>
>
> On Tue, Mar 1, 2022 at 5:45 PM Andrew Zakharov <Andrew898(at)mail(dot)ru> wrote:
>
> Hi Marc –
>
> Since there is a DWH fed by ETL there no risks to have same gids in
> different region partitions. I considered simple partitioned table w/o any
> keys but I’d believed there is a solutions with keys that’s why I’m seeking
> the clue.
>
> Thanks.
>
> Andrew.
>
>
>
> *From:* Marc Millas <marc(dot)millas(at)mokadb(dot)com>
> *Sent:* Tuesday, March 01, 2022 7:29 PM
> *To:* Andrew Zakharov <Andrew898(at)mail(dot)ru>
> *Cc:* pgsql-performance(at)lists(dot)postgresql(dot)org
> *Subject:* Re: Simple task with partitioning which I can't realize
>
>
>
> Hi,
>
>
>
> is there any chance (risk ?) that a given gid be present in more than one
> region ?
>
> if not (or if you implement it via a dedicated, non partition table),
>
>
>
> you may create a simple table partitioned by region, and create unique
> indexes for each partition.
>
> this is NOT equivalent to a unique constraint at global table level, of
> course.
>
>
> Marc MILLAS
>
> Senior Architect
>
> +33607850334
>
> www.mokadb.com
>
>
>
>
>
>
>
> On Tue, Mar 1, 2022 at 4:37 PM Andrew Zakharov <Andrew898(at)mail(dot)ru> wrote:
>
> Hello all –
>
> I have a task which is simple at the first look. I have a table which
> contains hierarchy of address objects starting with macro region end ends
> with particular buildings. You can imagine how big is it.
>
> Here is short sample of table declaration:
>
>
>
> create table region_hierarchy(
>
> gid uuid not null default uuid_generate_v1mc(),
>
> parent_gid uuid null,
>
> region_code int2,
>
> …
>
> constraint pk_region_hierarchy primary key (gid),
>
> constraint fk_region_hierarchy_region_hierarchy_parent foreign key
> (parent_gid) references region_hierarchy(gid)
>
> );
>
>
>
> Being an Oracle specialist, I planned to using same declarative
> partitioning by list on the region_code field as I did in Oracle database.
> I’ve carefully looked thru docs/faqs/google/communities and found out that
> I must include “gid” field into partition key because a primary key field.
> Thus partition method “by list” is not appropriate method in this case and
> “by range” either. What I have left from partition methods? Hash? How can I
> create partitions by gid & region_code by hash? Feasible? Will it be
> working properly (with partition pruning) when search criteria is by
> region_code only? Same problem appears when there is simple serial “id”
> used as primary identifier. Removing all constraints is not considered. I
> understand that such specific PostgreSQL partitioning implementation has
> done by tons of reasons but how I can implement partitioning for my EASY
> case? I see the only legacy inheritance is left, right? Very sad if it’s
> true.
>
> Your advices are very important.
>
> Thanks in advance.
>
> Andrew.
>
>
> _________________________________________________________________________________________
>
> Hi
>
> To say it using Oracle vocabulary, PostgreSQL doesn’t offer GLOBAL
> INDEXES. Even when we create an index on the partitioned table which is now
> possible, PostgreSQL create LOCAL indexes on each partition separately.
>
> There is no global indexes on partitioned tables in PostgreSQL. So it is
> not simple to offer uniqueness at global level using indexes. That is why,
> it is required that partition key columns be part of the primary key AND
> any other UNIQE constraint.
>
>
>
> *Michel SALAIS*
>