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 1uezeS-000gt0-PN for pgsql-general@arkaria.postgresql.org; Thu, 24 Jul 2025 17:22:41 +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 1uezeQ-009HiL-J2 for pgsql-general@arkaria.postgresql.org; Thu, 24 Jul 2025 17:22:38 +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 1ueZqg-002SRg-S3 for pgsql-general@lists.postgresql.org; Wed, 23 Jul 2025 13:49:35 +0000 Received: from mail-oo1-xc33.google.com ([2607:f8b0:4864:20::c33]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1ueZqd-000QZT-0n for pgsql-general@postgresql.org; Wed, 23 Jul 2025 13:49:34 +0000 Received: by mail-oo1-xc33.google.com with SMTP id 006d021491bc7-617a9a4e72cso448345eaf.0 for ; Wed, 23 Jul 2025 06:49:32 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=amiralearning.com; s=google; t=1753278570; x=1753883370; darn=postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=SKjCrsPZS/xpALy/eaBY9LDHwn1iYoij1YZuhLqiQgw=; b=LkwFqPsDMDq0QaJqC/mt8N7yMLQKJN50l13kw8nitggAXgiEf3mmiIr9rUO8N/DXa/ nSmkBx1OO5SdqZivzsAQRZOwbVJnU+ikC2L6v2dvId7KHZH9qWAdrU9GVkKoLv2pYfVK +oar+84T/jzbqlBTX3SzDq5axl0cYx2CtrFmQo/xm7S/zOySe70RNuwiHNWKouDTevqo 0kuJx7Je2foMKOGWxkiEfE1t2g086pAYewUV4iGcjF+u9M7IzoOdoJl7tg3v14Q3SyiG Aso3V3LQxFhREkFOT4U8glgkVSUU2YVVqLCnkpgipPV9ShZzuy0sfE6dMMpgy5laa3Mc DhRA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1753278570; x=1753883370; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=SKjCrsPZS/xpALy/eaBY9LDHwn1iYoij1YZuhLqiQgw=; b=qRAI4O+7Crs0MrgJXCrlpNJd3IjXL2MtHkPc/B9A0B18MnMPYU3l4JxMrkBZayxzjZ 9si8eFn/fFDOZL+l0xMvAsOgIyimOi6uCnvl9oPp8Xu7zYt8PweqPjWynxOKkQ4CSNLv Tjcbqqvc1Ayy3srZjQVRgr7JrE9qx0Bm6ul8jxGtuaPDQ71cLXMeK1JiJlVboZHSgD+F 0whHSa0RSRDAWzWp3Zh2OqMvKG3CENhuBbX5/jqn4QhCplMjGELn8u8eEyPbQ3tXRTPS aLyuyHkOGrjsvmEgA/Jq9OZSBlQHfVEBwXYaViGuQuFJNY48Zhd4wOkXhB6Yzn+pMfk+ kwuw== X-Gm-Message-State: AOJu0Yw1ETVnfneZGN15EKHKM9S+sC9tlYXk/5DIZAX7/h1sRNl5n5OC YrdY6oc9oktMy4jpXFvrtBPWTrE0dFn+wmYCsUVpMeDyHeAPkvglgceyGMSPpwb6B0tD+6DeNPg yuVazm7gtYvIIYBZXNZU1zlLKkGGDzAxjocGwagOQg1kR1AzH98Q= X-Gm-Gg: ASbGncuisRNmnCLtvvzUvrlHhAn7PyiHtzNf4op674HiRk14ySbvNb+0Nsyo8xHNIJ0 dtgsueANGykEq7N3ACFX3DhmH8T+gS+pdHW9eknwJO4yvqY9o73VEEh98WzleYilOM98iBARRWb Rdf7vwjBB02PR1MPchrK2u0REZ5iceKIb22lfCZoN31iNbvYTcNCdlzCUJIWBmtPeChBeAc6ntJ Vfive6bpc1crkT+F54= X-Google-Smtp-Source: AGHT+IH9xFJClIkDRlNdP0FoQbZFPFya7zSwmGqHid69U6yQv/2oyy9n5A8yrq9JLHhoFgwLortLgstwX1r2YRwWji8= X-Received: by 2002:a05:6820:4a08:b0:611:74c1:6771 with SMTP id 006d021491bc7-615ee723197mr4494292eaf.3.1753278570306; Wed, 23 Jul 2025 06:49:30 -0700 (PDT) MIME-Version: 1.0 References: <1453510076.1900935.1753260637232.ref@mail.yahoo.com> <1453510076.1900935.1753260637232@mail.yahoo.com> <959901171.1916220.1753271729336@mail.yahoo.com> In-Reply-To: <959901171.1916220.1753271729336@mail.yahoo.com> From: David Barbour Date: Wed, 23 Jul 2025 08:49:18 -0500 X-Gm-Features: Ac12FXx4EsysCABVulU8RYnNZLYCBPnhCAvf2dbSUToMvWQi6MJHgpETstsF77k Message-ID: Subject: Re: Is there any limit on the number of rows to import using copy command To: "sivapostgres@yahoo.com" Cc: Pgsql-general , Laurenz Albe Content-Type: multipart/alternative; boundary="000000000000603ba4063a98fae4" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000603ba4063a98fae4 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable You might also consider Backup/Restore. It appears you're not concerned with data being inserted into the source table after the backup is complete? If so, you can then easily sync the two post restore. On Wed, Jul 23, 2025 at 6:55=E2=80=AFAM sivapostgres@yahoo.com < sivapostgres@yahoo.com> wrote: > Thanks Laurenz Albe, > > 1. I tried running Copy From command from PGAdmin. > 2. I ran pg_stat_activity also in another tab [ PGAdmin ]. > > What I observed, > 1. In about 2 min, in the Dashboard of PGAdmin, the colour changed to > Orange for that particular pid. > 2. After few seconds, the colour again changed to Red. > 3. The stat column in both Dashboard and pg_stat_activity.state shows > 'active'. > 4. No messages relevant to this pid in postgres' log files > 5. CPU usage is normal, hovering around 36% overall, 15-18% for > postgresql server, from the beginning and even after 3 min. > 6. We didn't run any other application in that machine. > 7. Unique Index is there in table2 which will return only one row for > that Select Count(*) query. > 8. No record is there in the target table when transfer started. This > transfer is the first batch for that particular table. > 9. Once color turned into Red, I could cancel the query execuion in > PGAdmin, which immediately stops the execution. > 10. I could not see any locks or blocking pids. > > Happiness Always > BKR Sivaprakash > > On Wednesday 23 July, 2025 at 03:46:40 pm IST, Laurenz Albe < > laurenz.albe@cybertec.at> wrote: > > > On Wed, 2025-07-23 at 08:50 +0000, sivapostgres@yahoo.com wrote: > > Tried in PostgreSQL 11.11 , PostgreSQL 15.2 in Windows 10 > > Both of these choices are unsavory. Don't use the unsupported v11, > and use 15.13 with v15. > > > Here we try to transfer data from one database to another (remote) > database. > > > > Tables do have records ranging from 85000 to 3600000 along with smaller > sized 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. Trigger 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. Transfer the data from source database to a csv file. 85000 record= s > transferred. 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 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 ',', > HEADER TRUE) > > > > The above command succeeds, when > > 1. The trigger in Table1 is disabled with all other constraints on. > > 2. The no. of rows is within 16000 or less, with Trigger enabled. We > 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 other > 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 way to transfer data is to > use Copy command. > > And I couldn't find any limit in transferring data using that command. > > One could easily transfer millions of rows using this command. > > There is no limit for the number of rows that get created by a single COP= Y. > > You should research why processing fails for higher row counts: > - Are there any messages on the client or the server side? > - Is the backend process on the server busy (consuming CPU) when > processing hangs? > - Do you see locks or other wait events in "pg_stat_activity"? > > > Here are the triggers. > > > > Trigger function, which is called from Table1 on After Insert, Update, > Delete > > One thing you could try is a BEFORE trigger. That should work the same, > unless > there are foreign key constraints. Do you see high memory usage or pagin= g > for > the backend process when the COPY hangs? > > > [...] > > If (Select Count(*) > > From table2 > > WHERE companycode =3D company_code > > AND branchcode =3D branch_code > > AND locationfk =3D location_fk > > AND barcode =3D variety_code ) > 0 Then > > [...] > > That may well be slow, particularly without a matching index. > A better way to write that would be > > IF EXISTS (SELECT 1 FROM table2 > > WHERE ...) > > > because that can stop processing after the first match. > It still needs an index for fast processing. > > Yours, > Laurenz Albe > > --000000000000603ba4063a98fae4 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
You might also consider Backup/Restore.=C2=A0 It appears y= ou're not concerned with data being inserted into the source table afte= r the backup is complete?=C2=A0 If so, you can then easily sync the two pos= t restore.

On Wed, Jul 23, 2025 at 6:55=E2=80=AFAM sivapostgres@yahoo.com <sivapostgres@yahoo.com> wrote:<= br>
Thanks Laurenz Albe,

1.=C2=A0 I tried running Copy From command from PGAdmin= .=C2=A0=C2=A0
2.=C2=A0 I ran pg_stat_activity also in= another tab [ PGAdmin ].

= What I observed,
1.=C2=A0 In about 2 min, in the Dash= board of PGAdmin, the colour changed to Orange for that particular pid.
2.=C2=A0 After few seconds, the colour again changed to = Red.=C2=A0 =C2=A0
3.=C2=A0 The stat column in both Da= shboard and pg_stat_activity.state shows 'active'.
4.=C2=A0 No messages relevant to this pid in postgres' log files<= /div>
5.=C2=A0 CPU usage is normal, hovering around 36% ove= rall, 15-18% for postgresql server, from the beginning and even after 3 min= .
6.=C2=A0 We didn't run any other application in= that machine.=C2=A0=C2=A0
7.=C2=A0 Unique Index is t= here in table2 which will return only one row for that Select Count(*) quer= y.
8.=C2=A0 No record is there in the target table wh= en transfer started.=C2=A0 This transfer is the first batch for that partic= ular table.
9.=C2=A0 Once color turned into Red, I co= uld cancel the query execuion in PGAdmin, which immediately stops the execu= tion.
10. I could not see any locks or blocking pids.=

Happiness Always
BKR S= ivaprakash

=20
=20
On Wednesday 23 July, 2025 at 03:46:40 pm IST, Laur= enz Albe <= laurenz.albe@cybertec.at> wrote:


=20 =20
On Wed, 2025-07-23 at 08:50 +0000, si= vapostgres@yahoo.com wrote:
> Tried in PostgreSQL = 11.11 , PostgreSQL 15.2 in Windows 10

= Both of these choices are unsavory.=C2=A0 Don't use the unsupported v11= ,
and use 15.13 with v15.

> Here we try to transfer data from one database to another (remot= e) database.=C2=A0
>
> Tables do= have records ranging from 85000 to 3600000 along with smaller sized tables= .
> No issues while transferring smaller sized tables.=
>
> I here take one particular = table [table1] which has 85000 records.
> The table go= t Primary Key, Foreign Key(s), Triggers.=C2=A0 Trigger updates another tabl= e [table2]
> Table2 have 2 triggers, one to arrive a c= losing 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 85000 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 command. - Fails when try to transfer all the = 85000 records at once.=C2=A0=C2=A0
>
> Copy from command is
>
> = Copy public.table1 From 'E:\temp\file1.csv' (FORMAT CSV, DELIMITER = ',', HEADER TRUE)
>
> Th= e 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 Tri= gger and other constraints in table1 enabled.
>=C2=A0 = =C2=A0 =C2=A0 We waited for 1.5 hrs first time and 2.5 hrs second time befo= re cancelling the operation.
>
>= I read in the documentation that the fastest way to transfer data is to us= e Copy command.
> And I couldn't find any limit in= transferring data using that command.
> One could eas= ily transfer millions of rows using this command.

There is no limit for the number of rows that get created by a = single COPY.

You should research why p= rocessing fails for higher row counts:
- Are there any me= ssages on the client or the server side?
- Is the backend= process on the server busy (consuming CPU) when processing hangs?
- Do you see locks or other wait events in "pg_stat_activity= "?

> Here are the triggers.>
> Trigger function, which is cal= led from Table1 on After Insert, Update, Delete

One thing you could try is a BEFORE trigger.=C2=A0 That should wo= rk the same, unless
there are foreign key constraints.=C2= =A0 Do you see high memory usage or paging for
the backen= d process when the COPY hangs?

> [.= ..]
> If (Select Count(*)
> =C2= =A0From=C2=A0 =C2=A0table2
> =C2=A0WHERE=C2=A0 company= code =3D company_code
> =C2=A0AND=C2=A0 =C2=A0 branchc= ode=C2=A0 =3D branch_code
> =C2=A0AND=C2=A0 =C2=A0 loc= ationfk=C2=A0 =3D location_fk
> =C2=A0AND=C2=A0 =C2=A0= barcode=C2=A0 =C2=A0 =C2=A0=3D variety_code ) > 0 Then
> [...]

That may well be slow, pa= rticularly without a matching index.
A better way to writ= e that would be

=C2=A0 IF EXISTS (SELE= CT 1 FROM table2

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 WHERE ...)


because that can stop processing after the first = match.
It still needs an index for fast processing.

Yours,
Laurenz Albe

--000000000000603ba4063a98fae4--