From: Justin Pryzby <pryzby@telsasoft.com>
To: Nagaraj Raj <nagaraj.sf@yahoo.com>
Cc: Mike Sofen <msofen@runbox.com>
Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: Partition column should be part of PK
Date: Sun, 11 Jul 2021 19:38:46 -0500
Message-ID: <20210712003846.GA2004@telsasoft.com> (raw)
In-Reply-To: <613808845.2913413.1625801566230@mail.yahoo.com>
References: <20210708221703.GA1301@telsasoft.com>
<202107082228.obnak7c7mx4n@alvherre.pgsql>
<080901d7744e$ac0f3d70$042db850$@runbox.com>
<613808845.2913413.1625801566230@mail.yahoo.com>
On Fri, Jul 09, 2021 at 03:32:46AM +0000, Nagaraj Raj wrote:
> My apologies for making confusion with new thread. Yes its same issue related to earlier post.
> I was trying to figure out how to ensure unique values for columns (billing_account_guid, ban). If i add partition key to constraint , it wont be possible what im looking for.
> My use case as below
> INSERT INTO t1 SELECT * from t2 ON CONFLICT (billing_account_guid,ban) DO UPDATE SET something…
>
> Or
> INSERT INTO t1 SELECT * from t2 ON CONFLICT constraint (pk or uk)(billing_account_guid,ban) DO UPDATE SET something…
I'm not sure, but did you see the 2nd half of what I wrote in June ?
lightly edited:
> 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
> partition, then you could do INSERT ON CONFLICT to a partition rather
> than its parent.
>
> But it cannot be a unique, "partitioned" index, without including load_dttm.
You could try something that doesn't use a parent/partitioned index (as I was
suggesting). Otherwise I think you'd have to partition by something else
involving the unique columns, or not use declarative partitioning, or not use
insert on conflict.
Justin
PS, I'm sorry for my own confusion, but happy if people found it amusing.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: pryzby@telsasoft.com, nagaraj.sf@yahoo.com, msofen@runbox.com, alvherre@alvh.no-ip.org
Subject: Re: Partition column should be part of PK
In-Reply-To: <20210712003846.GA2004@telsasoft.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox