1. Testcase. Created a new database, modified the triggers (split into
three), populated required master data, lookup tables. Then transferred 86420
records. Checked whether all the 86420 records inserted in table1 and also
whether the trigger created the required records in table2. Yes, it created.
2. In the test case above, the total time taken to insert 86420 records is
1.15 min only. Earlier (before splitting the triggers) we waited for more
than 1.5 hrs first time and 2.5 hrs second time with no records inserted.
3. Regarding moving the logic to procedure. Won't the trigger work? Will it
be a burden for 86420 records? It's working, if we insert few thousand
records. After split of trigger function, it's working for 86420 records. Are
triggers overhead for handling even 100000 records? In production system, the
same (single) trigger is working with 3 millions of records. There might be
better alternatives to triggers, but triggers should also work. IMHO.
4. Staging tables. Yes, I have done that in another case, where there was a
need to add data / transform for few more columns. It worked like a charm. In
this case, since there was no need for any other calculations (transformation),
and with just column to column matching, I thought copy command will do.
Before splitting the trigger into three, we tried1. Transferring data using
DataWindow / PowerBuilder (that's the tool we use to develop our front end).
With the same single trigger, it took few hours (more than 4 hours, exact time
not noted down) to transfer the same 86420 records. (Datawindow fires insert
statements for every row). Works, but the time taken is not acceptable.
2. Next, we split the larger csv file into 8, with each file containing 10,000
records and the last one with 16420 records. Copy command worked. Works, but
the time taken to split the file not acceptable. We wrote a batch file to
split the larger csv file. We felt batch file is easier to automate the whole
process using PowerBuilder.
3. What we observed here, is insert statement succeeds and copy command fails,
if the records exceed a certain no. Haven't arrived the exact number of rows
when the copy command fails.
Will do further works after my return from a holiday.
Happiness Always
BKR Sivaprakash
On Thursday 24 July, 2025 at 08:18:07 pm IST, Adrian Klaver
<adrian.kla...@aklaver.com> wrote:
On 7/24/25 05:18, sivapostg...@yahoo.com wrote:
> 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!.
It worked before, it just slowed down as your cases got bigger. You need
to provide more information on what test case you used and how you
define worked.
>
> Shouldn't we have one trigger function for all the three trigger
> events? Is it prohibited for bulk insert like this?
No. Triggers are overhead and they add to the processing that need to be
done for moving the data into the table. Whether that is an issue is a
case by case determination.
>
> I tried this in PGAdmin only, will complete the testing from the program
> which we are developing, after my return from holiday.
From Merlin Moncure's post:
"* 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."
I would suggest looking into implementing the above.
>
> Happiness Always
> BKR Sivaprakash
>
--
Adrian Klaver
adrian.kla...@aklaver.com
