Re: [HACKERS] -f output file option for pg_dumpall
--- Original Message --- From: Tom Lane [EMAIL PROTECTED] To: Dave Page [EMAIL PROTECTED] Sent: 1/5/07, 10:48:17 PM Subject: Re: [HACKERS] -f output file option for pg_dumpall Wouldn't it be easier/better to re-point stdout at the -f file, and not touch pg_dump at all? First thing I tried, but using virtually identical code to that which we use with pg_dump it just hangs. The debugger tells me there's a deadlock - my best guess is that stdin/out/err of the child shell pg_dump processes don't get redirected along with their parents. I'm testing on Windows btw - dunno if *nix would be different. /D ---(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] -f output file option for pg_dumpall
--- Original Message --- From: Tom Lane [EMAIL PROTECTED] To: Dave Page [EMAIL PROTECTED] Sent: 1/5/07, 10:52:37 PM Subject: Re: [HACKERS] -f output file option for pg_dumpall I think this will be an exercise in time-wasting, and very possibly destabilize *both* tools. pg_dump has never been designed to reconnect to a different database; for instance there isn't any code for resetting all the internal state that it gathers. I think forking a separate pg_dump for each database is a perfectly fine arrangement, and should be left alone. Hmm, would you be happy with my original proposal to add an append option to pg_dump? I'd also like to allow separate dumping of roles and tablespaces, and allow a default db to be specified instead of postgres/template1. /D ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] PGCon 2007 Program Committee
What's about spronsoring ? I and Teodor would like to present new full text search, now built into PostgreSQL core. We already have patch for 8.3 and current documentation is available http://mira.sai.msu.su/~megera/pgsql/ftsdoc/ Oleg On Fri, 5 Jan 2007, Dan Langille wrote: I have the pleasure of announcing your PGCon 2007 program committee. Bruce Momjian Christopher Browne Josh Berkus Robert Treat Luke Lonergan Neil Conway Robert Bernier These people are responsible for reviewing your submissions and selecting the presentations for PGCon 2007. Speaking of presentations, now that the major holidays are over, please submit your proposal now. Instructions for submissions are at http://www.pgcon.org/2007/submissions.php The original call for papers: http://www.pgcon.org/2007/papers.php NOTE: Please get your proposal in by 19 Jan (that is in two weeks). Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Parsing ambiguity for ORDER BY ... NULLS FIRST/LAST
On Fri, 2007-01-05 at 20:19 -0500, Tom Lane wrote: The only other solution I can see is to make use of the lookahead filter we already have in filtered_base_yylex() to combine NULLS FIRST and NULLS LAST into single tokens. This is not an ideal solution: consider SELECT * FROM nulls first; This should be considered a valid selection from a relation named nulls with alias first, but if it's reduced to a single token the grammar will not see it that way, and will give an error. However, that's a sufficiently unlikely scenario that maybe we can just ignore it. (It's possible to work around the case by inserting AS, of course.) We could probably fix it if we really had to, but it would involve some pretty ugly coding AFAICS. BTW: the existing lookahead hack for WITH CASCADED etc. has the identical problem. Since we already have that problem, it seems sensible to go that way with the NULLS FIRST issue. Presumably you could put extra grammar rules in to throw errors when you see FROM NULLS FIRST? Or should we just document it, somewhere? -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] -f output file option for pg_dumpall
Dave Page wrote: In pgAdmin we use pg_dump's -f option to write backup files. The IO streams are redirected to display status and errors etc. in the GUI. In order to enhance the interface to allow backup of entire clusters as well as role and tablespace definitions, we need to be able to get pg_dumpall to write it's output directly to a file in the same way, because we cannot redirect the child pg_dump IO streams (which also means we may miss errors, but I need to think about that some more). I don't understand this. Errors are sent to stderr, and the real output is sent to stdout, where you can capture and save it. What is the problem? -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PGCon 2007 Program Committee
On 6 Jan 2007 at 12:09, Oleg Bartunov wrote: What's about spronsoring ? Are you asking if your and Teodor can be sponsored? I and Teodor would like to present new full text search, now built into PostgreSQL core. We already have patch for 8.3 and current documentation is available http://mira.sai.msu.su/~megera/pgsql/ftsdoc/ Please submit a proposal at the URL below. :) Oleg On Fri, 5 Jan 2007, Dan Langille wrote: I have the pleasure of announcing your PGCon 2007 program committee. Bruce Momjian Christopher Browne Josh Berkus Robert Treat Luke Lonergan Neil Conway Robert Bernier These people are responsible for reviewing your submissions and selecting the presentations for PGCon 2007. Speaking of presentations, now that the major holidays are over, please submit your proposal now. Instructions for submissions are at http://www.pgcon.org/2007/submissions.php The original call for papers: http://www.pgcon.org/2007/papers.php NOTE: Please get your proposal in by 19 Jan (that is in two weeks). Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- Dan Langille : Software Developer looking for work my resume: http://www.freebsddiary.org/dan_langille.php PGCon - The PostgreSQL Conference - http://www.pgcon.org/ ---(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] 8.3 pending patch queue
On Mon, 2007-01-01 at 19:04 -0500, Bruce Momjian wrote: I will start processing the patches held for 8.3 this week or next, now that the holiday break is over: http://momjian.postgresql.org/cgi-bin/pgpatches_hold The following patches don't appear on this list: Concurrent psql Original submission http://archives.postgresql.org/pgsql-patches/2006-08/msg00249.php Latest http://archives.postgresql.org/pgsql-hackers/2006-12/msg00527.php Described here: http://community.enterprisedb.com/concurrent/index.html WAL Index Split Original submission http://archives.postgresql.org/pgsql-patches/2006-12/msg00045.php Latest http://archives.postgresql.org/pgsql-patches/2007-01/msg0.php Grouped Items Latest http://archives.postgresql.org/pgsql-patches/2006-11/msg00051.php Described here: http://community.enterprisedb.com/git/index.html Maintain Cluster Order http://archives.postgresql.org/pgsql-patches/2006-08/msg00124.php All have been awaiting review for at least a month (though in one case the latest version is quite recent). They probably ought to be on the hold queue; all are ready to be reviewed for final application/rejection. I'd hasten to add that none of those are mine. My patches have received good attention, so I'm not complaining just completing admin. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] 8.3 pending patch queue
Simon Riggs wrote: All have been awaiting review for at least a month (though in one case the latest version is quite recent). They probably ought to be on the hold queue; all are ready to be reviewed for final application/rejection. I'd hasten to add that none of those are mine. My patches have received good attention, so I'm not complaining just completing admin. You might remember months ago that people were complaining I was pushing things into CVS too quickly, so while the patches are in my mailbox, they are not in the queue until I feel the community has the time to focus on 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 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: [HACKERS] -f output file option for pg_dumpall
Dave Page [EMAIL PROTECTED] writes: From: Tom Lane [EMAIL PROTECTED] I think forking a separate pg_dump for each database is a perfectly fine arrangement, and should be left alone. Hmm, would you be happy with my original proposal to add an append option to pg_dump? I don't object to it in principle, but I think a bit more thought is needed as to what's the goal. A stupid append option would be enough for pg_dumpall's current capabilities (ie, text output only) --- but is it reasonable to consider generalizing -Fc and -Ft modes to deal with multiple databases, and if so how would that need to change pg_dump's API? (I'm not at all sure this is feasible, but let's think about it before plastering warts onto pg_dump, not after.) I'd also like to allow separate dumping of roles and tablespaces, and allow a default db to be specified instead of postgres/template1. Can't get excited about either, but no objection. 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: [HACKERS] [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 5: don't forget to increase your free space map settings
Re: [HACKERS] Parsing ambiguity for ORDER BY ... NULLS FIRST/LAST
Simon Riggs [EMAIL PROTECTED] writes: Presumably you could put extra grammar rules in to throw errors when you see FROM NULLS FIRST? It'll throw an error just fine without any extra rules, because there won't be any production allowing the NULLS_FIRST pseudo-token there. You already see this in 8.2: regression=# select * from with cascaded; ERROR: syntax error at or near cascaded LINE 1: select * from with cascaded; ^ regression=# One of the possibilities for fixing it is to add productions that allow table_ref to expand to NULLS_FIRST, WITH_CASCADED, and the other two-word pseudo-tokens, and then build the appropriate relation-with-alias syntax tree out of whole cloth. I find this pretty ugly though, and I'm not sure that table_ref would be the only place to fix, so I'm inclined not to do it unless we actually get complaints from the field. (The other avenue for fixing it would be to try to give the lookahead filter enough context to know when not to combine the tokens, but I think that way will probably be unworkably convoluted.) Oh BTW, there's an interesting bug here: the expected workaround doesn't work: regression=# select * from with as cascaded; ERROR: relation as does not exist regression=# It should be complaining about with not as. I think that the lookahead filter is getting out of sync somehow. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [COMMITTERS] pgsql: Check for ERANGE in exp() as well.
Bruce Momjian wrote: Log Message: --- Check for ERANGE in exp() as well. this broke the regression tests on a number of boxes: http://buildfarm.postgresql.org/cgi-bin/show_status.pl example: http://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=spongedt=2007-01-06%2015:30:02 Stefan ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [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 6: explain analyze is your friend
Re: [HACKERS] [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 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [COMMITTERS] pgsql: Check for ERANGE in exp()
Stefan Kaltenbrunner wrote: Bruce Momjian wrote: Log Message: --- Check for ERANGE in exp() as well. this broke the regression tests on a number of boxes: http://buildfarm.postgresql.org/cgi-bin/show_status.pl example: http://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=spongedt=2007-01-06%2015:30:02 Thanks. This is something I wanted to ask Tom about today. I was worried that ERANGE could be generated by underflow as well as overflow, and setting result to Inf would not work for underflow. I have applied the following patch to test for != 0 and != Inf, which should elimintate the underflow case. Tom, on HPPA, does ERANGE get set for both overflow and underflow? I assume only overflow. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + Index: src/backend/utils/adt/float.c === RCS file: /cvsroot/pgsql/src/backend/utils/adt/float.c,v retrieving revision 1.145 diff -c -c -r1.145 float.c *** src/backend/utils/adt/float.c 6 Jan 2007 15:18:02 - 1.145 --- src/backend/utils/adt/float.c 6 Jan 2007 20:15:22 - *** *** 1459,1465 else result = 1; } ! else if (errno == ERANGE !isinf(result)) result = get_float8_infinity(); CHECKFLOATVAL(result, isinf(arg1) || isinf(arg2), arg1 == 0); --- 1459,1465 else result = 1; } ! else if (errno == ERANGE result != 0 !isinf(result)) result = get_float8_infinity(); CHECKFLOATVAL(result, isinf(arg1) || isinf(arg2), arg1 == 0); *** *** 1478,1484 errno = 0; result = exp(arg1); ! if (errno == ERANGE !isinf(result)) result = get_float8_infinity(); CHECKFLOATVAL(result, isinf(arg1), false); --- 1478,1484 errno = 0; result = exp(arg1); ! if (errno == ERANGE result != 0 !isinf(result)) result = get_float8_infinity(); CHECKFLOATVAL(result, isinf(arg1), false); ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] -f output file option for pg_dumpall
Peter Eisentraut wrote: Dave Page wrote: In pgAdmin we use pg_dump's -f option to write backup files. The IO streams are redirected to display status and errors etc. in the GUI. In order to enhance the interface to allow backup of entire clusters as well as role and tablespace definitions, we need to be able to get pg_dumpall to write it's output directly to a file in the same way, because we cannot redirect the child pg_dump IO streams (which also means we may miss errors, but I need to think about that some more). I don't understand this. Errors are sent to stderr, and the real output is sent to stdout, where you can capture and save it. What is the problem? On Windows at least, I cannot redirect stdout/stderr of child processes, only the parent (ie. pg_dumpall) that is executed by pgAdmin (the pg_dump processes just seem to hang, and the debugger reports a deadlock with no other useful info). I'm sure there is a way round this but a) it'll probably mean hacking wxWidgets or pg_dump about in ways I'm sure Vadim or Tom would dislike, and b) it would seem reasonable to offer similar functionality in pg_dumpall to that already in pg_dump anyway. Regards, Dave. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] [HACKERS] [Fwd: Index Advisor]
I have looked over this patch, and it completes part of this TODO item: o Add SET PERFORMANCE_TIPS option to suggest INDEX, VACUUM, VACUUM ANALYZE, and CLUSTER Here is the foundation of it: For an incoming EXPLAIN command, the planner generates the plan and, if the Index Adviser is enabled, then the query is sent to the Index Adviser for any suggestions it can make. The Adviser derives a set of potentially useful indexes (index candidates) for this query by analyzing the query predicates. These indexes are inserted into the system catalog as virtual indexes; that is, they are not created on disk. Then, the query is again sent to the planner, and this time the planner makes it's decisions taking the just-created vitual indexes into account too. All index candidates used in the final plan represent the recommendation for the query and are inserted into the advise_index table by the Adviser. The gain of this recommendation is estimated by comparing the execution cost difference of this plan to the plan generated before virtual indexes were created. It involves a patch to the backend, and a /contrib module to access it. I think we have to decide if we want this, and whether it should be in /contrib or fully integrated into the backend. I am thinking the API needs to be simpified, perhaps by removing the system table and having the recommendations just logged to the server logs. --- Gurjeet Singh wrote: Hi All, Please find attached the latest version of the patch attached. It is based on REL8_2_STABLE. It includes a few bug fixes and an improvement to the size estimation function. It also includes a work-around to circumvent the problem we were facing earlier in xact.c; it now fakes itself to be a PL/xxx module by surrounding the BIST()/RARCST() calls inside an SPI_connect()/SPI_finish() block. Please note that the sample_*.txt files in the contrib module, which show a few different sample runs, may be a little out of date. Best regards, -- [EMAIL PROTECTED] [EMAIL PROTECTED] gmail | hotmail | yahoo }.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 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] -f output file option for pg_dumpall
Tom Lane wrote: Dave Page [EMAIL PROTECTED] writes: From: Tom Lane [EMAIL PROTECTED] I think forking a separate pg_dump for each database is a perfectly fine arrangement, and should be left alone. Hmm, would you be happy with my original proposal to add an append option to pg_dump? I don't object to it in principle, but I think a bit more thought is needed as to what's the goal. A stupid append option would be enough for pg_dumpall's current capabilities (ie, text output only) --- but is it reasonable to consider generalizing -Fc and -Ft modes to deal with multiple databases, and if so how would that need to change pg_dump's API? (I'm not at all sure this is feasible, but let's think about it before plastering warts onto pg_dump, not after.) Hmm, OK. I'll need to have a good look at the code before I can even think about commenting on that, which will have to wait until after I've finished bundling releases. Regards, Dave ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] 8.3 pending patch queue
Simon Riggs wrote: I'm not clear about the difference between the unapplied patches list and the hold list. What is the significance of the two lists? There's a number of patches submitted to pgsql-patches that don't show up on either list. I haven't made a list of these, but they include major patches such as Grouped Item indexes and a number of others. Many of those are clearly marked as ready to apply/review/reject. Can I request that those be reviewed first? The unapplied patches list looks long and many things on it aren't even patches, AFAICS - presumably TODO items-in-waiting? Some minor points: [PATCHES] Incrementally Updated Backup, Simon Riggs has already been applied to 8.2 [PATCHES] WAL logging freezing, Heikki Linnakangas has already been agreed/applied to 8.2 Thanks. These two items have been removed from the patches hold queue. -- 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: [HACKERS] 8.3 pending patch queue
On Sat, 2007-01-06 at 10:56 -0500, Bruce Momjian wrote: Simon Riggs wrote: All have been awaiting review for at least a month (though in one case the latest version is quite recent). They probably ought to be on the hold queue; all are ready to be reviewed for final application/rejection. I'd hasten to add that none of those are mine. My patches have received good attention, so I'm not complaining just completing admin. You might remember months ago that people were complaining I was pushing things into CVS too quickly, so while the patches are in my mailbox, they are not in the queue until I feel the community has the time to focus on it. I'm sorry if I explained that badly. All I meant to say was that the patches aren't on the queue for review, so could they be placed at the appropriate chronological point in the queue. (I was/am imagining the queue to be ordered in time of arrival). Patch review is, for me, harder than writing patches in the first place, so with that in mind I don't expect it to happen quickly. You've explained your on it now, so I'm patient. -- 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] [HACKERS] [Fwd: Index Advisor]
Kenneth Marshall wrote: One problem with only putting this information in the system logs is that when we provide database services to a member of our community we do not actually give them an account of the DB server or log server. This means that this very useful information would need to be passed through an intermediary or another tool developed to allow access to this information. I think that having this available from a table would be very nice. My two cents. Well, you can still run EXPLAIN manually and see the suggestions. I am not sure even how a system table is going to work in a shared environment for this usage. Perhaps we need to allow a table name to be passed using the EXPLAIN, or now that I think of it, EXPLAIN output is actually is just a single-column text table, and perhaps we would just need to give people a way of saving that off. The really nifty use seemed to be setting the GUC to ON and running and application, and capturing all the suggestions. Perhaps we need to be able to pass a single-text-column table as the GUC value and use that for capturing the output suggestions. But again, if you are doing it for an application and setting it for all logins, don't you probably have access to the server logs. Anyway, this is a new direction for us, but I think a useful one, and I find the implementation used here creative. --- Ken On Sat, Jan 06, 2007 at 04:08:24PM -0500, Bruce Momjian wrote: I have looked over this patch, and it completes part of this TODO item: o Add SET PERFORMANCE_TIPS option to suggest INDEX, VACUUM, VACUUM ANALYZE, and CLUSTER Here is the foundation of it: For an incoming EXPLAIN command, the planner generates the plan and, if the Index Adviser is enabled, then the query is sent to the Index Adviser for any suggestions it can make. The Adviser derives a set of potentially useful indexes (index candidates) for this query by analyzing the query predicates. These indexes are inserted into the system catalog as virtual indexes; that is, they are not created on disk. Then, the query is again sent to the planner, and this time the planner makes it's decisions taking the just-created vitual indexes into account too. All index candidates used in the final plan represent the recommendation for the query and are inserted into the advise_index table by the Adviser. The gain of this recommendation is estimated by comparing the execution cost difference of this plan to the plan generated before virtual indexes were created. It involves a patch to the backend, and a /contrib module to access it. I think we have to decide if we want this, and whether it should be in /contrib or fully integrated into the backend. I am thinking the API needs to be simpified, perhaps by removing the system table and having the recommendations just logged to the server logs. --- Gurjeet Singh wrote: Hi All, Please find attached the latest version of the patch attached. It is based on REL8_2_STABLE. It includes a few bug fixes and an improvement to the size estimation function. It also includes a work-around to circumvent the problem we were facing earlier in xact.c; it now fakes itself to be a PL/xxx module by surrounding the BIST()/RARCST() calls inside an SPI_connect()/SPI_finish() block. Please note that the sample_*.txt files in the contrib module, which show a few different sample runs, may be a little out of date. Best regards, -- [EMAIL PROTECTED] [EMAIL PROTECTED] gmail | hotmail | yahoo }.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 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 -- 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: [HACKERS] 8.3 pending patch queue
Simon Riggs wrote: On Sat, 2007-01-06 at 10:56 -0500, Bruce Momjian wrote: Simon Riggs wrote: All have been awaiting review for at least a month (though in one case the latest version is quite recent). They probably ought to be on the hold queue; all are ready to be reviewed for final application/rejection. I'd hasten to add that none of those are mine. My patches have received good attention, so I'm not complaining just completing admin. You might remember months ago that people were complaining I was pushing things into CVS too quickly, so while the patches are in my mailbox, they are not in the queue until I feel the community has the time to focus on it. I'm sorry if I explained that badly. All I meant to say was that the patches aren't on the queue for review, so could they be placed at the appropriate chronological point in the queue. (I was/am imagining the queue to be ordered in time of arrival). It is. Patch review is, for me, harder than writing patches in the first place, so with that in mind I don't expect it to happen quickly. You've explained your on it now, so I'm patient. The issue is that the _hold_ patches are for patches that arrived after feature freeze. The patches that arrived after 8.2 was released don't go in there because it might cause confusion. I also have to control how quickly I push out patches from the queue so as not to overwhelm folks. -- 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] 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 5: don't forget to increase your free space map settings
Re: [HACKERS] 8.3 pending patch queue
Bruce Momjian wrote: The issue is that the _hold_ patches are for patches that arrived after feature freeze. The patches that arrived after 8.2 was released don't go in there because it might cause confusion. I also have to control how quickly I push out patches from the queue so as not to overwhelm folks. Perhaps it would cause less confusion to name the queues for the version they will be reviewed/applied for, rather than to toggle between queue 1 and 2, the logic of which isn't aways immediately obvious to the causal observer. Regards, Dave. ---(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] 8.3 pending patch queue
Dave Page wrote: Bruce Momjian wrote: The issue is that the _hold_ patches are for patches that arrived after feature freeze. The patches that arrived after 8.2 was released don't go in there because it might cause confusion. I also have to control how quickly I push out patches from the queue so as not to overwhelm folks. Perhaps it would cause less confusion to name the queues for the version they will be reviewed/applied for, rather than to toggle between queue 1 and 2, the logic of which isn't aways immediately obvious to the causal observer. I don't actually toggle. Hold is for stuff during feature freeze. I am open to new names. -- 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: [HACKERS] 8.3 pending patch queue
Bruce Momjian wrote: Dave Page wrote: Bruce Momjian wrote: The issue is that the _hold_ patches are for patches that arrived after feature freeze. The patches that arrived after 8.2 was released don't go in there because it might cause confusion. I also have to control how quickly I push out patches from the queue so as not to overwhelm folks. Perhaps it would cause less confusion to name the queues for the version they will be reviewed/applied for, rather than to toggle between queue 1 and 2, the logic of which isn't aways immediately obvious to the causal observer. I don't actually toggle. Hold is for stuff during feature freeze. But then you go back to the other one once we're through freeze is what I mean. I am open to new names. patches-8_3 ? Anything coming in after FF then goes to patches-8_4. /D ---(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] 8.3 pending patch queue
Dave Page wrote: Bruce Momjian wrote: Dave Page wrote: Bruce Momjian wrote: The issue is that the _hold_ patches are for patches that arrived after feature freeze. The patches that arrived after 8.2 was released don't go in there because it might cause confusion. I also have to control how quickly I push out patches from the queue so as not to overwhelm folks. Perhaps it would cause less confusion to name the queues for the version they will be reviewed/applied for, rather than to toggle between queue 1 and 2, the logic of which isn't aways immediately obvious to the causal observer. I don't actually toggle. Hold is for stuff during feature freeze. But then you go back to the other one once we're through freeze is what I mean. I kind of do both at the same time until the hold queue is empty. I am open to new names. patches-8_3 ? Anything coming in after FF then goes to patches-8_4. The problem there is that the web site references these, so changing the URL for every release is odd, plus right now both queues are for 8.3. -- 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] InitPostgres and flatfiles question
Ron Mayer wrote: Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: What value is allowing multiple queies via PQexec() The only argument I can think of is that it allows applications to be sloppy about parsing a SQL script into individual commands before they send it. (I think initdb may be guilty of exactly that BTW...) At the same time you could argue that such sloppiness is inherently a Bad Idea. Doesn't it also avoid some network(?) overhead when you have a large number of small inserts or updates? I seem to recall a previous company where we had a major performance by concatenating a bunch of updates with ;s in between and sending them to postgresql as a single command. Added to TODO list: o Consider parsing the -c string into individual queries so each is run in its own transaction o Consider disallowing multiple queries in PQexec() as an additional barrier to SQL injection attacks -- 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: [HACKERS] InitPostgres and flatfiles question
Do we need a TODO for this? --- Tom Lane wrote: Markus Schiltknecht [EMAIL PROTECTED] writes: I've just found the stumbling block: the -c option of psql wraps all in a transaction, as man psql says: ... Thank you for clarification, I wouldn't have expected that (especially because CREATE DATABASE itself says, it cannot be run inside a transaction block... A transaction block (with BEGIN and COMMIT) seems to be more than just a transaction, right?) Hm, that's an interesting point. psql's -c just shoves its whole argument string at the backend in one PQexec(), instead of dividing at semicolons as psql does with normal input. And so it winds up as a single transaction because postgres.c doesn't force a transaction commit until the end of the querystring. But that's not a transaction block in the normal sense and so it doesn't trigger the PreventTransactionChain defense in CREATE DATABASE and elsewhere. I wonder whether we ought to change that? The point of PreventTransactionChain is that we don't want the user rolling back the statement post-completion, but it seems that psql -c 'CREATE DATABASE foo; ABORT; BEGIN; ...' would bypass the check. 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 -- 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: [HACKERS] Mark/Restore and avoiding RandomAccess sorts
I saw no replies to this. --- Simon Riggs wrote: Merge Joins require us to potentially Mark and Restore positions in the tuples arriving from executor sub-nodes. This currently means that if the tuples arrive from a Sort node, as they often do in an MJ, the sort node will be instructed to prepare a random access version of the sort result. That requires a full final merge of the output, so as to allow rewinding the input when a Restore operation is called. An MJ doesn't actually need random access, it just needs to be able to rewind. The question is: how far does it need to rewind? In many cases, the Restore operation moves back a small number of tuples, with a unique inner scan requiring a rewind of just one tuple. It would certainly be cheaper, in most cases, for the Sort node to maintain a variable size rewind buffer, where the history of prior tuples is truncated each time we perform a Mark operation. This could be implemented as a modified Tuplestore that could then be trimmed down each time a Mark operation took place. If the tuplestore has not yet spilled to disk this could be a trivial operation. Doing that would almost completely remove the overhead of the final merge step in the sort. The final merge often doubles elapsed time in cases where the sort is larger than work_mem, which it often is. Implementing the variable mark/restore buffer as a dumb Tuplestore would mean that the space usage of the Sort could in worst case go as high as x2 total space. The worst case is where the inner scan is all a single value. The best case is where the inner scan is sufficiently unique over all its values that it never writes back to disk at all. So a further refinement of this idea would be to simply defer the final merge operation for the sort until the history required for the Mark operation exceeded, say, 10% of the sort size. That would then be sufficient to improve performance for most common cases, without risking massive space overflow for large and highly non-unique data. There's no problem with running the final merge slightly later than before; everything's still there to allow it. Reusing space in the tuplestore is also straightforward since that's exactly what the final merge already does, so some rework of that code should be sufficient. This is a separate, but related idea of being able to avoid mark/restores completely when the outer scan is provably unique. I'm not intending to implement this idea just yet, but it seemed worth recording since it occurred to me - and discussing it as a TODO item. Comments? -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(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 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_ctl options
Added to TODO: * Make consistent use of long/short command options --- pg_ctl needs long ones, pg_config doesn't have short ones, postgres doesn't have enough long ones, etc. --- Andrew Dunstan wrote: I notice that quite a few pg_ctl options have no long form equivalents, namely these: NopPwW Also, none of the long forms seems to be documented at all. Should this be cleared up (maybe a nice first project for lurking new contributors)?\ If we don't want long forms for some reason, then a comment in the code saying why would make sense. cheers andrew ---(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 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [pgsql-advocacy] PGCon 2007 Program Committee
On Sat, 6 Jan 2007, Dan Langille wrote: On 6 Jan 2007 at 12:09, Oleg Bartunov wrote: What's about spronsoring ? Are you asking if your and Teodor can be sponsored? yes I and Teodor would like to present new full text search, now built into PostgreSQL core. We already have patch for 8.3 and current documentation is available http://mira.sai.msu.su/~megera/pgsql/ftsdoc/ Please submit a proposal at the URL below. :) ok, I'll try, but first I need to know about support. It's too expensive for us. Oleg On Fri, 5 Jan 2007, Dan Langille wrote: I have the pleasure of announcing your PGCon 2007 program committee. Bruce Momjian Christopher Browne Josh Berkus Robert Treat Luke Lonergan Neil Conway Robert Bernier These people are responsible for reviewing your submissions and selecting the presentations for PGCon 2007. Speaking of presentations, now that the major holidays are over, please submit your proposal now. Instructions for submissions are at http://www.pgcon.org/2007/submissions.php The original call for papers: http://www.pgcon.org/2007/papers.php NOTE: Please get your proposal in by 19 Jan (that is in two weeks). Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [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 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_ctl options
Bruce Momjian wrote: Added to TODO: * Make consistent use of long/short command options --- pg_ctl needs long ones, pg_config doesn't have short ones, postgres doesn't have enough long ones, etc. Certainly postgres has plenty of long ones. And I don't know why pg_config would need short ones. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] pg_ctl options
Peter Eisentraut wrote: Bruce Momjian wrote: Added to TODO: * Make consistent use of long/short command options --- pg_ctl needs long ones, pg_config doesn't have short ones, postgres doesn't have enough long ones, etc. Certainly postgres has plenty of long ones. And I don't know why I don't see them. pg_config would need short ones. Seems we should have some, though you could make the case it has too many options to support single letters. -- 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: [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 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] Plannode with a righttree
Hi! I'm trying to implement a top level node that must have a lefttree and a righttree. The lefttree consists of a regular tree of other nodes required for the query (that I let postgres' optimizer build on its own) and the righttree should be a table scan (of a table called for in the FROM clause of the query). My problem is, that I don't manage to create that scan node for the table and properly attach it to my node. Any help or pointers will be greatly appretiated... -- Vanessa V. Gonzalez D.
Re: [HACKERS] pg_ctl options
Bruce Momjian wrote: pg_config would need short ones. Seems we should have some, But why? What is the use case? It's not like pg_config is a frequently typed command. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
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 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [pgsql-advocacy] PGCon 2007 Program Committee
On 7 Jan 2007 at 1:39, Oleg Bartunov wrote: On Sat, 6 Jan 2007, Dan Langille wrote: On 6 Jan 2007 at 12:09, Oleg Bartunov wrote: What's about spronsoring ? Are you asking if your and Teodor can be sponsored? yes I and Teodor would like to present new full text search, now built into PostgreSQL core. We already have patch for 8.3 and current documentation is available http://mira.sai.msu.su/~megera/pgsql/ftsdoc/ Please submit a proposal at the URL below. :) ok, I'll try, but first I need to know about support. It's too expensive for us. Please submit your proposal. If it is accepted, we'll find a way to get you there. -- Dan Langille : Software Developer looking for work my resume: http://www.freebsddiary.org/dan_langille.php PGCon - The PostgreSQL Conference - http://www.pgcon.org/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] pg_ctl options
Peter Eisentraut wrote: Bruce Momjian wrote: pg_config would need short ones. Seems we should have some, But why? What is the use case? It's not like pg_config is a frequently typed command. I thought consistency. Why do any of the commands have long and short options? -- 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: [HACKERS] InitPostgres and flatfiles question
On Fri, Jan 05, 2007 at 08:45:51PM -0500, Tom Lane wrote: Ron Mayer [EMAIL PROTECTED] writes: Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: What value is allowing multiple queies via PQexec() The only argument I can think of is that it allows applications to be sloppy about parsing a SQL script into individual commands before they send it. (I think initdb may be guilty of exactly that BTW...) At the same time you could argue that such sloppiness is inherently a Bad Idea. Doesn't it also avoid some network(?) overhead when you have a large number of small inserts or updates? I seem to recall a previous company where we had a major performance by concatenating a bunch of updates with ;s in between and sending them to postgresql as a single command. These days you'd probably be better off using a multi-row VALUES() list if relevant. Also, if you really want to send multiple statements like that, there's a cleaner way to do it: use the extended query protocol and don't Sync or wait for a reply until you've sent them all. regards, tom lane In shell scripts that do things in the database I often put 1 statement in the line. Since it is the shell, I want quick results. Usually it is an INSERT/UPDATE followed by a SELECT. It would be very frustrating not to be able to send multiple commands with one -c in psql. --elein ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] InitPostgres and flatfiles question
elein wrote: On Fri, Jan 05, 2007 at 08:45:51PM -0500, Tom Lane wrote: Ron Mayer [EMAIL PROTECTED] writes: Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: What value is allowing multiple queies via PQexec() The only argument I can think of is that it allows applications to be sloppy about parsing a SQL script into individual commands before they send it. (I think initdb may be guilty of exactly that BTW...) At the same time you could argue that such sloppiness is inherently a Bad Idea. Doesn't it also avoid some network(?) overhead when you have a large number of small inserts or updates? I seem to recall a previous company where we had a major performance by concatenating a bunch of updates with ;s in between and sending them to postgresql as a single command. These days you'd probably be better off using a multi-row VALUES() list if relevant. Also, if you really want to send multiple statements like that, there's a cleaner way to do it: use the extended query protocol and don't Sync or wait for a reply until you've sent them all. regards, tom lane In shell scripts that do things in the database I often put 1 statement in the line. Since it is the shell, I want quick results. Usually it is an INSERT/UPDATE followed by a SELECT. It would be very frustrating not to be able to send multiple commands with one -c in psql. We aren't going to disable that --- we are considering disabling the backend from treating it as a single transaction. -- 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] InitPostgres and flatfiles question
Bruce Momjian [EMAIL PROTECTED] writes: Do we need a TODO for this? Well, if we *don't* change the backend to disallow multi statements per PQexec, then we'd probably better do something about this. If we do make that change then it's not a problem anymore. Hm, that's an interesting point. psql's -c just shoves its whole argument string at the backend in one PQexec(), instead of dividing at semicolons as psql does with normal input. And so it winds up as a single transaction because postgres.c doesn't force a transaction commit until the end of the querystring. But that's not a transaction block in the normal sense and so it doesn't trigger the PreventTransactionChain defense in CREATE DATABASE and elsewhere. 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_ctl options
On Sat, 2007-01-06 at 20:14 -0500, Bruce Momjian wrote: Peter Eisentraut wrote: Bruce Momjian wrote: pg_config would need short ones. Seems we should have some, But why? What is the use case? It's not like pg_config is a frequently typed command. I thought consistency. Why do any of the commands have long and short options? That would be my argument. Consistency is good. 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 6: explain analyze is your friend
Re: [HACKERS] InitPostgres and flatfiles question
Bruce Momjian [EMAIL PROTECTED] writes: We aren't going to disable that --- we are considering disabling the backend from treating it as a single transaction. Or even more specifically, making sure that that only happens if you explicitly put begin/commit into the -c string. One thing I think we probably ought *not* change is that if any of the statements fail the rest of the -c string is abandoned --- that is, -c would work like a script with ON_ERROR_STOP enabled. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] [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] 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 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] pg_ctl options
Bruce Momjian [EMAIL PROTECTED] writes: Peter Eisentraut wrote: Certainly postgres has plenty of long ones. And I don't know why I don't see them. postgres/postmaster accept --any-guc-variable=value. AFAIR all the single-letter options these days are equivalent to one of those. There's not anything else to do there, except perhaps fix the documentation (I'm not clear on why Table 17-1 is where it is and not on the postgres command reference page). I can't get excited about inventing short options for pg_config. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [PATCHES] COPY with no WAL, in certain circumstances
Robert Treat [EMAIL PROTECTED] writes: On Saturday 06 January 2007 16:36, Simon Riggs wrote: snip 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: [HACKERS] [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: [HACKERS] [COMMITTERS] pgsql: Check for ERANGE in exp()
Bruce Momjian [EMAIL PROTECTED] writes: Tom, on HPPA, does ERANGE get set for both overflow and underflow? I assume only overflow. Yeah, AFAICT exp() just returns zero for underflow cases. I get regression=# select exp(-2000); ERROR: value out of range: underflow but I was getting that before your last patch, too. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [COMMITTERS] pgsql: Check for ERANGE in exp()
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Tom, on HPPA, does ERANGE get set for both overflow and underflow? I assume only overflow. Yeah, AFAICT exp() just returns zero for underflow cases. I get regression=# select exp(-2000); ERROR: value out of range: underflow but I was getting that before your last patch, too. Uh, if you were getting that before my last patch, then I don't think you return ERANGE for underflow. -- 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] 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 5: don't forget to increase your free space map settings
Re: [HACKERS] [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 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [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 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] 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 2: Don't 'kill -9' the postmaster
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 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 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
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 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
