Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uf5r1-0021ta-46 for pgsql-general@arkaria.postgresql.org; Fri, 25 Jul 2025 00:00:04 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1uf5qy-00CfnN-VC for pgsql-general@arkaria.postgresql.org; Fri, 25 Jul 2025 00:00:01 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uf5qy-00Cfmu-Io for pgsql-general@lists.postgresql.org; Fri, 25 Jul 2025 00:00:00 +0000 Received: from sonic314-20.consmr.mail.sg3.yahoo.com ([106.10.240.144]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1uf5qu-000ggl-37 for pgsql-general@postgresql.org; Fri, 25 Jul 2025 00:00:00 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1753401591; bh=/fS/ixqgjYDwzXGY/Q2PNjMsMm/CoKD8zYgoy1ay7n8=; h=Date:From:Reply-To:To:Cc:In-Reply-To:References:Subject:From:Subject:Reply-To; b=hpZ99G3wD0WAXs+4R48Ot5skkd2kG44MtNFgmBoMbeR/AppXxzfZcwNv1eGG+DoEdgXdKihgwb0ass9llPrx4q3HmtBkyEUIp8HCU24eH7YVGXp9ilUTeX1Q0/zAKXosEmf0QFsSn5NelFAru5wo4S+Svj435qZjVE3hsnQmwKO/BWk0/XlguLMxZ5UXdZo1HkNK83pVZRnCnwWy3Am/cGqQxzpayX74CThiRS9szd5k1jiaQ22jfLwvTFIZuOXwbZTmyCH1qL8ibYSK3uwm2YeXTqYt9oZUyIScq/yYXEgp1ulRrNztvWy2pqzVINtoLfRiuyGe+eR+lEzjS7MxTw== X-SONIC-DKIM-SIGN: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1753401591; bh=W+mPaLS/6ei5ZPY+Umzt5QVjXvUkX1CON071pyRlhdn=; h=X-Sonic-MF:X-Sonic-MF:Date:From:To:Subject:From:Subject; b=D6sh4RmNMHmnAbgUuUn2TAxdZ5V/5PM4u07jNa4MUYYkf2vh/Lza0cSgUjCLc3FkJ9BOwZNL4HBj3ogN3hu+qFGTCqImnOcETAyMSXGYvj0td/EduhBRACkFEgUFYwogpTTOVP8V+idCj7RPiaoLb+sSNBLcmzWhaiS4w3v7efWWwZoLV2Vo8iBH7ToOgb0nKHEwOcPOw9ZS+YLhnGvKO4CkWyQvEXQzsGS5AZDp57YEr06zlPIHlfn3vx8ICIR6UYWzQJSScYertPgVuv4oXoNPn2PFOHXsi1VwdMahhmxU6h/nRDyIY14I1jl6LfUf+4ZcH823bv17keUfw7qoUA== X-YMail-OSG: 0VKCGU0VM1n.wpSUkX30rRq___ZD0aR5_Kxi4MI0uvVNH1CmeVDErU0iPE0w30f Hql.rzHKEc5wR6AZsux.PdiiXkmjJw7xXwKsPlFqfVkjh5XQRJ535qA_JqpbS2ClmlTNDe3h5fSM WHo1MhD9mCVKZvKkffoaSZMZBwswtf3JsD7wF_g2U4QX3kOvFlvHg0sVDx2sLOv0RBInowhgmDd0 8AwVIpEdCVjV_P2dRgtgAWtOSW7yachR2Iz5voEVhrNOG03vlcNxAORYt1t07n2wZgVmc66EMMol kClrNjj.nb3J2EbnXX7L_ooNleUnVZTG5Wi8dkdjjGvr94D_7Y.jUdotbdjbUTXDvydT4pG4YhgR rZtE_.BEI4KoRTNFw0I9xKjoSy1.nnsofk7QMbFrO7eM.EnmgYvOuRUKclzBSwEpsvK1swtkWiza YmJYTcLXiO_e5nOG3Ke98e5374438wTse7X.pMgYy9yJTfLbY3tOxCC3DcErLsqx308ixZ90PprA eYUoiedJZ_4yvY3cKmORx.PPJmLk0Tc6RIZ7cvnq7jwkd0VlTlcWiGOJcRzpYbIdis6HFLyXW61h .kTvxSr18U2nhFvRl6JzrrAsm_Htio6HpzpTi9VsjEwGOt.VHSPgK.dm10e9BhlUbCD3yJ81Mx_V gKEciNeGc5T.VkY6o5f2cuUg1lRkpbKSiCxEdYQsoicM8hlIrtThlCQGgAxOJOER6I.oFe2ma5qe MOEZwBvm2ZqSyfunX2nZ_bouU12zmQb4FPoG5C0MDC6rFB7N1Acj0bpaEMw3sApY1DbPKbc.3qIs 7KdHX_p.j9igmo9flzeGlAZCgIYcH19Zay_NUNc1VidBYRlFv8vyijnhOtXWjcapRop1dh980bY3 88tNqifFGVZsW06Q5.q1xIQ2FJbDrAgMNjG7c7yox2ecWnPa4MEqlgpbvV31xZXE.4KEc1A07i31 .GcpgYkYKNLYvLjJtRvyM9a8aOiMNdkY8IhW.h4Dawnlw3pMenk8_pwYSUX7NGgGitDSqNgzubEi z1OY6oYtZLNrW7y.2Be6xGrI3glnM1AbZqq89PJppqsvYJgUC35jlGee6vUqKjlL_oO0ucLau0yR Xb3WOACEcwUDM__hv0KT5DD3RQQKof_B0bRwSdoBh8cqZbCkFyCzXOA_psID0YajBBiJQHbHSxyR wXcLZj1zdsqIXO4Zbu63UZi8g__clTdA5Z0G_eegTR7FMwsryFxu.eDa8ILPntxX7Mzszt3La1Vf 4aeFG5yAif268ikPV40P9d9y.RI8uS9ZkcdE9OhMM.5PMEEoZdDYPHJOS3_qEN_l439DH6Iyhf04 iJXVN6nX_G6yvfrALs5BUQ6pPj6WUKRO_EST9hHrwHRhM2WvFMXR203X.9qxOdDu3MS7XdASAYjG AsQWDqWyKUrjx6cxFXTGJgAjFiSywQOjR7u2V8VfzAyvzq9Se9hV5cxFvEjubyhM8KpcdeXEtumi 1xI7La0RDKZQPcRUeyAeTPStPtcGn6aMCrTmYTe3PyVwRx6eQswGP1KkCAuAwW7qTdfK2TVGFFZZ e6vZet0id.jWwKxK7xRSKttm98JK1tqjLwt_5w6vZpOAT7LbBgeELluo722suHtYmjFVm62CzS1o kL0cPMjoGMSTz9.q2G5fDkKmaU0Gez638jXrs18fQcKD0qbKZFfl9Ij6y4y6Ha_9ZuOMTikRePmy Pe2drXzL9w0ESJIjGjcjScwDKp5bJikBHCankbB098JaU4UbYfXhgLEbwwHpBdO3CuZ_EoPPCwyp ZhTwMh36hfxx1Go8m6LjfJ7..FTTUVMoKMGR4_IboIxxpXsTkzbTh.8MmPt2qIVdp5vQt8cHi9Ez o8c6Ll98NNn3l88jWOlns.5I0CqxFry.tzqmMIT4AhVXRPUTQ.hN58pFkWyjSoKxwe3rCUmqHNFh iaJBj6jVD7i.PQ.xUJAGQ6AH9kSmojO9QwIGo6KgVk.qFn4bfJEbr28wxD4BSi3FaNH32M1EEQtX K0KJEcRjHvlpii0BEH1Y4_LtOYylM7DlR2Qfv5TydLOwb6h5.yBIhPe2Nq37GJN9UxkCLnfQFXK6 KIHpa8srvFknaR3a6kD1z.Mhk0_OLS435I3tMZT_uFjEA74Eh1qA15q1w7CZAMhzim8xavEN.a87 OPJjQqsZUAmI5HThL6No6E7vmslFgu7eBM93Mf1DiHcTDROEmfinY_ss3LDu3l.lWytDr3gc.Wss fzIZpzYXaBLLQDmAkcqOgKFMqbNqjtgoN6ZYxjHrpUvq_lRhjYPuG2jsXGowpbQysx1b9_br555R hx2HWy19mHiS3TQ-- X-Sonic-MF: X-Sonic-ID: 90bb4620-6914-4664-ac33-10b65bbfac63 Received: from sonic.gate.mail.ne1.yahoo.com by sonic314.consmr.mail.sg3.yahoo.com with HTTP; Thu, 24 Jul 2025 23:59:51 +0000 Received: by hermes--production-gq1-74d64bb7d7-cskn8 (Yahoo Inc. Hermes SMTP Server) with ESMTPA ID 3e6a06d1af2ca25dd30e56fa7fad4f4a; Thu, 24 Jul 2025 23:59:48 +0000 (UTC) X-YMail-OSG: QzC4QRYVM1kmlZfTgPB8KKYndlS84JyhicGHFJRMyCcaewyhIMOsqx6EqW5cygP wsgI2bft4ws9fAkJpAP5.D0T60Bh3U7zaZTJQL7qsYQbKiAsfLNuqlNdfurQiQBmcY4Tw2cSUcQG .lgg4QEWpADwgRbB.KS99Z6PG_wGb8SSFDTnFOp_Bqd.EmOg0yztuZPKPsR6edosLRvtsA5Vr5oi huOh.EET6cHYa1WD4v_RGpxLrGkm8kVFyFX9RmO9r51UdkwhwsaaKo9gqCxP0hAZS0_ipRitML3g F6ShlflXALwZsYkCT87dkquEkP62wSSo675MAo4BupjIFHA7Vth5tO4nHqgVut2oyhi1gYeUOfbx DiGKKr5epguqUS1OwdyAks4LFioyZzmgZbnl72blAIDJtB21cSRpzR19zBLbj8neThugbI5onZOY YelO9sY9nm1vZ2RiClUm17NgghCPeHrDc3rWU12Wh8jfIzyO77CM3cOtrfnKLNkXDGLprevS5c0L u_KpnlIIurm4mwKSi3SY3AnV551yLvsPWoGeuRFd_SW8NOp0k2sAflsZ4nDMrCd9iAtEdHYTxElJ v89.YTps.KYv58khneeoo458h40sAP2IQ3AIhliP_rQnQzbIgRqSvIf.mlFmAdr5UpahmPjIaHsP o6CelkjfD0uBf9TGRt3nqBKuLbzxWtx_FuXvR87zZ00R9ZGDD0RH_A1V5q49iSzW0yevOQylatW8 a1h4obyFljy1STghLqKU53rkcwTMFN8MVQpMKoQSoHD20Kk_rblQZYmlaslV7EhEp199VcHqJPsy anTMQ6Bmn_94_GF9NW7TvpACtnjUCANiEHY0pzpJzEXojQ5FySRg6l2r5zre7TWAgkcDa.ReIo6A DD5RZnYvZkwNTN4BSNuOfwCgS79QLsIS_69GBi7mX99yiSMLnTT29if9JljmngIU9FD8aQ4MruKN SiinYXFm9kozWqJhNPoR54Yh_JqFm9Z8ozspLECef2pNas3GVOqtK4UjAKwSjSnjm86cJLa0bh5_ fy0fFAQfH1AFGB859C6v5Mcwk7EQr7PkLkb5EHY_KDW8c5fgK5KVkizr_XOkXGjkwZRHzdUlT3xf P.S.NVU3V1CG8j5Ck4ENcJS9iSujoUxcYaDzBGbHQ9Lf_10E44MvSnjPwds8DhmQrmeGSYg1F4t4 g0.rK4NhS9uU8oqdHsDEB7NrCQ.kbvRHAoUx5vao3a2w2JwKa4ROme.CP7gtudM5zzSh.ROWz1X. vuwRfaiLl546gGs5GBR8ytw7hMyIze9BtKEvJ1Hr92J7DAZchn_LoYoWNdUuawkswxCBEuab3FNu eYUrUE8r7GxomnkpOaes1Zw7f9FEQBt6rr9pz5wxGC8YUOoq4n60d0O.s5Ggk2csUiRy1tFRMTUc zeibclrbpJZTnBTAXs3.99Z0ws.eEJDApnRoT1zuq2sXj3fkugh8vO.E2TewKNlUUi9IEN1vAj14 mPSh9FJN6Fv26bQ9dndLVG0.ZUyPS64B8.BOwpPiGAEU30Bv9DTLxGJDG37o_ef8y1EdjfmLOXJc .5ZX9k.9YzWvjHvomNHFcgDT8vk6AC2It1x_JE7Y9wbqIPHwPQVmPfhuoYG3z1b2yj485u.sZiLT 1JlcaeX12haw6PsWBzmXTVJg5_K2B0249ci9d_CVoUc1ZWk_tXKgHfgtR4XbGaHeRj7s3gndUCDT eIZ5ry6cXCuBvVITG5xQoY4xdEF3lpwH5jJaOBQI5gztONacvIGEJCrC9SGSYY60C1pPjkt63V19 VytJp3iGbZ9XE3JnOtZ8NCtJ00a0YifuR1vduh3IMtZxQr8FVigii6ZE14_h.lj3T9pRLTHnSAbP TKGQk6Wh3KIrSFBi6ePP12TAYPX5Sa2ZrRZxRuaWep3LFtc.xK1VOPKriyG_PwNHiSzuWW0kzn.f YMh3AB.BmQ1cBdE2nuZrck.NbUsfjKEW2TIzLWh3z_pyk6p4xaEkItJp91MiVg24YKhutz5kABkC mcDm8ymZ0QuGpODfSSyneP5senHNMVrX88KNr6eqL3IUlVwTNgfnwME9KfN0uLV4S3a1p9jmoFSw Rj2ngjjKcjLXESKRw9d2SWOABu2OCc38o75f07Gix7nGfr48JwAp_hjNPZ1cWE_hZduEETBiJRcZ tCa8rtfVtprbnJlRaDBY9oRkZg8WRNkFqSISqmjX16bPExFAONgO7efceRVY725qYkjscQQ_7mii 4znQUtZKdemEh724idMufncBXRzYTe0y4l9fXpyVpoFa6qf6X.zurjZk1JfM6jLQzxX7zoLTJP4G jbd_OEyMHU6SwNq3lJSI7VyrCCDOUU9l1ah6uCGY9yc4XnnEOM2Mjsmc- X-Sonic-MF: X-Sonic-ID: 4826c39b-9745-4f25-ac01-68089998caa9 Received: from sonic.gate.mail.ne1.yahoo.com by sonic322.consmr.mail.gq1.yahoo.com with HTTP; Thu, 24 Jul 2025 23:59:46 +0000 Date: Thu, 24 Jul 2025 23:59:33 +0000 (UTC) From: "sivapostgres@yahoo.com" Reply-To: "sivapostgres@yahoo.com" To: Merlin Moncure , Laurenz Albe , Adrian Klaver Cc: Pgsql-general Message-ID: <26359739.2491053.1753401573405@mail.yahoo.com> In-Reply-To: References: <1453510076.1900935.1753260637232.ref@mail.yahoo.com> <1453510076.1900935.1753260637232@mail.yahoo.com> <2129916799.2277829.1753359532611@mail.yahoo.com> Subject: Re: Is there any limit on the number of rows to import using copy command MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_2491052_959831396.1753401573403" X-Mailer: WebService/1.1.24187 YMailNorrin Content-Length: 10929 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------=_Part_2491052_959831396.1753401573403 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable 1.=C2=A0 Testcase.=C2=A0 Created a new database, modified the triggers (sp= lit into three), populated required master data, lookup tables.=C2=A0 Then = transferred 86420 records. Checked whether all the 86420 records inserted i= n table1 and also whether the trigger created the required records in table= 2.=C2=A0 =C2=A0Yes, it created. 2.=C2=A0 In the test case above, the total time taken to insert 86420 recor= ds is 1.15 min only.=C2=A0 =C2=A0Earlier (before splitting the triggers) we= waited for more than 1.5 hrs first time and 2.5 hrs second time with no re= cords inserted. 3.=C2=A0 Regarding moving the logic to procedure.=C2=A0 Won't the trigger w= ork?=C2=A0 Will it be a burden for 86420 records?=C2=A0 It's working, if we= insert few thousand records.=C2=A0 After split of trigger function, it's w= orking for 86420 records.=C2=A0 Are triggers overhead for handling even 100= 000 records?=C2=A0 In production system, the same (single) trigger is worki= ng with 3 millions of records.=C2=A0 There might be better alternatives to = triggers, but triggers should also work.=C2=A0 IMHO. 4.=C2=A0 Staging tables.=C2=A0 Yes, I have done that in another case, where= there was a need to add data / transform for few more columns.=C2=A0 It wo= rked like a charm.=C2=A0 In this case, since there was no need for any othe= r calculations (transformation), and with just column to column matching, I= thought copy command will do. Before splitting the trigger into three, we tried1.=C2=A0 Transferring data= using DataWindow / PowerBuilder (that's the tool we use to develop our fro= nt end).=C2=A0 With the same single trigger, it took few hours (more than 4= hours, exact time not noted down) to transfer the same 86420 records.=C2= =A0 (Datawindow fires insert statements for every row).=C2=A0 Works, but th= e time taken is not acceptable. 2.=C2=A0 Next, we split the larger csv file into 8, with each file containi= ng 10,000 records and the last one with 16420 records.=C2=A0 Copy command w= orked.=C2=A0 Works, but the time taken to split the file not acceptable.=C2= =A0 We wrote a batch file to split the larger csv file.=C2=A0 We felt batch= file is easier to automate the whole process using PowerBuilder. 3.=C2=A0 What we observed here, is insert statement succeeds and copy comma= nd fails, if the records exceed a certain no.=C2=A0 Haven't arrived the exa= ct number of rows when the copy command fails. Will do further works after my return from a holiday. Happiness Always BKR Sivaprakash On Thursday 24 July, 2025 at 08:18:07 pm IST, Adrian Klaver wrote: =20 =20 On 7/24/25 05:18, sivapostgres@yahoo.com wrote: > Thanks Merlin, adrain, Laurenz >=20 > As a testcase, I split the trigger function into three, one each for=20 > insert, update, delete, each called from a separate trigger. >=20 > IT WORKS!. It worked before, it just slowed down as your cases got bigger. You need=20 to provide more information on what test case you used and how you=20 define worked. >=20 > Shouldn't we have one trigger function for all the three trigger=20 > events?=C2=A0 Is it prohibited for bulk insert like this? No. Triggers are overhead and they add to the processing that need to be=20 done for moving the data into the table. Whether that is an issue is a=20 case by case determination. >=20 > I tried this in PGAdmin only, will complete the testing from the program= =20 > which we are developing, after my return from holiday. From Merlin Moncure's post: "* reconfiguring your logic to a procedure can be a better idea; COPY your data into some staging tables (perhaps temp, and indexed), then write to various tables with joins, upserts, etc." I would suggest looking into implementing the above. >=20 > Happiness Always > BKR Sivaprakash >=20 --=20 Adrian Klaver adrian.klaver@aklaver.com =20 ------=_Part_2491052_959831396.1753401573403 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
1.  Testcase.  Cre= ated a new database, modified the triggers (split into three), populated re= quired master data, lookup tables.  Then transferred 86420 records. Ch= ecked whether all the 86420 records inserted in table1 and also whether the= trigger created the required records in table2.   Yes, it create= d.

2.  In the test case above, the total time taken = to insert 86420 records is 1.15 min only.   Earlier (before split= ting the triggers) we waited for more than 1.5 hrs first time and 2.5 hrs s= econd time with no records inserted.

3.  Regarding m= oving the logic to procedure.  Won't the trigger work?  Will it b= e a burden for 86420 records?  It's working, if we insert few thousand= records.  After split of trigger function, it's working for 86420 rec= ords.  Are triggers overhead for handling even 100000 records?  I= n production system, the same (single) trigger is working with 3 millions o= f records.  There might be better alternatives to triggers, but trigge= rs should also work.  IMHO.

4.  Staging tables.=   Yes, I have done that in another case, where there was a need to add= data / transform for few more columns.  It worked like a charm. = In this case, since there was no need for any other calculations (transfor= mation), and with just column to column matching, I thought copy command wi= ll do.

Before splitting the trigger into three, we tried<= /div>
1.  Transferring data usin= g DataWindow / PowerBuilder (that's the tool we use to develop our front en= d).  With the same single trigger, it took few hours (more than 4 hour= s, exact time not noted down) to transfer the same 86420 records.  (Da= tawindow fires insert statements for every row).  Works, but the time = taken is not acceptable.

2.  Next, we split the larg= er csv file into 8, with each file containing 10,000 records and the last o= ne with 16420 records.  Copy command worked.  Works, but the time= taken to split the file not acceptable.  We wrote a batch file to spl= it the larger csv file.  We felt batch file is easier to automate the = whole process using PowerBuilder.

3.  What we observ= ed here, is insert statement succeeds and copy command fails, if the record= s exceed a certain no.  Haven't arrived the exact number of rows when = the copy command fails.

Will do further works after my re= turn from a holiday.

=
Happiness Always
BKR Sivaprakash<= /div>



=20
=20
On Thursday 24 July, 2025 at 08:18:07 pm IST, Adria= n Klaver <adrian.klaver@aklaver.com> wrote:


=20 =20
On 7/24/25 05:18, si= vapostgres@yahoo.com wrote:
> Thanks Merlin, adrai= n, Laurenz
>
> As a testcase, I = split the trigger function into three, one each for
>= insert, update, delete, each called from a separate trigger.
>
> IT WORKS!.

It worked before, it just slowed down as your cases got bigger. You = need
to provide more information on what test case you u= sed and how you
define worked.

>
> Shouldn't we have one trigger func= tion for all the three trigger
> events?  Is it = prohibited for bulk insert like this?

= No. Triggers are overhead and they add to the processing that need to be done for moving the data into the table. Whether that is a= n issue is a
case by case determination.

>
> I tried this in PGAdmin = only, will complete the testing from the program
> wh= ich we are developing, after my return from holiday.

From Merlin Moncure's post:

"* reconfiguring your logic to a procedure can be a better idea; COPY=
your data into some staging tables (perhaps temp, and in= dexed), then
write to various tables with joins, upserts,= etc."

I would suggest looking into im= plementing the above.


>
> Happiness Always
> BKR Sivaprakash

>


<= br clear=3D"none">--
Adrian Klaver
adrian.klaver@aklaver.com

------=_Part_2491052_959831396.1753401573403--