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 1ueuv0-00GgPP-Do for pgsql-general@arkaria.postgresql.org; Thu, 24 Jul 2025 12:19:27 +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 1ueuuy-007qFt-Lq for pgsql-general@arkaria.postgresql.org; Thu, 24 Jul 2025 12:19:24 +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 1ueuuy-007qFk-1M for pgsql-general@lists.postgresql.org; Thu, 24 Jul 2025 12:19:24 +0000 Received: from sonic307-20.consmr.mail.sg3.yahoo.com ([106.10.241.37]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1ueuut-000b9Y-2Q for pgsql-general@postgresql.org; Thu, 24 Jul 2025 12:19:23 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1753359555; bh=3neDFpuXCBlft1ZnNslrtAliy+YGJqW5pm089tO+0Cc=; h=Date:From:Reply-To:To:Cc:In-Reply-To:References:Subject:From:Subject:Reply-To; b=Kc5bcT0jNWkDoPHFdho0CTpgJgDo31J1bwmsElNOfE4R5HhS8+DAFAh3dFidGBJw4fZBauugMc2Fku757bGTssKCpwRaaUNuRQ+9xDTbEIniH7jKDOauCL2YmNUwGc7km4MowSgvm3oqjCKN8oM/IUUz9BbCZxam98K+0KoBLHmsf3z5VdfgPoe8eQvZxBrP3eZPDTopJKcnRs8aVurnnBWY7myk2/rHW/r68KRm1YWs67BIEFNIRCRyAeon1OJ4VqDp13E5YJGeOeYzPbwpzeYWIkJf7rRWYI/Gjj4F0DapAHfhsyMfzF5AymVBFY5IYQwS6asnvLOclBaVsJ4PJQ== X-SONIC-DKIM-SIGN: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1753359555; bh=u07+aLfmqOxnT1+hl4CPKFU/fK/6mAOs+H9iPaVPXQE=; h=X-Sonic-MF:X-Sonic-MF:Date:From:To:Subject:From:Subject; b=pJEvc6HM+Ke535xvR633/yuaJgAVqQ7Gs8eVMMJ6IQDDRvubCGWSrORjITZNKxisg9RX/TyIorpKQJJtdDBfCb95Ao8azfs88aj6ueQi3oskgFZfmFH4m2SkAr0fIWNxeG+mH3jqMh3vP0Ec72llWfFHh7i81ZYAklhqi1R3F6COx5sPWDwn4VqenqfD/slKQ3wqjOEbOrGO35YmciT/VRfgIZl02G7UD9ehRgXF8fewLiPm+ti2QjAjwgGmo+2Uu3fbklRw2+yrlEvOL9rYG2wo0h9szGMjhxLnB+sDhFNakc0gmeDsHYED9WzLtwVRQzEy34d0PFcTTd4WEFtiig== X-YMail-OSG: P3hqdJQVM1m7wiahnP9bgXAEppen03EcNgFwegunZDp_YWb1OmpW.0iNvvmN6co sPb2QdX1p68VCyUZ8uJgFubpoT6wHS4fDi9ks8Z035.Yuc_4qIpUsuVNQqJ4.TC4dJZU9InfwAzm fQ5WJdtEISPY8v2c5Cw9rq2tgpMVUeqY7HJKgvcAH9ni9dRzMVRIE7N6vSr4txKPvg71P_KeYUwF g1f9gIdUQdI2Li6LoNJKWGj4zN5GmrMkgS2Hbxa1Wq2jSenWjqBlUZzd7sO.X0pExP.V_RXRW.4i Uj_MsM3pITNHAKzDAlDjeoV9uEqgmTZ.1m5YA2DTQfLLx7F_fuUpOuJQqabRfAT6iU02We1ECFoV ItOiXi2M1aXTvdFxX7woBQLws7O90HCZg_7QHY0zFnZNJu.tdJeMOisnrXxVLGKAm_nniWYf2LUk gRwOH_mYP4rI3sqYEp5uFUg1UF23lEgdzb7HqtVy3fdthzlCKhbOS6Bm4FqK.s5Vhkiogpd2xFuG EecVlTjpvk43Htw3drQA2jllVCytxBemmOGSfApwu2sLe9gZdaXr9FAIuPiKHcVLbZHwbm7Wyy0S Qw9DjH.vBhIYlTwyuUQ3jH4SIaFcUdX5Hhex2arhdqAYAz67ykSCfiL6ZdtidKSuB.e9Fq9pLir9 x36ijBcyj..D4kz9XNCkoDDXakpK.UyXD0cEAMtvF15oFR3xirMyWbfTLrL1lRSrGFdmfl2IZ.87 J1mlHV4TictbMHgG2E8QuGeXx0A1jeAPgrU36YNkemquyHTBdFv1HTDD6oo9CpYdU5kTFke2vsf6 LmIomWowu3_3qWvbKWMxCnPJS1.m9PUfckwPl2iug6yEMY7cFCE0EmtNavE0pDxarXBgldk6NzOj Dk6U2Xv55.9gwRD.FRULKeJoHQIBqJFwweFyHCE2Kdd_TdgVtqo0QmLmHXBTVsk4JqYaskdtM3P6 50ePMXdZrBiRpHKtofVp0U_mbjq.6lLHOJG_MZ5TnISQrL9oowmyMr5bg7RjaDNAS7ZYc5z83H04 QTHGWaujGpusRwNGvnJ.QQK5kdrMe.fgsNma9s2dlrGgUspB9Sf91mihZIME37.OgUvdXNdDEbVa Il3niwpd8BavALVZ5lw9veJszjbJS8WaEeL.yyALaZ3MsFUwMEskm68h3KV3xkcawrlCmGIyrjSQ fE9id3VR2YogHY54MAT7_DLr85e7.zIE7pVqbF0vUYTi2k23S0Jt5NhAmOxeWv4VHaXsDUa5XLDR UXVQBxEhJh2x_qlvgal8ASDf4XZCtGzw1n.j81EI8cuflURp74TWaklzKCLY81W4rRfuUorluiKV hFN.abEa.37DYJIUfBneNkC19ghg8P9W2xsR4kQn3nm8BsMNn07Lt3_TsdLBmSEE5u9i4xv2CGkt K8_QhSrZs4S1oUIO4FSHSek_1tGaYOKAbyrvxiDKLrMNOQqqSJJy6itsKwx6tlJ5Ne43dSy8R5a. fWFPXI_bpmSNgqa0dlXFpgDw0nI4_NUZ427IcS9HaLyNKGl5bVfAY7nhM40lufc7HFBbwlTUDlaB TBqQD15mOmEXiEx28y3F78qPHhTHBLJTjfXHN1uPrtBjtVIRwcbVaVfPl9WKxy7VPCHXMM5eIGit X45De74A.EWTo7uM0VIa0Q2HFAwqiprXUzRyNb_BOaQgdtLxH9_qwDuIXx1kU2UDq1kxKFVtNjLH tNqFaTQnnMrR5BVCBCIfjHellTX8VUtpHasqRoVBfDKO3yzlaQrh4KlynMAPOkm071ZotjEgt17P dDkJfEwZZkvmZxOuUxqjgbFh0w1bDzhaYzBDKKNvAEMDBROqHGLpDn3aiXFuQjoAKBmLvS4Cc_t0 D.WA34h1d2eMlwFa6PDt2Deeubg_fqNLemA1dd.laE8HlsGgOPAe5PuaWONmljoDD1XtM9_dQvCd NdKukWIpEn5_Op75GOw5LaBS4Y.rOR66m.bIZ0Or7qjdEQqfy9jgfziV0UcL.20fuBtn5URn6aU_ h.lsXWclv8H6iAV88CBrYGHHMh6qT04qT0uppWmnHq9vZfwA7gWW9.4aY8Iqalt9K4TWU8kpKao1 z6lKvZZ9OX8G3X6o.4Jr02EmQ.j67z_bYP.HJ3GmsoOcBXPrHsc8kSnswRDjv2Ua4iDagaM2JrLH KYFM4UI21EX9yJEoGE2VngZrcbCaZ8SBBtSn1Mhv6huTkQdhG2GynV0kBjRxPCotCv8SBNuypKcM qeTJmQOjxN6NTuEmyFAc6ezUhT01AZrYN2Lf8OTB_CHz_LPFFKdd13p3Y1fuptB1rf7LRO4bKSs8 92EbZ6BhkHAjaXn89gVBeijfkQx0ixo0- X-Sonic-MF: X-Sonic-ID: 8863f6c0-03fd-491f-a86e-0c2d292979d6 Received: from sonic.gate.mail.ne1.yahoo.com by sonic307.consmr.mail.sg3.yahoo.com with HTTP; Thu, 24 Jul 2025 12:19:15 +0000 Received: by hermes--production-gq1-74d64bb7d7-6nlps (Yahoo Inc. Hermes SMTP Server) with ESMTPA ID e16e15eab8c831a696fb1edd7f22b7ff; Thu, 24 Jul 2025 12:19:10 +0000 (UTC) X-YMail-OSG: oMEtjrcVM1nP_.zffNRXZ1mosi66zt6Ne4ogTpS6k3pzJJ2QYKP0y1pypN8v1BO Hk4tjAkOoA.d0egTm9UgRbGnF5mYAG8YA8GMaLnc06mr7UvgY81RT8iQTCi2RB.joN4cuC5lG6.Y uEuLAFuMcjAIldae.heAHkG4Q0BflZB6gUNkMzNwlydlWFH0s9TsbYoNeWM1h1iMQYyV.bvNNt5x O0kKfJKKxkZ.kOFiZfIf1KbSV_v3TCMC5DaW1dmmCdwFeFzMBTybn99JQ_gmw3kCRnfYcnuv9P6Z Vfwq5l.9UCa3OK8otZeEo_hV9nA.BUDQYYWCVHZLiRZYfZqNOzaWhyYynQqsqsg_czijNPM4O6Qq 3RlaLGcsGKg8Zu_3zYaPmVzqv7RoIO.xrLQZXB9b_8lh2VSVhiQVd1sNaooj2YHEI.AgXdKk4mLQ o2wkp9SJBq1D6pyRkTXbACT9yfpS.8V18futpmAI77BygyNr7wECKAbGMhXDhXfjnr48bT5OBZc9 iFgUmvSAgMfTZggZePavHBbnmbnlDJ5VlaHAPQpRe3U4sGcAosF8IcKz7rw2AW9Up2fogsMinFlS mPGzM0QPYG6JiotpGunsYOFG3_KpuUU4y5O4N8vQUkG87IqIbigb5.hfUxy3Tk.1GqRxRhftyu2a OBxevIsRq5AjoX7FYOhA4.NdOJfjeN8VEwaBdQ2K8tYhhrfRiOdcMvEDE58SU.cSNAs5yM4hT6Gc pVitfG3wxBem7OIUHN8WUQjHesVKkNn2loKY_UNZII.uLYSGY871MvuBhVSLgV2dVPpiXhoS3QJD LqVEl58KcUhMLl17EEqQSpSgbTE_wKk5XqX62EY.O3tmbBypqPyA9lb4br52Epee6Efxc.mPmB5t UQaIY_Vwgckva9hou1ozFHBRZ9Mgfyn5fRC7RxllyOxgA75wYUSWfFkaBk5kdndOn7X5F6GkO76C F0dY70P.yE_1kLRMD1ObPFfXM.XUYZ_loximcT0sUPhBsClE87bYKiSpAUE5w0.qhvdlfFsixkE3 gs1APpJPXr_BOHLlNvKBjZHGDoaf.EndYhZA2BwOrlO3COPt14hbDB.qFvT0v4pm8bZ2aPNKBuIv O_VniJnCFi2bmMz5B_qZKYjs1tnAlFjZEwFtPW4umK4OkPZm4IqK9vR6cX3zbdqgD3jLiTeB.qq4 NPzb2hWufsDQ.h1qo5F7loZDA9bpOr4hQFpykgAZWtIuHbLCdFb5N.sQ4QztKQydMPybX_yCwrZf dceWPK944mIIbGWOjteyjqaFBv4fdlvCzvB3LptvBEyT6EccJ.FyGhSgiGCefJhrlWYTKnaTzv7n 5mA.c_lh565PXl_lOJkljSiLp5o5smW8hxmepFIb4pdqnzTAD_XkuY0syGyAkMOXnsgibnpMjFNM iGA.gZ8cgsF177zuj6cDu07aVDsMNDVLSSKb81hTZvyR9xIlAtBdwA9OL0Hz3LiHrV7kLmzf3ET8 PDVWktCw2XPrzdvGTaIz2_lgElOyE7q8sn80.7hyhzALaUapywyhxkdGGkq5kQ_DhHSLVjw.UoWD rYCmHJQsPpr1_7uzFBZgJlGqxdI6eb6RgshbRdyQf.O_TLjdbneHw_GL7O2KhB3EHdsecP4o89E. bSM8YnUcV.U0prDap5zS1q6xfcmoopSDQwEAOOucJeyMVsjSCw06Nk2ffaJkZbQWfALUv6Am8hsD BmgD1szOXRg_4dE3EpjhbwS9tp9F0B7hJM8NoIDCTb0BHfz.XdVwawH7gRJjWkAaW.6XQqMs8qMd .q9zS8pF.hho39eY3vXvIkZ1_Cc8OZvUnnF2Lnf5d8e9hkpjR9PnqmMYJ2B6Vey_ijDrCoYf9TTG 2XD5uNsCNcCNzy_4Zb8oK.VNMSMfCOMlUxVerADUO.6h4b1ysh7AiDE4fQ82B_fJFOVm6uBRpr9Y QV6QeFneIFVYr7Nu3G.vtbkRhLaMeEvKrJ002dtHRB1VNooLrfaqvUCIGKNlIf8sDM7MXZnLLMYx UaEvyQlUo8IiDoWaMVoy1yXX6oCJ1SGEINeYMdnmzCjn0Fn9RBycE_JcIyV.zCMt03ucgQi3wTDE 48nmdVie27scj2j_JVNMzgJ4hQDN8JWJfrVvWicF83lbQmD_AYERTCQvcnCdwodjwWxAC1n9MIgI rBJYevGOhuP3zSkuW0N41UFpgHjrnYKRe9.4S0Po1YrC6kZ4eqdP2aUyac1R0JRCE6X3lNmQxL88 GjjMo_tF.q7lIT8SSczdpIq.CHz4Tv20lai1kyMiEKJv.AOs9ms8OgKG2SYzYDkgWSoGuxE5r1ks Ew4MrMAWQ5b7SoggNdwocXFzmAat7sGgEJ1GAZ1dxko0FntpPAL8gMRwZcAF6g9S3h4834vF6Sog 4ln4A0P3FSDo- X-Sonic-MF: X-Sonic-ID: b0b669be-aeb1-46c5-9e69-6dbf57eeb4a8 Received: from sonic.gate.mail.ne1.yahoo.com by sonic304.consmr.mail.gq1.yahoo.com with HTTP; Thu, 24 Jul 2025 12:19:09 +0000 Date: Thu, 24 Jul 2025 12:18:52 +0000 (UTC) From: "sivapostgres@yahoo.com" Reply-To: "sivapostgres@yahoo.com" To: Merlin Moncure , "adrian.klaver@aklaver.com" , Laurenz Albe Cc: Pgsql-general Message-ID: <2129916799.2277829.1753359532611@mail.yahoo.com> In-Reply-To: References: <1453510076.1900935.1753260637232.ref@mail.yahoo.com> <1453510076.1900935.1753260637232@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_2277828_495571915.1753359532610" X-Mailer: WebService/1.1.24187 YMailNorrin Content-Length: 9757 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------=_Part_2277828_495571915.1753359532610 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Thanks Merlin, adrain, Laurenz As a testcase, I split the trigger function into three, one each for insert= , update, delete, each called from a separate trigger.=C2=A0=C2=A0 IT WORKS!. Shouldn't we have one trigger function for all the three trigger events?=C2= =A0 Is it prohibited for bulk insert like this? I tried this in PGAdmin only, will complete the testing from the program wh= ich we are developing, after my return from holiday. Happiness Always BKR Sivaprakash On Wednesday 23 July, 2025 at 11:20:03 pm IST, Merlin Moncure wrote: =20 =20 On Wed, Jul 23, 2025 at 2:51=E2=80=AFAM sivapostgres@yahoo.com wrote: > > Tried in PostgreSQL 11.11 , PostgreSQL 15.2 in Windows 10 > > Here we try to transfer data from one database to another (remote) databa= se. > > Tables do have records ranging from 85000 to 3600000 along with smaller s= ized tables. > No issues while transferring smaller sized tables. > > I here take one particular table [table1] which has 85000 records. > The table got Primary Key, Foreign Key(s), Triggers.=C2=A0 Trigger update= s another table [table2] > Table2 have 2 triggers, one to arrive a closing value and other to delete= , if the closing value is zero. > > 1.=C2=A0 Transfer the data from source database to a csv file.=C2=A0 8500= 0 records transferred. No issues. > 2.=C2=A0 Transfer the file to the remote location.=C2=A0 No issues. > 3.=C2=A0 Transfer the contents of the file to the table using Copy From c= ommand. - Fails when try to transfer all the 85000 records at once. > > Copy from command is > > Copy public.table1 From 'E:\temp\file1.csv' (FORMAT CSV, DELIMITER ',', H= EADER TRUE) > > The above command succeeds, when > 1.=C2=A0 The trigger in Table1 is disabled with all other constraints on. > 2.=C2=A0 The no. of rows is within 16000 or less, with Trigger enabled.= =C2=A0 We haven't tried with higher no of rows. > > The above command goes on infinite loop, when > 1.=C2=A0 We try to transfer all 85000 rows at once, with Trigger and othe= r constraints in table1 enabled.=C2=A0 We waited for 1.5 hrs first time and= 2.5 hrs second time before cancelling the operation. > > I read in the documentation that the fastest way to transfer data is to u= se Copy command.=C2=A0 And I couldn't find any limit in transferring data u= sing that command.=C2=A0 One could easily transfer millions of rows using t= his command. Most likely, you are getting yourself into trouble with the trigger dependencies.=C2=A0 Triggers are powerful, but also can be dangerous, and this could be 'wrong tool for the job' situation. Here are some general tips: * pg_trigger_depth(): can tell you if trigger A calls trigger B and back to trigger A, etc.=C2=A0 you can use it with raise notify, and also use it to guard execution on CREATE TRIGGER * reconfiguring your logic to statement level triggers can be a good idea. this can take some thinking, but can be much more efficient when bulk processing since trigger execution can be deferred until the load completes. (one trick is to use now() to check for records inserted since it is stable though the transaction) * 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. merlin =20 ------=_Part_2277828_495571915.1753359532610 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Thanks Merlin, adrain, Laure= nz

As a testcase, I split the trigger function into three= , one each for insert, update, delete, each called from a separate trigger.=   

IT WORKS!.

Shouldn't we ha= ve one trigger function for all the three trigger events?  Is it prohi= bited for bulk insert like this?

I tried this in PGAdmin = only, will complete the testing from the program which we are developing, a= fter my return from holiday.
Happiness Always
BKR Siva= prakash

=20
=20
On Wednesday 23 July, 2025 at 11:20:03 pm IST, Merl= in Moncure <mmoncure@gmail.com> wrote:


=20 =20
On Wed, Jul 23, 2025 at 2:51=E2=80=AF= AM sivapostgres@yahoo.com

<sivapostgres@yahoo.com> wrote:
>
> Tried in PostgreSQL 11.11 , PostgreSQL 15.2 in Windows 10>
> Here we try to transfer data fr= om one database to another (remote) database.
>
> Tables do have records ranging from 85000 to 3600000 along= with smaller sized tables.
> No issues while transfer= ring smaller sized tables.
>
> I = here take one particular table [table1] which has 85000 records.
> The table got Primary Key, Foreign Key(s), Triggers.  T= rigger updates another table [table2]
> Table2 have 2 = triggers, one to arrive a closing value and other to delete, if the closing= value is zero.
>
> 1.  Tran= sfer the data from source database to a csv file.  85000 records trans= ferred. No issues.
> 2.  Transfer the file to the= remote location.  No issues.
> 3.  Transfer= the contents of the file to the table using Copy From command. - Fails whe= n try to transfer all the 85000 records at once.
>
> Copy from command is
>
> Copy public.table1 From 'E:\temp\file1.csv' (FORMAT CSV, DELIMI= TER ',', HEADER TRUE)
>
> The abo= ve command succeeds, when
> 1.  The trigger in Ta= ble1 is disabled with all other constraints on.
> 2.&n= bsp; The no. of rows is within 16000 or less, with Trigger enabled.  W= e haven't tried with higher no of rows.
>
> The above command goes on infinite loop, when
= > 1.  We try to transfer all 85000 rows at once, with Trigger and o= ther constraints in table1 enabled.  We waited for 1.5 hrs first time = and 2.5 hrs second time before cancelling the operation.
= >
> I read in the documentation that the fastest wa= y to transfer data is to use Copy command.  And I couldn't find any li= mit in transferring data using that command.  One could easily transfe= r millions of rows using this command.


Most likely, you are getting yourself into trouble with the trigger<= br clear=3D"none">dependencies.  Triggers are powerful, but also can b= e dangerous, and
this could be 'wrong tool for the job' s= ituation.

Here are some general tips:=

* pg_trigger_depth(): can tell you if= trigger A calls trigger B and
back to trigger A, etc.&nb= sp; you can use it with raise notify, and also
use it to= guard execution on CREATE TRIGGER

* r= econfiguring your logic to statement level triggers can be a good
idea. this can take some thinking, but can be much more efficient= when
bulk processing since trigger execution can be defe= rred until the load
completes. (one trick is to use now()= to check for records inserted
since it is stable though = the transaction)

* 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.

merlin

------=_Part_2277828_495571915.1753359532610--