Content-Type: text/plain;
	charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 13.4 \(3608.120.23.2.1\))
Subject: Re: Partition column should be part of PK
From: Christophe Pettus <xof@thebuild.com>
In-Reply-To: <613808845.2913413.1625801566230@mail.yahoo.com>
Date: Thu, 8 Jul 2021 20:52:21 -0700
Cc: pgsql-performance@postgresql.org
Content-Transfer-Encoding: quoted-printable
Message-Id: <5B9DB883-EED5-4378-ABEC-03FCC11560B2@thebuild.com>
References: <20210708221703.GA1301@telsasoft.com>
 <202107082228.obnak7c7mx4n@alvherre.pgsql>
 <080901d7744e$ac0f3d70$042db850$@runbox.com>
 <613808845.2913413.1625801566230@mail.yahoo.com>
To: Nagaraj Raj <nagaraj.sf@yahoo.com>
Archived-At: 
 <https://www.postgresql.org/message-id/5B9DB883-EED5-4378-ABEC-03FCC11560B2%40thebuild.com>
Precedence: bulk


> On Jul 8, 2021, at 20:32, Nagaraj Raj <nagaraj.sf@yahoo.com> wrote:
>=20
> My apologies for making confusion with new thread. Yes its same issue =
related to earlier post.
>=20
> 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.
>=20
> My use case as below=20
>=20
> INSERT INTO t1 SELECT * from t2 ON CONFLICT (billing_account_guid,ban) =
DO UPDATE SET something=E2=80=A6
>=20
> Or
>=20
> INSERT INTO t1 SELECT * from t2 ON CONFLICT constraint (pk or =
uk)(billing_account_guid,ban) DO UPDATE SET something=E2=80=A6

Right now, PostgreSQL does not support unique indexes on partitioned =
tables (that operate across all partitions) unless the partition key is =
included in the index definition.  If it didn't have that requirement, =
it would have to independently (and in a concurrency-supported way) scan =
every partition individually to see if there is a duplicate key =
violation in any of the partitions, and the machinery to do that does =
not exist right now.

If the goal is to make sure there is only one (billing_account_guid, =
ban, date) combination across the entire partition set, you can create =
an index unique index on the partitioned set as (billing_account_guid, =
ban, date), and INSERT ... ON CONFLICT DO NOTHING works properly then.

If the goal is to make sure there is only one (billing_account_uid, ban) =
in any partition regardless of date, you'll need to do something more =
sophisticated to make sure that two sessions don't insert an =
(billing_account_uid, ban) value into two different partitions.  This =
isn't a great fit for table partitioning, and you might want to =
reconsider if partitioning the table is the right answer here.  If you =
*must* have table partitioning, a possible algorithm is:

-- Start a transaction
-- Hash the (billing_account_uid, ban) key into a 64 bit value.
-- Use that 64 bit value as a key to a call to pg_advisory_xact_lock() =
[1] to, in essence, create a signal to any other transaction attempting =
to insert that pair that it is being modified.
-- SELECT on that pair to make sure one does not already exist.
-- If one does not, do the INSERT.
-- Commit, which releases the advisory lock.

This doesn't provide quite the same level of uniqueness that a =
cross-partition index would, but if this is the only code path that does =
the INSERT, it should keep duplicate from showing up in different =
partitions.

[1] =
https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADV=
ISORY-LOCKS=