Re: [HACKERS] [PATCHES] SGML index build fix
Joshua D. Drake wrote: > On Sat, 2007-01-06 at 23:38 -0500, Tom Lane wrote: > > Bruce Momjian <[EMAIL PROTECTED]> writes: > > > The attached patch warns users when they create documentation output > > > that has no index, and suggests re-running 'gmake'. > > > > This is just useless noise. If it could tell the difference between an > > up-to-date index and a not-up-to-date one, there might be some value > > to it ... but as-is I think it's just getting in the user's face. > > Everyone using these tools knows about the two-pass behavior. > > No, not everyone knows. In fact I would argue that most do not know. It > isn't intuitive to the process. You *expect* that an index will be made. The idea for the warning message actually came from Peter. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] SGML index build fix
On Sat, 2007-01-06 at 23:38 -0500, Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > The attached patch warns users when they create documentation output > > that has no index, and suggests re-running 'gmake'. > > This is just useless noise. If it could tell the difference between an > up-to-date index and a not-up-to-date one, there might be some value > to it ... but as-is I think it's just getting in the user's face. > Everyone using these tools knows about the two-pass behavior. No, not everyone knows. In fact I would argue that most do not know. It isn't intuitive to the process. You *expect* that an index will be made. Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] SGML index build fix
Bruce Momjian wrote: > Tom Lane wrote: > > Bruce Momjian <[EMAIL PROTECTED]> writes: > > > The attached patch warns users when they create documentation output > > > that has no index, and suggests re-running 'gmake'. > > > > This is just useless noise. If it could tell the difference between an > > up-to-date index and a not-up-to-date one, there might be some value > > to it ... but as-is I think it's just getting in the user's face. > > Everyone using these tools knows about the two-pass behavior. > > I certainly did not, and it warns only when an invalid HTML.index is > used. And the people creating our PDFs didn't know because we often have to update the web site with valid ones that have indexes. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] SGML index build fix
Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > The attached patch warns users when they create documentation output > > that has no index, and suggests re-running 'gmake'. > > This is just useless noise. If it could tell the difference between an > up-to-date index and a not-up-to-date one, there might be some value > to it ... but as-is I think it's just getting in the user's face. > Everyone using these tools knows about the two-pass behavior. I certainly did not, and it warns only when an invalid HTML.index is used. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(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: [PATCHES] SGML index build fix
Bruce Momjian <[EMAIL PROTECTED]> writes: > The attached patch warns users when they create documentation output > that has no index, and suggests re-running 'gmake'. This is just useless noise. If it could tell the difference between an up-to-date index and a not-up-to-date one, there might be some value to it ... but as-is I think it's just getting in the user's face. Everyone using these tools knows about the two-pass behavior. I just got done reading an interesting comparison of MS Vista versus Mac OS X: http://www.informationweek.com/news/showArticle.jhtml?articleID=196800670 The guy's very first complaint about Vista is how it demands your attention constantly with trivial warning messages. This seems in much the same vein. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[PATCHES] SGML index build fix
The attached patch warns users when they create documentation output that has no index, and suggests re-running 'gmake'. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + Index: doc/src/sgml/Makefile === RCS file: /cvsroot/pgsql/doc/src/sgml/Makefile,v retrieving revision 1.86 diff -c -c -r1.86 Makefile *** doc/src/sgml/Makefile 15 Dec 2006 16:50:07 - 1.86 --- doc/src/sgml/Makefile 7 Jan 2007 04:26:05 - *** *** 95,101 ifeq ($(vpath_build), yes) @cp $(srcdir)/stylesheet.css . endif ! COLLATEINDEX := LC_ALL=C $(PERL) $(COLLATEINDEX) -f -g --- 95,103 ifeq ($(vpath_build), yes) @cp $(srcdir)/stylesheet.css . endif ! # for some reason $wildcard expands too early, so we use 'test' ! @test -f bookindex.valid || echo "Run 'gmake' again to generate output with a proper index" ! COLLATEINDEX := LC_ALL=C $(PERL) $(COLLATEINDEX) -f -g *** *** 105,113 --- 107,119 ifeq (,$(wildcard HTML.index)) bookindex.sgml: $(COLLATEINDEX) -o $@ -N + @rm -f bookindex.valid + @touch bookindex.dummy else bookindex.sgml: HTML.index $(COLLATEINDEX) -i 'bookindex' -o $@ $< + @rm -f bookindex.dummy + @touch bookindex.valid endif version.sgml: $(top_builddir)/src/Makefile.global *** *** 141,155 --- 147,165 %-A4.tex-ps: %.sgml $(ALLSGML) stylesheet.dsl bookindex.sgml $(JADE.tex.call) -V texdvi-output -V '%paper-type%'=A4 -o $@ $< + @test -f bookindex.valid || echo "Run 'gmake' again to generate output with a proper index" %-US.tex-ps: %.sgml $(ALLSGML) stylesheet.dsl bookindex.sgml $(JADE.tex.call) -V texdvi-output -V '%paper-type%'=USletter -o $@ $< + @test -f bookindex.valid || echo "Run 'gmake' again to generate output with a proper index" %-A4.tex-pdf: %.sgml $(ALLSGML) stylesheet.dsl bookindex.sgml $(JADE.tex.call) -V texpdf-output -V '%paper-type%'=A4 -o $@ $< + @test -f bookindex.valid || echo "Run 'gmake' again to generate output with a proper index" %-US.tex-pdf: %.sgml $(ALLSGML) stylesheet.dsl bookindex.sgml $(JADE.tex.call) -V texpdf-output -V '%paper-type%'=USletter -o $@ $< + @test -f bookindex.valid || echo "Run 'gmake' again to generate output with a proper index" %.dvi: %.tex-ps @rm -f $*.aux $*.log *** *** 233,238 --- 243,249 -e '1a\' -e 'http://www.oasis-open.org/docbook/xml/4.2/docbookx.dtd";>' \ >$@ # ' hello Emacs + @test -f bookindex.valid || echo "Run 'gmake' again to generate output with a proper index" override XSLTPROCFLAGS += --stringparam pg.version '$(VERSION)' *** *** 285,291 clean distclean maintainer-clean: # HTML ! rm -f HTML.manifest *.html *.gif # man rm -rf *.1 *.$(DEFAULTSECTION) man1 man$(DEFAULTSECTION) manpage.refs manpage.links manpage.log # print --- 296,302 clean distclean maintainer-clean: # HTML ! rm -f HTML.manifest *.html *.gif bookindex.dummy bookindex.valid # man rm -rf *.1 *.$(DEFAULTSECTION) man1 man$(DEFAULTSECTION) manpage.refs manpage.links manpage.log # print ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] COPY with no WAL, in certain circumstances
On Sat, 2007-01-06 at 22:09 -0500, Tom Lane wrote: > "Joshua D. Drake" <[EMAIL PROTECTED]> writes: > >> cost is having to fsync the whole table afterwards. So it really only > >> makes sense for commands that one can expect are writing pretty much > >> all of the table. I could easily see it being a net loss for individual > >> INSERTs. > > > What about multi value inserts? Just curious. > > I wouldn't want the system to assume that a multi-VALUES insert is > writing most of the table. Would you? The thing is reasonable for > inserting maybe a few hundred or few thousand rows at most, and that's > still small in comparison to typical tables. Good point. :) Joshua D. Drake > > regards, tom lane > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] COPY with no WAL, in certain circumstances
"Joshua D. Drake" <[EMAIL PROTECTED]> writes: >> cost is having to fsync the whole table afterwards. So it really only >> makes sense for commands that one can expect are writing pretty much >> all of the table. I could easily see it being a net loss for individual >> INSERTs. > What about multi value inserts? Just curious. I wouldn't want the system to assume that a multi-VALUES insert is writing most of the table. Would you? The thing is reasonable for inserting maybe a few hundred or few thousand rows at most, and that's still small in comparison to typical tables. regards, tom lane ---(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: [PATCHES] COPY with no WAL, in certain circumstances
> > Is there some technical reason that the INSERT statements need to use WAL > > in > > these scenarios? > > First, there's enough other overhead to an INSERT that you'd not save > much percentagewise. Second, not using WAL doesn't come for free: the > cost is having to fsync the whole table afterwards. So it really only > makes sense for commands that one can expect are writing pretty much > all of the table. I could easily see it being a net loss for individual > INSERTs. What about multi value inserts? Just curious. Joshua D. Drake > > regards, tom lane > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] COPY with no WAL, in certain circumstances
Robert Treat <[EMAIL PROTECTED]> writes: > On Saturday 06 January 2007 16:36, Simon Riggs wrote: > >> BEGIN; >> CREATE TABLE foo... >> INSERT INTO foo --uses WAL >> COPY foo.. --no WAL >> INSERT INTO foo --uses WAL >> COPY foo.. --no WAL >> INSERT INTO foo --uses WAL >> COPY foo... --no WAL >> COMMIT; > Is there some technical reason that the INSERT statements need to use WAL in > these scenarios? First, there's enough other overhead to an INSERT that you'd not save much percentagewise. Second, not using WAL doesn't come for free: the cost is having to fsync the whole table afterwards. So it really only makes sense for commands that one can expect are writing pretty much all of the table. I could easily see it being a net loss for individual INSERTs. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] COPY with no WAL, in certain circumstances
Bruce Momjian <[EMAIL PROTECTED]> writes: > Simon Riggs wrote: >> Reason for no documentation was that CREATE INDEX and CREATE TABLE AS >> SELECT already use this optimisation, but to my knowledge neither was/is >> documented on those command pages. > I wasn't aware those used the optimization. Seems they all should be > documented somewhere. We don't document every single optimization in the system ... if we did, the docs would be as big as the source code and equally unreadable by non-programmers. I think it's a much better idea just to mention it one place and not try to enumerate exactly which commands have the optimization. regards, tom lane ---(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: [PATCHES] COPY with no WAL, in certain circumstances
"Simon Riggs" <[EMAIL PROTECTED]> writes: > The rule is: if the relfilenode for a table is new in this transaction > (and therefore the whole things will be dropped at end-of-transaction) > then *all* COPY commands are able to avoid writing WAL safely, if: > - PITR is not enabled > - there is no active portal (which could have been opened on an earlier > commandid and could therefore see data prior to the switch to the new > relfilenode). In those cases, *not* using WAL causes no problems at all, > so sleep well without it. Uh ... what in the world has an active portal got to do with it? I think you've confused snapshot considerations with crash recovery. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [PATCHES] Allow the identifier length to be increased via a configure option
Peter Eisentraut <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> ... why is NAMEDATALEN exported at all?) > I think because it used to be used in libpq's notification structure. Yeah, you're probably right. Maybe we should take it out of postgres_ext.h and move it to pg_config_manual.h. If no one complains after a release cycle or so, we could reconsider making it configurable more easily. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] COPY with no WAL, in certain circumstances
On Saturday 06 January 2007 16:36, Simon Riggs wrote:
> The rule is: if the relfilenode for a table is new in this transaction
> (and therefore the whole things will be dropped at end-of-transaction)
> then *all* COPY commands are able to avoid writing WAL safely, if:
> - PITR is not enabled
> - there is no active portal (which could have been opened on an earlier
> commandid and could therefore see data prior to the switch to the new
> relfilenode). In those cases, *not* using WAL causes no problems at all,
> so sleep well without it.
>
> BEGIN;
> CREATE TABLE foo...
> INSERT INTO foo --uses WAL
> COPY foo.. --no WAL
> INSERT INTO foo --uses WAL
> COPY foo.. --no WAL
> INSERT INTO foo --uses WAL
> COPY foo... --no WAL
> COMMIT;
>
Is there some technical reason that the INSERT statements need to use WAL in
these scenarios? ISTM that in the above scenario there are no cases where
the INSERT statements are any more recoverable than the COPY statements.
While there might not be much gain from bypassing WAL on a single insert, in
bunches, or more importantly when doing INSERT INTO foo SELECT *, it could be
a nice improvement as well. Am I overlooking something?
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
---(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: [PATCHES] COPY with no WAL, in certain circumstances
On Saturday 06 January 2007 16:40, Bruce Momjian wrote:
> Simon Riggs wrote:
> > > > Or in other words, does this patch mean that all COPY execution that
> > > > is within a transaction will ignore WAL?
> > >
> > > Yes, because it is possible to do in all cases.
> >
> > Very happy to add documentation where Tom suggested.
> >
> > Reason for no documentation was that CREATE INDEX and CREATE TABLE AS
> > SELECT already use this optimisation, but to my knowledge neither was/is
> > documented on those command pages.
>
> I wasn't aware those used the optimization. Seems they all should be
> documented somewhere.
Might I suggest somewhere under chapter 27, with something akin to what we
have for documenting lock levels and the different operations that use them.
We document the reasons you want to avoid WAL and various operations in the
database that do this automagically.
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
---(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] [PATCHES] Allow the identifier length to be increased via a configure option
Tom Lane wrote: > I'm wondering how this got into the TODO list. It seems rather > pointless, and likely to create client compatibility problems (if > not, why is NAMEDATALEN exported at all?) I think because it used to be used in libpq's notification structure. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(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: [PATCHES] Allow the identifier length to be increased via
Dhanaraj M wrote: > Tom Lane wrote: > > Alvaro Herrera <[EMAIL PROTECTED]> writes: > > > >> Dhanaraj M wrote: > >> > >>> I am sending the patch for the following TODO item: > >>> Allow the identifier length to be increased via a configure option > >>> > > > > > >> You should use pg_config.h, not mangle postgres_ext.h like that. Or > >> maybe generate postgres_ext.h from an hypotetical postgres_ext.h.in (but > >> I wouldn't do that, really). > >> > > > > I'm wondering how this got into the TODO list. It seems rather > > pointless, and likely to create client compatibility problems (if not, > > why is NAMEDATALEN exported at all?) > > > Will this TODO item be removed from the list? > Or I shall proceed with the suggestions given. TODO item removed. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://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: [PATCHES] COPY with no WAL, in certain circumstances
Joshua D. Drake wrote: > > IMHO, this deserves an GUC parameter (use_wal_in_copy?). Because a lot > > of people use COPY because it's faster than INSERT but expects that it > > will be in WAL. The default would be use_wal_in_copy = true. > > That I don't think makes sense. A copy is an all or nothing option, if a > copy fails in the middle the whole thing is rolled back. > I was worried about PITR, but Simon answers my question: PITR enables so uses WAL. -- Euler Taveira de Oliveira http://www.timbira.com/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] COPY with no WAL, in certain circumstances
Simon Riggs wrote: > > > Or in other words, does this patch mean that all COPY execution that is > > > within a transaction will ignore WAL? > > > > Yes, because it is possible to do in all cases. > > Very happy to add documentation where Tom suggested. > > Reason for no documentation was that CREATE INDEX and CREATE TABLE AS > SELECT already use this optimisation, but to my knowledge neither was/is > documented on those command pages. I wasn't aware those used the optimization. Seems they all should be documented somewhere. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://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: [PATCHES] COPY with no WAL, in certain circumstances
On Sat, 2007-01-06 at 15:24 -0500, Bruce Momjian wrote:
> Joshua D. Drake wrote:
> >
> > > > BEGIN;
> > > >CREATE TABLE foo...
> > > >INSERT INTO foo VALUES ('1');
> > > >COPY foo...
> > > >
> > > > COMMIT;
> > >
> > > On ABORT, the entire table disappears, as well as the INSERT, so I don't
> > > see any problem. I assume the INSERT is WAL logged.
> >
> > No I don't see any problems, I am just trying to understand the
> > boundaries. E.g., is there some weird limitation where if I have any
> > values in the table before the copy (like the example above) that copy
> > will go through WAL.
> >
> > Or in other words, does this patch mean that all COPY execution that is
> > within a transaction will ignore WAL?
>
> Yes, because it is possible to do in all cases.
Very happy to add documentation where Tom suggested.
Reason for no documentation was that CREATE INDEX and CREATE TABLE AS
SELECT already use this optimisation, but to my knowledge neither was/is
documented on those command pages.
The rule is: if the relfilenode for a table is new in this transaction
(and therefore the whole things will be dropped at end-of-transaction)
then *all* COPY commands are able to avoid writing WAL safely, if:
- PITR is not enabled
- there is no active portal (which could have been opened on an earlier
commandid and could therefore see data prior to the switch to the new
relfilenode). In those cases, *not* using WAL causes no problems at all,
so sleep well without it.
So all of these work as shown
BEGIN;
COPY foo... --uses WAL
TRUNCATE foo...
COPY foo.. --no WAL
COPY foo.. --no WAL
COMMIT;
BEGIN;
CREATE TABLE foo...
INSERT INTO foo --uses WAL
COPY foo.. --no WAL
INSERT INTO foo --uses WAL
COPY foo.. --no WAL
INSERT INTO foo --uses WAL
COPY foo... --no WAL
COMMIT;
BEGIN;
CREATE TABLE foo... AS SELECT
--no WAL
INSERT INTO foo --uses WAL
COPY foo.. --no WAL
COMMIT;
BEGIN;
DECLARE CURSOR cursor
CREATE TABLE foo...
COPY foo.. --uses WAL because active portal
COPY foo.. --uses WAL because active portal
CLOSE cursor
COPY foo.. --no WAL
COPY foo.. --no WAL
COMMIT;
psql --single-transaction -f mydb.pgdump
Come to think of it, I should be able to use
pg_current_xlog_insert_location() to come up with a test case.
--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] wal_checksum = on (default) | off
Simon Riggs wrote: > > Somehow, neither of these statements seem likely to be uttered by > > a sane DBA ... > > If I take a backup of a server and bring it up on a new system, the > blocks in the backup will not have been CRC checked before they go to > disk. > > If I take the same server and now stream log records across to it, why > *must* that data be CRC checked, when the original data has not been? > > I'm proposing choice, with a safe default. That's all. Are there performance numbers to justify the option? We don't give people options unless there is real value to it. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] COPY with no WAL, in certain circumstances
Joshua D. Drake wrote:
>
> > > BEGIN;
> > >CREATE TABLE foo...
> > >INSERT INTO foo VALUES ('1');
> > >COPY foo...
> > >
> > > COMMIT;
> >
> > On ABORT, the entire table disappears, as well as the INSERT, so I don't
> > see any problem. I assume the INSERT is WAL logged.
>
> No I don't see any problems, I am just trying to understand the
> boundaries. E.g., is there some weird limitation where if I have any
> values in the table before the copy (like the example above) that copy
> will go through WAL.
>
> Or in other words, does this patch mean that all COPY execution that is
> within a transaction will ignore WAL?
Yes, because it is possible to do in all cases.
--
Bruce Momjian [EMAIL PROTECTED]
EnterpriseDBhttp://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [PATCHES] xlog directory at initdb time
Patch applied. Thanks. --- Euler Taveira de Oliveira wrote: > Peter Eisentraut wrote: > > > On the name of the option, it's not actually a "data" directory, so I'd > > just > > call it --xlogdir, parallel to --datadir. > > > Seems reasonable. Patch modified is attached. > > > -- > Euler Taveira de Oliveira > http://www.timbira.com/ [ Attachment, skipping... ] > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > >http://archives.postgresql.org -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] COPY with no WAL, in certain circumstances
Euler Taveira de Oliveira <[EMAIL PROTECTED]> writes: > Simon Riggs wrote: >> The enclosed patch implements this, as discussed. There is no user >> interface to enable/disable, just as with CTAS and CREATE INDEX; no >> docs, just code comments. >> > IMHO, this deserves an GUC parameter (use_wal_in_copy?). Why? The whole point is that it's automatic and transparent. regards, tom lane ---(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: [PATCHES] COPY with no WAL, in certain circumstances
On Sat, 2007-01-06 at 16:41 -0200, Euler Taveira de Oliveira wrote: > Simon Riggs wrote: > > > As discussed on -hackers, its possible to avoid writing any WAL at all > > for COPY in these circumstances: > > > Cool. > > > The enclosed patch implements this, as discussed. There is no user > > interface to enable/disable, just as with CTAS and CREATE INDEX; no > > docs, just code comments. > > > IMHO, this deserves an GUC parameter (use_wal_in_copy?). Because a lot > of people use COPY because it's faster than INSERT but expects that it > will be in WAL. The default would be use_wal_in_copy = true. That I don't think makes sense. A copy is an all or nothing option, if a copy fails in the middle the whole thing is rolled back. Sincerely, Joshua D. Drake > > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate ---(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: [PATCHES] COPY with no WAL, in certain circumstances
Simon Riggs wrote: > As discussed on -hackers, its possible to avoid writing any WAL at all > for COPY in these circumstances: > Cool. > The enclosed patch implements this, as discussed. There is no user > interface to enable/disable, just as with CTAS and CREATE INDEX; no > docs, just code comments. > IMHO, this deserves an GUC parameter (use_wal_in_copy?). Because a lot of people use COPY because it's faster than INSERT but expects that it will be in WAL. The default would be use_wal_in_copy = true. -- Euler Taveira de Oliveira http://www.timbira.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] COPY with no WAL, in certain circumstances
> > BEGIN;
> >CREATE TABLE foo...
> >INSERT INTO foo VALUES ('1');
> >COPY foo...
> >
> > COMMIT;
>
> On ABORT, the entire table disappears, as well as the INSERT, so I don't
> see any problem. I assume the INSERT is WAL logged.
No I don't see any problems, I am just trying to understand the
boundaries. E.g., is there some weird limitation where if I have any
values in the table before the copy (like the example above) that copy
will go through WAL.
Or in other words, does this patch mean that all COPY execution that is
within a transaction will ignore WAL?
Joshua D. Drake
>
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] COPY with no WAL, in certain circumstances
Bruce Momjian <[EMAIL PROTECTED]> writes: > FYI, I am going need to add documentation in the COPY manual page or no > one will know about this performance enhancement. I don't think it belongs in COPY. What would make more sense is another item under the "populating a database" performance tips, suggesting that wrapping the restore into a single transaction is a good idea. We don't really want to be documenting this separately under COPY, CREATE INDEX, and everywhere else that might eventually optimize the case. Come to think of it, that page also fails to suggest that PITR logging shouldn't be on during bulk load. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] COPY with no WAL, in certain circumstances
Joshua D. Drake wrote:
> On Sat, 2007-01-06 at 11:05 -0500, Bruce Momjian wrote:
> > FYI, I am going need to add documentation in the COPY manual page or no
> > one will know about this performance enhancement.
>
> I have some questions:
>
> > > As discussed on -hackers, its possible to avoid writing any WAL at all
> > > for COPY in these circumstances:
> > >
> > > BEGIN;
> > > CREATE TABLE foo..
> > > COPY foo...
> > > COMMIT;
>
> What if I do this?
>
> BEGIN;
>CREATE TABLE foo...
>INSERT INTO foo VALUES ('1');
>COPY foo...
>
> COMMIT;
On ABORT, the entire table disappears, as well as the INSERT, so I don't
see any problem. I assume the INSERT is WAL logged.
--
Bruce Momjian [EMAIL PROTECTED]
EnterpriseDBhttp://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [PATCHES] COPY with no WAL, in certain circumstances
On Sat, 2007-01-06 at 11:05 -0500, Bruce Momjian wrote:
> FYI, I am going need to add documentation in the COPY manual page or no
> one will know about this performance enhancement.
I have some questions:
> > As discussed on -hackers, its possible to avoid writing any WAL at all
> > for COPY in these circumstances:
> >
> > BEGIN;
> > CREATE TABLE foo..
> > COPY foo...
> > COMMIT;
What if I do this?
BEGIN;
CREATE TABLE foo...
INSERT INTO foo VALUES ('1');
COPY foo...
COMMIT;
?
E.g., what are the boundaries of ignoring the WAL?
Joshua D. Drake
> >
> > BEGIN;
> > TRUNCATE foo..
> > COPY foo...
> > COMMIT;
> >
> > The enclosed patch implements this, as discussed. There is no user
> > interface to enable/disable, just as with CTAS and CREATE INDEX; no
> > docs, just code comments.
> >
> > This plays nicely with the --single-transaction option in psql to allow
> > fast restores/upgrades.
> >
> > YMMV but disk bound COPY will benefit greatly from this patch, some
> > tests showing 100% gain. COPY is still *very* CPU intensive, so some
> > tests have shown negligible benefit, fyi, but that isn't the typical
> > case.
> >
> > Applies cleanly to CVS HEAD, passes make check.
> >
> > --
> > Simon Riggs
> > EnterpriseDB http://www.enterprisedb.com
> >
>
> [ Attachment, skipping... ]
>
> >
> > ---(end of broadcast)---
> > TIP 5: don't forget to increase your free space map settings
>
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
---(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: [PATCHES] COPY with no WAL, in certain circumstances
FYI, I am going need to add documentation in the COPY manual page or no one will know about this performance enhancement. --- Simon Riggs wrote: > http://archives.postgresql.org/pgsql-hackers/2006-10/msg01172.php > > As discussed on -hackers, its possible to avoid writing any WAL at all > for COPY in these circumstances: > > BEGIN; > CREATE TABLE foo.. > COPY foo... > COMMIT; > > BEGIN; > TRUNCATE foo.. > COPY foo... > COMMIT; > > The enclosed patch implements this, as discussed. There is no user > interface to enable/disable, just as with CTAS and CREATE INDEX; no > docs, just code comments. > > This plays nicely with the --single-transaction option in psql to allow > fast restores/upgrades. > > YMMV but disk bound COPY will benefit greatly from this patch, some > tests showing 100% gain. COPY is still *very* CPU intensive, so some > tests have shown negligible benefit, fyi, but that isn't the typical > case. > > Applies cleanly to CVS HEAD, passes make check. > > -- > Simon Riggs > EnterpriseDB http://www.enterprisedb.com > [ Attachment, skipping... ] > > ---(end of broadcast)--- > TIP 5: don't forget to increase your free space map settings -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] wal_checksum = on (default) | off
On Fri, 2007-01-05 at 22:57 -0500, Tom Lane wrote: > Jim Nasby <[EMAIL PROTECTED]> writes: > > On Jan 5, 2007, at 6:30 AM, Zeugswetter Andreas ADI SD wrote: > >> Ok, so when you need CRC's on a replicate (but not on the master) you > > > Which sounds to me like a good reason to allow the option in > > recovery.conf as well... > > Actually, I'm not seeing the use-case for a slave having a different > setting from the master at all? > > "My backup server is less reliable than the primary." > > "My backup server is more reliable than the primary." > > Somehow, neither of these statements seem likely to be uttered by > a sane DBA ... If I take a backup of a server and bring it up on a new system, the blocks in the backup will not have been CRC checked before they go to disk. If I take the same server and now stream log records across to it, why *must* that data be CRC checked, when the original data has not been? I'm proposing choice, with a safe default. That's all. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PATCHES] Updated XML patch
> s/XMLGEN/XMLAGG/ > > Sorry, I meant XMLAGG() not XMLGEN() And now that I have the name right, I found the answer: http://archives.postgresql.org/pgsql-hackers/2006-12/msg01022.php Sorry to be a bother... -- Korry
Re: [PATCHES] Updated XML patch
[EMAIL PROTECTED] wrote: > Are you planning to add XMLAGG() support? And if so, will it work > basically the same (from the user's perspective) as the XMLAGG() > syntax in Nikolay's patch? The XMLAGG in Nikolay's patch didn't work right, so I removed it. If someone wants to clean it up, please send a patch. (Or maybe it did work right and I misjudged it, but it was mentally in the way of all the other complications.) -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(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
