Re: [HACKERS] pg_dump and insert multi-rows option
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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] pg_dump and insert multi-rows option
Tom Lane wrote: > David Fetter <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] pg_dump and insert multi-rows option
Tom Lane wrote: David Fetter <[EMAIL PROTECTED]> 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
Re: [HACKERS] pg_dump and insert multi-rows option
David Fetter <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] pg_dump and insert multi-rows option
On Tue, Sep 04, 2007 at 01:22:01PM -0400, Tom Lane wrote: > =?iso-8859-15?q?C=E9dric_Villemain?= <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! Consider donating to PostgreSQL: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] pg_dump and insert multi-rows option
=?iso-8859-15?q?C=E9dric_Villemain?= <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] pg_dump and insert multi-rows option
Le mardi 4 septembre 2007, Tom Lane a écrit : > =?utf-8?q?C=C3=A9dric_Villemain?= <[EMAIL PROTECTED]> 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 signature.asc Description: This is a digitally signed message part.
Re: [HACKERS] pg_dump and insert multi-rows option
=?utf-8?q?C=C3=A9dric_Villemain?= <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] 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 signature.asc Description: This is a digitally signed message part.