Re: Is there any limit on the number of rows to import using copy command

On Wed, 2025-07-23 at 08:50 +0000, sivapostgres(at)yahoo(dot)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 records 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 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.
>
> 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 paging for
the backend process when the COPY hangs?

> [...]
> If (Select Count(*)
>  From   table2
>  WHERE  companycode = company_code
>  AND    branchcode  = branch_code
>  AND    locationfk  = location_fk
>  AND    barcode     = 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