pg_dump and insert multi-rows option
Hello,
In order to make some trivial test, I have start patching pg_dump to get
insert using multi-rows (like --inserts option).
I say "start" because it was just a poor hack to pg_dump.c.
If you are interested I can finish it, and try to get a code that feet
postgresql guidelines ?
--
Cédric Villemain
Administrateur de Base de Données
Cel: +33 (0)6 74 15 56 53
http://dalibo.com - http://dalibo.org
=?utf-8?q?C=C3=A9dric_Villemain?= <cedric.villemain@dalibo.com> writes:
In order to make some trivial test, I have start patching pg_dump to get=20
insert using multi-rows (like --inserts option).
Why? COPY is faster and won't have issues for very large numbers of
rows.
regards, tom lane
Le mardi 4 septembre 2007, Tom Lane a écrit :
=?utf-8?q?C=C3=A9dric_Villemain?= <cedric.villemain@dalibo.com> writes:
In order to make some trivial test, I have start patching pg_dump to
get=20 insert using multi-rows (like --inserts option).Why? COPY is faster and won't have issues for very large numbers of
rows.
Well, I can only make a new ask.
Why is there the --inserts and --attribute-inserts options ?
As copy is very fast, I find fast (in front of simple insert) the multi-rows
inserts. For a very small test I had 20, 30 and 440 seconds to restore
respectively copy, mutiinserts and inserts.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
--
Cédric Villemain
Administrateur de Base de Données
Cel: +33 (0)6 74 15 56 53
http://dalibo.com - http://dalibo.org
=?iso-8859-15?q?C=E9dric_Villemain?= <cedric.villemain@dalibo.com> writes:
Well, I can only make a new ask.
Why is there the --inserts and --attribute-inserts options ?
It's mostly historical IMHO. The COPY option used to have bugs for
corner cases like inheritance child tables that had been ALTERed after
creation. That's all gone now. The only reason I can see to use
these options today is if you want to try to port the dump to some
other database ... and in that case you probably shouldn't assume
multi-insert will work anyway.
regards, tom lane
On Tue, Sep 04, 2007 at 01:22:01PM -0400, Tom Lane wrote:
=?iso-8859-15?q?C=E9dric_Villemain?= <cedric.villemain@dalibo.com> writes:
Well, I can only make a new ask. Why is there the --inserts and
--attribute-inserts options ?It's mostly historical IMHO. The COPY option used to have bugs for
corner cases like inheritance child tables that had been ALTERed
after creation. That's all gone now. The only reason I can see to
use these options today is if you want to try to port the dump to
some other database ... and in that case you probably shouldn't
assume multi-insert will work anyway.
I'm pretty sure it does in the current versions of most other DBMSs.
Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter
Remember to vote!
Consider donating to PostgreSQL: http://www.postgresql.org/about/donate
David Fetter <david@fetter.org> writes:
On Tue, Sep 04, 2007 at 01:22:01PM -0400, Tom Lane wrote:
... The only reason I can see to
use these options today is if you want to try to port the dump to
some other database ... and in that case you probably shouldn't
assume multi-insert will work anyway.
I'm pretty sure it does in the current versions of most other DBMSs.
Up to a point, perhaps. Do you want to make assumptions about what
the maximum acceptable command length will be for other DBMSes?
It'd be hard enough being sure what to use for Postgres.
regards, tom lane
Tom Lane wrote:
David Fetter <david@fetter.org> writes:
On Tue, Sep 04, 2007 at 01:22:01PM -0400, Tom Lane wrote:
... The only reason I can see to
use these options today is if you want to try to port the dump to
some other database ... and in that case you probably shouldn't
assume multi-insert will work anyway.I'm pretty sure it does in the current versions of most other DBMSs.
Up to a point, perhaps. Do you want to make assumptions about what
the maximum acceptable command length will be for other DBMSes?
It'd be hard enough being sure what to use for Postgres.
If we were going to allow it then I think we'd also need a param to
specify either a max number of rows or a max statement size. IIRC, in
MySQL at least, one of these is tunable for input.
cheers
andrew
Tom Lane wrote:
David Fetter <david@fetter.org> writes:
On Tue, Sep 04, 2007 at 01:22:01PM -0400, Tom Lane wrote:
... The only reason I can see to
use these options today is if you want to try to port the dump to
some other database ... and in that case you probably shouldn't
assume multi-insert will work anyway.I'm pretty sure it does in the current versions of most other DBMSs.
Up to a point, perhaps. Do you want to make assumptions about what
the maximum acceptable command length will be for other DBMSes?
It'd be hard enough being sure what to use for Postgres.
Wow, first mention of "Postgres" in the wild. :-) LOL
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
This has been saved for the 8.4 release:
http://momjian.postgresql.org/cgi-bin/pgpatches_hold
---------------------------------------------------------------------------
Andrew Dunstan wrote:
Tom Lane wrote:
David Fetter <david@fetter.org> writes:
On Tue, Sep 04, 2007 at 01:22:01PM -0400, Tom Lane wrote:
... The only reason I can see to
use these options today is if you want to try to port the dump to
some other database ... and in that case you probably shouldn't
assume multi-insert will work anyway.I'm pretty sure it does in the current versions of most other DBMSs.
Up to a point, perhaps. Do you want to make assumptions about what
the maximum acceptable command length will be for other DBMSes?
It'd be hard enough being sure what to use for Postgres.If we were going to allow it then I think we'd also need a param to
specify either a max number of rows or a max statement size. IIRC, in
MySQL at least, one of these is tunable for input.cheers
andrew
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +