Обсуждение: 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
Cédric 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 : > Cédric 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? > > http://www.postgresql.org/docs/faq -- Cédric Villemain Administrateur de Base de Données Cel: +33 (0)6 74 15 56 53 http://dalibo.com - http://dalibo.org
Cédric 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: > Cédric 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? > > http://www.postgresql.org/docs/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. +