Date: Thu, 24 Jun 2021 23:22:28 -0500
From: Justin Pryzby <pryzby@telsasoft.com>
To: Nagaraj Raj <nagaraj.sf@yahoo.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Partition column should be part of PK
Message-ID: <20210625042228.GJ29179@telsasoft.com>
References: <836265572.4593017.1624590607279.ref@mail.yahoo.com>
 <836265572.4593017.1624590607279@mail.yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <836265572.4593017.1624590607279@mail.yahoo.com>
User-Agent: Mutt/1.9.4 (2018-02-28)
Archived-At: 
 <https://www.postgresql.org/message-id/20210625042228.GJ29179%40telsasoft.com>
Precedence: bulk

Declarative partitioning was new in v10
In v11, it was allowed to create an index on a partitioned table, including
unique indexes.

However it's not a "global" index - instead, it's an "inherited" index.
For a unique index, uniqueness is enforced within each individual index.
And so global uniqueness is only guaranteed if the partition key is included in
the index.

On Fri, Jun 25, 2021 at 03:10:07AM +0000, Nagaraj Raj wrote:
> CREATE TABLE c_account_p (
>     billing_account_guid character varying(40)  NOT NULL,
>     ingestion_process_id bigint NOT NULL DEFAULT '-1'::integer,
>     load_dttm timestamp(6) without time zone NOT NULL,
>     ban integer NOT NULL,
>     CONSTRAINT billing_account_pkey PRIMARY KEY (billing_account_guid, ban)
> ) PARTITION by RANGE(load_dttm);
> When I try the create table, it's throwing below error:
> ERROR:  insufficient columns in the PRIMARY KEY constraint definition
> DETAIL:  PRIMARY KEY constraint on table "l_billing_account_p" lacks column "load_dttm" which is part of the partition key.
> 
> If the partition column should be supposed to be a PK, it's challenging to create a partition by range with the date column, cause the load_dttm column chances to have duplicate if data loaded COPY or INSERT.INSERT INTO c_account_p SELECT * from c_account_p_bkp ON CONFLICT (billing_account_guid,ban,load_dttm) DO UPDATE SET 'some stuff..'
> 
> If I receive billing_account_guid, ban combination with different load_dttm then it will end up with duplicate keys.

It sounds like you want a unique index on (billing_account_guid, ban) to
support INSERT ON CONFLICT.  If DO UPDATE SET will never move tuples to a new
partittion, then you could do the INSERT ON CONFLICT on the partition rather
than its parent.

But it cannot be a unique, "partitioned" index, without including load_dttm.

-- 
Justin