Re: [PATCHES] Fix for VACUUM in psql autocommit off
Tom Lane wrote: If we're going to do that, we should also include the other statements that disallow execution in a transaction, and we should rename is_transact_command to something more appropriate (not to mention fix its comments). A quick grep shows PreventTransactionChain((void *) stmt, CREATE DATABASE); PreventTransactionChain((void *) dbname, DROP DATABASE); PreventTransactionChain((void *) stmt, CLUSTER); PreventTransactionChain((void *) dbname, REINDEX DATABASE); PreventTransactionChain((void *) vacstmt, stmttype); PreventTransactionChain((void *) stmt, CREATE TABLESPACE); PreventTransactionChain((void *) stmt, DROP TABLESPACE); Handling the multi-keyword cases is going to take a nontrivial increment of functionality. Perhaps while we're at it, we could teach this code about nested /* comments ... Currently there is no need for nested comments, because those are only single word queries. Or do I not understand what you mean by nested comments? (There is code for ignore /* .. */ before the first keyword.) Any suggestion how to that? I can think of a way myself, but it may not be the best, as I don't consider C my natural language. I can try, or does anyone else feel inclined to fix this? Best Regards, Michael Paesold ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] Fix for VACUUM in psql autocommit off
Tom Lane wrote: It is now fixed in the attached patch. Applied with some additional cleanup (the code wasn't multibyte-aware, and so could get fooled in some Far Eastern encodings). I am very pleased to hear. This was my first patch submitted. :-) Best Regards, Michael Paesold ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PATCHES] Fix for VACUUM in psql autocommit off
Tom Lane wrote: It is now fixed in the attached patch. Applied with some additional cleanup (the code wasn't multibyte-aware, and so could get fooled in some Far Eastern encodings). Looking at your cleanup is a good for learning more about C. :-) But I have one another question, you wrote: ---(end of broadcast)--- TIP 3: 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
[PATCHES] psql \set case sensitive for boolean (OFF/off)
psql is currently case sensitive for boolean values in \set, only off is regarded as off, whereas OFF is regarded as on. There is a comment in the code that explains, why other values than off are always treated as true: for backwards compatibility, anything except off is taken as true I would recommend to make this check at least case insensitive, because a) Since SQL is case insensitive, the current behaviour is not what you would expect. b) I have stumbled over this myself (setting AUTOCOMMIT to 'OFF' and then having problems because it was on) c) I think there are other who were/will be bitten by this (recommendation on general to use --set AUTOCOMMIT=OFF...) I would change strcmp to strcasecmp in GetVariableBool in variable.c. For the other functions (VariableEquals e.g.) I cannot speak, since I don't know if the case sensitivity is needed. Patch attached. Is strcasecmp ok, or should pg_strcasecmp be used here? Best Regards, Michael Paesold psql-autocommit.diff Description: Binary data ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PATCHES] psql \set case sensitive for boolean (OFF/off)
I wrote: Patch attached. Is strcasecmp ok, or should pg_strcasecmp be used here? I don't know how I did it, but this was the wrong patch. Correct patch attached now. Best Regards, MIchael Paesold variables.c.diff Description: Binary data ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[PATCHES] psql: rollback only last query on error
I am sorry for kind of reposting this, but I have not got any response to my patch sent for comment to hackers (Subject: Rollback on error): http://archives.postgresql.org/pgsql-hackers/2004-09/msg00576.php I just want to find out, if I should try to solve the issues with this patch (and add regression tests, docs, etc.) now or leave it till after release of version 8.0. Reading responses on my intial post I think the feature is not unwelcome, at least if implemented well, so that it will not do anything unexpected. I understand this is beta now, and this is a new feature in psql. Nevertheless I believe it is good to include the feature now, because a) It increases the testing of savepoints since more people will use savepoints (all who activate the mode in psql that my patch provides). b) Given I finish the open issues, the patch has no backward compatibility issues with scripts etc., even if you put \set IMPLICIT_SAVEPOINTS 'on' in .psqlrc. For IMPLICIT_SAVEPOINTS 'off' there is no change at all. c) The code change is rather local and does not add much complexity. d) Some people trying 8.0 for the first time might find the current behavior of psql odd if they are used to oracle, mssql etc. At least at the interactive level, the patch would give them the option to have their accustomed way of handling e.g. typos. Thank you for your time and thank you for any response! Best Regards, Michael Paesold P.S. attached is a version of the patch with more/better comments. savepoints.patch Description: Binary data ---(end of broadcast)--- TIP 3: 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] psql: rollback only last query on error
Alvaro Herrera wrote: On Tue, Sep 21, 2004 at 02:30:17PM +0200, Michael Paesold wrote: http://archives.postgresql.org/pgsql-hackers/2004-09/msg00576.php One potential problem I see with the patch is that it opens lots of savepoints but does not release any. In this mode, given autocommit off (and even without that), there's potential for lots and lots of savepoints. Not sure how to fix that given that you shouldn't release user-specified savepoints ... Well, given that EXCEPTION blocks in Pl/pgSQL and the like also never release savepoints I think there will be more issues with savepoints in that area. Nevertheless, I have added a note to the documentation about the feature that warns of the possible consequences of those many savepoints. Also Tom Lane mentioned in another thread that even releasing savepoints does not really help because of the other resources used, e.g. for trx id locks, that can't be released at all till COMMIT. Also, you need to do something with \i. I think the global variable will be a less intrusive approach, at least while we are in beta. When 8.1 development starts, you can submit a patch to clean up. I have added a field to the pset settings to do that. Everything works now as I expected it to do. Using \i and psql file.sql the feature is disabled, otherwise it's controlled by the variable IMPLICIT_SAVEPOINTS and the transaction state (of course not useful when AUTOCOMMIT is on and no BEGIN issued). A final thing is the name for this option... I don't really like IMPLICIT_SAVEPOINTS. Other ideas were AUTO_SAVEPOINT, STATEMENT_SAVEPOINTS or STATEMENT_ROLLBACK. They aren't really good either... Patch is attached for review. Please decide if this can be included with PostgreSQL 8.0 or not. If so, I would like to add a regression test for the feature. In that case, could you please tell me where to put that one? Best Regards, Michael savepoints.patch Description: Binary data ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PATCHES] psql: rollback only last query on error
Michael Paesold [EMAIL PROTECTED] writes: Alvaro Herrera wrote: One potential problem I see with the patch is that it opens lots of savepoints but does not release any. Well, given that EXCEPTION blocks in Pl/pgSQL and the like also never release savepoints That statement is flat wrong. I have to say that I am sorry that I misunderstood that. Regards, Michael Paesold -- +++ GMX DSL Premiumtarife 3 Monate gratis* + WLAN-Router 0,- EUR* +++ Clevere DSL-Nutzer wechseln jetzt zu GMX: http://www.gmx.net/de/go/dsl ---(end of broadcast)--- TIP 3: 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] Casting INT4 to BOOL...
Peter Eisentraut wrote: Sean Chittenden wrote: Alrighty. Do you want an updated patch for the single character tweak or can you futz with it before committing? :) I oppose casts from boolean to integer or vice versa. Even _explicit_ casts only? It would not have any bad side effects on people not using it, would it? Best Regards, Michael Paesold ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PATCHES] pg_autovacuum Win32 Service startup delay
Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: On Mon, Jan 24, 2005 at 06:57:54PM -0500, Tom Lane wrote: (Five minutes at least has a defensible rationale, ie it's the default checkpoint interval and we expect we can replay the log at least as fast as it was created initially.) Hmm, I remember Mark Wong from OSDL saying that it took to replay the logs after a crash more than the six hours it had taken to generate them. Six hours? Did he have checkpoints disabled somehow? No, I remember they were talking about recovery from backup using PITR. (i.e. not simple crash recovery, but replaying the logs from the whole benchmark session) Best Regards, Michael Paesold ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PATCHES] WAL: O_DIRECT and multipage-writer
ITAGAKI Takahiro wrote: I think that there is room for improvement in WAL. Here is a patch for it. I think you should resend your patch as a context diff (diff -c). Otherwise it's hard to see what your patch does. Best Regards, Michael Paesold ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PATCHES] dbsize patch
Neil Conway wrote: On Tue, 2005-01-25 at 16:49 -0700, Ed L. wrote: The attached dbsize patch: + makes relation_size(relname) include toast tables; + adds aggregate_relation_size(relname) to count table data and indices; + adds indices_size(relname) to report the size of indices for a relation; I've minimally tested it against PostgreSQL 8.1devel on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.2 20030222 (Red Hat Linux 3.2.2-5). Barring any objections, I'll apply this to HEAD tomorrow. Perhaps you could rename indices_size to indexes_size. A quick google search on site:postgresql.org indices and site:postgresql.org indexes shows that indices is used much less (7,080) than indexes (23,400). Top hits for indices are 7.1 docs, for indexes it's 7.3 and 7.4. It seems to me that indexes is the term more commonly used with postgresql. Best Regards, Michael ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PATCHES] Continue transactions after errors in psql
Robert Treat wrote: I've attached a revised patch which fixes the problem, however I'm sure there is a better way. Thanks to Neil for putting up with me on irc :-) In September 2004 I had already sent a patch to implement this behaviour, the patch, still in the archives, is here: http://archives.postgresql.org/pgsql-patches/2004-09/bin00040.bin (savepoints.patch) There are some issues it addressed: Assuming you put this option in your .psqlrc file, you will still probably not want this to be active when you execute commands from a file (non-interactive). So pset.notty must be checked. Again, when using \i, resetting errors seems dangerous. Using \i should also temporarily disable those savepoints. The real problem with my patch was, that it did not release the savepoints. Why? Look at this example (with the current patch reseterrors patch): template1=# \reseterror Reset error is on. template1=# BEGIN; BEGIN template1=# SAVEPOINT a; SAVEPOINT template1=# CREATE TABLE TEST ( a integer); CREATE TABLE template1=# ROLLBACK TO a; ERROR: no such savepoint So to get this right, you have to track savepoints created by the user and only release psql savepoints when there is no user savepoint sitting on top of your savepoint. Two ways come to my mind: 1) Parse SQL for savepoint and rollback to and create a stack of all savepoints. Then you can always release all savepoints as long as they are your own. 2) Implement a server-side function to get the savepoints from the server and query that before every release. What do you think? Best Regards, Michael Paesold ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PATCHES] Continue transactions after errors in psql
Greg Sabino Mullane wrote: Michael Paesold wrote: 2) Implement a server-side function to get the savepoints from the server and query that before every release. I could not find a way to do this. Is there any interface to the list? Alvaro suggested to implement such a function. It is not there yet. I think you would have to access the sub xact stack, but I have not looked into that code for quite some time. http://archives.postgresql.org/pgsql-general/2004-10/msg00370.php First, I'm not of the opinion that it should automatically be turned off when running non-interactively. That's too much assuming of what the user wants, when this is a settable flag. However, it should be settable via a script to a definite state. So \reseterror will take an optional argument, off or on, which sets it rather than toggles it. Discussion here last year showed some concern from people that this feature could bite people and is not really safe. Perhaps the best way would be to create three states: \reseterrors (on|off|auto) where auto means it's only active for interactive queries. (auto could be named interactive) The other problem is not stepping on other people's savepoints. The best solution we came up with was to check for savepoint commands ourselves, similar to the way psql already checks for transaction affecting commands, and handle things appropriately. Specifically, if someone issues a savepoint while in \reseterror mode, it switches off automatically*. Since the implementation of reseterror is pretty much a lazy shortcut to issuing savepoints yourself, it should be safe to say that you do not want to mix manual and automatic ones, and we'll back off (with a message) if you issue your own. Plus there will be a warning in the docs to be careful about mixing savepoints and the \reseterror method. * We could also switch it back on after rollback or release, but this would entail a little more tracking. Comments? I would prefer a solution, where the feature is not disabled as soon as I use my own savepoints. I like \reseterror because it prevents making typos from aborting my transaction. Explicit savepoints I rather use to try a whole bunch of statements and then decide if I want to commit so far. I can still make typos. If you don't want to, I can implement such a savepoint stack. I don't think it's that hard. The parsing, as you mentioned, should also not be too hard, because the infrastructure (removing white space) is already there. Best Regards, Michael Paesold ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] Continue transactions after errors in psql
Greg Sabino Mullane wrote: Finally had a chance to sit down at look at this afresh, and I'm pretty sure I've got all the kinks worked out this time. Apologies for not attaching, but my mail system is not working well enough at the moment. So, please try to break this patch: http://www.gtsm.com/pg/psql_error_recovery.diff Some suggestions in random order: * I think you should use PSQLexec instead of using PQexec directly. PSQLexec is used by all \-commands and prints out queries with -E, which is very helpful for debugging. -E display queries that internal commands generate * You do not check for the server version before activating \reseterror. - use PQserverVersion() to check for = 8 * Perhaps the name should be \reseterrors (plural)? Just my personal opinion though. * If I read the code correctly, you now don't destroy user savepoints anymore, but on the other hand, you do not release the psql savepoint after a user-defined savepoint is released. In other words, each time a user creates a savepoint, one psql savepoint is left on the subxact stack. I don't know if this is a real problem, though. * You have not yet implemented a way to savely put \reseterror in .psqlrc. I previously suggested an AUTO setting (additional to ON/OFF) that disables \reseterror when reading from a non-tty. So putting \reseterror AUTO in .psqlrc would be save. Otherwise, I could not find a way to break it. :-) Best Regards, Michael Paesold ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] Continue transactions after errors in psql
Greg Sabino Mullane wrote: * You have not yet implemented a way to savely put \reseterror in .psqlrc. I previously suggested an AUTO setting (additional to ON/OFF) that disables \reseterror when reading from a non-tty. So putting \reseterror AUTO in ..psqlrc would be save. Hmm...I suppose we could do that. Do we have anything else that does something similar? I guess I'm not convinced that we need to change a switch's behavior based on the tty status. I do think so. In it's current state, would you yourself put \reseterror in your .psqlrc? Or even an /etc/psqlrc? It would break all my scripts that must either succeed or fail -- now they will produce garbage in my databases when something goes wrong! In my opinion, the behaviour should depend on tty in all settings, but I am o.k. with an AUTO setting, because so it's at least usable. I think without tty-detection, the patch just conflicts with PostgreSQL philosophy that the user should be kept save from unintended data-destruction. The SQL-Standard itself says that errors inside transactions should only rollback the last statement, if possible. So why is that not implemented in PostgreSQL? What I read from past discussions here, is because it's just unsave and will lead to data-garbage if you aren't very careful. * If I read the code correctly, you now don't destroy user savepoints anymore, but on the other hand, you do not release the psql savepoint after a user-defined savepoint is released. In other words, each time a user creates a savepoint, one psql savepoint is left on the subxact stack. I don't know if this is a real problem, though. Correct. More detail: we release our own temporary savepoint, unless the user has successfully implemented their own savepoint... The current way is ok for me at the moment. I still think there is a better way (parsing statements like it's already done for no-transaction-allowed-statements), but hey, as soon as your patch will be applied, I can myself propose another patch to improve this. ;-) Best Regards, Michael Paesold ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PATCHES] Continue transactions after errors in psql
Bruce Momjian wrote: Michael Paesold wrote: Some suggestions in random order: * I think you should use PSQLexec instead of using PQexec directly. PSQLexec is used by all \-commands and prints out queries with -E, which is very helpful for debugging. -E display queries that internal commands generate It is now \set ON_ERROR_ROLLBACK, and PQexec seems right for that. Also, this isn't something like \d where anyone would want to see the queries, I think. I just thought it was nice for debugging. E.g. your example below would be more easy to analyze if one could see the queries with -E. * You do not check for the server version before activating \reseterror. - use PQserverVersion() to check for = 8 Added. Patch just posted. Ok, looks good. * Perhaps the name should be \reseterrors (plural)? Just my personal opinion though. Changed, as you see above. My first patch for this feature (last year) also used \set. I think this is more consistent. On the other hand there is no auto-completition for \set. Perhaps this should be added later. * If I read the code correctly, you now don't destroy user savepoints anymore, but on the other hand, you do not release the psql savepoint after a user-defined savepoint is released. In other words, each time a user creates a savepoint, one psql savepoint is left on the subxact stack. I don't know if this is a real problem, though. Interesting. I thought this would fail, but it doesn't: [example...] Yeah, I tried that earlier. What Greg's code does, effectively, is to move the savepoint down below the SAVEPOINt/RELEASE/ROLLBACK so it doesn't discard the user command. Nice trick: [code...] I think it is quite good. But note: I did not say that the feature broke user savepoint, I just mentioned that with user savepoints, some (internal) savepoint could be left on the stack (in the server) until the user defined savepoints below the interal ones would be released. Nevertheless, I think this is no problem in the real-world. * You have not yet implemented a way to savely put \reseterror in .psqlrc. I previously suggested an AUTO setting (additional to ON/OFF) that disables \reseterror when reading from a non-tty. So putting \reseterror AUTO in .psqlrc would be save. Good question, or rather, should ON_ERROR_ROLLBACK have an effect when commands come from a file? There is no way to test for the error in psql so it seems you would never want the transaction to continue after an error. I am inclined to make ON_ERROR_ROLLBACK work only for interactive sessions, just like ON_ERROR_STOP works only for non-interactive sessions. +1 for disabling ON_ERROR_ROLLBACK if pset.cur_cmd_interactive is false. Or provide another switch that can be put in .psqlrc and is only activated for pset.cur_cmd_interactive. Btw. thanks Bruce for getting this done. Best Regards, Michael Paesold ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] [PATCHES] Continue transactions after errors in psql
Greg Sabino Mullane wrote: To reiterate my opinion, I think the behavior should be the same for interactive and non-interactive sessions. Not only will it prevent nasty surprises, but unless we make a third 'setting', there will be no way to enable this in non-interactive scripts, which is something that I would want to be able to do. I don't buy the but what if I set it in .psqlrc and forget argument. That could be applied to a lot of things you could put in there. This setting defaults to off and must be explicitly enabled. I'd be okay with a smart mode that explicitly enables the interactive/non-interactive split. But people (like me for example) will want to enable this behaviour by default. So they (me too) will put the option in .psqlrc. It is then enabled by default. But then many of my scripts will destroy data instead of just erroring out. I just don't see why non-interactive mode does need such a switch because there is no way to check if there was an error. So just put two queries there and hope one will work? If you really want this for scripts, there must be two options: * one to put savely into .psqlrc (what some people will want, I have \set AUTOCOMMIT off in my .psqlrc file, too, and I know I am not the only one) * another one that will also work in scripts I hope you understand and accept the issue here. Best Regards, Michael Paesold ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [PATCHES] Continue transactions after errors in psql
Richard Huxton wrote: Michael Paesold wrote: But people (like me for example) will want to enable this behaviour by default. So they (me too) will put the option in .psqlrc. It is then enabled by default. But then many of my scripts will destroy data instead of just erroring out. I just don't see why non-interactive mode does need such a switch because there is no way to check if there was an error. So just put two queries there and hope one will work? DROP TABLE foo; CREATE TABLE foo... This would be: \set AUTOCOMMIT off DROP TABLE foo; -- error, rolled back CREATE TABLE foo ... COMMIT; You could as well do: \set AUTOCOMMIT on -- default DROP TABLE foo; -- print error message CREATE TABLE foo ... There is not much difference, except for locking, ok. I see your point, but I don't think this makes enabling it by default (even in .psqlrc) any safer. Best Regards, Michael Paesold ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] [HACKERS] patches for items from TODO list
Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: Here is an updated version of the COPY \x patch. It is the first patch attached. Also, I realized that if we support \x in COPY, we should also support \x in strings to the backend. This is the second patch. Do we really want to do any of these things? We've been getting beaten up recently about the fact that we have non-SQL-spec string escapes (ie, all the backslash stuff) so I'm a bit dubious about adding more, especially when there's little if any demand for it. I don't object too much to the COPY addition, since that's outside any spec anyway, but I do think we ought to think twice about adding this to SQL literal handling. +1 from me on this for Tom -- please don't break spec compliance! Best Regards, Michael Paesold ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PATCHES] [HACKERS] Proposed TODO: --encoding option for pg_dump
Alvaro Herrera wrote: On Tue, Jun 28, 2005 at 10:24:19PM +0200, Magnus Hagander wrote: I *think* that's easy enough to do in time for 8.1. Trivial patch attached. I hope it's enough :-) It passed my very quick testing... (Yup, I read the mails aobut PGCLIENTENCODING, but an option to pg_dump is certainly easier) You forgot to document the long option, I think. Are the man pages generated from the sgml docs? Have never had a look at that. Best Regards, Michael Paesold ---(end of broadcast)--- TIP 3: 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] [PATCHES] Dbsize backend integration
Bruce Momjian wrote: Dave Page wrote: pg_relation_size(text) - Get relation size by name/schema.name pg_relation_size(oid)- Get relation size by OID pg_tablespace_size(name) - Get tablespace size by name pg_tablespace_size(oid) - Get tablespace size by OID pg_database_size(name) - Get database size by name pg_database_size(oid)- Get database size by OID pg_table_size(text) - Get table size (including all indexes and toast tables) by name/schema.name pg_table_size(oid)- Get table size (including all indexes and toast tables) by OID pg_size_pretty(int8) - Pretty print (and round) the byte size specified (eg, 123456 = 121KB) OK, so you went with relation as heap/index/toast only, and table as the total of them. I am not sure that makes sense because we usually equate relation with table, and an index isn't a relation, really. Do we have to use pg_object_size? Is there a better name? Are indexes/toasts even objects? Relation is not an ideal names, but I heard people talk about heap relation and index relation. Indexes and tables (and sequences) are treated in a similar way quite often. Think of ALTER TABLE example_index RENAME TO another_index. This is even less obvious. Of course in relational theory, an index would not be a relation, because an index is just implementation detail. I don't like object_size any better, since that makes me rather think of large objects or rows as objects (object id...). Perhaps pg_table_size should be split into pg_table_size and pg_indexes_size, where pg_indexes_size is the aggregate of all indexes on a table und pg_table_size is just table+toast+toast-index. If noone has a better idea for pg_relation_size, I would rather keep it for consistency with the contrib module, and because it's not too far off. Best Regards, Michael Paesold ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PATCHES] Disable page writes when fsync off, add GUC
Bruce Momjian wrote: Bruce Momjian wrote: This also adds a full_page_writes GUC to turn off page writes to WAL. Some people might not want full_page_writes. Fsync linkage removed, patch attached and applied. ... + When this option is on, the productnamePostgreSQL/ server + writes full pages to WAL when they first modified after a checkpoint + so full recovery is possible. I believe this should be when they _are_ first modified after. Perhaps you should also mention power failure, not only an operating system crash as disaster scenario, even if the latter includes the former. Best Regards, Michael Paesold ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PATCHES] [HACKERS] Autovacuum loose ends
Alvaro Herrera wrote: I still haven't added custom cost-based delays, but I don't see that as a showstopper for removing it. I just went through the CVS log and I don't see anything else that applies. I think you should at least add an autovacuum specific value for vacuum_cost_delay because it turns cost-based vacuum delay on or off. I believe not many will have vacuum_cost_delay enabled in postgresql.conf, but will want to enable it for autovacuum. At least I do. Best Regards, Michael Paesold ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] [HACKERS] Autovacuum loose ends
Alvaro Herrera wrote: Here is another patch for autovacuum: ... - Xid-wraparound VACUUM is now FULL without ANALYZE Am I right in my assumption that this VACUUM FULL can happen for any database, not just a template database? I think this is a bad idea. Vacuum full is not an option for our and many other production databases. I suggest that should be a plain VACUUM. Otherwise I think you have done great job finally integrating auto vacuum into the backend. Best Regards, Michael Paesold ---(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] Autovacuum docs
Alvaro Herrera wrote: These settings control the default behavior for the autovacuum daemon. Please refer to _Section 22.1.4_ for more information. I don't see how to cleanly fit The auto-vacuum daemon in that sentence in a way that looks like a reference. So, in short, if there is a policy on this, I propose to update it to use endterm wherever the surrounding text allows it. IIRC the reasoning for not using endterm is that the docs should work well with output formats that don't have hyperlinks, e.g. print. There it is necessary to include the section number for easy navigation. Best Regards, Michael Paesold ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[PATCHES] Bug in psql (on_error_rollback)
There is a bug in psql for the new ON_ERROR_ROLLBACK feature. In AUTOCOMMIT off mode it does not work correctly for the first statement. This is how it works usually: postgres=# \set AUTOCOMMIT off postgres=# \set ON_ERROR_ROLLBACK interactive postgres=# SELECT 1; ?column? -- 1 (1 row) postgres=# SELECT a; ERROR: column a does not exist postgres=# SELECT 1; ?column? -- 1 (1 row) postgres=# BEGIN; WARNING: there is already a transaction in progress BEGIN postgres=# ROLLBACK; ROLLBACK For the first statement in a transaction after the implicit BEGIN it does not work: postgres=# ROLLBACK; ROLLBACK postgres=# postgres=# SELECT a; ERROR: column a does not exist postgres=# SELECT 1; ERROR: current transaction is aborted, commands ignored until end of transaction block With the attaced patch it works correctly even for the first statement. postgres=# \set AUTOCOMMIT off postgres=# \set ON_ERROR_ROLLBACK interactive postgres=# SELECT a; ERROR: column a does not exist postgres=# SELECT 1; ?column? -- 1 (1 row) postgres=# BEGIN; WARNING: there is already a transaction in progress BEGIN postgres=# ABORT; ROLLBACK Please check the patch and apply to CVS tip. I think it would be good to add regression tests for AUTOCOMMIT and ON_ERROR_ROLLBACK and possibly others. There are currently no regression tests specifically for psql features, but since the regression tests are executed via psql, there would be no problem in creating a set of such tests, right?. I could write some. Best Regards, Michael Paesold psql.patch Description: Binary data ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] Proposed patch for sequence-renaming problems
Tom Lane wrote:
Here's an updated version of the patch. There's now just one nextval()
function, taking regclass, and backwards compatibility is handled
through an implicit text-to-regclass cast. Existing dumps will not see
any behavioral changes because nextval('foo') will be dumped as
nextval('foo'::text), but new entries of nextval('foo') will be
captured as regclass constants instead.
I noted that this version caused a couple more regression tests to fail;
for instance, the constraints test was expecting that it could drop and
recreate a sequence that was referenced by a default expression spelled
as nextval('foo'). So we are paying for improved ease of use by
taking a larger backwards-compatibility risk than the original patch
did.
Last call for objections ...
No objection, but +1 from me. If this is the best solution people can
agree on, better now than later. The missing dependencies for sequences
were a bug in the first place, IMHO.
Best Regards,
Michael Paesold
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [PATCHES] [HACKERS] Should libedit be preferred to libreadline?
Bruce Momjian wrote: I wanted to distinguish libreadline from readline-functionality. Why is it Readline? The GNU Readline Library is usually referred to as Readline, not libreadline. The offical name for libedit is really Libedit. See e.g.: http://sourceforge.net/projects/libedit/ http://cnswww.cns.cwru.edu/~chet/readline/rltop.html IMHO libreadline does not sound good. Best Regards, Michael Paesold ---(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] running script on server shutdown (TODO)
Joshua D. Drake wrote: I guess I just don't understand why so many other projects larger then us and much smaller then us can do it, but we can't. Perhaps it is a question of project culture. As I see PostgreSQL development, there is no such thing as a roadmap or an agreed plan, what the group will implement feature-wise. (Of course everyone agrees that we should improve performance, support new SQL standard features, etc... that is not my point). I believe it is pretty much each individual's (or backing company's) decision, what features they will hack on. If there is interest in a feature from more than one person, sometimes they will work together in groups on that part. But I don't see anyone making lists of features that should be in version X and then say: Let's get this list of work done!. (Btw. I think this is a strengh of PostgreSQL, not a weakness.) As Bruce said, if you want to tell what will be in the next release, you can only look at CVS commits and discussions. The projects of some people are more likely to be in the next release than others, but who knows before the patches are committed. What I would find good for marketing is a list of features that should be in the next release (after they have been committed to CVS). Nevertheless this is not a trivial task. IIRC Bruce did regular updates to the release notes during the development cycle of a release or two ago. He gave it up because it cost too much time. Perhaps someone else wants to step up to maintain such a list, not as detailed as the release notes probably. Just my two (Euro)cents. Best Regards, Michael Paesold ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] Constraint trigger doc patch
[Added pgsql-hackers to CC:]
Michael Glaesemann wrote:
[ a patch for constraint trigger docs]
Great! I was just going to try to use constraint triggers (because I
needed deferrable constraints). So I personally appreciate this
documentation update very much.
Just some notes:
Index: doc/src/sgml/ref/create_constraint.sgml
...
--- 21,32
refsynopsisdiv
synopsis
CREATE CONSTRAINT TRIGGER replaceable
class=parametername/replaceable
! AFTER replaceable class=parameterevent [ OR ... ]/replaceable
! ON replaceable class=parametertable_name/replaceable
! [ FROM replaceable
class=parameterreferenced_table_name/replaceable ]
! { NOT DEFERRABLE | [ DEFERABBLE ] { INITIALLY IMMEDIATE |
INITIALLY DEFERRED } }
! FOR EACH ROW
! EXECUTE PROCEDURE replaceable
class=parameterfuncname/replaceable ( replaceable
class=parameterarguments/replaceable )
/synopsis
/refsynopsisdiv
It's spelled DEFERRABLE. You got it right in NOT DEFERRABLE but wrong in
[ DEFERABBLE ].
The name of the constraint trigger. The actual name of the
created trigger will be of the form
literalRI_ConstraintTrigger_literal (where is some number
assigned by the server). Use this assigned name is when dropping the
constraint.
It think you should drop the is from the last sentence here.
Additionally, I would prefer Use this assigned name when dropping the
trigger. here, because this one confused me to try to ALTER TABLE DROP
CONSTRAINT instead of DROP TRIGGER.
Thanks again.
Best Regards,
Michael Paesold
---(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] Constraint trigger doc patch
Bruce Momjian schrieb: Manual page changed applied. Thanks. Ok, here is small patch fixing the remaining items I found when reviewing the built page. Two items are just SGML bugs, the other item is to finish an incomplete change from the previous events to the new event [ OR ... ] notation. Best Regards, Michael Paesold For quick reference: http://momjian.us/main/writings/pgsql/sgml/sql-createconstraint.html Index: create_constraint.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/create_constraint.sgml,v retrieving revision 1.16 diff -c -r1.16 create_constraint.sgml *** doc/src/sgml/ref/create_constraint.sgml 16 Oct 2006 19:33:12 - 1.16 --- doc/src/sgml/ref/create_constraint.sgml 17 Oct 2006 06:48:14 - *** *** 52,58 para The name of the constraint trigger. The actual name of the created trigger will be of the form ! literalRI_ConstraintTrigger_literal (where is some number assigned by the server). Use this assigned name when dropping the trigger. /para --- 52,58 para The name of the constraint trigger. The actual name of the created trigger will be of the form ! literalRI_ConstraintTrigger_/literal (where is some number assigned by the server). Use this assigned name when dropping the trigger. /para *** *** 60,71 /varlistentry varlistentry ! termreplaceable class=PARAMETERevents/replaceable/term listitem para One of literalINSERT/literal, literalUPDATE/literal, or literalDELETE/literal; this specifies the event that will fire the ! trigger. Multiple events can be specified using literalORliteral. /para /listitem /varlistentry --- 60,71 /varlistentry varlistentry ! termreplaceable class=PARAMETERevent/replaceable/term listitem para One of literalINSERT/literal, literalUPDATE/literal, or literalDELETE/literal; this specifies the event that will fire the ! trigger. Multiple events can be specified using literalOR/literal. /para /listitem /varlistentry ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] Feature: POSIX Shared memory support
Tom Lane wrote: Chris Marcellino [EMAIL PROTECTED] writes: To this end, I have ported the svsv_shmem.c layer to use the POSIX calls (which are some ways more robust w.r.t reducing collision by using strings as shared memory id's, instead of ints). This has been suggested before, and rejected before, on the grounds that the POSIX API provides no way to detect whether anyone else is attached to the segment. Not being able to tell that is a tremendous robustness hit for us. We are not going to risk destroying someone's database (or in the alternative, failing to restart after most crashes, which it looks like your patch would do) in order to make installation fractionally easier. I read through your patch in the hopes that you had a solution for this, but all I find is a copied-and-pasted comment /* * We detect whether a shared memory segment is in use by seeing whether * it (a) exists and (b) has any processes are attached to it. */ followed by code that does no such thing. Just an idea, but would it be possible to have a small SysV area as an advisory lock (using the existing semantics) to protect the POSIX segment. Best Regards Michael Paesold ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] WIP: rewrite numeric division
Tom Lane wrote: I wrote: ... Now it's unlikely that real-world applications are going to be as dependent on the speed of div_var for long inputs as numeric_big is. And getting the right answer has to take priority over speed anyway. Still this is a bit annoying. Anyone see a way to speed it up, or have another approach? regards, tom lane +1 for the change from me. We use PostgreSQL for financial accounting stuff, including plpgsql triggers and functions etc. And we use numeric for all that. I always thought that numeric division was exact! :-) I never saw problem, perhaps because we round to very few digits, but well. Please apply this patch. I can accept the performance drop, as long as there won't be bad surprises with correctness. Best Regards Michael Paesold ---(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] WIP: rewrite numeric division
Please, let's revisit this, and not postpone it without further discussion. I never knew about the correctness issues in div_var(), but since I know about it, I feel I am just waiting until that problem will hit me or anyone else. So can you, Tom, please describe in what situations the old code is really unsafe? We usually *round* all values to at maximum 4 decimal places -- are we on the save side? Does this only affect high precision division, or any divisions? Best Regards Michael Paesold Bruce Momjian wrote: Because this has not been applied, this has been saved for the 8.4 release: http://momjian.postgresql.org/cgi-bin/pgpatches_hold --- Tom Lane wrote: I wrote: I just blew the dust off my old copy of Knuth vol 2, and see that his algorithm for multi-precision division generates output digits that are correct to start with (or at least he never needs to revisit a digit after moving on to the next). ISTM we should go over to an approach like that. The attached proposed patch rewrites div_var() using Knuth's algorithm, meaning that division should always produce an exact truncated output when asked to truncate at X number of places. This passes regression tests and fixes both of the cases previously exhibited: http://archives.postgresql.org/pgsql-bugs/2007-06/msg00068.php http://archives.postgresql.org/pgsql-general/2005-05/msg01109.php The bad news is that it's significantly slower than the CVS-HEAD code; it appears that for long inputs, div_var is something like 4X slower than before, depending on platform. The numeric_big regression test takes about twice as long as before on one of my machines, and 50% longer on another. This is because the innermost loop now involves integer division, which it didn't before. (According to oprofile, just about all the time goes into the loop that subtracts qhat * divisor from the working dividend, which is what you'd expect.) Now it's unlikely that real-world applications are going to be as dependent on the speed of div_var for long inputs as numeric_big is. And getting the right answer has to take priority over speed anyway. Still this is a bit annoying. Anyone see a way to speed it up, or have another approach? regards, tom lane Content-Description: numeric-div.patch.gz [ Type application/octet-stream treated as attachment, skipping... ] ---(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] [DOCS] Partition: use triggers instead of rules
NikhilS wrote: The argument I made for keeping the example around is not dependent on the assumption that using a rule is a good idea. It's dependent on the established fact that we have recommended that in prior releases, and therefore people are going to be seeing that construct in real databases. And they could refer back to the older version of the documentation for it. In fact, we should mention that in the patch: noteparaIf you have a partitioning setup that uses rules please refer to the 8.2 documentation on partitioning/para/note +1 I would also add another sentence about *why* the recommendation was changed. We have one rule-based setup here, and it has been working flawlessly for us,... so personally I don't even know the reasons. Best Regards Michael Paesold ---(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] [DOCS] Partition: use triggers instead of rules
Joshua D. Drake wrote: Michael Paesold wrote: I would also add another sentence about *why* the recommendation was changed. We have one rule-based setup here, and it has been working flawlessly for us,... so personally I don't even know the reasons. Rules are extremely slow in comparisons and not anywhere near as flexible. As I said up post yesterday... they work well in the basic partitioning configuration but anything else they are extremely deficient. Ah, thanks for that summary. How do they compare to triggers dynamically EXECUTEing the inserts? Is that a better solution, or should one really just use the IF ... ELSIF ... ELSIF ... ELSE pattern as suggested in the new docs? (Which means one has to re-create the complete trigger each time a partition is added.) Best Regards Michael Paesold ---(end of broadcast)--- TIP 6: explain analyze is your friend
