public inbox for [email protected]  

help / color / mirror / Atom feed

From: [email protected] <[email protected]>
To: Merlin Moncure <[email protected]>
To: [email protected] <[email protected]>
To: Laurenz Albe <[email protected]>
Cc: Pgsql-general <[email protected]>
Subject: Re: Is there any limit on the number of rows to import using copy command
Date: Thu, 24 Jul 2025 12:18:52 +0000 (UTC)
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAHyXU0x1uW_349ONE+35KT87Ua-dX8-QaZ5Sj6eENTMwsX=okw@mail.gmail.com>
References: <[email protected]>
	<[email protected]>
	<CAHyXU0x1uW_349ONE+35KT87Ua-dX8-QaZ5Sj6eENTMwsX=okw@mail.gmail.com>

 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.  
IT WORKS!.
Shouldn't we have one trigger function for all the three trigger events?  Is it prohibited for bulk insert like this?
I tried this in PGAdmin only, will complete the testing from the program which 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 <[email protected]> wrote:  
 
 On Wed, Jul 23, 2025 at 2:51 AM [email protected]
<[email protected]> wrote:
>
> Tried in PostgreSQL 11.11 , PostgreSQL 15.2 in Windows 10
>
> 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.

Most likely, you are getting yourself into trouble with the trigger
dependencies.  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.  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
  

view thread (5+ 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], [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