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 wrote:
> *De :* Marc Millas
> *Envoyé :* mardi 1 mars 2022 19:00
> *À :* Andrew Zakharov
> *Cc :* [email protected]
> *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 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
> *Sent:* Tuesday, March 01, 2022 7:29 PM
> *To:* Andrew Zakharov
> *Cc:* [email protected]
> *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 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*
>
