Thread: pg_dump and transactions
Hi, We have lots of trouble with using the COPY dump, so we always use de INSERT mode. Because the INSERT mode does'nt use transactions, this methode is very slow. This (simple) patch creates (if the -T flag is set) a transaction of every insert block, so de import is mostly 10 times faster. I hope you like it. Regards, Ferdinand
Attachment
Can you explain the COPY troubles you have had. Seems like a problem. What about this patch which batches INSERTS into transaction blocks? Seems if you are worried about performance, you should be using COPY. I am not inclined to apply it but rather find out why COPY isn't working for this person. --------------------------------------------------------------------------- Ferdinand Smit wrote: > Hi, > > We have lots of trouble with using the COPY dump, so we always use de INSERT > mode. Because the INSERT mode does'nt use transactions, this methode is very > slow. > > This (simple) patch creates (if the -T flag is set) a transaction of every > insert block, so de import is mostly 10 times faster. > > I hope you like it. > Regards, > > Ferdinand [ Attachment, skipping... ] > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Hi Bruce, > Can you explain the COPY troubles you have had. Seems like a problem. We have about 30 different postgres databases and de versions differ from 6.5 to 7.3. Maybe that causes sometimes the poblem. An other thing is dat COPY fails when you use "strange characters". Expecialy when you are changing from encoding. (Lots of trouble with changing from unicode to sql-ascii) > What about this patch which batches INSERTS into transaction blocks? > Seems if you are worried about performance, you should be using COPY. I > am not inclined to apply it but rather find out why COPY isn't working > for this person. Wy not ?? I think that transactions in a dump are a normal thing. Beside of that, our database-dumps must be useble even when we change from version or database-type. Ferdinand
Ferdinand Smit <ferdinand@telegraafnet.nl> writes:
> We have lots of trouble with using the COPY dump, so we always use de INSERT
> mode. Because the INSERT mode does'nt use transactions, this methode is very
> slow.
What would really be appreciated is some investigation into why the COPY
mode doesn't work for you. I prefer to fix problems rather than work
around them.
> This (simple) patch creates (if the -T flag is set) a transaction of every
> insert block, so de import is mostly 10 times faster.
... kinda seems like a kluge ...
regards, tom lane
> > This (simple) patch creates (if the -T flag is set) a transaction of every
> > insert block, so de import is mostly 10 times faster.
> ... kinda seems like a kluge ...
I haven't looked at the patch to see if, for example, the -T flag takes
a tuple count to determine the size of the transaction blocks. But istm
that this is actually a very useful feature, even though it should not
be required as a direct result of Ferdinand's problems with the copy
functionality.
Why not have a feature which can increase the speed of an insert-style
import? We're not demanding that everyone use it, and we have someone
who went to the trouble to implement it.
- Thomas
Ferdinand Smit <ferdinand@telegraafnet.nl> writes:
>> Can you explain the COPY troubles you have had. Seems like a problem.
> We have about 30 different postgres databases and de versions differ from 6.5
> to 7.3. Maybe that causes sometimes the poblem.
> An other thing is dat COPY fails when you use "strange characters". Expecialy
> when you are changing from encoding. (Lots of trouble with changing from
> unicode to sql-ascii)
This is completely unhelpful. Give us a specific case that fails in
the current release, and we can try to fix it. No, we do not want to
hear about how you're afraid of COPY because it had bugs in 6.5.
regards, tom lane
Hi,
> >> Can you explain the COPY troubles you have had. Seems like a problem.
>
> This is completely unhelpful. Give us a specific case that fails in
> the current release, and we can try to fix it. No, we do not want to
> hear about how you're afraid of COPY because it had bugs in 6.5.
>
Sorry i was in a hurry.
The export-database is PG 7.0.3 in UNICODE.
The export-table is from type text.
The import-database is PG 7.3 in SQL-ASCII.
The problem-records has an euro-sign.
teleportal=# \d uiting
Table "uiting"
Attribute | Type | Modifier
-----------+-----------+-------------------------------------------------
id | integer | not null default nextval('uiting_id_seq'::text)
carrousel | integer | not null
tekst | text |
url | text | not null
start | timestamp |
einde | timestamp |
clicks | integer | not null default 0
views | integer | default 0
otherid | integer |
inarchief | boolean |
Index: uiting_pkey
teleportal=# select tekst from uiting where id = 26109;
tekst
-----------------------------------------------------------
Audi TT Coupé 2000, 77.000km 33.850
(1 row)
teleportal=#
The COPY dump gives :
26109 4 Audi TT Coupé 2000, 77.000km~@| 33.850
http://autotelegraaf.nl/GebruiktOpWeg/Speurder/Toon/?id=475739&referrer=etalage
\N 2002-03-26 14:12:00+01 1907 630563 475739 t
The INSERT dump gives:
INSERT INTO "uiting" VALUES (26109,4,'Audi TT Coup\351 2000, 77.000km
\200\24033.850','http://autotelegraaf.nl/GebruiktOpWeg/Speurder/Toon/?id=475739&referrer=etalage',NULL,'2002-03-26
14:12:00+01',1907,630563,475739,'t');
Regards,
Ferdinand