TPgDump eats all memory

[meszarosistvan]

I export a very large table. TPgDump eats all memory. What should I do?

Version is: 2.10.0.4

[AlexP]

Hello,

Please specify the number of fields and records in the table, and the amount of memory used for backup.
Also specify where you save the backup of your table - to memory (BackupToStream) or to file (BackupToFile)?

[meszarosistvan]

Hello,

Here is the code, I use BackupToFile.

I would like to backup a very large table. It has 216 fields and about 12 000 000 rows (no kidding). later you can find the table script.

I do not know the memory used for backup. It go higher and higher. After 1 GB, I must stop, because it can crash my pc.

procedure TMainForm.StartBitBtnClick(Sender: TObject);
begin
PgConnection.Connected:=True;

PgDump.BackupToFile('C:\aaaaa\a.dmp');

PgConnection.Connected:=False;
end;

procedure TMainForm.PgDumpBackupProgress(Sender: TObject; ObjectName: String; ObjectNum, ObjectCount, Percent: Integer);
begin
StatusBar.Panels[0].Text:=ObjectName+' '+IntToStr(ObjectNum)+'/'+IntToStr(ObjectCount)+' '+IntToStr(Percent)+'%';
Application.ProcessMessages;
end;

CREATE TABLE "public"."catalog_line" (
"catalog_line_id" INTEGER NOT NULL,
"catalog_id" INTEGER NOT NULL,
"zero_catalog" BOOLEAN,
"field_2_value" VARCHAR(120),
"field_3_value" VARCHAR(120),
"field_4_value" VARCHAR(2000),
"field_5_value" VARCHAR(120),
"field_6_value" VARCHAR(120),
"field_7_value" VARCHAR(120),
"field_8_value" VARCHAR(120),
"field_9_value" VARCHAR(120),
"field_10_value" VARCHAR(120),
"field_11_value" VARCHAR(120),
"field_12_value" VARCHAR(120),
"field_13_value" VARCHAR(120),
"field_14_value" VARCHAR(120),
"field_15_value" VARCHAR(120),
"field_16_value" VARCHAR(120),
"field_17_value" VARCHAR(2000),
"field_18_value" VARCHAR(120),
"field_4033_value" VARCHAR(120),
"field_4034_value" VARCHAR(120),
"field_4037_value" VARCHAR(120),
"field_4038_value" VARCHAR(120),
"field_4039_value" VARCHAR(120),
"field_4040_value" VARCHAR(120),
"field_4041_value" VARCHAR(300),
"field_4042_value" VARCHAR(120),
"field_4043_value" VARCHAR(120),
"field_4044_value" VARCHAR(120),
"field_4045_value" VARCHAR(120),
"field_4054_value" VARCHAR(120),
"field_4087_value" VARCHAR(120),
"field_4088_value" VARCHAR(120),
"field_4089_value" VARCHAR(120),
"field_4090_value" VARCHAR(120),
"field_4129_value" VARCHAR(120),
"field_4147_value" VARCHAR(120),
"field_4148_value" VARCHAR(120),
"field_4149_value" VARCHAR(120),
"field_4150_value" VARCHAR(120),
"field_4167_value" VARCHAR(120),
"field_4297_value" VARCHAR(120),
"field_4298_value" VARCHAR(120),
"field_4299_value" VARCHAR(120),
"field_4300_value" VARCHAR(120),
"field_4301_value" VARCHAR(120),
"field_4307_value" VARCHAR(120),
"field_4308_value" VARCHAR(120),
"field_4309_value" VARCHAR(120),
"field_4310_value" VARCHAR(120),
"field_4311_value" VARCHAR(120),
"field_4312_value" VARCHAR(120),
"field_4313_value" VARCHAR(120),
"field_4314_value" VARCHAR(120),
"field_4315_value" VARCHAR(120),
"field_4316_value" VARCHAR(120),
"field_4317_value" VARCHAR(120),
"field_4318_value" VARCHAR(120),
"field_4319_value" VARCHAR(120),
"field_4320_value" VARCHAR(120),
"field_4321_value" VARCHAR(120),
"field_4322_value" VARCHAR(120),
"field_4323_value" VARCHAR(120),
"field_4324_value" VARCHAR(120),
"field_4325_value" VARCHAR(120),
"field_4326_value" VARCHAR(120),
"field_4327_value" VARCHAR(120),
"field_4328_value" VARCHAR(120),
"field_4329_value" VARCHAR(120),
"field_4330_value" VARCHAR(120),
"field_4332_value" VARCHAR(120),
"field_4333_value" VARCHAR(120),
"field_4334_value" VARCHAR(120),
"field_4335_value" VARCHAR(120),
"field_4336_value" VARCHAR(120),
"field_4648_value" VARCHAR(120),
"field_48227_value" VARCHAR(120),
"field_48228_value" VARCHAR(120),
"field_48229_value" VARCHAR(120),
"field_48294_value" VARCHAR(120),
"field_48295_value" VARCHAR(120),
"field_48296_value" VARCHAR(120),
"field_48297_value" VARCHAR(120),
"field_48298_value" VARCHAR(120),
"field_48492_value" VARCHAR(120),
"field_48493_value" VARCHAR(120),
"field_48494_value" VARCHAR(120),
"field_48495_value" VARCHAR(120),
"field_48496_value" VARCHAR(120),
"field_48497_value" VARCHAR(120),
"field_49086_value" VARCHAR(120),
"field_49087_value" VARCHAR(120),
"field_54907_value" VARCHAR(120),
"field_54908_value" VARCHAR(120),
"field_54909_value" VARCHAR(120),
"field_54910_value" VARCHAR(2000),
"field_54911_value" VARCHAR(120),
"field_54917_value" VARCHAR(120),
"field_56248_value" VARCHAR(120),
"field_59787_value" VARCHAR(120),
"field_59807_value" VARCHAR(120),
"field_59808_value" VARCHAR(120),
"field_59809_value" VARCHAR(120),
"field_59810_value" VARCHAR(120),
"field_65490_value" VARCHAR(120),
"field_65491_value" VARCHAR(120),
"field_65492_value" VARCHAR(120),
"field_65547_value" VARCHAR(120),
"field_4331_value" VARCHAR(120),
"field_69127_value" VARCHAR(120),
"field_69128_value" VARCHAR(120),
"field_69129_value" VARCHAR(120),
"field_70830_value" VARCHAR(120),
"field_71189_value" VARCHAR(120),
"field_71190_value" VARCHAR(120),
"field_71191_value" VARCHAR(120),
"field_71192_value" VARCHAR(120),
"field_71193_value" VARCHAR(120),
"field_71194_value" VARCHAR(120),
"field_72427_value" VARCHAR(120),
"field_75267_value" VARCHAR(120),
"field_75268_value" VARCHAR(120),
"field_75269_value" VARCHAR(120),
"field_75273_value" VARCHAR(120),
"field_75274_value" VARCHAR(120),
"field_75275_value" VARCHAR(120),
"field_76827_value" VARCHAR(120),
"field_76828_value" VARCHAR(120),
"field_77690_value" VARCHAR(120),
"field_78007_value" VARCHAR(120),
"field_78008_value" VARCHAR(120),
"field_78009_value" VARCHAR(120),
"field_78010_value" VARCHAR(120),
"field_78011_value" VARCHAR(120),
"field_78012_value" VARCHAR(120),
"field_78042_value" VARCHAR(120),
"field_78043_value" VARCHAR(120),
"field_78044_value" VARCHAR(120),
"field_78045_value" VARCHAR(120),
"field_78051_value" VARCHAR(120),
"field_78052_value" VARCHAR(120),
"field_78053_value" VARCHAR(120),
"field_78704_value" VARCHAR(120),
"field_78827_value" VARCHAR(120),
"field_81007_value" VARCHAR(120),
"field_81008_value" VARCHAR(120),
"field_81366_value" VARCHAR(120),
"field_81367_value" VARCHAR(120),
"field_81368_value" VARCHAR(120),
"field_81369_value" VARCHAR(120),
"field_82088_value" VARCHAR(120),
"field_82089_value" VARCHAR(120),
"field_87227_value" VARCHAR(120),
"field_87522_value" VARCHAR(120),
"field_87523_value" VARCHAR(120),
"field_87524_value" VARCHAR(120),
"field_87525_value" VARCHAR(120),
"field_87526_value" VARCHAR(120),
"field_87527_value" VARCHAR(120),
"field_87528_value" VARCHAR(120),
"field_87529_value" VARCHAR(120),
"field_87530_value" VARCHAR(120),
"field_87531_value" VARCHAR(120),
"field_87532_value" VARCHAR(120),
"field_87533_value" VARCHAR(120),
"field_88030_value" VARCHAR(120),
"field_88031_value" VARCHAR(120),
"field_88032_value" VARCHAR(120),
"field_88111_value" VARCHAR(120),
"field_88112_value" VARCHAR(120),
"field_88113_value" VARCHAR(120),
"field_88147_value" VARCHAR(120),
"field_88148_value" VARCHAR(120),
"field_88149_value" VARCHAR(120),
"field_88150_value" VARCHAR(120),
"field_88151_value" VARCHAR(120),
"field_88152_value" VARCHAR(120),
"field_88153_value" VARCHAR(120),
"field_88154_value" VARCHAR(120),
"field_88155_value" VARCHAR(120),
"field_88156_value" VARCHAR(120),
"field_88157_value" VARCHAR(120),
"field_88158_value" VARCHAR(120),
"field_88159_value" VARCHAR(120),
"field_88160_value" VARCHAR(120),
"field_88161_value" VARCHAR(120),
"field_88162_value" VARCHAR(120),
"field_88163_value" VARCHAR(120),
"field_88164_value" VARCHAR(120),
"field_88165_value" VARCHAR(120),
"field_88166_value" VARCHAR(120),
"field_88167_value" VARCHAR(120),
"field_90147_value" VARCHAR(120),
"field_90148_value" VARCHAR(120),
"field_90149_value" VARCHAR(120),
"field_90151_value" VARCHAR(120),
"field_90152_value" VARCHAR(120),
"field_90153_value" VARCHAR(120),
"field_90154_value" VARCHAR(120),
"field_90155_value" VARCHAR(120),
"field_90156_value" VARCHAR(120),
"field_90201_value" VARCHAR(120),
"field_92067_value" VARCHAR(120),
"field_92610_value" VARCHAR(120),
"field_92647_value" VARCHAR(120),
"field_93884_value" VARCHAR(120),
"field_97667_value" VARCHAR(120),
"field_97668_value" VARCHAR(120),
"field_97787_value" VARCHAR(120),
"field_98686_value" VARCHAR(120),
"field_98890_value" VARCHAR(120),
"field_98891_value" VARCHAR(120),
"field_101725_value" VARCHAR(120),
"field_101726_value" VARCHAR(120),
"field_101727_value" VARCHAR(120),
CONSTRAINT "catalog_line_pkey" PRIMARY KEY("catalog_line_id"),
CONSTRAINT "fk_catalog_line" FOREIGN KEY ("catalog_id")
REFERENCES "public"."catalog"("catalog_id")
ON DELETE RESTRICT
ON UPDATE RESTRICT
NOT DEFERRABLE
) WITHOUT OIDS;

CREATE INDEX "catalog_line_f1" ON "public"."catalog_line"
USING btree ("catalog_id");

CREATE INDEX "catalog_line_idx_10" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_10_value");

CREATE INDEX "catalog_line_idx_10_12" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_10_value", "field_12_value");

CREATE INDEX "catalog_line_idx_11" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_11_value");

CREATE INDEX "catalog_line_idx_12" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_12_value");

CREATE INDEX "catalog_line_idx_13" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_13_value");

CREATE INDEX "catalog_line_idx_14" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_14_value");

CREATE INDEX "catalog_line_idx_15" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_15_value");

CREATE INDEX "catalog_line_idx_16" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_16_value");

CREATE INDEX "catalog_line_idx_17" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_17_value");

CREATE INDEX "catalog_line_idx_18" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_18_value");

CREATE INDEX "catalog_line_idx_2" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_2_value");

CREATE INDEX "catalog_line_idx_2_3" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_2_value", "field_3_value");

CREATE INDEX "catalog_line_idx_2_zero" ON "public"."catalog_line"
USING btree ("field_2_value", "zero_catalog");

CREATE INDEX "catalog_line_idx_3" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_3_value");

CREATE INDEX "catalog_line_idx_4" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_4_value");

CREATE INDEX "catalog_line_idx_4033" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_4033_value");

CREATE INDEX "catalog_line_idx_4037" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_4037_value");

CREATE INDEX "catalog_line_idx_4038" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_4038_value");

CREATE INDEX "catalog_line_idx_4039" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_4039_value");

CREATE INDEX "catalog_line_idx_4040" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_4040_value");

CREATE INDEX "catalog_line_idx_4041" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_4041_value");

CREATE INDEX "catalog_line_idx_4042" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_4042_value");

CREATE INDEX "catalog_line_idx_4043" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_4043_value");

CREATE INDEX "catalog_line_idx_4044" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_4044_value");

CREATE INDEX "catalog_line_idx_4054" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_4054_value");

CREATE INDEX "catalog_line_idx_4129" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_4129_value");

CREATE INDEX "catalog_line_idx_4147" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_4147_value");

CREATE INDEX "catalog_line_idx_4148" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_4148_value");

CREATE INDEX "catalog_line_idx_4149" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_4149_value");

CREATE INDEX "catalog_line_idx_4319" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_4319_value");

CREATE INDEX "catalog_line_idx_4320" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_4320_value");

CREATE INDEX "catalog_line_idx_4324" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_4324_value");

CREATE INDEX "catalog_line_idx_4325" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_4325_value");

CREATE INDEX "catalog_line_idx_48493" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_48493_value");

CREATE INDEX "catalog_line_idx_5" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_5_value");

CREATE INDEX "catalog_line_idx_6" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_6_value");

CREATE INDEX "catalog_line_idx_7" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_7_value");

CREATE INDEX "catalog_line_idx_78827" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_78827_value");

CREATE INDEX "catalog_line_idx_8" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_8_value");

CREATE INDEX "catalog_line_idx_88032" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_88032_value");

CREATE INDEX "catalog_line_idx_9" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_9_value");

CREATE INDEX "catalog_line_idx_97667" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_97667_value");

CREATE INDEX "catalog_line_idx_98686" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_98686_value");

CREATE INDEX "catalog_line_idx_s_0" ON "public"."catalog_line"
USING btree ("field_2_value", "catalog_id");

[AlexP]

Hello,

Thank you for the information.
We have reproduced and fixed the problem.
This fix will be included in the next build.

[meszarosistvan]

Hello,

I have installed the new TPgDump. It does not eat memory, but

DoBackupProgress(TableName, TableNum, TableCount, Trunc((Query.RecNo / Query.RecordCount) * 100));

is not correct. The Query.RecordCount does not have the right value, and the progress jumps between 99% to 100% (large table...).

Best regards.

[AlexP]

Hello,

To get the correct record count in the RecordCount property, you should set the QueryRecCount property to true or set the FetchAll property to true.
If it doesn't help, please send a complete small sample to alexp*devart*com to demonstrate the problem.

[meszarosistvan]

Hello, thanks for your answer...

I make the following changes in the PgServices.pas. If it Is OK, please put it into the next release.

Best regards.

István

procedure TCustomPgDumpProcessor.BackupData(const Schema, TableName, QueryText: _string;
TableNum, TableCount: integer);
var
Query: TCustomDADataSet;
i: integer;
FieldList, Sql, Header: _string;
begin
Query := CreateQuery;
try

// !!!!!!!!!!!!!!!!!!!!!
Query.Options.QueryRecCount:=True;
// !!!!!!!!!!!!!!!!!!!!!

TDBAccessUtils.CheckConnection(Query);
TDBAccessUtils.GetIRecordSet(Query).SetProp(prFieldsAsText, True);

[AlexP]

Hello,

Thank you for the information.
We will add this change to the next build.