Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1m2jwS-0007ci-Dh for pgsql-performance@arkaria.postgresql.org; Mon, 12 Jul 2021 00:37:00 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1m2jwR-00086V-C3 for pgsql-performance@arkaria.postgresql.org; Mon, 12 Jul 2021 00:36:59 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1m2jwP-00086M-EC for pgsql-performance@lists.postgresql.org; Mon, 12 Jul 2021 00:36:59 +0000 Received: from sonic306-22.consmr.mail.ne1.yahoo.com ([66.163.189.84]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1m2jwM-0001wp-Fo for pgsql-performance@postgresql.org; Mon, 12 Jul 2021 00:36:56 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1626050213; bh=BEBMpCzs+MTkxZW/0a/2sE6EFbur/2hFxrzHkJrhLuQ=; h=Date:From:To:Cc:In-Reply-To:References:Subject:From:Subject:Reply-To; b=LCjAdqZ63gdXuWHhXGoqPUUI1ADuuqJI1dTyUBCSrDtxpcKT+3fquAsrU7Cp3HESGSKHUx68uu3ExDnflpvugOeCY43/jjrdUP0sOzMfWtLIgBADvb7dk9Gu3wwNZbR9vkAeq7MkurKxViR7hp+c0nKUtlM16EaiQNOYkVjfJFu9BY9nmaHmT5GwHSidbNzQ9Q/eZGiP0gjRJ4+3Tm6Qz/2yknEEzf+0WdRn25CaB90b9kvzvZuaMIF4CU3XyOU8u8Jre12h5oqcrotEkVDs23ePRcKlvyfLDrUlGXaOF0jY6ntJWFrwtM2HH6Lfv6sZj7fCcLr1AEiRA5S4PQwpnA== X-SONIC-DKIM-SIGN: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1626050213; bh=+tFh4J3MQoQ3p37MgYRz0mP5xxebl/SEnz6JHNyipWF=; h=X-Sonic-MF:Date:From:To:Subject:From:Subject; b=DZGt3DgZrnpYC5UxWNOGWgyzQ7zCNQivisKAh1pZcxbROieXZW3+g5MM5a0A0nxYM7+b69bI/NBHvqkAiptK+BweiHcrhJErkbyPOiBzQVqyHFe6YK4KK2ASNrZYQsotxIaMv1VLsiPeDUU5KNcPnNa8JDCLQH5uccNAadYBhaEGM2r74xoLY0QTeG10YH7trRjCSodeq+y+SWBzHJhNetO4SMHdNIUuu1f0iAYX2OkNbxXgFsZcIB7PZJX/kIQ5n53Ek0ngNIxBOPbIBpSGvFxWM7EKP6jJAtOskQB3vWOMtFdppajeF9ZnD/RBKgO1idnS3VPnFAe89H7WF82WlQ== X-YMail-OSG: F4olM8IVM1lfcV7kj.Cy_OHXRphZspq51Rk_4gJKdMOUWXKefraKoKfCKnoaVUP pAYhKh7gB9ctYBoD.brNmc0UfhZFIJohR5rQ184KIqfhvgA0TUBAexqlDXR4vmEGtEhcJm4LXlBs QciXFzWmk1riIz_VarxJ0dWpu0UROPjnnEPEQsh9QTyRYc2rY3b3nkBKxHpqvIsKUUfGIvWsFOZ2 VR321fhXJvfzK65gtMlbsAdPhRiZXQn71i3KVzJ9GcCxxHKfpSJm4s1aUD0tmMjir7vTZqLGDRU7 3fG7eZQFWwn1oEjdMWQTpvhLRLMzLSNBFn_pxSmkx_G0R_ZdTdrHIsB.PtUAs6ftl2QiXVgUxi9x SwhhwP7.AiNWVoiSutZbekdsw5iFdv7oJJJXp.wptz4nLeiL6E0eEk46cs.65cQhrH34cEM9k28N XmQdiPBMQtKC79hZ9WC_j0ctZdvlS6dTu4OFDyMbm8nXagr5HedAcksVFHMuw4je0vc6XgYjHWwv PMfRYyI.hKJCdg7NcAbqYSaUYQDsim8if.stS7_ieNFRNWFR3sgLZX1nNqKbc9aChThc4tSZNyAi 6Kj2YDX4xJ3ZjPQjxY9L.ElK0k4Rk5hw1uDD.AajPQT4XJ8fDJQCut1FewgMDDKfXLU0sOBWEIrM 9V3gQxPJHYdwPnwEke1o5trtwtdpJ8FedAL_hYdVWf3C_9biaiTvgsbZBjK0_dsMmh6J9lQSRNzY aPmcs7N3bxrzk7drg_uxvT8cmZhDphBt8_UqyuZyIWTnhdZ9PfAv300okx7VRG4f4I3DNP521FdZ 0ozGW116HKtAUbPoLiBt16fzXkzTXmJTz0Qpd1Lzj4NsJXUn0yRTKOAsLPbajqzpcWXBAl.h4.NB vnGMX9V56GDXR8x5kgULb.iih2s_zaLdRxdJBr5Qev_rEoHvlTKvPbJPy0rz2F.QWZ.2w559FVPZ _2UvmnU3BNvzs3rgNEULwQgB83oS1obYmkLSmkPod72OchZBFCZ6wRuQbw5rDpYlEj0zgQuWNjsy JMgTDvxowwyYtAxQ_ymEppwuzox4t7SUnaF6_ChlM0eec8MrasKEbHUIBoMS4HdFtq_4U.cfAk1D GHdln9YEyQping4ySTTGqymaTxZ6W8JV7fY39LrCWKo1tm3pgU5QMdAqZzZvCdK_sXtIGncGMDg1 QSrNCdlTlPpzyvexAiaiOS.q4OzSk5nEqBWoxMGeYq8RouuSkQuqwYQnS._b6pHHmEMzcjCDRfLB KvQTSDigg8eTNpvQiPV9mC1d7tneYeHrmFKo.SUeOkykIhDZRIkcdB7lJKeFxTUIliigTXVyeYa8 mIkdxDaNoVheWzennawu3M.KH6893bWXLCplxN8cqpgTJkFDoJaiL_Xie2o9ehvawfTgLSxQVUSr ED4yA5OpsqiSvOgVngJCcVlPw._foQh8stsvi1VDROrBayIFCmlyVbDYFpC56vsgK4UTxL.LKp3u vnupEd0FC7AABh5nXUYtMyzDo3mzrhUXWb8YaGhr83nIM_E_VwAqrR3IQlB3n7OZVW0.PQs7HdWz E8CPQhMOxCGvovr30I1wBSGSrxm1sCvq1vbypmlimpDFqyGDB.EwkOT.aCRA_pUT1ynCwHgXXCDa hG4tclnvS32c8gEYoXXdNi6WOFHEr61ksRt3Z3lGPMr5snqiELV8OlAV_qebP9p.m0.Gf_manEuw jHvNqSf0FWWbSY_SvEgg8gIWAxJGsG.TXG8DVuq9RH9XZcnh0VzCuu6dSiIzcEThmlOxjXQTPre_ 9BcYWuQvNe2P.JUV2ILHqqZ_aeYbqDb4T2DBuyrYJzHbHoY_F3locigcZYoqZE0Ok8mvEvzROwAZ sneqL9TWdwPoMLoQrhqvoQUL0ZEpzDg4RQMGy7z0yWGaFsZazSk8ep5RQ4t0.Nb1dJ0WRclXqOrG MVRc.G_KYSzCTY8yc.kghbwu_e7EcQn_yFYDxO7DUrvME3aQndPISVc4GAjoSq7r.bkUnI6tjTuU x4KZKIMv3Bdnh5fTMc_vt4d0pFpmzjqKm7hMg.W0UXE4lELrOSJ_Gs8LQZj_TurR9ggiadrhoIfZ e073CHhyamZuSxBizdEcMkd.nSo7A7DE2wGsIXrijBeOdgeTaUoJQI.qPu.Cmbb6ESo0VoSQ0Pix HhW2L6BcS6sc6GIsSY0iYiPZzW6J65IYHywgsvVOrmQnhGoYqqXlkyAC9tB8NhWYwBkAZ30iGbyy ZpOZrTLnvsa.05oktRhgkTT6Sv40ltgGNzqtg2YhMxceQaZamLySjbEfLFIxcem4MYR1w5xlqKuK Jb7KDFXGilFqhU4.MFzg92pnfuZ5FpHYBS9934yT0qyDKpH.i20l4VBhfLo70VfhomLbZSIBmN6J PLgsKs529WF0VrZY3BwG9OsGDw0yGie1xUROTUE0nDDMElaJ9troTygspMFcqJZ0T5HE53hOyeL4 BasnTxsdAheJgVruKXj.P.wXc2gJ9AlPBWSiz9rJ5oeYvxDjDe8hE4QzVrQ-- X-Sonic-MF: Received: from sonic.gate.mail.ne1.yahoo.com by sonic306.consmr.mail.ne1.yahoo.com with HTTP; Mon, 12 Jul 2021 00:36:53 +0000 Date: Mon, 12 Jul 2021 00:36:51 +0000 (UTC) From: Nagaraj Raj To: Christophe Pettus Cc: "pgsql-performance@postgresql.org" Message-ID: <859150623.3882063.1626050211237@mail.yahoo.com> In-Reply-To: <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> <5B9DB883-EED5-4378-ABEC-03FCC11560B2@thebuild.com> Subject: Re: Partition column should be part of PK MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_3882062_1564783107.1626050211233" X-Mailer: WebService/1.1.18469 YMailNorrin Content-Length: 14209 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------=_Part_3882062_1564783107.1626050211233 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable personally, I feel this design is very bad compared to other DB servers. > 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 >=C2= =A0sophisticated to make sure that two sessions don't insert an (billing_ac= count_uid, ban) value into two different partitions.=C2=A0 This isn't a gre= at fit >=C2=A0for table partitioning, and you might want to reconsider if p= artitioning the table is the right answer here.=C2=A0 If you *must* have ta= ble partitioning, a >=C2=A0possible algorithm is: yes, this is my use case. can I use some trigger on the partition table before inserting the call tha= t function this one handle conflict?=C2=A0 CREATE or replace FUNCTION insert_trigger()=C2=A0 =C2=A0 RETURNS trigger=C2= =A0 =C2=A0 LANGUAGE 'plpgsql'=C2=A0 =C2=A0 COST 100=C2=A0 =C2=A0 VOLATILE N= OT LEAKPROOFAS $BODY$DECLARE=C2=A0 =C2=A0 =C2=A0 conn_name text; =C2=A0 c_t= able TEXT; =C2=A0 t_schema text; =C2=A0 c_table1 text; =C2=A0 m_table1 text= ; =C2=A0 =C2=A0 BEGIN =C2=A0 =C2=A0 c_table1 :=3D TG_TABLE_NAME;=C2=A0 =C2= =A0 t_schema :=3D TG_TABLE_SCHEMA;=C2=A0 =C2=A0 m_table1 :=3D t_schema||'.'= ||TG_TABLE_NAME;=C2=A0 =C2=A0 SELECT conname FROM pg_constraint WHERE conre= lid =3D TG_TABLE_NAME ::regclass::oid and contype =3D 'u' into conn_name;= =C2=A0 =C2=A0 execute 'insert into '|| m_table1 || ' values ' || new.* || '= on conflict on constraint ' || conn_name || ' do nothing -- or somthing';= =C2=A0 =C2=A0 RETURN null;=C2=A0 =C2=A0 end;=C2=A0 =C2=A0 $BODY$; CREATE TRIGGER insert=C2=A0 =C2=A0 BEFORE INSERT=C2=A0 =C2=A0 ON t4=C2=A0 = =C2=A0 FOR EACH ROW=C2=A0 =C2=A0 WHEN (pg_trigger_depth() < 1)=C2=A0 =C2=A0= EXECUTE FUNCTION insert_trigger();=C2=A0 =C2=A0 CREATE TRIGGER insert=C2= =A0 =C2=A0 BEFORE INSERT=C2=A0 =C2=A0 ON t3=C2=A0 =C2=A0 FOR EACH ROW=C2=A0= =C2=A0 WHEN (pg_trigger_depth() < 1)=C2=A0 =C2=A0 EXECUTE FUNCTION insert_= trigger(); .. so on ..=C2=A0 =C2=A0=C2=A0 https://dbfiddle.uk/?rdbms=3Dpostgres_11&fiddle=3Dbcfdfc26685ffb498bf82e6d5= 0da95e3 Please suggest. Thanks,Rj On Thursday, July 8, 2021, 08:52:35 PM PDT, Christophe Pettus wrote: =20 =20 =20 > On Jul 8, 2021, at 20:32, Nagaraj Raj wrote: >=20 > My apologies for making confusion with new thread. Yes its same issue rel= ated to earlier post. >=20 > I was trying to figure out=C2=A0 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.=C2=A0 If it didn't have that requirement, it would= have to independently (and in a concurrency-supported way) scan every part= ition individually to see if there is a duplicate key violation in any of t= he 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, d= ate) combination across the entire partition set, you can create an index u= nique index on the partitioned set as (billing_account_guid, ban, date), an= d 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 sophist= icated to make sure that two sessions don't insert an (billing_account_uid,= ban) value into two different partitions.=C2=A0 This isn't a great fit for= table partitioning, and you might want to reconsider if partitioning the t= able is the right answer here.=C2=A0 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 inse= rt 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-partit= ion 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-= ADVISORY-LOCKS =20 ------=_Part_3882062_1564783107.1626050211233 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
=
personally, I feel this design is very bad compared to other DB = servers.

> If the goal is to make sure t= here 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 session= s don't insert an (billing_account_uid, ban) value into two different parti= tions.  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:
yes, this is my use case.

can I use some trigger on the partition table before inserti= ng the call that function this one handle conflict? 


CREATE or repla= ce FUNCTION insert_trigger()
    RETURNS trigger
<= div>    LANGUAGE 'plpgsql'
    COST 100
=
    VOLATILE NOT LEAKPROOF
AS $BODY$
DEC= LARE
      conn_name text;
=09  c_table TEXT;
=09  t_schema text;
= =09  c_table1 text;
=09  m_table1 text= ;
=09
&nb= sp;   BEGIN=09
    c_table1 :=3D TG_TABLE_NAME;
    t_schem= a :=3D TG_TABLE_SCHEMA;
    m_table1 :=3D t_schema||'.'= ||TG_TABLE_NAME;
    SELECT conname FROM pg_constraint = WHERE conrelid =3D TG_TABLE_NAME ::regclass::oid and contype =3D 'u' into c= onn_name;
    execute 'insert into '|| m_table1 || ' va= lues ' || new.* || ' on conflict on constraint ' || conn_name || ' do nothi= ng -- or somthing';
    RETURN null;
  &= nbsp; end;
    $BODY$;

<= div>CREATE TRIGGER insert
    BEFORE INSERT
&= nbsp;   ON t4
    FOR EACH ROW
  &n= bsp; WHEN (pg_trigger_depth() < 1)
    EXECUTE FUNCT= ION insert_trigger();
    CREATE TRIGGER insert
    BEFORE INSERT
    ON t3
&nbs= p;   FOR EACH ROW
    WHEN (pg_trigger_depth() <= ; 1)
    EXECUTE FUNCTION insert_trigger(); .. so on ..=
    



=20
=20
On Thursday, July 8, 2021, 08:52:35 PM PDT, Christophe = Pettus <xof@thebuild.com> wrote:




> On Jul 8, 2021, at 20:32, Nagaraj Raj <nagar= aj.sf@yahoo.com> wrote:
>
&g= t; My apologies for making confusion with new thread. Yes its same issue re= lated to earlier post.
>
> I was= trying to figure out  how to ensure unique values for columns (billin= g_account_guid, ban). If i add partition key to constraint , it wont be pos= sible what im looking for.
>
> M= y use case as below
>
> INSERT = INTO t1 SELECT * from t2 ON CONFLICT (billing_account_guid,ban) DO UPDATE S= ET something=E2=80=A6
>
> Or
>
> 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, PostgreS= QL does not support unique indexes on partitioned tables (that operate acro= ss all partitions) unless the partition key is included in the index defini= tion.  If it didn't have that requirement, it would have to independen= tly (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 crea= te an index unique index on the partitioned set as (billing_account_guid, b= an, date), and INSERT ... ON CONFLICT DO NOTHING works properly then.

If the goal is to make sure there is only o= ne (billing_account_uid, ban) in any partition regardless of date, you'll n= eed to do something more sophisticated to make sure that two sessions don't= insert an (billing_account_uid, ban) value into two different partitions.&= nbsp; 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 (b= illing_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 t= o make sure one does not already exist.
-- If one does no= t, do the INSERT.
-- Commit, which releases the advisory = lock.

This doesn't provide quite the s= ame 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 show= ing up in different partitions.


[1]
https://www.postgresql.org/docs/current/f= unctions-admin.html#FUNCTIONS-ADVISORY-LOCKS


------=_Part_3882062_1564783107.1626050211233--