Re: Is there any limit on the number of rows to import using copy command - Mailing list pgsql-general
| From | sivapostgres@yahoo.com |
|---|---|
| Subject | Re: Is there any limit on the number of rows to import using copy command |
| Date | |
| Msg-id | 1453510076.1900935.1753260637232@mail.yahoo.com Whole thread Raw |
| Responses |
Re: Is there any limit on the number of rows to import using copy command
Re: Is there any limit on the number of rows to import using copy command Re: Is there any limit on the number of rows to import using copy command |
| List | pgsql-general |
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)
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.
Here are the triggers.
Trigger function, which is called from Table1 on After Insert, Update, Delete
Declare variety_code character varying(30);
Declare company_code character varying(10);
Declare branch_code character varying(10);
Declare location_fk character varying(32);
Declare opening_quantity numeric(17,3) ;
Declare modified_user character varying(50) ;
Declare modified_date timestamp without time zone ;
Declare modified_computer character varying(50);
BEGIN
If (TG_OP = 'INSERT') Then
company_code = NEW.companycode ;
branch_code = NEW.branchcode ;
location_fk = NEW.locationfk ;
variety_code = NEW.barcode ;
opening_quantity = Coalesce(NEW.openingquantity,0);
End If ;
If (TG_OP = 'UPDATE') Then
company_code = NEW.companycode ;
branch_code = NEW.branchcode ;
location_fk = NEW.locationfk ;
variety_code = NEW.barcode ;
opening_quantity = Coalesce(NEW.openingquantity,0) - OLD.openingquantity ;
modified_user = NEW.modifieduser ;
modified_date = NEW.modifieddate ;
modified_computer = NEW.modifiedcomputer ;
End If ;
If (TG_OP = 'DELETE') Then
company_code = OLD.companycode ;
branch_code = OLD.branchcode ;
location_fk = OLD.locationfk ;
variety_code = OLD.barcode ;
opening_quantity = OLD.openingquantity * -1 ;
modified_user = OLD.modifieduser ;
modified_date = OLD.modifieddate ;
modified_computer = OLD.modifiedcomputer ;
End If ;
If (Select Count(*)
From table2
WHERE companycode = company_code
AND branchcode = branch_code
AND locationfk = location_fk
AND barcode = variety_code ) > 0 Then
BEGIN
UPDATE table2
SET openingquantity = Coalesce(openingquantity,0) + opening_quantity,
modifieduser = modified_user, modifieddate = modified_date, modifiedcomputer = modified_computer
WHERE companycode = company_code
AND branchcode = branch_code
AND locationfk = location_fk
AND barcode = variety_code ;
END ;
Else
BEGIN
INSERT INTO public.table2(
barcodestockpk, companycode, branchcode, locationfk, barcode, baleopenheaderfk, lrentryheaderfk, lrentrydetailfk,
baleopendetailfk, lrentrydetailsequencenumber, baleopendetailsequencenumber, barcodeopeningstockfk, sequencenumber,
varietyfk, brandfk, modelfk, patternfk, shadefk, materialfk, finishfk, sizefk,
meterbreakup, unit, barcodesinglebulk, barcodeitem, effectiverate,
openingquantity, barcodedquantity, purchasereturnquantity, salesquantity, salescancellationquantity,
salesreturnquantity, salesreturncancellationquantity, stockissuequantity, stockreceiptquantity, locationissuequantity, locationreceiptquantity,
branchissuequantity, branchreceiptquantity, closingstock, salesrate, mrprate, labelrate, ratecode, discountpercent, discountrate,
defectiveitem, locked, insertuser, insertdate, insertcomputer,
modifieduser, modifieddate, modifiedcomputer, wsrate, reversecalculation, hsnnumber)
VALUES ( replace(uuid_generate_v4()::text, '-', ''), company_code , branch_code, location_fk, variety_code, Null, Null,Null,
Null, Null, Null, NEW.barcodeopeningstockpk, NEW.Sequencenumber,
NEW.varietyfk, NEW.brandfk, NEW.modelfk, NEW.patternfk, NEW.shadefk, NEW.materialfk, NEW.finishfk, NEW.sizefk,
NEW.meterbreakup, NEW.unit, NEW.barcodesinglebulk, NEW.barcodeitem, NEW.effectiverate,
opening_quantity, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, NEW.salesrate, NEW.mrprate, NEW.labelrate, NEW.ratecode, 0, 0,
'N', NEW.locked, NEW.insertuser, NEW.insertdate, NEW.insertcomputer,
NEW.modifieduser, NEW.modifieddate, NEW.modifiedcomputer, NEW.wsrate, 'N', NEW.hsnnumber);
END ;
End IF ;
RETURN NEW ;
END ;
Trigger functions in Table 2
First Trigger, which calculates the closing stock, on before insert, update
BEGIN
If (TG_OP = 'INSERT') Then
NEW.closingstock = coalesce(NEW.barcodedquantity,0) +
coalesce(NEW.openingquantity,0) -
coalesce(NEW.salesquantity,0) +
coalesce(NEW.salesreturnquantity,0) +
coalesce(NEW.salescancellationquantity,0) -
coalesce(NEW.salesreturncancellationquantity,0) -
coalesce(NEW.purchasereturnquantity,0) -
coalesce(NEW.stockissuequantity,0) +
coalesce(NEW.stockreceiptquantity,0) -
coalesce(NEW.locationissuequantity,0) +
coalesce(NEW.locationreceiptquantity,0) -
coalesce(NEW.branchissuequantity,0) +
coalesce(NEW.branchreceiptquantity,0) ;
Return New ;
End If ;
If (TG_OP = 'UPDATE') Then
NEW.closingstock = coalesce(NEW.barcodedquantity,0) +
coalesce(NEW.openingquantity,0) -
coalesce(NEW.salesquantity,0) +
coalesce(NEW.salesreturnquantity,0) +
coalesce(NEW.salescancellationquantity,0) -
coalesce(NEW.salesreturncancellationquantity,0) -
coalesce(NEW.purchasereturnquantity,0) -
coalesce(NEW.stockissuequantity,0) +
coalesce(NEW.stockreceiptquantity,0) -
coalesce(NEW.locationissuequantity,0) +
coalesce(NEW.locationreceiptquantity,0) -
coalesce(NEW.branchissuequantity,0) +
coalesce(NEW.branchreceiptquantity,0) ;
Return New ;
End If ;
END
Begin
If Coalesce(NEW.openingquantity,0) = 0 and
Coalesce(NEW.barcodedquantity,0) = 0 and
Coalesce(NEW.salesquantity,0) = 0 and
Coalesce(NEW.salescancellationquantity,0) = 0 and
Coalesce(NEW.salesreturnquantity,0) = 0 and
Coalesce(NEW.salesreturncancellationquantity,0) = 0 and
Coalesce(NEW.purchasereturnquantity,0) = 0 and
Coalesce(NEW.stockissuequantity,0) = 0 and
Coalesce(NEW.stockreceiptquantity,0) = 0 and
Coalesce(NEW.locationissuequantity,0) = 0 and
Coalesce(NEW.locationreceiptquantity,0) = 0 and
Coalesce(NEW.branchissuequantity,0) = 0 and
Coalesce(NEW.branchreceiptquantity,0) = 0 Then
Delete
From tx_barcode_stock
Where barcodestockpk = new.barcodestockpk ;
End If ;
Return New ;
END
Any (other) suggestion to transfer successfully is really appreciated.
Happiness Always
BKR Sivaprakash
BKR Sivaprakash
pgsql-general by date: