Message-ID: <de08fd016dd9c630f65c52b80292550e0bcdea4c.camel@cybertec.at>
Subject: Re:  Is there any limit on the number of rows to import using copy
 command
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: "sivapostgres@yahoo.com" <sivapostgres@yahoo.com>, Pgsql-general
	 <pgsql-general@postgresql.org>
Date: Wed, 23 Jul 2025 12:16:35 +0200
In-Reply-To: <1453510076.1900935.1753260637232@mail.yahoo.com>
References: <1453510076.1900935.1753260637232.ref@mail.yahoo.com>
	 <1453510076.1900935.1753260637232@mail.yahoo.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.56.2 (3.56.2-1.fc42) 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/de08fd016dd9c630f65c52b80292550e0bcdea4c.camel%40cybertec.at>
Precedence: bulk

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) databa=
se.=C2=A0
>=20
> Tables do have records ranging from 85000 to 3600000 along with smaller s=
ized tables.
> No issues while transferring smaller sized tables.
>=20
> 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.
>=20
> 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.=C2=A0=
=C2=A0
>=20
> Copy from command is
>=20
> Copy public.table1 From 'E:\temp\file1.csv' (FORMAT CSV, DELIMITER ',', H=
EADER TRUE)
>=20
> 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.
>=20
> 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.
>      We waited for 1.5 hrs first time and 2.5 hrs second time before canc=
elling the operation.
>=20
> I read in the documentation that the fastest way to transfer data is to u=
se 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 COPY.

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.
>=20
> Trigger function, which is called from Table1 on After Insert, Update, De=
lete

One thing you could try is a BEFORE trigger.  That should work the same, un=
less
there are foreign key constraints.  Do you see high memory usage or paging =
for
the backend process when the COPY hangs?

> [...]
> If (Select Count(*)
> =C2=A0From=C2=A0 =C2=A0table2
> =C2=A0WHERE=C2=A0 companycode =3D company_code
> =C2=A0AND=C2=A0 =C2=A0 branchcode=C2=A0 =3D branch_code
> =C2=A0AND=C2=A0 =C2=A0 locationfk=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, 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