public inbox for [email protected]
help / color / mirror / Atom feedFrom: Laurenz Albe <[email protected]>
To: [email protected] <[email protected]>
To: Pgsql-general <[email protected]>
Subject: Re: Is there any limit on the number of rows to import using copy command
Date: Wed, 23 Jul 2025 12:16:35 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<[email protected]>
On Wed, 2025-07-23 at 08:50 +0000, [email protected] 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
view thread (6+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: Is there any limit on the number of rows to import using copy command
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox