Re: [HACKERS] pg_stop_backup does not complete
On Tue, 2 Mar 2010 15:20:36 +0900, Fujii Masao masao.fu...@gmail.com wrote: Setting archive_mode to a command that does nothing but return true, e.g. /bin/true, return true seems ambiguous for me. How about writing clearly return a zero exit status instead? For the record. I hate the fact that I ever mentioned this and I think it is a terrible hack that we would mention it in the docs. From a professional perspective, I cringe at the idea of telling a customer to do this, not to mention it won't work on w32. Joshua D. Drake -- PostgreSQL - XMPP: jdrake(at)jabber(dot)postgresql(dot)org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] WAL replay does not verify integrity
As demonstrated here: http://archives.postgresql.org/pgsql-general/2010-02/msg01057.php it can happen that you end up with a corrupt database if you use PITR to recover past a hardware failure. It seems that unique constraints are not verified during WAL replay. Is that intentional or an oversight that should be fixed? Yours, Laurenz Albe -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] A thought on Index Organized Tables
I think you have to take up a simpler project as a first project. This is a major overhaul of transaction information and it depends on understanding how a lot of different areas work -- all of which are very complex tricky areas to understand. Greg, I just feel the fast full index scan may not be of much value, if we have to go to the table for visibility information. I think the feature needs the visibility map to get completed. Please let me know, if you feel otherwise. Thanks, Gokul.
Re: [HACKERS] Hung postmaster (8.3.9)
We should probably also check and prohibit including directories as files. On Tuesday, March 2, 2010, Tom Lane t...@sss.pgh.pa.us wrote: In the meantime, it seems like we ought to take two defensive steps: -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WAL replay does not verify integrity
Albe Laurenz wrote: As demonstrated here: http://archives.postgresql.org/pgsql-general/2010-02/msg01057.php it can happen that you end up with a corrupt database if you use PITR to recover past a hardware failure. It seems that unique constraints are not verified during WAL replay. Is that intentional or an oversight that should be fixed? It's intentional. WAL replay works at a lower level, it has no knowledge of operators or the concept of uniqueness. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_stop_backup does not complete
On Tue, 2010-03-02 at 15:20 +0900, Fujii Masao wrote: On Fri, Feb 26, 2010 at 2:47 AM, Bruce Momjian br...@momjian.us wrote: Postgres 9.0 will be the first release to mention /bin/true as a way of turning off archiving in extraordinary circumstances: http://developer.postgresql.org/pgdocs/postgres/runtime-config-wal.html Setting archive_mode to a command that does nothing but return true, e.g. /bin/true, return true seems ambiguous for me. How about writing clearly return a zero exit status instead? Docs are already quite clear on that point. I think we should avoid specifying it twice. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] function side effects
On mån, 2010-03-01 at 15:30 -0500, Jaime Casanova wrote: so IMMUTABLE = DETERMINISTIC NO SQL, STABLE = DETERMINISTIC READS SQL DATA VOLATILE = NOT DETERMINISTIC MODIFIES SQL DATA It might be tempting to create such a mapping, but there could be a number of pitfalls, especially if you define it as a commutative equivalence rather than say logical implications. For example, MODIFIES SQL DATA ought to imply VOLATILE, but the reverse is not true. When the volatility attribute was introduced, we briefly looked at the standard deterministic attribute, but concluded that it would be better to create settings that describe how the PostgreSQL planner/executor works instead of some abstract setting that is descriptive but doesn't actually help optimizing the query. We might actually end up with all three groups of settings at some point. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] function side effects
On mån, 2010-03-01 at 16:29 -0500, Tom Lane wrote: Peter Eisentraut pete...@gmx.net writes: SQL standard: SQL-data access indication ::= NO SQL | CONTAINS SQL | READS SQL DATA | MODIFIES SQL DATA Huh. I understand three of those, but what is the use of CONTAINS SQL? My reading is that CONTAINS SQL allows/indicates the use of non-data statements such as CREATE or ALTER, whereas READS SQL DATA and MODIFIES SQL DATA specifically refer to reading or writing table data. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] dedebugging and a functions that just don't work on debian flavour
On Sun, 28 Feb 2010 23:02:39 -0500 Robert Haas robertmh...@gmail.com wrote: On Sun, Feb 28, 2010 at 8:43 AM, Ivan Sergio Borgonovo m...@webthatworks.it wrote: I've no idea what to do next other than asking if someone can give a look to the code and check my comprehension of what a tsquery should be in memory. Have you tried valgrind? It was something related to my misinterpretation on how a tsquery is built. operand.length is the length of the cstring *excluding* the \0. I was mislead by the fact that in tsvector there are no ending \0 while in tsquery there are. It still need more extensive testing but I think I got it working. Once I'm sure I got it I'll make the polished source available and write some docs for all the poor guys like me willing to start to write extensions ;) thanks to everybody for the patience. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] How far are projections pushed down the execution tree?
Consider a table and a query referring to only a subset of the columns in that table. How early in the query evaluation is the projection carried out? Are the columns to be selected filtered out as early as in the very access method that reads the table rows from the buffer, or are the projection handled later, after the whole row has been fetched by the access method? Does it depend on the complexity of the query, how far down the three that the projection is handled out? Thanks! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] How far are projections pushed down the execution tree?
tmp wrote: Consider a table and a query referring to only a subset of the columns in that table. How early in the query evaluation is the projection carried out? Are the columns to be selected filtered out as early as in the very access method that reads the table rows from the buffer, or are the projection handled later, after the whole row has been fetched by the access method? Does it depend on the complexity of the query, how far down the three that the projection is handled out? It depends on the kind of plan chosen. In general the planner delays projection and prefers to do it as high as possible when it can pass a lower-level tuple to an upper node as is, but if it needs to e.g sort, projections are done before the sort to avoid carrying unnecessary data through the sort. EXPLAIN VERBOSE (in 8.4 upwards) shows the columns that are being output from node, you can use that to examine the behavior. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Anyone know if Alvaro is OK?
scrawf...@pinpointresearch.com (Steve Crawford) writes: Marc G. Fournier wrote: Is there a higher then normal amount of earthquakes happening recently? haiti, japan just had one for 6.9, there was apparently one in illinos a few weeks back, one on the Russia/China/N.Korean border and now Chile? Random events come in bunches - something I always stop to remind myself of whenever there is a sudden bunch of quakes, celebrity deaths, plane crashes, etc. Especially with relatively unusual events like great-quakes and plane crashes, it can be tough to see if there is any signal in the noise - a job I have to leave to experienced statisticians. I'll nit pick a little bit... Random events are often *noticed* when there is some reason to think it's an unusually large batch. Nobody really notices the carnage on the highways, because, stochastically, there are such a large number of events, both positive and negative (e.g. - millions of people making it home safely, and a tiny number that don't) that it's difficult for there to be a sufficiently large number of adverse events to notice. People are a lot more worried about terrorists than about car accidents, even though the latter are *enormously* more likely to cause one's demise, by a *huge* factor. (This mismeasurement irritates me a lot, particularly when I visit airports!) 2010 has had more news about earthquakes than other nearby years, but as you say, it is not obvious that there is any signal to be found in the noise. http://en.wikipedia.org/wiki/List_of_deadly_earthquakes_since_1900 is an interesting list. Very few quakes are listed for 2009; I wonder if this results from events not being reported yet? Preceding years consistently have quite a lot of deadly earthquakes, dating back for many years. From that list, Chile has been seeing pretty potent earthquakes on a regular basis since 1905. I'm mighty glad to hear that Alvaro is OK, and that things weren't too disastrously shaken up, for him. -- (format nil ~...@~s cbbrowne gmail.com) http://linuxfinances.info/info/lsf.html Sponges grow in the ocean. I wonder how much deeper the ocean would be if that didn't happen. -- Steven Wright -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Streaming replication and pg_xlogfile_name()
Fujii Masao wrote: On Fri, Feb 26, 2010 at 6:26 AM, Erik Rijkers e...@xs4all.nl wrote: With this patch the standby compiles, tests, installs OK. I wanted to check with you if the following is expected. Thanks for the test and bug report! With standby (correctly) as follows : LOG: redo starts at 0/120 LOG: consistent recovery state reached at 0/200 LOG: database system is ready to accept read only connections This is OK. However, initially (even after the above 'ready' message) the timeline value as reported by pg_xlogfile_name_offset(pg_last_xlog_replay_location()) is zero. When we try to read the WAL record discontinuously (e.g., the REDO starting record and the last applied record), the lastPageTLI is always reset. If that record is not in the buffer, it's read from the disk and the lastPageTLI is set to the right timeline. Otherwise, the lastPageTLI remains at zero wrongly. This is the cause of the problem that you reported. I revised the patch so that the lastPageTLI is always set correctly. Please try this new patch. This still suffers from ambiguity around a shutdown checkpoint that changes the TLI. On the page the shutdown checkpoint is on, what is the TLI in the page header? The TLI before the checkpoint record, I presume. Now consider a record on the same page after the checkpoint record. It's on the new timeline, but pg_last_xlog_replay_location() will return the old TLI, because that's on the page header. It's not clear what it should return, a TLI corresponding the filename of the WAL segment the record was replayed from, so that you can use pg_xlogfile_name() to find out the filename of the WAL segment being replayed, or the accurate TLI of the record being replayed. I'm leaning towards the latter, it feels more correct and accurate, but you could argue for the former too. In any case, it needs to be well-defined. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] How far are projections pushed down the execution tree?
Thanks for the clarification! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] USE_LIBXSLT in MSVC builds
Tom Lane wrote: BTW, it looks like the MSVC build scripts don't bother to make sure that USE_LIBXSLT is defined (or not) correctly --- at least I can't see any reference to that symbol in the Windows-specific files. This is now necessary to avoid disabling the xslt functionality in contrib/xml2. I'm not too sure what to poke there, any help? I have committed a fix for this. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] building postgres-A4.tex-pdf crushed
Hi there, just to inform, that building of postgres-A4.tex-pdf (8.4) crushed on my Ubuntu 9.10 machine. No problem with CVS HEAD. openjade -D . -c /usr/share/sgml/docbook/stylesheet/dsssl/modular/catalog -d ./stylesheet.dsl -t tex -V tex-backend -i output-print -V texpdf-output -V '%paper-type%'=A4 -o postgres-A4.tex-pdf postgres.sgml openjade:./stylesheet.dsl:502:2:E: flow object not accepted by port; only display flow objects accepted make: *** [postgres-A4.tex-pdf] Segmentation fault (core dumped) Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_stop_backup does not complete
On Tue, Mar 2, 2010 at 9:48 AM, Simon Riggs si...@2ndquadrant.com wrote: Setting archive_mode to a command that does nothing but return true, e.g. /bin/true, return true seems ambiguous for me. How about writing clearly return a zero exit status instead? Docs are already quite clear on that point. I think we should avoid specifying it twice. Why do we disallow turning off archive_mode anyways? I understand not turning it on -- though even that would be nice if it took effect after the next checkpoint but turning it off should always be safe, no? -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_stop_backup does not complete
On Tue, 2010-03-02 at 13:13 +, Greg Stark wrote: On Tue, Mar 2, 2010 at 9:48 AM, Simon Riggs si...@2ndquadrant.com wrote: Setting archive_mode to a command that does nothing but return true, e.g. /bin/true, return true seems ambiguous for me. How about writing clearly return a zero exit status instead? Docs are already quite clear on that point. I think we should avoid specifying it twice. Why do we disallow turning off archive_mode anyways? Because it is needed for safety and nobody has got around to coding the idea of turning it on/off during normal running, which is possible, with appropriate care. I understand not turning it on -- though even that would be nice if it took effect after the next checkpoint but turning it off should always be safe, no? We don't support that behaviour in parameters. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_stop_backup does not complete
Simon Riggs wrote: On Tue, 2010-03-02 at 13:13 +, Greg Stark wrote: Why do we disallow turning off archive_mode anyways? Because it is needed for safety and nobody has got around to coding the idea of turning it on/off during normal running, which is possible, with appropriate care. It's actually made it pretty high up on the list of desired features for some of the replication projects: http://wiki.postgresql.org/wiki/ClusterFeatures#Start.2Fstop_archiving_at_runtime Since that is one of the easier items on that list to actually knock off (probably an order of magnitude so than the average feature there), it's completely feasible somebody will do so for 9.1. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Anyone know if Alvaro is OK?
On Mon, Mar 1, 2010 at 11:11 PM, Chris Browne cbbro...@acm.org wrote: Nobody really notices the carnage on the highways, because, stochastically, there are such a large number of events, both positive and negative (e.g. - millions of people making it home safely, and a tiny number that don't) that it's difficult for there to be a sufficiently large number of adverse events to notice. I don't think the number of positive events factors into it. It's that the law of large numbers kicks in and the rate of death is pretty much constant. Every now and then there's an atypical weekend for a given town or city and the death toll spikes and people do in fact notice. Suddenly the news is filled with stories about the carnage the prior weekend and various imagined causal factors just like when the stock market goes up or down and the news people try to explain why. People are a lot more worried about terrorists than about car accidents, even though the latter are *enormously* more likely to cause one's demise, by a *huge* factor. (This mismeasurement irritates me a lot, particularly when I visit airports!) Well there is also a difference here. Because there is an active opponent in the terrorism case the security has non-linear game-theory effects. In the car safety case you could spend 10x as much money and reduce accident death rates by 1/10th. But there's a point of diminishing returns and an optimal value somewhere. In the case of terrorism it may well be the case that if you spend any money on security you must spend a lot of money for it to reach the threshold at which terrorists redirect their attacks elsewhere. Earthquakes are of course not in that category. They just occur rarely enough and then our perception of their severity is heavily influenced by where they occur so clumpings are just inevitable. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] building postgres-A4.tex-pdf crushed
Oleg Bartunov wrote: just to inform, that building of postgres-A4.tex-pdf (8.4) crushed on my Ubuntu 9.10 machine. No problem with CVS HEAD. Alvaro and I are both having the opposite problem on Ubuntu 9.04: 8.4 works fine, but CVS HEAD dumps core: http://archives.postgresql.org/message-id/20100215141242.ga2...@alvh.no-ip.org I wonder if someone more familiar with doing git bisects than me might track down the exact commit that swapped the behavior here. Since this looks like an awful openjade bug regardless, not sure what we could do about it; would be nice to know exactly how it happened though. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Streaming replication and pg_xlogfile_name()
On Tue, Mar 2, 2010 at 8:54 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: This still suffers from ambiguity around a shutdown checkpoint that changes the TLI. On the page the shutdown checkpoint is on, what is the TLI in the page header? The TLI before the checkpoint record, I presume. Now consider a record on the same page after the checkpoint record. It's on the new timeline, but pg_last_xlog_replay_location() will return the old TLI, because that's on the page header. Oh, I see. You are right. It's not clear what it should return, a TLI corresponding the filename of the WAL segment the record was replayed from, so that you can use pg_xlogfile_name() to find out the filename of the WAL segment being replayed, or the accurate TLI of the record being replayed. I'm leaning towards the latter, it feels more correct and accurate, but you could argue for the former too. In any case, it needs to be well-defined. I agree with you that the latter is more correct and accurate. The simple fix is updating the lastPageTLI with the CheckPoint-ThisTimeLineID when replaying the shutdown checkpoint record. Though we might need to use new variable to keep the last applied timeline instead of the lastPageTLI. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] double and numeric conversion
On Mar 1, 2010, at 4:35 PM, Tom Lane wrote: Theo Schlossnagle je...@omniti.com writes: I'm writing some extension and I have a hot code path that has a lot of double (C type) data and needs to output NUMERIC tuple data. The current methods I can find in the code to convert sprintf the double to a buffer and then invoke the numeric_in function on them. I've profile my stuff and I'm spending (wasting) all my time in that conversion. Is there a more efficient method of converting a double into a postgres numeric value? If you're worried about micro-optimization, why are you using NUMERIC at all? It's no speed demon. Although you might be able to shave some cycles with a dedicated code path for this conversion, binary to decimal is fundamentally not cheap. I feared that was the case. I spent an hour or so coding that last night and the speedups for me were worth it, I see a 2 fold speedup in conversion operations (or a 50% reduction in CPU cycles per conversion). The integer ones were trivial, the double one has the imperfect issue of reasonably guessing the dscale, but seems to work in my tests. I didn't look deeply at the postgres internals to see if there was a way to do double - numeric and integer-types - numeric without intermediary string format. If that sort of thing is easy to leverage, I'd be happy to share the code. -- Theo Schlossnagle http://omniti.com/is/theo-schlossnagle -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] trouble with to_char('L')
Bruce Momjian wrote: Hiroshi Inoue wrote: Bruce Momjian wrote: Bruce Momjian wrote: Hiroshi Inoue wrote: Bruce Momjian wrote: Hiroshi Inoue wrote: Bruce Momjian wrote: Where are we on this issue? Oops I forgot it completely. I have a little improved version and would post it tonight. Ah, very good. Thanks. Attached is an improved version. I spent many hours on this patch and am attaching an updated version. I have restructured the code and added many comments, but this is the main one: * Ideally, the server encoding and locale settings would * always match. Unfortunately, WIN32 does not support UTF-8 * values for setlocale(), even though PostgreSQL runs fine with * a UTF-8 encoding on Windows: * * http://msdn.microsoft.com/en-us/library/x99tb11d.aspx * * Therefore, we must set LC_CTYPE to match LC_NUMERIC and * LC_MONETARY, call localeconv(), and use mbstowcs() to * convert the locale-aware string, e.g. Euro symbol, which * is not in UTF-8 to the server encoding. I need someone with WIN32 experience to review and test this patch. I don't understand why cache_locale_time() works on Windows. It sets the LC_CTYPE but does not do any encoding coversion. Doesn't strftime_win32 do the conversion? Oh, I now see strftime is redefined as a macro in that C files. Thanks. Do month and day-of-week names not work either, or do they work and the encoding conversion for numeric/money, e.g. Euro, it not necessary? db_strdup does the conversion. Should we pull the encoding conversion into a separate function and have strftime_win32() and db_strdup() both call it? We may be able to pull the conversion WideChars = UTF8 = a PG encoding into an function. BTW both PGLC_localeconv() and cache_locale_time() save the current LC_CTYPE first and restore them just before returning the functions. I'm suspicious if it's OK when errors occur in middle of the functions. regards, Hiroshi Inoue -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] USE_LIBXSLT in MSVC builds
Andrew Dunstan and...@dunslane.net writes: Tom Lane wrote: BTW, it looks like the MSVC build scripts don't bother to make sure that USE_LIBXSLT is defined (or not) correctly --- at least I can't see any reference to that symbol in the Windows-specific files. This is now necessary to avoid disabling the xslt functionality in contrib/xml2. I'm not too sure what to poke there, any help? I have committed a fix for this. Thanks, but please backpatch --- all active branches need this. Also, mastodon is still failing the xml2 test, not sure why. Looks to be a build problem, but why is (for instance) baiji unaffected? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] USE_LIBXSLT in MSVC builds
Tom Lane wrote: Andrew Dunstan and...@dunslane.net writes: Tom Lane wrote: BTW, it looks like the MSVC build scripts don't bother to make sure that USE_LIBXSLT is defined (or not) correctly --- at least I can't see any reference to that symbol in the Windows-specific files. This is now necessary to avoid disabling the xslt functionality in contrib/xml2. I'm not too sure what to poke there, any help? I have committed a fix for this. Thanks, but please backpatch --- all active branches need this. OK, I have gone back as far as 8.2, which is where the MSVC build system started. I'm keeping my fingers crossed slightly about the 8.2 fix. 8.3 and 8.4 should be fine. We don't have buildfarm coverage for 8.2, as the MSVC build system wasn't mature enough at that stage. Also, mastodon is still failing the xml2 test, not sure why. Looks to be a build problem, but why is (for instance) baiji unaffected? Baiji built before I put in the patch, so it didn't have USE_LIBXSLT enabled. Looks like we have a bit more work to do ;-( cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] USE_LIBXSLT in MSVC builds
Andrew Dunstan and...@dunslane.net writes: Tom Lane wrote: Also, mastodon is still failing the xml2 test, not sure why. Looks to be a build problem, but why is (for instance) baiji unaffected? Baiji built before I put in the patch, so it didn't have USE_LIBXSLT enabled. But mastodon is failing entirely, which isn't explained by having or omitting USE_LIBXSLT. AFAICS that would just result in suppressing the xslt functionality, which the regression test wouldn't complain about now that we have a variant expected file that allows it. Looks like we have a bit more work to do ;-( Yeah. I guess what this indicates is that contrib/xml2 never worked at all in some/all Windows configurations? Do we know of anyone having used it successfully? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] USE_LIBXSLT in MSVC builds
BTW, now that I look at red_bat's failure, it seems that the MSVC build scripts try to run the contrib/xml2 test even when xml2 hasn't been built for lack of libxml. This doesn't explain mastodon's problem but maybe a second look is needed at all the conditionals around that. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] USE_LIBXSLT in MSVC builds
Tom Lane wrote: BTW, now that I look at red_bat's failure, it seems that the MSVC build scripts try to run the contrib/xml2 test even when xml2 hasn't been built for lack of libxml. This doesn't explain mastodon's problem but maybe a second look is needed at all the conditionals around that. Well, the trouble is to some extent that the MSVC build system tries to be a bit more maximal than the stardard build system. I'm going to make a patch to fix that immediate problem, and test it very quickly on red_bat. Maybe Magnus and I need to have another go round on the general logic, however. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] trouble with to_char('L')
Hiroshi Inoue wrote:
I need someone with WIN32 experience to review and test this patch.
I don't understand why cache_locale_time() works on Windows. It sets
the LC_CTYPE but does not do any encoding coversion.
Doesn't strftime_win32 do the conversion?
Oh, I now see strftime is redefined as a macro in that C files. Thanks.
Do month and
day-of-week names not work either, or do they work and the encoding
conversion for numeric/money, e.g. Euro, it not necessary?
db_strdup does the conversion.
Should we pull the encoding conversion into a separate function and have
strftime_win32() and db_strdup() both call it?
We may be able to pull the conversion WideChars = UTF8 =
a PG encoding into an function.
OK, I have created a new function, win32_wchar_to_db_encoding(), to
share the conversion from wide characters to the database encoding.
New patch attached.
BTW both PGLC_localeconv() and cache_locale_time() save the current
LC_CTYPE first and restore them just before returning the functions.
I'm suspicious if it's OK when errors occur in middle of the functions.
Yea, I added a comment questioning if that is a problem.
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do
Index: src/backend/utils/adt/pg_locale.c
===
RCS file: /cvsroot/pgsql/src/backend/utils/adt/pg_locale.c,v
retrieving revision 1.53
diff -c -c -r1.53 pg_locale.c
*** src/backend/utils/adt/pg_locale.c 27 Feb 2010 20:20:44 - 1.53
--- src/backend/utils/adt/pg_locale.c 2 Mar 2010 18:11:41 -
***
*** 4,10
*
* Portions Copyright (c) 2002-2010, PostgreSQL Global Development Group
*
! * $PostgreSQL: pgsql/src/backend/utils/adt/pg_locale.c,v 1.53 2010/02/27 20:20:44 momjian Exp $
*
*---
*/
--- 4,10
*
* Portions Copyright (c) 2002-2010, PostgreSQL Global Development Group
*
! * $PostgreSQL: pgsql/src/backend/utils/adt/pg_locale.c,v 1.51 2010/01/02 16:57:54 momjian Exp $
*
*---
*/
***
*** 96,101
--- 96,109
static char *IsoLocaleName(const char *); /* MSVC specific */
#endif
+ #ifdef WIN32
+ static size_t win32_wchar_to_db_encoding(const wchar_t *wbuf,
+ const size_t wchars, char *dst, size_t dstlen);
+ static char *db_encoding_strdup(const char *item, const char *str);
+ static size_t strftime_win32(char *dst, size_t dstlen, const wchar_t *format,
+ const struct tm *tm);
+ #endif
+
/*
* pg_perm_setlocale
***
*** 387,392
--- 395,488
}
+ #ifdef WIN32
+ /*
+ * Convert wide character string (UTF16 on Win32) to UTF8, and then
+ * optionally to the db encoding.
+ */
+ static size_t win32_wchar_to_db_encoding(const wchar_t *wbuf,
+ const size_t wchars, char *dst, size_t dstlen)
+ {
+ int db_encoding = GetDatabaseEncoding();
+ int utf8len;
+
+ /* Convert wide string (UTF16) to UTF8 */
+ utf8len = WideCharToMultiByte(CP_UTF8, 0, wbuf, wchars, dst, dstlen, NULL, NULL);
+ if (utf8len == 0)
+ /* Does this leave LC_CTYPE set incorrectly? */
+ elog(ERROR,
+ could not convert string %04x to UTF-8: error %lu, wbuf[0], GetLastError());
+ pfree(wbuf);
+
+ dst[utf8len] = '\0';
+ if (db_encoding != PG_UTF8)
+ {
+ PG_TRY();
+ {
+ char *convstr = pg_do_encoding_conversion(dst, utf8len, PG_UTF8, db_encoding);
+ if (dst != convstr)
+ {
+ strlcpy(dst, convstr, dstlen);
+ pfree(convstr);
+ }
+ }
+ PG_CATCH();
+ {
+ FlushErrorState();
+ dst[0] = '\0';
+ }
+ PG_END_TRY();
+ }
+
+ return pg_mbstrlen(dst);
+ }
+
+ /*
+ * This converts the LC_CTYPE-encoded string returned from the
+ * locale routines to the database encoding.
+ */
+ static char *db_encoding_strdup(const char *item, const char *str)
+ {
+ int db_encoding = GetDatabaseEncoding();
+ size_t wchars, ilen, wclen, dstlen;
+ int bytes_per_char;
+ wchar_t *wbuf;
+ char *dst;
+
+ if (!str[0])
+ return strdup(str);
+
+ /* allocate wide character string */
+ ilen = strlen(str) + 1;
+ wclen = ilen * sizeof(wchar_t);
+ wbuf = (wchar_t *) palloc(wclen);
+
+ /* Convert multi-byte string using current LC_CTYPE to a wide-character string */
+ wchars = mbstowcs(wbuf, str, ilen);
+ if (wchars == (size_t) -1)
+ elog(ERROR,
+ could not convert string to wide characters: error %lu, GetLastError());
+
+ /* allocate target string */
+ bytes_per_char = pg_encoding_max_length(PG_UTF8);
+ if (pg_encoding_max_length(db_encoding) bytes_per_char)
+ bytes_per_char = pg_encoding_max_length(db_encoding);
+ dstlen = wchars * bytes_per_char + 1;
+ if ((dst = malloc(dstlen)) == NULL)
+ elog(ERROR, could not allocate
Re: [HACKERS] Re: Hot Standby query cancellation and Streaming Replication integration
Bruce Momjian wrote: 'max_standby_delay = -1' is really only a reasonable idea if you are absolutely certain all queries are going to be short, which we can't dismiss as an unfounded use case so it has value. I would expect you have to also combine it with a matching reasonable statement_timeout to enforce that expectation to make that situation safer. Well, as you stated in your blog, you are going to have one of these downsides: o master bloat o delayed recovery o cancelled queries Right now you can't choose master bloat, but you can choose the other two. I think that is acceptable for 9.0, assuming the other two don't have the problems that Tom foresees. I was wrong. You can choose master bloat with vacuum_defer_cleanup_age, but only crudely because it is measured in xids and the master defers no matter what queries are running on the slave, and there is still the possibility for query cancel for long queries. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: Hot Standby query cancellation and Streaming Replication integration
Greg Smith wrote: I assumed they would set max_standby_delay = -1 and be happy. The admin in this situation might be happy until the first time the primary fails and a failover is forced, at which point there is an unbounded amount of recovery data to apply that was stuck waiting behind whatever long-running queries were active. I don't know if you've ever watched what happens to a pre-8.2 cold standby when you start it up with hundreds or thousands of backed up WAL files to process before the server can start, but it's not a fast process. I watched a production 8.1 standby get 4000 files behind once due to an archive_command bug, and it's not something I'd like to ever chew my nails off to again. If your goal was HA and you're trying to bring up the standby, the server is down the whole time that's going on. This is why no admin who prioritizes HA would consider 'max_standby_delay = -1' a reasonable setting, and those are the sort of users Joachim's example was discussing. Only takes one rogue query that runs for a long time to make the standby so far behind it's useless for HA purposes. And you also have to ask yourself if recovery is halted while waiting for this query to run, how stale is the data on the standby getting?. That's true for any large setting for this parameter, but using -1 for the unlimited setting also gives the maximum possible potential for such staleness. 'max_standby_delay = -1' is really only a reasonable idea if you are absolutely certain all queries are going to be short, which we can't dismiss as an unfounded use case so it has value. I would expect you have to also combine it with a matching reasonable statement_timeout to enforce that expectation to make that situation safer. Well, as you stated in your blog, you are going to have one of these downsides: o master bloat o delayed recovery o cancelled queries Right now you can't choose master bloat, but you can choose the other two. I think that is acceptable for 9.0, assuming the other two don't have the problems that Tom foresees. Our documentation should probably just come how and state that clearly. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: Hot Standby query cancellation and Streaming Replication integration
On 3/2/10 10:30 AM, Bruce Momjian wrote: Right now you can't choose master bloat, but you can choose the other two. I think that is acceptable for 9.0, assuming the other two don't have the problems that Tom foresees. Actually, if vacuum_defer_cleanup_age can be used, master bloat is an option. Hopefully I'll get some time for serious testing this weekend. --Josh Berkus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: Hot Standby query cancellation and Streaming Replication integration
Bruce Momjian wrote: Right now you can't choose master bloat, but you can choose the other two. I think that is acceptable for 9.0, assuming the other two don't have the problems that Tom foresees. I was wrong. You can choose master bloat with vacuum_defer_cleanup_age, but only crudely because it is measured in xids and the master defers no matter what queries are running on the slave... OK with you finding the situation acceptable, so long as it's an informed decision. From how you're writing about this, I'm comfortable you (and everybody else still involved here) have absorbed the issues enough that we're all talking about the same thing now. Since there are a couple of ugly user-space hacks possible for prioritizing master bloat, and nobody is stepping up to work on resolving this via my suggestion involving better SR integration, seems to me heated discussion of code changes has come to a resolution of sorts I (and Simon, just checked) can live with. Sounds like we have three action paths here: -Tom already said he was planning a tour through the HS/SR code, I wanted that to happen with him aware of this issue. -Josh will continue doing his testing, also better informed about this particular soft spot. -I'll continue test-case construction for the problems here there are still concerns about (pathologic max_standby_delay and b-tree split issues being the top two on that list), and keep sharing particularly interesting ones here to help everyone else's testing. If it turns out any of those paths leads to a must-fix problem that doesn't have an acceptable solution, at least the idea of this as a plan B is both documented and more widely understood then when I started ringing this particular bell. I just updated the Open Items list: http://wiki.postgresql.org/wiki/PostgreSQL_9.0_Open_Items to officially put myself on the hook for the following HS related documentation items that have come up recently, aiming to get them all wrapped up in time before or during early beta: -Update Hot Standby documentation: clearly explain relationships between the 3 major setup trade-offs, buffer cleanup lock, notes on which queries are killed once max_standby_delay is reached, measuring XID churn on master for setting vacuum_defer_cleanup_age -Clean up archive_command docs related to recent /bin/true addition. Given that's where I expect people who run into the pg_stop_backup warning message recently added will end up at, noting its value for escaping from that particular case might be useful too. To finish airing my personal 9.0 TODO list now that I've gone this far, I'm also still working on completing the following patches that initial versions have been submitted of, was close to finishing both before getting side-tracked onto this larger issue: -pgbench 4000 scale bug fix: http://archives.postgresql.org/message-id/4b621ba3.7090...@2ndquadrant.com -Improving the logging/error reporting/no timestamp issues in pg_standby re-raised recently by Selena: http://archives.postgresql.org/message-id/2b5e566d1001250945oae17be8n6317f827e3bd7...@mail.gmail.com If nobody else claims them as something they're working on before, I suspect I'll then move onto building some of the archiver UI improvements discussed most recently as part of the pg_stop_backup does not complete thread, despite Heikki having crushed my dreams of a simple solution to those by pointing out the shared memory memory limitation involved. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us
Re: [HACKERS] Re: Hot Standby query cancellation and Streaming Replication integration
On Mon, 2010-03-01 at 12:04 -0800, Josh Berkus wrote: does anyone dispute his analysis? Simon? No dispute. I think I've discussed this before. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: Hot Standby query cancellation and Streaming Replication integration
On Mon, 2010-03-01 at 14:43 -0500, Tom Lane wrote: Speaking of which, does the current HS+SR code have a provision to force the standby to stop tracking WAL and come up live, even when there's more WAL available? Yes, trigger file. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: Hot Standby query cancellation and Streaming Replication integration
On Sun, 2010-02-28 at 16:56 +0100, Joachim Wieland wrote: Now let's take a look at both scenarios from the administrators' point of view: Well argued, agree with all of your points. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Hot Standby query cancellation and Streaming Replication integration
On Mon, 2010-03-01 at 16:12 -0400, pgsql-hackers-ow...@postgresql.org wrote: . . . However there is a concern with max_standby_age. If you set it to, say, 300s. Then run a 300s query on the slave which causes the slave to fall 299s behind. Now you start a new query on the slave -- it gets a snapshot based on the point in time that the slave is currently at. If it hits a conflict it will only have 1s to finish before the conflict causes the query to be cancelled. In short in the current setup I think there is no safe value of max_standby_age which will prevent query cancellations short of -1. If the slave has a constant stream of queries and always has at least one concurrent query running then it's possible that the slave will run continuously max_standby_age-epsilon behind the master and cancel queries left and right, regardless of how large max_standby_age is. To resolve this I think you would have to introduce some chance for the slave to catch up. Something like refusing to use a snapshot older than max_standby_age/2 and instead wait until the existing queries finish and the slave gets a chance to catch up and see a more recent snapshot. The problem is that this would result in very unpredictable and variable response times from the slave. A single long-lived query could cause replay to pause for a big chunk of max_standby_age and prevent any new query from starting. Does anyone see any way to guarantee that the slave gets a chance to replay and new snapshots will become visible without freezing out new queries for extended periods of time? At the risk of looking foolish, I have a hand-wavy, unlikely-to-be-possible, not-going-to-make-it-for-9.0, and maybe unoriginal idea that I'll share. As Greg has identified, no matter what max_standby_age you select, a sequence of overlapping queries will eventually exceed the max_standby_delay threshold, and tuples that your query depends on would then be modified underneath you. IIUC this is only a problem for WAL from HOT updates and vacuums. If no vacuums or HOT updates have been performed, there is no risk of returning bad data. So WAL that does not contain HOT updates or vacuums could be applied on the standby without risk, even if there are long-running queries in play. This is not a complete solution but may reduce the likelihood of queries having to be cancelled. I guess the approach here would be to check WAL before applying it, and only cancel queries if the WAL contains HOT updates or vacuums. Taking the idea further, if WAL records contained the tid of the latest tuples that were overwritten, even more WAL could be applied without having to cancel queries. To take it further still, if vacuum on the master could be prevented from touching records that are less than max_standby_delay seconds old, it would be safe to apply WAL from the very latest vacuum. I guess HOT could be handled similarly though that may eliminate much of the advantage of HOT updates. Apologies if this has already been covered, some of this discussion went over my head. /action Puts on asbestos underwear __ Marc -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: Hot Standby query cancellation and Streaming Replication integration
Greg Stark wrote: On Mon, Mar 1, 2010 at 5:50 PM, Josh Berkus j...@agliodbs.com wrote: I don't think that defer_cleanup_age is a long-term solution. ?But we need *a* solution which does not involve delaying 9.0. So I think the primary solution currently is to raise max_standby_age. However there is a concern with max_standby_age. If you set it to, say, 300s. Then run a 300s query on the slave which causes the slave to fall 299s behind. Now you start a new query on the slave -- it gets a snapshot based on the point in time that the slave is currently at. If it hits a conflict it will only have 1s to finish before the conflict causes the query to be cancelled. In short in the current setup I think there is no safe value of max_standby_age which will prevent query cancellations short of -1. If the slave has a constant stream of queries and always has at least one concurrent query running then it's possible that the slave will run continuously max_standby_age-epsilon behind the master and cancel queries left and right, regardless of how large max_standby_age is. To resolve this I think you would have to introduce some chance for the slave to catch up. Something like refusing to use a snapshot older than max_standby_age/2 and instead wait until the existing queries finish and the slave gets a chance to catch up and see a more recent snapshot. The problem is that this would result in very unpredictable and variable response times from the slave. A single long-lived query could cause replay to pause for a big chunk of max_standby_age and prevent any new query from starting. That is a good point. I have added the attached documentation patch to mention that max_standby_delay increases the master/slave inconsistency, and not to use it for xid-keepalive connections. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do Index: doc/src/sgml/config.sgml === RCS file: /cvsroot/pgsql/doc/src/sgml/config.sgml,v retrieving revision 1.256 diff -c -c -r1.256 config.sgml *** doc/src/sgml/config.sgml 27 Feb 2010 14:46:05 - 1.256 --- doc/src/sgml/config.sgml 2 Mar 2010 21:03:14 - *** *** 1869,1875 this parameter makes sense only during replication, so when performing an archive recovery to recover from data loss a very high parameter setting or -1 which means wait forever is recommended. ! The default is 30 seconds. This parameter can only be set in the filenamepostgresql.conf/ file or on the server command line. /para --- 1869,1876 this parameter makes sense only during replication, so when performing an archive recovery to recover from data loss a very high parameter setting or -1 which means wait forever is recommended. ! The default is 30 seconds. Increasing this parameter can delay ! master server changes from appearing on the standby. This parameter can only be set in the filenamepostgresql.conf/ file or on the server command line. /para Index: doc/src/sgml/high-availability.sgml === RCS file: /cvsroot/pgsql/doc/src/sgml/high-availability.sgml,v retrieving revision 1.52 diff -c -c -r1.52 high-availability.sgml *** doc/src/sgml/high-availability.sgml 27 Feb 2010 09:29:20 - 1.52 --- doc/src/sgml/high-availability.sgml 2 Mar 2010 21:03:14 - *** *** 1410,1416 that the primary and standby nodes are linked via the WAL, so the cleanup situation is no different from the case where the query ran on the primary node itself. And you are still getting the benefit of off-loading the ! execution onto the standby. /para para --- 1410,1418 that the primary and standby nodes are linked via the WAL, so the cleanup situation is no different from the case where the query ran on the primary node itself. And you are still getting the benefit of off-loading the ! execution onto the standby. varnamemax_standby_delay/ should ! not be used in this case because delayed WAL files might already ! contain entries that invalidate the current shapshot. /para para -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] caracara failing to bind to localhost?
Dickson S. Guedes wrote: 2010/2/27 Tom Lane t...@sss.pgh.pa.us: Buildfarm member caracara has been failing the last few days because of this: LOG: could not bind socket for statistics collector: Cannot assign requested address LOG: disabling statistics collector for lack of working socket That code hasn't changed recently, AFAIK, so I'm thinking something's broken in the machine's environment. Any ideas? Firewall or that ipv6's issues when ::1 for localhost is enabled in /etc/hosts or that cases when there is an ipv4 address and an ipv6 alias in the same eth0? Was the tcpip_socket variable changed on caracara's host? []s Dickson S. Guedes mail/xmpp: gue...@guedesoft.net - skype: guediz http://guedesoft.net - http://www.postgresql.org.br I had dropped an interface on my laptop... ifup lo brought back the local loopback, thereby restoring 127.0.0.1, so everything should work now. I hope... :-). -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: Hot Standby query cancellation and Streaming Replication integration
Greg Stark wrote: On Mon, Mar 1, 2010 at 5:50 PM, Josh Berkus j...@agliodbs.com wrote: I don't think that defer_cleanup_age is a long-term solution. ?But we need *a* solution which does not involve delaying 9.0. So I think the primary solution currently is to raise max_standby_age. However there is a concern with max_standby_age. If you set it to, say, 300s. Then run a 300s query on the slave which causes the slave to fall 299s behind. Now you start a new query on the slave -- it gets a snapshot based on the point in time that the slave is currently at. If it hits a conflict it will only have 1s to finish before the conflict causes the query to be cancelled. In short in the current setup I think there is no safe value of max_standby_age which will prevent query cancellations short of -1. If the slave has a constant stream of queries and always has at least one concurrent query running then it's possible that the slave will run continuously max_standby_age-epsilon behind the master and cancel queries left and right, regardless of how large max_standby_age is. This is sobering. I have added the attached documentation so at least this odd behavior is documented. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do Index: doc/src/sgml/config.sgml === RCS file: /cvsroot/pgsql/doc/src/sgml/config.sgml,v retrieving revision 1.257 diff -c -c -r1.257 config.sgml *** doc/src/sgml/config.sgml 2 Mar 2010 21:18:59 - 1.257 --- doc/src/sgml/config.sgml 2 Mar 2010 23:34:38 - *** *** 1862,1879 listitem para When server acts as a standby, this parameter specifies a wait policy ! for queries that conflict with data changes being replayed by recovery. If a conflict should occur the server will delay up to this number ! of seconds before it begins trying to resolve things less amicably, as ! described in xref linkend=hot-standby-conflict. Typically, ! this parameter makes sense only during replication, so when ! performing an archive recovery to recover from data loss a very high ! parameter setting or -1 which means wait forever is recommended. ! The default is 30 seconds. Increasing this parameter can delay ! master server changes from appearing on the standby. This parameter can only be set in the filenamepostgresql.conf/ file or on the server command line. /para /listitem /varlistentry --- 1862,1892 listitem para When server acts as a standby, this parameter specifies a wait policy ! for applying WAL entries that conflict with active queries. If a conflict should occur the server will delay up to this number ! of seconds before it cancels conflicting queries, as ! described in xref linkend=hot-standby-conflict. ! Typically, this parameter is used only during replication. ! The default is 30 seconds. This parameter can only be set in the filenamepostgresql.conf/ file or on the server command line. /para +para + A high value makes query cancel less likely, and -1 + causes the standby to wait forever for a conflicting query to + complete. Increasing this parameter might delay master server + changes from appearing on the standby. + /para + para +While it is tempting to believe that varnamemax_standby_delay/ +is the maximum number of seconds a query can run before +cancellation is possible, this is not true. When a long-running +query ends, there is a finite time required to apply backlogged +WAL logs. If a second long-running query appears before the +WAL has caught up, the snapshot taken by the second query will +allow significantly less than varnamemax_standby_delay/ +before query cancellation is possible. + /para /listitem /varlistentry -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: Hot Standby query cancellation and Streaming Replication integration
Greg Smith wrote: Bruce Momjian wrote: Right now you can't choose master bloat, but you can choose the other two. I think that is acceptable for 9.0, assuming the other two don't have the problems that Tom foresees. I was wrong. You can choose master bloat with vacuum_defer_cleanup_age, but only crudely because it is measured in xids and the master defers no matter what queries are running on the slave... OK with you finding the situation acceptable, so long as it's an informed decision. From how you're writing about this, I'm comfortable Well, consider that in November we were not even sure SR or HS would be in 9.0. We got them both, so if it is a little kudgy in 9.0, that's OK. We are much farther along than I ever expected. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Avoiding bad prepared-statement plans.
Dimitri Fontaine wrote: Craig Ringer cr...@postnewspapers.com.au writes: 1) People preparing statements to save on parse+plan time; and 2) People preparing statements to get convenenient param placement. I suspect that most of (1) also want (2), but many of (2) don't care much about (1) and are just preparing statements for sql-injection safety (param placement), because they've been told to by someone, because their library does it for them, etc. So: Would it be easier to handle control of replan vs no-replan at PREPARE time? Or would that have very much the same protocol/pl change issues? http://www.postgresql.org/docs/8.4/static/libpq-exec.html#LIBPQ-EXEC-MAIN PQexecParams Submits a command to the server and waits for the result, with the ability to pass parameters separately from the SQL command text. So I think what you're talking about is already in there. There are three levels, SQL, libpq, and the wire protocol. The wire protocol offers this via unnamed statements. libpq offers it via PQexecParams(). SQL does not, as far as I can tell. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Avoiding bad prepared-statement plans.
Robert Haas wrote: Adding SQL to indicate whether it should be re-planned or not is completely unappealing. If I could change the code, today, I'd just turn off or choose not to use PREPARE/EXECUTE. Today, PREPARE/EXECUTE seems like it should always be considered slower unless one can prove it is actually faster in a specific case, which is the exact opposite of what people expect. I don't really understand most of what you're saying here, but there's definitely some truth to your last sentence. This has easily got to be one of the top ten questions on -performance. It seems it is the problem everyone knows about but no one fixes. :-( -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby query cancellation and Streaming Replication integration
On 3/2/10 12:47 PM, Marc Munro wrote: To take it further still, if vacuum on the master could be prevented from touching records that are less than max_standby_delay seconds old, it would be safe to apply WAL from the very latest vacuum. I guess HOT could be handled similarly though that may eliminate much of the advantage of HOT updates. Aside from the inability to convert between transcation count and time, isn't this what vacuum_defer_cleanup_age is supposed to do? Or does it not help with HOT updates? --Josh Berkus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] USE_LIBXSLT in MSVC builds
Andrew Dunstan wrote: Tom Lane wrote: BTW, now that I look at red_bat's failure, it seems that the MSVC build scripts try to run the contrib/xml2 test even when xml2 hasn't been built for lack of libxml. This doesn't explain mastodon's problem but maybe a second look is needed at all the conditionals around that. Well, the trouble is to some extent that the MSVC build system tries to be a bit more maximal than the stardard build system. I'm going to make a patch to fix that immediate problem, and test it very quickly on red_bat. Maybe Magnus and I need to have another go round on the general logic, however. OK, I have made patches that get red_bat working both with and without XML and with XML+XSLT configured. It has now gone green again, so I'm fairly sure the patches I have applied are right. Baiji is now failing, however. Perhaps it is not finding the XSLT lib or dll? cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: Could we do pgindent on just utils/adt/xml.c in the 8.3 branch?
Tom Lane wrote: Bruce Momjian br...@momjian.us writes: Sure, I can easily do that, but I am not sure how to generate a typedef list for that release, except to use the old typedef collection method for that file. Is that OK? One of the things I noticed while patching was that the 8.3 typedef list was missing xmlChar and a few other typedefs that are used a lot in that file :-(. So please don't use the same method you used in 8.3. Yes, I don't have libxml installed on this machine so it didn't pick up those typedefs. I think it would be close enough to use the current (just added to CVS) typedef list. Or use the one you used for 8.4 if you have it handy. There is not a lot of difference between the 8.3 and current code in that file, so either of those are probably going to give you the same results. Done. Sorry for the delay. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] plperl _init settings
There appears to be some significant misunderstanding of what can be done effectively using the various *_init settings for plperl. In particular, some people have got an expectation that modules loaded in plperl.on_init will thereby be available for use in trusted plperl. I propose to add the following note to the docs: Preloading modules using plperl.on_init does not make them available for use by plperl. External perl modules can only be used in plperlu. Comments? cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] psql with Function Type in \df
David Fetter wrote: On Thu, Feb 25, 2010 at 07:20:58PM -0500, Bruce Momjian wrote: Did we ever get tab completion support for these backslash commands? Nope :/ Not sure if I'll be able to get to it this week, either. What is the TODO description then? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ALTER TABLE SET STATISTICS requires AccessExclusiveLock
Peter Eisentraut wrote: On m?n, 2010-02-22 at 10:32 -0500, Bruce Momjian wrote: Simon Riggs wrote: On Mon, 2009-10-19 at 12:56 -0300, Alvaro Herrera wrote: Simon Riggs wrote: On Fri, 2009-08-07 at 15:58 -0400, Alvaro Herrera wrote: Tom Lane wrote: Peter Eisentraut pete...@gmx.net writes: Is there a good reason for $subject, other than that the code is entangled with other ALTER TABLE code? I think it could be lower, but it would take nontrivial restructuring of the ALTER TABLE support. In particular, consider what happens when you have a list of subcommands that don't all require the same lock level. I think you'd need to scan the list and find the highest required lock level before starting ... IIRC there was a patch from Simon to address this issue, but it had some holes which he didn't have time to close, so it sank. Maybe this can be resurrected and fixed. I was intending to finish that patch in this release cycle. Since you're busy with Hot Standby, any chance you could pass it on? If you'd like. It's mostly finished, just one last thing to finish: atomic changes to pg_class via an already agreed API. I assume this did not get done for 9.0. Do we want a TODO item? Yes. Added: Reduce locking required for ALTER commands * http://archives.postgresql.org/pgsql-hackers/2009-08/msg00533.php * http://archives.postgresql.org/pgsql-hackers/2009-10/msg01083.php * http://archives.postgresql.org/pgsql-hackers/2010-01/msg02349.php -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] plperl _init settings
On Mar 2, 2010, at 4:33 PM, Andrew Dunstan wrote: I propose to add the following note to the docs: Preloading modules using plperl.on_init does not make them available for use by plperl. External perl modules can only be used in plperlu. Perhaps add a note that some other interface to allow preloaded modules to also be available to plperl may be provided in the future. Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Placement of block label in plpgsql block with DECLARE
There's a novice question here http://archives.postgresql.org/pgsql-novice/2010-03/msg5.php that shows up a problem in plpgsql's grammar. The documentation states that when a block is labeled, the label must precede the DECLARE keyword if any: http://developer.postgresql.org/pgdocs/postgres/plpgsql-structure.html In the aforementioned question, the user tried to put the label just before BEGIN, after some variable declarations. So how come it didn't throw a syntax error? Well, when you dig into the grammar, you find out that it contains an extremely lame attempt to allow the label to be placed there. The code doesn't actually work; the label is swallowed and effectively ignored, as the user's question indicates. Some quick testing shows that it never has worked in any version back to 7.0, so this isn't recent breakage. If it did work it would create an ambiguity because there would be multiple possible labels. As the grammar stands you can write a DECLARE b x int; c BEGIN Try to guess which label is actually active. Rather than trying to fix this, I think we should just rip it out and allow only the documented syntax. Objections? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Placement of block label in plpgsql block with DECLARE
On Mar 2, 2010, at 5:14 PM, Tom Lane wrote: Rather than trying to fix this, I think we should just rip it out and allow only the documented syntax. Objections? +1 If it never worked, seems like a no-brainer. Best, David PS: Do you have builds of 7.0 laying around? Sheesh! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Placement of block label in plpgsql block with DECLARE
David E. Wheeler da...@kineticode.com writes: PS: Do you have builds of 7.0 laying around? Sheesh! Use it for testing pg_dump back that far ... regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby query cancellation and Streaming Replication integration
On Tue, 2010-03-02 at 12:47 -0800, Marc Munro wrote: IIUC this is only a problem for WAL from HOT updates and vacuums. If no vacuums or HOT updates have been performed, there is no risk of returning bad data. So WAL that does not contain HOT updates or vacuums could be applied on the standby without risk, even if there are long-running queries in play. This is not a complete solution but may reduce the likelihood of queries having to be cancelled. I guess the approach here would be to check WAL before applying it, and only cancel queries if the WAL contains HOT updates or vacuums. That's what we do. Taking the idea further, if WAL records contained the tid of the latest tuples that were overwritten, even more WAL could be applied without having to cancel queries. To take it further still, if vacuum on the master could be prevented from touching records that are less than max_standby_delay seconds old, it would be safe to apply WAL from the very latest vacuum. I guess HOT could be handled similarly though that may eliminate much of the advantage of HOT updates. Thanks for your ideas. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] plperl _init settings
David E. Wheeler wrote: On Mar 2, 2010, at 4:33 PM, Andrew Dunstan wrote: I propose to add the following note to the docs: Preloading modules using plperl.on_init does not make them available for use by plperl. External perl modules can only be used in plperlu. Perhaps add a note that some other interface to allow preloaded modules to also be available to plperl may be provided in the future. No, that's far from a done deal. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] renameatt() can rename attribute of index, sequence, ...
Is it an expected behavior? postgres= CREATE SEQUENCE s; CREATE SEQUENCE postgres= ALTER TABLE s RENAME sequence_name TO abcd; ALTER TABLE postgres= CREATE TABLE t (a int primary key, b text); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index t_pkey for table t CREATE TABLE postgres= ALTER TABLE t_pkey RENAME a TO xyz; ALTER TABLE The documentation says: http://developer.postgresql.org/pgdocs/postgres/sql-altertable.html : RENAME The RENAME forms change the name of a table (or an index, sequence, or view) or the name of an individual column in a table. There is no effect on the stored data. It seems to me the renameatt() should check relkind of the specified relation, and raise an error if relkind != RELKIND_RELATION. -- KaiGai Kohei kai...@ak.jp.nec.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] renameatt() can rename attribute of index, sequence, ...
KaiGai Kohei kai...@ak.jp.nec.com writes: Is it an expected behavior? There's no particular reason to forbid it, is there? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] renameatt() can rename attribute of index, sequence, ...
(2010/03/03 11:22), Tom Lane wrote: KaiGai Koheikai...@ak.jp.nec.com writes: Is it an expected behavior? There's no particular reason to forbid it, is there? Perhaps, it is harmless. It just seemed to me the renameatt() was not implemented correctly according to the documentation. -- KaiGai Kohei kai...@ak.jp.nec.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] USE_LIBXSLT in MSVC builds
Andrew Dunstan and...@dunslane.net writes: Baiji is now failing, however. Perhaps it is not finding the XSLT lib or dll? Curious indeed, because it passed yesterday, *after* I had made all those changes in contrib/xml2 itself. The only deltas since then are your MSVC script additions. Now presumably, the XSLT support was commented out in yesterday's run for lack of USE_LIBXSLT, and the pass was against the variant output file that allowed for that to fail. So I think you're right that there's something wrong with the link to libxslt, but how come no sign of trouble in the build log? Does Windows have an equivalent of rpath that maybe we're forgetting to add libxslt to? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] plperl _init settings
On Mar 2, 2010, at 6:00 PM, Andrew Dunstan wrote: Perhaps add a note that some other interface to allow preloaded modules to also be available to plperl may be provided in the future. No, that's far from a done deal. Hence the “may.” But either way. David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] USE_LIBXSLT in MSVC builds
Tom Lane wrote: Andrew Dunstan and...@dunslane.net writes: Baiji is now failing, however. Perhaps it is not finding the XSLT lib or dll? Curious indeed, because it passed yesterday, *after* I had made all those changes in contrib/xml2 itself. The only deltas since then are your MSVC script additions. Now presumably, the XSLT support was commented out in yesterday's run for lack of USE_LIBXSLT, and the pass was against the variant output file that allowed for that to fail. So I think you're right that there's something wrong with the link to libxslt, but how come no sign of trouble in the build log? Does Windows have an equivalent of rpath that maybe we're forgetting to add libxslt to? There is something funny about the iconv setup (libxml requires iconv). My paths on red_bat are a bit different from baiji's so it might not expose the problem. I'm going to make the iconv setup work like the other libraries, and hope it fixes baiji's problem. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] to_timestamp() and quarters
Scott Bailey wrote:
Tom Lane wrote:
Asher Hoskins as...@piceur.co.uk writes:
I can't seem to get to_timestamp() or to_date() to work with quarters,
The source code says
* We ignore Q when converting to date because it is not
* normative.
*
* We still parse the source string for an integer, but it
* isn't stored anywhere in 'out'.
That might be a reasonable position, but it seems like it'd be better to
throw an error than silently do nothing. Anybody know what Oracle does
with this?
+1 for throwing error.
Oracle 10g throws ORA-01820: format code cannot appear in date input format.
Well, I can easily make it do what you expect, and I don't see many
error returns in that area of the code, so I just wrote a patch that
does what you would expect rather than throw an error.
test= select to_date('2010-1', '-Q');
to_date
2010-01-01
(1 row)
test= select to_date('2010-3', '-Q');
to_date
2010-07-01
(1 row)
test= select to_date('2010-7', '-Q');
to_date
2011-07-04
(1 row)
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do
Index: src/backend/utils/adt/formatting.c
===
RCS file: /cvsroot/pgsql/src/backend/utils/adt/formatting.c,v
retrieving revision 1.168
diff -c -c -r1.168 formatting.c
*** src/backend/utils/adt/formatting.c 26 Feb 2010 02:01:08 - 1.168
--- src/backend/utils/adt/formatting.c 3 Mar 2010 03:29:05 -
***
*** 2671,2685
s += SKIP_THth(n-suffix);
break;
case DCH_Q:
!
! /*
! * We ignore Q when converting to date because it is not
! * normative.
! *
! * We still parse the source string for an integer, but it
! * isn't stored anywhere in 'out'.
! */
! from_char_parse_int((int *) NULL, s, n);
s += SKIP_THth(n-suffix);
break;
case DCH_CC:
--- 2671,2678
s += SKIP_THth(n-suffix);
break;
case DCH_Q:
! from_char_parse_int(out-mm, s, n);
! out-mm = (out-mm - 1) * 3 + 1;
s += SKIP_THth(n-suffix);
break;
case DCH_CC:
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Simple fix for contrib/xml2
Tom Lane wrote: I'm beginning to think nobody is going to step up and fix contrib/xml2, so I had a look at it myself. Funny -- I was about to start working on this when the earthquake hit here ... glad you got it :-) -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: [COMMITTERS] pgsql: Instead of trying (and failing) to allow label at the end of
On Tue, Mar 02, 2010 at 11:02:54PM -0500, Jaime Casanova wrote: On Tue, Mar 2, 2010 at 8:53 PM, Tom Lane t...@postgresql.org wrote: Log Message: --- Instead of trying (and failing) to allow label at the end of a DECLARE section, throw an error message saying explicitly that the label must go before DECLARE. with the docs saying another thing, this couldn't be considered as a bug fix and therefore backpatchable +1 for back-patching. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Avoiding bad prepared-statement plans.
On Tue, Mar 2, 2010 at 6:54 PM, Bruce Momjian br...@momjian.us wrote: Robert Haas wrote: Adding SQL to indicate whether it should be re-planned or not is completely unappealing. If I could change the code, today, I'd just turn off or choose not to use PREPARE/EXECUTE. Today, PREPARE/EXECUTE seems like it should always be considered slower unless one can prove it is actually faster in a specific case, which is the exact opposite of what people expect. I don't really understand most of what you're saying here, but there's definitely some truth to your last sentence. This has easily got to be one of the top ten questions on -performance. It seems it is the problem everyone knows about but no one fixes. :-( I'd work on it, but Tom doesn't like my proposed fix. *shrug* ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] renameatt() can rename attribute of index, sequence, ...
2010/3/2 KaiGai Kohei kai...@ak.jp.nec.com: Is it an expected behavior? postgres= CREATE SEQUENCE s; CREATE SEQUENCE postgres= ALTER TABLE s RENAME sequence_name TO abcd; ALTER TABLE postgres= CREATE TABLE t (a int primary key, b text); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index t_pkey for table t CREATE TABLE postgres= ALTER TABLE t_pkey RENAME a TO xyz; ALTER TABLE The documentation says: http://developer.postgresql.org/pgdocs/postgres/sql-altertable.html : RENAME The RENAME forms change the name of a table (or an index, sequence, or view) or the name of an individual column in a table. There is no effect on the stored data. It seems to me the renameatt() should check relkind of the specified relation, and raise an error if relkind != RELKIND_RELATION. Are we talking about renameatt() or RenameRelation()? Letting RenameRelation() rename whatever seems fairly harmless; renameatt(), on the other hand, should probably refuse to allow this: CREATE SEQUENCE foo; ALTER TABLE foo RENAME COLUMN is_cycled TO bob; ...because that's just weird. Tables, indexes, and views make sense, but the attributes of a sequence should be nailed down I think; they're basically system properties. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] to_timestamp() and quarters
In response to Bruce Momjian :
Well, I can easily make it do what you expect, and I don't see many
error returns in that area of the code, so I just wrote a patch that
does what you would expect rather than throw an error.
Well, that's great and better than an error, thx.
test= select to_date('2010-7', '-Q');
to_date
2011-07-04
(1 row)
Is this per SQL-Spec? I would expect an error for a quarter not in
(1,2,3,4).
But stop, now i see:
test=*# select to_date('2010-02-29', '-MM-DD');
to_date
2010-03-01
(1 row)
So it is maybe a congruously behavior ;-)
Regards, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Instead of trying (and failing) to allow label at the end of
David Fetter da...@fetter.org writes: On Tue, Mar 02, 2010 at 11:02:54PM -0500, Jaime Casanova wrote: On Tue, Mar 2, 2010 at 8:53 PM, Tom Lane t...@postgresql.org wrote: Instead of trying (and failing) to allow label at the end of a DECLARE section, throw an error message saying explicitly that the label must go before DECLARE. with the docs saying another thing, this couldn't be considered as a bug fix and therefore backpatchable +1 for back-patching. No, I intentionally didn't back-patch that. The only benefit of the change is throwing a useful error message for questionable syntax. As against that we have the possibility of breaking code that works now, if someone's got an incorrectly-placed label that they're not actually referencing. I grant that that's not a very likely case; but the error that the message would help people fix isn't very likely either, as demonstrated by the fact that this never came up before. We shouldn't be making changes in the behavior of back branches for such things. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] renameatt() can rename attribute of index, sequence, ...
(2010/03/03 14:26), Robert Haas wrote: 2010/3/2 KaiGai Koheikai...@ak.jp.nec.com: Is it an expected behavior? postgres= CREATE SEQUENCE s; CREATE SEQUENCE postgres= ALTER TABLE s RENAME sequence_name TO abcd; ALTER TABLE postgres= CREATE TABLE t (a int primary key, b text); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index t_pkey for table t CREATE TABLE postgres= ALTER TABLE t_pkey RENAME a TO xyz; ALTER TABLE The documentation says: http://developer.postgresql.org/pgdocs/postgres/sql-altertable.html : RENAME The RENAME forms change the name of a table (or an index, sequence, or view) or the name of an individual column in a table. There is no effect on the stored data. It seems to me the renameatt() should check relkind of the specified relation, and raise an error if relkind != RELKIND_RELATION. Are we talking about renameatt() or RenameRelation()? Letting RenameRelation() rename whatever seems fairly harmless; renameatt(), on the other hand, should probably refuse to allow this: CREATE SEQUENCE foo; ALTER TABLE foo RENAME COLUMN is_cycled TO bob; ...because that's just weird. Tables, indexes, and views make sense, but the attributes of a sequence should be nailed down I think; they're basically system properties. I'm talking about renameatt(), not RenameRelation(). If our perspective is these are a type of system properties, we should be able to reference these attributes with same name, so it is not harmless to allow renaming these attributes. I also agree that it makes sense to allow renaming attributes of tables and views. But I don't know whether it makes sense to allow it on indexs, like sequence and toast relations. Thanks, -- KaiGai Kohei kai...@ak.jp.nec.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Instead of trying (and failing) to allow label at the end of
On Wed, Mar 3, 2010 at 1:00 AM, Tom Lane t...@sss.pgh.pa.us wrote: David Fetter da...@fetter.org writes: On Tue, Mar 02, 2010 at 11:02:54PM -0500, Jaime Casanova wrote: On Tue, Mar 2, 2010 at 8:53 PM, Tom Lane t...@postgresql.org wrote: Instead of trying (and failing) to allow label at the end of a DECLARE section, throw an error message saying explicitly that the label must go before DECLARE. with the docs saying another thing, this couldn't be considered as a bug fix and therefore backpatchable +1 for back-patching. No, I intentionally didn't back-patch that. The only benefit of the change is throwing a useful error message for questionable syntax. then, maybe a fix in the docs of back branches? OTOH, anyone that already had the docs downloaded will not benefit of that, but that is just as fair as if you don't update the server you don't get the last fixes ;) -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Instead of trying (and failing) to allow label at the end of
Jaime Casanova jcasa...@systemguards.com.ec writes: On Wed, Mar 3, 2010 at 1:00 AM, Tom Lane t...@sss.pgh.pa.us wrote: No, I intentionally didn't back-patch that. Â The only benefit of the change is throwing a useful error message for questionable syntax. then, maybe a fix in the docs of back branches? What's to fix? The documentation already makes it quite clear where you're supposed to put the label. I think documenting the code's actual behavior could only confuse people. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Instead of trying (and failing) to allow label at the end of
On Wed, Mar 3, 2010 at 1:12 AM, Tom Lane t...@sss.pgh.pa.us wrote: Jaime Casanova jcasa...@systemguards.com.ec writes: On Wed, Mar 3, 2010 at 1:00 AM, Tom Lane t...@sss.pgh.pa.us wrote: No, I intentionally didn't back-patch that. The only benefit of the change is throwing a useful error message for questionable syntax. then, maybe a fix in the docs of back branches? What's to fix? The documentation already makes it quite clear where you're supposed to put the label. I think documenting the code's actual behavior could only confuse people. ah! yeah! sorry for the noise... it was just the need to say something else... ;) -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
