Re: [HACKERS] Avoiding bad prepared-statement plans.
On 02/27/2010 11:20 PM, Craig Ringer wrote: Essentially, you have: 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? I think if SQL hints were sufficient, that clients would only need to remove the prepared statement and re-create it whenever required. It should do the right thing automatically. I'm convinced that means generic plans are always wrong, and that some combination of performing fixed operations in PREPARE and variable operations in EXECUTE, combined with a plan caching against the prepared statement with criteria to determine whether or not the parameters match the assumptions made when creating one of the cached plans. Tom says extracting the fixed part of the planning out to PREPARE would be difficult or less valuable than I think. And the multi-plan caching with criteria seems to have been brought up and not commented on much by several people. So, it doesn't look like I will get this unless I learn how to implement it myself - which is probably not feasible at this time. :-) Not getting this, I think I'd be happy if PREPARE/EXECUTE can *easily* detect the worst cases (i.e. not slower in the general case), and generic plan plus custom plan plus custom execution is still significantly faster than generic plan plus generic execution. 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. Cheers, mark -- 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?
Hi Alvaro. Ooops, surprised at news now:-( I'm wishing you and your familys is no trouble. However, I look at one relief because your mail has arrived. ! - Original Message - From: "Marc G. Fournier" On Sat, 27 Feb 2010, Alvaro Herrera wrote: Hi. We're out of town right now, and it seems I can't get to my home machine (probably just a loose cable). Our building was shaken badly enough that we'll have a lot of work to do to make it usable again. Our earthquake was 8.3 or 8.8 depending on who you ask, and whatever it really was, it was strong enough to tear down a bunch of buildings. Not on my zone though, fortunately for us. I have several friends on the worst area though :-( Glad to hear you were in a safer zone .. something I've never had to weather so far in my life, and would rather keep it that way ;( Re: the more frequent earthquakes, yeah I was thinking the same today. An actual scientific study would be more useful than idle speculation though ... One comment that one guy at work had about this was along the lines of aftershocks, where there is a ripple effect that radiates out from a big one affecting seemingly unrelated areas ... not sure how much I subscribe to that theory, as one would think that the 'aftershocks' would be less intense then the original, and, so far, 8.3/8.8 sounds *alot* higher then anything I've heard of recently ... My thoughts and prays go out to you and your family ... Marc G. FournierHub.Org Hosting Solutions S.A. scra...@hub.org http://www.hub.org Yahoo:yscrappySkype: hub.orgICQ:7615664MSN:scra...@hub.org -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- 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?
At Saturday, 02/27/2010 on 4:21 pm "Marc G. Fournier" wrote: > On Sat, Feb 27, 2010 at 10:45 PM, Alvaro Herrera > 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? > > Re: the more frequent earthquakes, yeah I was thinking the same today. An > actual scientific study would be more useful than idle speculation though > This is a technical list so i won't insist on this but those of you that wanna give a try can read Matthew 24:3, 7, 8 and Luke 21:11 -- 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] Hot Standby query cancellation and Streaming Replication integration
Greg Stark wrote: On Sun, Feb 28, 2010 at 5:28 AM, Greg Smith wrote: The idea of the workaround is that if you have a single long-running query to execute, and you want to make sure it doesn't get canceled because of a vacuum cleanup, you just have it connect back to the master to keep an open snapshot the whole time. Also, I'm not sure this actually works. When your client makes this additional connection to the master it's connecting at some transaction in the future from the slave's point of view. The master could have already vacuumed away some record which the snapshot the client gets on the slave will have in view. Right, and there was an additional comment in the docs alluding to some sleep time on the master that intends to try and improve thins. If you knew how long archive_timeout was you could try to sleep longer than it to try and increase your odds of avoiding an ugly spot. But there are race conditions galore possible here, particularly if your archiver or standby catchup is backlogged. Still it's a handy practical trick even if it isn't 100% guaranteed to work. But I don't think it provides the basis for something we can bake in. Agreed on both counts, which is why it's in the current docs as a workaround people can consider, but not what I've been advocating as the right way to proceed. -- 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] Re: Hot Standby query cancellation and Streaming Replication integration
Robert Haas wrote: It seems to me that if we're forced to pass the xmin from the slave back to the master, that would be a huge step backward in terms of both scalability and performance, so I really hope it doesn't come to that. Not forced to--have the option of. There are obviously workloads where you wouldn't want this. At the same time, I think there are some pretty common ones people are going to expect HS+SR to work on transparently where this would obviously be the preferred trade-off to make, were it available as one of the options. The test case I put together shows an intentionally pathological but not completely unrealistic example of such a workload. I wish I understood better exactly what you mean by "the notion of synchronizing the WAL stream against slave queries" and why you don't think it will work. Can you elaborate? There's this constant WAL stream coming in from the master to the slave. Each time the slave is about to apply a change from that stream, it considers "will this disrupt one of the queries I'm already executing?". If so, it has to make a decision about what to do; that's where the synchronization problem comes from. The current two options are "delay applying the change", at which point the master and standby will drift out of sync until the query ends and it can catch back up, or "cancel the query". There are tunables for each of these, and they all seem to work fine (albeit without too much testing in the field yet). My concern is that the tunable that tries to implement the other thing you might want to optimize for--"avoid letting the master generate WAL entires that are the most likely ones to conflict"--just isn't very usable in its current form. Tom and I don't see completely eye to eye on this, in that I'm not so sure the current behaviors are "fundamentally wrong and we will never be able to make [them] work". If that's really the case, you may not ever get the scalability/performance results you're hoping for from this release, and really we're all screwed if those are the only approaches available. What I am sure of is that a SR-based xmin passing approach is simpler, easier to explain, more robust for some common workloads, and less likely to give surprised "wow, I didn't think *that* would cancel my standby query" reports from the field than any way you can configure Hot Standby alone right now. And since I never like to bet against Tom's gut feel, having it around as a "plan B" in case he's right about an overwhelming round of bug reports piling up against the max_standby_delay etc. logic doesn't hurt either. I spent a little time today seeing if there was any interesting code I might steal from the early "synchrep" branch at http://git.postgresql.org/gitweb?p=users/fujii/postgres.git;a=summary , but sadly when I tried to rebase that against the master to separate out just the parts unique to it the merge conflicts were overwhelming. I hate getting beaten by merge bitrot even when Git is helping. -- 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] Lock Wait Statistics (next commitfest)
Robert Haas wrote: This might be my fault, so I apologize for killing your enthusiasm. I think when I get wrapped up in a CommitFest (and especially during the second half) I get wound up in determining whether or not things are going to get applied and tend to give short shrift to thinks that seem like they won't. My bad. Generally speaking, I am always in favor of adding things to the CommitFest, but I guess the one exception I would make is if there are outstanding comments already given that haven't been addressed yet. In that case it seems a little unfair to ask people to review it further unless there are very specific questions you need answered. I think you were good about communicating that the patch wasn't ready to be applied yet, but I also think that it's to be expected that you'll get less feedback while it's in that state. Yeah, makes sense, altho perhaps there needs to be a way to get incremental progress reviewed? Anyway, my apologies for turning you off to the process... that wasn't my intent and I'm sorry that it had that effect. I think there was a level of confusion on both sides, especially with a newish process for me to get my head around, so no apology needed at all as it is/was clear that there was no intent on your part to make things hard! (that is why I said nothing at the time). But thank you for your kind words, much appreciated. Best wishes Mark best wishes -- 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
If i have got over excited in the previous update, please ignore that. a) We are already going from table to index to do unique checks. This is the same thing, which we will do to go and update the snapshot in the indexes. b) The way, it should work would be to have a check on whether the operator is broken / function is volatile and put the onus on the user to make sure that they are updated correctly. c) In the ItemId, instead of removing the size field completely, we can store the size as size/4(since it is MaxAligned). This will save us 2 bits. In index we only need 13 bits to store the complete size in the tuple, but we use 15 bits in the iid, so again we can have two more bit savings there. That's sufficient for us to express the hint fields in a index. I think Karl's way of expressing it requires only one bit, which looks very efficient. So we can check the hint bits from the iid itself. So just with a addition of 8 bytes per tuple, we can have the snapshot stored with the index. Can someone please comment on this? Thanks, Gokul.
[HACKERS] Re: Hot Standby query cancellation and Streaming Replication integration
On Sun, Feb 28, 2010 at 5:28 AM, Greg Smith wrote: > The idea of the workaround is that if you have a single long-running query > to execute, and you want to make sure it doesn't get canceled because of a > vacuum cleanup, you just have it connect back to the master to keep an open > snapshot the whole time. That's basically the same idea that > vacuum_defer_cleanup_age implements, except you don't have to calculate a > value--you just hold open the snapshot to do it. This sounds like it would require a separate connection for each client on the replica. That would be a pretty big burden for the master. Also, I'm not sure this actually works. When your client makes this additional connection to the master it's connecting at some transaction in the future from the slave's point of view. The master could have already vacuumed away some record which the snapshot the client gets on the slave will have in view. Even if you defer taking the snapshot on the slave until after connecting to the master it's still possibly "in the past" compared to the xmin on the master. I think to make this work you would have to connect to the master, establish a snapshot, then fetch pg_current_xlog_location(), then poll the slave and wait until it reaches that same position -- and only then perform your query taking care to establish a fresh snapshot for it such as by starting a new transaction on the slave. That's a lot of effort to go to. Still it's a handy practical trick even if it isn't 100% guaranteed to work. But I don't think it provides the basis for something we can bake in. -- 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] Hot Standby query cancellation and Streaming Replication integration
Bruce Momjian wrote: "The first option is to connect to the primary server and keep a query active for as long as needed to run queries on the standby. This guarantees that a WAL cleanup record is never generated and query conflicts do not occur, as described above. This could be done using contrib/dblink and pg_sleep(), or via other mechanisms." I am unclear how you would easily advance the snapshot as each query completes on the slave. The idea of the workaround is that if you have a single long-running query to execute, and you want to make sure it doesn't get canceled because of a vacuum cleanup, you just have it connect back to the master to keep an open snapshot the whole time. That's basically the same idea that vacuum_defer_cleanup_age implements, except you don't have to calculate a value--you just hold open the snapshot to do it. When that query ended, its snapshot would be removed, and then the master would advance to whatever the next latest one is. Nothing fancier than that. The only similarity is that if you made every query that happened on the standby do that, it would effectively be the same behavior I'm suggesting could be available via the standby->master xmin publication. -- 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] [GENERAL] trouble with to_char('L')
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.
--
Bruce Momjian http://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 28 Feb 2010 03:59:14 -
***
*** 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 $
*
*---
*/
***
*** 386,391
--- 386,459
free(s->positive_sign);
}
+ #ifdef WIN32
+ /*
+ * 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 utflen, bytes_per_char;
+ wchar_t *wbuf;
+ char *dst;
+
+ if (!str[0])
+ return strdup(str);
+ 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 a destination buffer");
+
+ /* Convert wide string to UTF8 */
+ utflen = WideCharToMultiByte(CP_UTF8, 0, wbuf, wchars, dst, dstlen, NULL, NULL);
+ if (utflen == 0)
+ elog(ERROR,
+ "could not convert string %04x to UTF-8: error %lu", wbuf[0], GetLastError());
+ pfree(wbuf);
+
+ dst[utflen] = '\0';
+ if (db_encoding != PG_UTF8)
+ {
+ PG_TRY();
+ {
+ char *convstr = pg_do_encoding_conversion(dst, utflen, PG_UTF8, db_encoding);
+ if (dst != convstr)
+ {
+ strlcpy(dst, convstr, dstlen);
+ pfree(convstr);
+ }
+ }
+ PG_CATCH();
+ {
+ FlushErrorState();
+ dst[0] = '\0';
+ }
+ PG_END_TRY();
+ }
+
+ return dst;
+ }
+ #else
+ static char *db_encoding_strdup(const char *item, const char *str)
+ {
+ return strdup(str);
+ }
+ #endif /* WIN32 */
/*
* Return the POSIX lconv struct (contains number/money formatting
***
*** 398,403
--- 466,475
struct lconv *extlconv;
char *save_lc_monetary;
char *save_lc_numeric;
+ #ifdef WIN32
+ char *save_lc_ctype = NULL;
+ bool lc_ctype_was_null = false;
+ #endif
/* Did we do it already? */
if (CurrentLocaleConvValid)
***
*** 413,442
if (save_lc_numeric)
save_lc_numeric = pstrdup(save_lc_numeric);
setlocale(LC_MONETARY, locale_monetary);
setlocale(LC_NUMERIC, locale_numeric);
!
! /* Get formatting information */
extlconv = localeconv();
/*
! * Must copy all values since restoring internal settings may overwrite
* localeconv()'s results.
*/
CurrentLocaleConv = *extlconv;
! Curren
Re: [HACKERS] Avoiding bad prepared-statement plans.
Robert Haas writes: > On Fri, Feb 26, 2010 at 7:03 PM, Tom Lane wrote: >> Wouldn't it be better if it just did the right thing automatically? >> >> The sort of heuristic I'm envisioning would essentially do "replan every >> time" for some number of executions, and give up only if it noticed that >> it wasn't getting anything better than the generic plan. So you'd have >> a fixed maximum overhead per session when the custom plan was useless, >> and the Right Thing when it wasn't. > Which is likely useless for my use case. [ shrug... ] You'd better explain exactly why, if you want me to take that objection seriously. 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] Avoiding bad prepared-statement plans.
On 26/02/2010 11:40 AM, Tom Lane wrote: But putting support for a per-query level of control into the protocol (and then every client library) as well as every PL is going to be painful to implement, and even more painful to use. You mean something like 'EXECUTE REPLAN' and protocol/PL-level equivalents? That's what people on -GENERAL often seem to need. A way, for a particular query, to say "replan this every time, because the stats are very signifcant here". I still like the idea of automatically replanning with the known parameter values, and noting whether the result plan was estimated to be noticeably cheaper than the generic plan, and giving up on generating custom plans if we didn't observe any such win over N tries. That risks making prepared statements less efficient when re-used between 2 and N times. People would end up asking for a 'no replan' knob, which I'm not sure is any improvement over a 'force replan' knob. OTOH, a GUC to turn that off would be (IMO) fairly harmless - if you know you don't have any problematic prepared queries, turn it off to save some cycles. Another thought: I wonder if this should be viewed from one step back. Many of these issues come from people who don't actually want prepared statements for performance, they're just using them to get convienient and secure parameter placement and server-side caching of the query text. Essentially, you have: 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? -- Craig Ringer -- 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 Sat, 27 Feb 2010, Alvaro Herrera wrote: Hi. We're out of town right now, and it seems I can't get to my home machine (probably just a loose cable). Our building was shaken badly enough that we'll have a lot of work to do to make it usable again. Our earthquake was 8.3 or 8.8 depending on who you ask, and whatever it really was, it was strong enough to tear down a bunch of buildings. Not on my zone though, fortunately for us. I have several friends on the worst area though :-( Glad to hear you were in a safer zone .. something I've never had to weather so far in my life, and would rather keep it that way ;( Re: the more frequent earthquakes, yeah I was thinking the same today. An actual scientific study would be more useful than idle speculation though ... One comment that one guy at work had about this was along the lines of aftershocks, where there is a ripple effect that radiates out from a big one affecting seemingly unrelated areas ... not sure how much I subscribe to that theory, as one would think that the 'aftershocks' would be less intense then the original, and, so far, 8.3/8.8 sounds *alot* higher then anything I've heard of recently ... My thoughts and prays go out to you and your family ... Marc G. FournierHub.Org Hosting Solutions S.A. scra...@hub.org http://www.hub.org Yahoo:yscrappySkype: hub.orgICQ:7615664MSN:scra...@hub.org -- 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?
Hi. We're out of town right now, and it seems I can't get to my home machine (probably just a loose cable). Our building was shaken badly enough that we'll have a lot of work to do to make it usable again. Our earthquake was 8.3 or 8.8 depending on who you ask, and whatever it really was, it was strong enough to tear down a bunch of buildings. Not on my zone though, fortunately for us. I have several friends on the worst area though :-( (I have to note that buildings here are built to resist this kind of thing, so the fact that some went down means that it was really strong) Re: the more frequent earthquakes, yeah I was thinking the same today. An actual scientific study would be more useful than idle speculation though ... At Saturday, 02/27/2010 on 4:21 pm "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?
Re: [HACKERS] Hot Standby query cancellation and Streaming Replication integration
Greg Smith wrote: > Josh Berkus wrote: > > > Now that I think about it, the xmin thing really doesn't seem > > conceptually difficult. If the slave just opens a 2nd, special query > > connection back to the master and publishes its oldest xmin there, as > > far as the master is concerned, it's just another query backend. > > Could it be that easy? > > > > Something just like that is in fact already suggested as a workaround in > the Hot Standby manual: > > "The first option is to connect to the primary server and keep a query > active for as long as needed to run queries on the standby. This > guarantees that a WAL cleanup record is never generated and query > conflicts do not occur, as described above. This could be done using > contrib/dblink and pg_sleep(), or via other mechanisms." I am unclear how you would easily advance the snapshot as each query completes on the slave. -- Bruce Momjian http://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: Performance Patches Was: [HACKERS] Lock Wait Statistics (next commitfest)
On Sat, Feb 27, 2010 at 6:22 PM, Mark Kirkwood wrote: > Greg Smith wrote: >> While I was in there I also added some more notes on my personal top patch >> submission peeve, patches whose purpose in life is to improve performance >> that don't come with associated easy to run test cases, including a sample >> of that test running on a system that shows the speedup clearly. If I were >> in charge I just would make it standard project policy to reject any >> performance patch without those characteristics immediately. > > While I completely agree that the submitter should be required to supply a > test case and their results, so the rest of us can try to reproduce said > improvement - rejecting the patch out of hand is a bit harsh I feel - Hey, > they may just have forgotten to supply these things! The reviewer can always > ask, can they not? I would prefer to see the wiki say something along the > lines of "If you don't supply a test case you will be asked for one before > any further review can proceed..." Agreed. Personally, I have no problem with giving a patch a brief once-over even if it lacks an appropriate test case, but serious review without a test case is really hard. That's one of the things that slowed down rbtree a lot this last CommitFest. We should probably try to make a point of trying to point this problem out to patch submitters before the CommitFest even starts, so that they can address it in advance. ...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] Anyone know if Alvaro is OK?
On Feb 27, 2010, at 20:33 , Robert Haas wrote: On Sat, Feb 27, 2010 at 7:21 PM, 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? Hrmmm ... Should I rocket my children to a solar system with a yellow sun? ...Robert Isn't that Rob-el? Michael Glaesemann grzm seespotcode net -- 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] Lock Wait Statistics (next commitfest)
On Sat, Feb 27, 2010 at 5:40 AM, Mark Kirkwood wrote: > I'd also like to take the opportunity to express a little frustration about > the commitfest business - really all I wanted was the patch *reviewed* as > WIP - it seemed that in order to do that I needed to enter it into the > various commitfests... then I was faced with comments to the effect that it > was not ready for commit so should not have been entered into a commifest at > all... sigh, a bit of an enthusiasm killer I'm afraid... This might be my fault, so I apologize for killing your enthusiasm. I think when I get wrapped up in a CommitFest (and especially during the second half) I get wound up in determining whether or not things are going to get applied and tend to give short shrift to thinks that seem like they won't. My bad. Generally speaking, I am always in favor of adding things to the CommitFest, but I guess the one exception I would make is if there are outstanding comments already given that haven't been addressed yet. In that case it seems a little unfair to ask people to review it further unless there are very specific questions you need answered. I think you were good about communicating that the patch wasn't ready to be applied yet, but I also think that it's to be expected that you'll get less feedback while it's in that state. Anyway, my apologies for turning you off to the process... that wasn't my intent and I'm sorry that it had that effect. ...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] Anyone know if Alvaro is OK?
On Sat, Feb 27, 2010 at 7:21 PM, 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? > > Hrmmm ... Should I rocket my children to a solar system with a yellow sun? ...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] C libpq frontend library fetchsize
On Fri, Feb 26, 2010 at 3:28 PM, Yeb Havinga wrote: > I'm wondering if there would be community support for adding using the > execute message with a rownum > 0 in the c libpq client library, as it is > used by the jdbc driver with setFetchSize. Not sure I follow what you're asking... what would the new/changed function signature be? ...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] add_path optimization
On Fri, Feb 26, 2010 at 10:07 PM, Bruce Momjian wrote: > Did this ever get applied/resolved? No, I never went back and tried to fix the brokenness Tom found. ...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] Avoiding bad prepared-statement plans.
On Fri, Feb 26, 2010 at 7:03 PM, Tom Lane wrote: > Robert Haas writes: >> Basically, what I really want here is some kind of keyword or other >> syntax that I can stick into a PL/pgsql query that requests a replan >> on every execution. > > Wouldn't it be better if it just did the right thing automatically? > > The sort of heuristic I'm envisioning would essentially do "replan every > time" for some number of executions, and give up only if it noticed that > it wasn't getting anything better than the generic plan. So you'd have > a fixed maximum overhead per session when the custom plan was useless, > and the Right Thing when it wasn't. Which is likely useless for my use case. ...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] Re: Hot Standby query cancellation and Streaming Replication integration
On Fri, Feb 26, 2010 at 1:53 PM, Tom Lane wrote: > Greg Stark writes: >> In the model you describe any long-lived queries on the slave cause >> tables in the master to bloat with dead records. > > Yup, same as they would do on the master. > >> I think this model is on the roadmap but it's not appropriate for >> everyone and I think one of the benefits of having delayed it is that >> it forces us to get the independent model right before throwing in >> extra complications. It would be too easy to rely on the slave >> feedback as an answer for hard questions about usability if we had it >> and just ignore the question of what to do when it's not the right >> solution for the user. > > I'm going to make an unvarnished assertion here. I believe that the > notion of synchronizing the WAL stream against slave queries is > fundamentally wrong and we will never be able to make it work. > The information needed isn't available in the log stream and can't be > made available without very large additions (and consequent performance > penalties). As we start getting actual beta testing we are going to > uncover all sorts of missed cases that are not going to be fixable > without piling additional ugly kluges on top of the ones Simon has > already crammed into the system. Performance and reliability will both > suffer. > > I think that what we are going to have to do before we can ship 9.0 > is rip all of that stuff out and replace it with the sort of closed-loop > synchronization Greg Smith is pushing. It will probably be several > months before everyone is forced to accept that, which is why 9.0 is > not going to ship this year. Somewhat unusually for me, I haven't been able to keep up with my email over the last few days, so I'm weighing in on this one a bit late. It seems to me that if we're forced to pass the xmin from the slave back to the master, that would be a huge step backward in terms of both scalability and performance, so I really hope it doesn't come to that. I wish I understood better exactly what you mean by "the notion of synchronizing the WAL stream against slave queries" and why you don't think it will work. Can you elaborate? ...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] Anyone know if Alvaro is OK?
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? Hrmmm ... On Sat, 27 Feb 2010, Bruce Momjian wrote: Josh Berkus wrote: There was a huge earthquake in Chile this morning ... Alvaro, you OK? Yes, I talked to Alvaro via IM about 2 hours ago. He was already online. His apartment building was shaken up but undamaged and his family is fine too. -- Bruce Momjian http://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 Marc G. FournierHub.Org Hosting Solutions S.A. scra...@hub.org http://www.hub.org Yahoo:yscrappySkype: hub.orgICQ:7615664MSN:scra...@hub.org -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: Performance Patches Was: [HACKERS] Lock Wait Statistics (next commitfest)
Mark Kirkwood wrote: While I completely agree that the submitter should be required to supply a test case and their results, so the rest of us can try to reproduce said improvement - rejecting the patch out of hand is a bit harsh I feel - Hey, they may just have forgotten to supply these things! I didn't put any strong wording in the Wiki, I was just mentioning my personal position is far less tolerant of this than the current project policy. What I added was: "If the patch is intended to improve performance, it's a good idea to include some reproducible tests to demonstrate the improvement. If a reviewer cannot duplicate your claimed performance improvement in a short period of time, it's very likely your patch will be bounced. Do not expect that a reviewer is going to find your performance feature so interesting that they will build an entire test suite to prove it works. You should have done that as part of patch validation, and included the necessary framework for testing with the submission." Finding a reviewer for a performance patch and getting them up to speed to evaluate any submitted patch is time intensive, and it really sucks from the perspective of the CF manager and any reviewer who is handed a messy one. The intention was not to cut people off without warning them. The position I would advocate as being a fair one is that if you don't provide a test case for a performance improvement patch, you can't then expect that you'll be assigned a reviewer by the CF manager either until that's corrected. And if time for the CF runs out before you do that, you're automatically moved to "returned with feedback"--specifically, "write us a test case". -- 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] Hot Standby query cancellation and Streaming Replication integration
Josh Berkus wrote: Hey, take it easy! I read Stark's post as tongue-in-cheek, which I think it was. Yeah, I didn't get that. We've already exchanged mutual off-list apologies for the misunderstanding in both directions, I stopped just short of sending flowers. I did kick off this discussion with noting a clear preference this not wander into any personal finger-pointing. And I am far too displeased with the technical situation here to have much of a sense of humor left about it either. -- 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
Performance Patches Was: [HACKERS] Lock Wait Statistics (next commitfest)
Greg Smith wrote: While I was in there I also added some more notes on my personal top patch submission peeve, patches whose purpose in life is to improve performance that don't come with associated easy to run test cases, including a sample of that test running on a system that shows the speedup clearly. If I were in charge I just would make it standard project policy to reject any performance patch without those characteristics immediately. While I completely agree that the submitter should be required to supply a test case and their results, so the rest of us can try to reproduce said improvement - rejecting the patch out of hand is a bit harsh I feel - Hey, they may just have forgotten to supply these things! The reviewer can always ask, can they not? I would prefer to see the wiki say something along the lines of "If you don't supply a test case you will be asked for one before any further review can proceed..." Cheers Mark -- 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
> Thank you for combining a small personal attack with a selfish > commentary about how yours is the only valid viewpoint. Saves me a lot > of trouble replying to your messages, can just ignore them instead if > this is how you're going to act. Hey, take it easy! I read Stark's post as tongue-in-cheek, which I think it was. Though, Stark, if you're going to be flip, I'd suggest using a smiley next time. --Josh -- 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
> The part I still don't have good visibility on is how much of the > necessary SR infrastructure needed to support this communications > channel is already available in some form. I had though the walsender > on the master was already receiving messages sometimes from the > walreceiver on the standby, but I'm getting the impression from Heikki's > comments that this not the case at all yet. I don't think asking for a 2nd connection back from the standby to the master would be horrible for 9.0. I think it would be quite reasonable, actually; even with 2 connections per slave, you could still have quite a few slaves before the # of connections bogged down the master. --Josh -- 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] Lock Wait Statistics (next commitfest)
Mark Kirkwood wrote: I don't mean to be ungrateful about the actual reviews at all - and I did value the feedback received (which I hope was reasonably clear in the various replies I sent). I sense a bit of attacking the messenger in your tone... I thought there was a moderately big difference between the reality of the review you got and how you were characterizing it, and I was just trying to provide some perspective on how bad a true "bit of review" only would have worked. Since I saw you disclaimed that wording with a smiley I know it wasn't intending to be ungrateful, and I didn't intend to shoot the messenger. Apologies if my tone grazed you though. In any case, process feedback noted and assimilated into recommended practice: I just added a section about WIP patches to http://wiki.postgresql.org/wiki/Submitting_a_Patch#Patch_submission While I was in there I also added some more notes on my personal top patch submission peeve, patches whose purpose in life is to improve performance that don't come with associated easy to run test cases, including a sample of that test running on a system that shows the speedup clearly. If I were in charge I just would make it standard project policy to reject any performance patch without those characteristics immediately. -- 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] Hot Standby query cancellation and Streaming Replication integration
Josh Berkus wrote: Now that I think about it, the xmin thing really doesn't seem conceptually difficult. If the slave just opens a 2nd, special query connection back to the master and publishes its oldest xmin there, as far as the master is concerned, it's just another query backend. Could it be that easy? Something just like that is in fact already suggested as a workaround in the Hot Standby manual: "The first option is to connect to the primary server and keep a query active for as long as needed to run queries on the standby. This guarantees that a WAL cleanup record is never generated and query conflicts do not occur, as described above. This could be done using contrib/dblink and pg_sleep(), or via other mechanisms." And the idea of doing it mainly in client land has its attractions. The main reason I wandered toward asking about it in the context of SR is that there's already this open "Standby delay on idle system" issue with Hot Standby, and the suggested resolution for that problem involves publishing keep-alive data with timestamps over SR. While all these problems and potential solutions have been floating around for a long time, as you pointed out, the little flash of insight I had here was that it's possible to bundle these two problems together with a combined keep-alive timestamp+xmin message that goes in both directions. That removes one serious Hot Standby issue altogether, and adds an additional conflict avoidance mechanism for people who want to enable it, all with something that needs to get done sooner or later anyway for sync rep. The part I still don't have good visibility on is how much of the necessary SR infrastructure needed to support this communications channel is already available in some form. I had though the walsender on the master was already receiving messages sometimes from the walreceiver on the standby, but I'm getting the impression from Heikki's comments that this not the case at all yet. -- 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] NaN/Inf fix for ECPG
On Fri, Feb 26, 2010 at 11:14:01AM -0500, Tom Lane wrote: > I think it would be a good idea, just to have all that code using > identical #includes. R�mi's problem may be a platform bug rather Sounds reasonable, done. Michael -- Michael Meskes Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) Michael at BorussiaFan dot De, Meskes at (Debian|Postgresql) dot Org ICQ 179140304, AIM/Yahoo/Skype michaelmeskes, Jabber mes...@jabber.org VfL Borussia! Força Barça! Go SF 49ers! Use Debian GNU/Linux, PostgreSQL -- 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] Lock Wait Statistics (next commitfest)
On 2/27/10 1:04 PM, Mark Kirkwood wrote: >> > > LOL - I said a bit - it was only a little, connected with the commit vs > review confusion. I think I just got caught in the bedding in time for > the new development processes, I was advised to add it to the > commitfests, and them advised that it should not have been at a later time. Yeah,this is only the 2nd year we have done CFs, and is the first year we've had non-Core Team managing them. So the cement on the procedure is still wet. --Josh -- 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] ECPG, two varchars with same name on same line
On Fri, Feb 26, 2010 at 10:23:19PM -0500, Bruce Momjian wrote: > Was this fixed? No, need to get along to fixing it. Michael -- Michael Meskes Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) Michael at BorussiaFan dot De, Meskes at (Debian|Postgresql) dot Org ICQ 179140304, AIM/Yahoo/Skype michaelmeskes, Jabber mes...@jabber.org VfL Borussia! Força Barça! Go SF 49ers! Use Debian GNU/Linux, PostgreSQL -- 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] Lock Wait Statistics (next commitfest)
Greg Smith wrote: Mark Kirkwood wrote: Greg Smith wrote: Returned with feedback in October after receiving a lot of review, no updated version submitted since then: https://commitfest.postgresql.org/action/patch_view?id=98 Hmm - I would say a bit of review rather than a lot :-) It looks like you got useful feedback from at least three people, and people were regularly looking at your patch in some form for about three months. That's a lot of review. In many other open-source projects, your first patch would have been rejected after a quick look as unsuitable and that would have been the end of things for you. I feel lucky every time I get a volunteer to spend time reading my work and suggesting how it could be better; your message here doesn't seem to share that perspective. I don't mean to be ungrateful about the actual reviews at all - and I did value the feedback received (which I hope was reasonably clear in the various replies I sent). I sense a bit of attacking the messenger in your tone... I've submitted several patches to Postgres in the past, and have previously always enjoyed the experience, and I do get the culture - being a volunteer myself. To lower your frustration level next time, make sure to label the e-mail and the entry on the CommitFest app with the magic abbreviation "WIP" and this shouldn't be so much of an issue. The assumption for patches is that someone submitted them as commit candidates, and therefore they should be reviewed to that standard, unless clearly labeled otherwise. You briefly disclaimed yours as not being in that category in the initial text of your first message, but it was easy to miss that, particularly once it had been >8 months from when that messages showed up and it was still being discussed. LOL - I said a bit - it was only a little, connected with the commit vs review confusion. I think I just got caught in the bedding in time for the new development processes, I was advised to add it to the commitfests, and them advised that it should not have been at a later time. Yes, a bit frustrating at the time but not earth shattering at all. I'm mentioning it now mainly to help clarify the situation for anyone else wanting a WIP patch reviewed! In my case while labeling as WIP could well have helped - the (pretty short) message accompanying the patch is very clear that there is stuff to do - using the magic phrase "...merely to spark discussion..." - as were all the followup accompanying ones, with phrases like "still todo...". So yes, next time I'll label any patches more clearly, reviewers need to read the text of the patch they are about to review (note that most did). If you wanted to pick this back up again, I'd think that a look at what's been happening with the lock_timeout GUC patch would be informative--I'd think that has some overlap with the sort of thing you were trying to do. FYI, I thought your patch was useful, but didn't spent time on it because it's not ambitious enough. I would like to see statistics on a lot more types of waiting than just locks, and keep trying to find time to think about that big problem rather than worrying about the individual pieces of it. Excellent thanks - that is exactly the sort of comment that would have been very valuable to have made at the time (echo's Gokul's recent one interestingly enough). After all if enough people share this view, then clearly I need to either forget about the current patch, or design something more ambitious! regards Mark -- 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] NaN/Inf fix for ECPG
=?iso-8859-1?Q?R=E9mi_Zara?= writes: > Le 27 févr. 2010 à 17:57, Tom Lane a écrit : >> I don't think it's our bug to fix. > It would mean retiring pika until/if the bug is fixed... :-( Grumble ... well, I suppose we've put in worse platform-specific hacks elsewhere. At least this is pretty localized. 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
Greg, > If you think of it in those terms, the idea that "you need to run PITR > backup/archive recovery" to not get that behavior isn't an important > distinction anymore. If you run SR with the option enabled you could > get it, any other setup and you won't. +1. I always expected that we'd get this kind of behavior with "synch" in 9.1. I can see that there are two desired modes of behaviour depending on what the replication config is: 1) Master full-speed, degraded operation on slaves: this is the current wal_standby_delay approach. It has the advantage of supporting possibly hundreds of slaves, and certainly dozens. 2) Master burdened, full operation on slaves: this is the publish-xmin-back-to-master approach, which IIRC the core team first discussed at pgCon 2008 before Simon started work, and which you and Tom seem to think can be done soon. I can see people wanting to use either mode depending on their use-case. Or, for that matter, using both modes to different slaves. Now that I think about it, the xmin thing really doesn't seem conceptually difficult. If the slave just opens a 2nd, special query connection back to the master and publishes its oldest xmin there, as far as the master is concerned, it's just another query backend. Could it be that easy? Also, I'm serious about what I suggested earlier for "delay" mode. We should have an option for cancelled queries to be immediately retried, if that's feasible. It would turn something which is now a major application design issue (lots of cancelled queries) into just degrated performance. Overall, though, I'd say that getting 9.0 out the door relatively on-time is more important than getting it perfect. "Release early, release often" isn't just a mantra; it's a very good idea if you want your project to keep improving and not bog down and fall apart. --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] Anyone know if Alvaro is OK?
Josh Berkus wrote: > There was a huge earthquake in Chile this morning ... Alvaro, you OK? Yes, I talked to Alvaro via IM about 2 hours ago. He was already online. His apartment building was shaken up but undamaged and his family is fine too. -- Bruce Momjian http://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] Anyone know if Alvaro is OK?
There was a huge earthquake in Chile this morning ... Alvaro, you OK? --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] Lock Wait Statistics (next commitfest)
Gokulakannan Somasundaram wrote: Statspack works by the following way -a) it takes a copy of important catalog tables(pg_ tables) which store the information like wait statistics against wait events, i/o statistics cumulative against each SQL_Hash( and SQL_Text), whether a particular plan went for hard parse/ soft parse(because of plan caching) and the status of different in-memory data structures etc. This is actually missing the real work that went into building this feature into Oracle. Before it was possible to build statspack, first they went to the trouble of noting every source of this form of latency--lock waits, I/O statistics and waits, buffer pool waits--and instrumented every single one of them internally. Basically, every time something that might wait for a resource you later wanted to monitor the wait for happens, a start/end timestamp for that wait is noted, and ultimately the difference between them noting how long the event took is stored into the database. That's the data you must have collected at some point in order to get the summary. Meanwhile, PostgreSQL development is resistant to making any changes in this direction under the theory that a) it adds a lot of code complexity and b) constant calls to get the current system time are too expensive on some platforms to do them all the time. Until those two things are sorted out, what the high-level interface to the direction you're suggesting looks like doesn't really matter. DTrace support has managed to clear both of those hurdles due to its optional nature, perceived low overhead, and removing *storing* all the events generated to something that happens outside of the database. I agree with you that something like statspack is the direction PostgreSQL eventually needs to go, but it's going to be an uphill battle the whole time to get it built. The objections will be that it will add too much overhead at the lowest levels, where the data needed to support it is collected at. Once that is cleared, the high-level interface is easy to build. -- 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] Lock Wait Statistics (next commitfest)
Mark Kirkwood wrote: Greg Smith wrote: Returned with feedback in October after receiving a lot of review, no updated version submitted since then: https://commitfest.postgresql.org/action/patch_view?id=98 Hmm - I would say a bit of review rather than a lot :-) It looks like you got useful feedback from at least three people, and people were regularly looking at your patch in some form for about three months. That's a lot of review. In many other open-source projects, your first patch would have been rejected after a quick look as unsuitable and that would have been the end of things for you. I feel lucky every time I get a volunteer to spend time reading my work and suggesting how it could be better; your message here doesn't seem to share that perspective. I'd also like to take the opportunity to express a little frustration about the commitfest business - really all I wanted was the patch *reviewed* as WIP - it seemed that in order to do that I needed to enter it into the various commitfests... then I was faced with comments to the effect that it was not ready for commit so should not have been entered into a commifest at all... sigh, a bit of an enthusiasm killer I'm afraid... To lower your frustration level next time, make sure to label the e-mail and the entry on the CommitFest app with the magic abbreviation "WIP" and this shouldn't be so much of an issue. The assumption for patches is that someone submitted them as commit candidates, and therefore they should be reviewed to that standard, unless clearly labeled otherwise. You briefly disclaimed yours as not being in that category in the initial text of your first message, but it was easy to miss that, particularly once it had been >8 months from when that messages showed up and it was still being discussed. If you wanted to pick this back up again, I'd think that a look at what's been happening with the lock_timeout GUC patch would be informative--I'd think that has some overlap with the sort of thing you were trying to do. FYI, I thought your patch was useful, but didn't spent time on it because it's not ambitious enough. I would like to see statistics on a lot more types of waiting than just locks, and keep trying to find time to think about that big problem rather than worrying about the individual pieces of it. -- 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] NaN/Inf fix for ECPG
Le 27 févr. 2010 à 17:57, Tom Lane a écrit : > =?iso-8859-1?Q?R=E9mi_Zara?= writes: >> Le 26 févr. 2010 à 17:11, Tom Lane a écrit : >>> Hmm. So what do you get from >>> SELECT 'nan'::numeric::float8; > >> regression=# select 'nan'::numeric::float8; >> float8 >> -- >> Infinity >> (1 row) > >> So it is indeed the same behavior. > > Yeah. So what it boils down to is that the platform has a NAN constant > but casting it to double produces the wrong thing. There's no doubt > that that's a bug in the floating-point support. You did say you'd > submitted it to the NetBSD folk right? > I submitted it. > BTW, what about the float4 case, 'nan'::numeric::float4 ? That works OK. > >> Maybe that should be added to the regression tests. > > Perhaps. A lot of this stuff was never stress-tested in the past > because when it was put in, it was a crap shoot whether NaN (or Inf) > really worked on most platforms. Our attitude was "if it works for you, > great, but we're not going to sweat about it if it doesn't". I'm not > sure whether full IEEE float support has gotten sufficiently universal > to justify expecting more. I guess we could try it and see how many > other buildfarm members fail. > >> So what's the best way to workaround the bug in NetBSD/mips ? > > I don't think it's our bug to fix. > It would mean retiring pika until/if the bug is fixed... :-( Regards, Rémi Zara -- 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] NaN/Inf fix for ECPG
=?iso-8859-1?Q?R=E9mi_Zara?= writes: > Le 26 févr. 2010 à 17:11, Tom Lane a écrit : >> Hmm. So what do you get from >> SELECT 'nan'::numeric::float8; > regression=# select 'nan'::numeric::float8; > float8 > -- > Infinity > (1 row) > So it is indeed the same behavior. Yeah. So what it boils down to is that the platform has a NAN constant but casting it to double produces the wrong thing. There's no doubt that that's a bug in the floating-point support. You did say you'd submitted it to the NetBSD folk right? BTW, what about the float4 case, 'nan'::numeric::float4 ? > Maybe that should be added to the regression tests. Perhaps. A lot of this stuff was never stress-tested in the past because when it was put in, it was a crap shoot whether NaN (or Inf) really worked on most platforms. Our attitude was "if it works for you, great, but we're not going to sweat about it if it doesn't". I'm not sure whether full IEEE float support has gotten sufficiently universal to justify expecting more. I guess we could try it and see how many other buildfarm members fail. > So what's the best way to workaround the bug in NetBSD/mips ? I don't think it's our bug to fix. 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] Lock Wait Statistics (next commitfest)
Mark Kirkwood writes: > I'd also like to take the opportunity to express a little frustration > about the commitfest business - really all I wanted was the patch > *reviewed* as WIP - it seemed that in order to do that I needed to enter > it into the various commitfests... then I was faced with comments to the > effect that it was not ready for commit so should not have been entered > into a commifest at all... sigh, a bit of an enthusiasm killer I'm afraid... Well, entering a patch in a commitfest is certainly the best way to ensure that you'll get some feedback. If you just pop it up on the mailing lists, you may or may not draw much commentary depending on how interested people are and how much time they have that day. (A day or so later there'll be other topics to distract them.) As long as the patch submission is clearly labeled WIP you shouldn't get complaints about it not being ready to commit. The other approach I'd suggest, if what you mainly want is design review, is to not post a patch at all. Post a design spec, or even just specific questions. It's less work for people to look at and so you're more likely to get immediate feedback. 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: Hot Standby query cancellation and Streaming Replication integration
> I think that what we are going to have to do before we can ship 9.0 > is rip all of that stuff out and replace it with the sort of closed-loop > synchronization Greg Smith is pushing. It will probably be several > months before everyone is forced to accept that, which is why 9.0 is > not going to ship this year. Oh, 9.0 will not be shipped in 2010? You guys share same opinion as Tom? -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- 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] Path separator
yeah, we have a couple of patches that aren't good... And I kind of lost track of it waiting for feedback on my last question. It's not a must-fix, but it'd be good to get better messages eventually. //Magnus 2010/2/26 Bruce Momjian : > > I assume we never came to any conclusion on this. > > --- > > Magnus Hagander wrote: >> Tom Lane wrote: >> > Magnus Hagander writes: >> >>> Answering myself here: the filesize for the "frontend only" part is >> >>> about 2k on this system. >> > >> >> Long meeting, time for coding.. :-) Here's a rough patch. Is this about >> >> what you had in mind? >> > >> > Hm, this seems to make the namespace pollution problem worse not better, >> > because of de-staticizing so many functions. I guess we could stick pg_ >> > prefixes on all of them. That's a bit ugly; anybody have a better idea? >> >> Not really. >> >> >> > It might be that it'd be better to push a couple more of the simple >> > path-munging functions (like join_path_components) over into the new >> > file, so as to have a more logical division of responsibilities. >> > In my mind the two key areas here are "path syntax knowledge" and >> > "extracting absolute paths from environmental information". The second >> > part seems to be the one that doesn't belong on the frontend side. >> >> What would be the gain there? To be able to re-static-ify for example >> skip_drive? Or just a nicer division? >> >> We should probably also consider moving get_home_path() over to the >> frontend one, and get rid of the copy that's in fe-connect.c. >> >> >> //Magnus >> >> >> -- >> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-hackers > > -- > Bruce Momjian http://momjian.us > EnterpriseDB http://enterprisedb.com > PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do > + If your life is a hard drive, Christ can be your backup. + > -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.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] Small change of the HS document
Fujii Masao wrote: > On Tue, Dec 22, 2009 at 11:25 AM, Fujii Masao wrote: > > I found there is no "primary" tag for the HS parameters > > in config.sgml. Attached patch adds that tag. > > I found another small problem in HS doc; though the type of > trace_recovery_messages is actually enum, it's described as > string in the doc. The attached patch does s/string/emum. Thanks, applied. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do + If your life is a hard drive, Christ can be your backup. + -- 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] visibility maps and heap_prune
Heikki Linnakangas wrote: > Bruce Momjian wrote: > > Pavan Deolasee wrote: > >> On Fri, Feb 26, 2010 at 8:19 AM, Bruce Momjian wrote: > >> > >>> Whatever happened to this? It was in the first 9.0 commitfest but was > >>> returned with feedback but never updated: > >>> > >>> > >> Though Alex did some useful tests and review, and in fact confirmed that > >> the > >> VACUUM time dropped from 16494 msec to 366 msec, I somehow kept waiting for > >> Heikki's decision on the general direction of the patch and lost interest > >> in > >> between. If we are still interested in this, I can work out a patch and > >> submit for next release if not this. > > > > OK, TODO added: > > > > Have single-page pruning update the visibility map > > * https://commitfest.postgresql.org/action/patch_view?id=75 > > > > Hopefully Heikki can comment on this. > > I think I was worried about the possible performance impact of having to > clear the bit in visibility map again. If you're frequently updating a > tuple so that HOT and page pruning is helping you, setting the bit in > visibility map seems counter-productive; it's going to be cleared soon > again by another UPDATE. That's just a hunch, though. Maybe the overhead > is negligible. Should I remove the TODO item? I updated the text to: Consider having single-page pruning update the visibility map and added a URL to Heikki's new comment. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do + If your life is a hard drive, Christ can be your backup. + -- 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
Greg Smith wrote: > Joshua D. Drake wrote: > > On Sat, 27 Feb 2010 00:43:48 +, Greg Stark wrote: > > > >> I want my ability to run large batch queries without any performance > >> or reliability impact on the primary server. > >> > > > > +1 > > > > I can use any number of other technologies for high availability. > > > > Remove "must be an instant-on failover at the same time" from the > requirements and you don't even need 9.0 to handle that, this has been a > straightforward to solve problem since 8.2. It's the combination of HA > and queries that make things hard to do. > > If you just want batch queries on another system without being concerned > about HA at the same time, the first option is to just fork the base > backup and WAL segment delivery to another server and run queries there. That is a lot of administrative overhead. It is hard to say it is equivalent to HS. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do + If your life is a hard drive, Christ can be your backup. + -- 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?
2010/2/27 Tom Lane : > 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 -- 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] NaN/Inf fix for ECPG
Le 26 févr. 2010 à 17:11, Tom Lane a écrit :
> =?iso-8859-1?Q?R=E9mi_Zara?= writes:
>> I've tried patch 1 and 2, but they do not work. The fact is that the code is
>> not used in the backend, because strtod("NaN", endptr) works.
>> (isnan(strtod("NaN", endptr)) is true).
>
> Hmm. So what do you get from
> SELECT 'nan'::numeric::float8;
> on that machine? That should exercise the backend's version of
> get_float8_nan().
>
regression=# select 'nan'::numeric::float8;
float8
--
Infinity
(1 row)
So it is indeed the same behavior. Maybe that should be added to the regression
tests.
So what's the best way to workaround the bug in NetBSD/mips ? (nan(""),
(0.0/0.0), strtod("nan", null) ?)
Regards,
Rémi Zara
--
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] Lock Wait Statistics (next commitfest)
Greg Smith wrote: Bruce Momjian wrote: What happened to this patch? Returned with feedback in October after receiving a lot of review, no updated version submitted since then: https://commitfest.postgresql.org/action/patch_view?id=98 Hmm - I would say a bit of review rather than a lot :-) The feeling I received from the various comments was a lukewarm level of interest at best, so I must confess that I let other things take precedence. Also I was after some clear feedback about whether a separate stats utility was necessary at all given we have Dtrace support - despite this not being available for Linux... and the only comment dealing to this concern is from Gokul just now! I'd also like to take the opportunity to express a little frustration about the commitfest business - really all I wanted was the patch *reviewed* as WIP - it seemed that in order to do that I needed to enter it into the various commitfests... then I was faced with comments to the effect that it was not ready for commit so should not have been entered into a commifest at all... sigh, a bit of an enthusiasm killer I'm afraid... regards Mark -- 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
Heikki Linnakangas wrote: > Dimitri Fontaine wrote: >> Bruce Momjian writes: >>> Doesn't the system already adjust the delay based on the length of slave >>> transactions, e.g. max_standby_delay. It seems there is no need for a >>> user switch --- just max_standby_delay really high. >> Well that GUC looks like it allows to set a compromise between HA and >> reporting, not to say "do not ever give the priority to the replay while >> I'm running my reports". At least that's how I understand it. > > max_standby_delay=-1 does that. The documentation needs to be updated to > reflect that, it currently says: > >> There is no wait-forever setting because of the potential for deadlock which >> that setting would introduce. This parameter can only be set in the >> postgresql.conf file or on the server command line. > > but that is false, -1 means wait forever. Simon removed that option at > one point, but it was later put back and apparently the documentation > was never updated. I've put back the mention of max_standby_delay=-1 option in the docs. -- 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] Hot Standby query cancellation and Streaming Replication integration
Dimitri Fontaine wrote: > Bruce Momjian writes: >> Doesn't the system already adjust the delay based on the length of slave >> transactions, e.g. max_standby_delay. It seems there is no need for a >> user switch --- just max_standby_delay really high. > > Well that GUC looks like it allows to set a compromise between HA and > reporting, not to say "do not ever give the priority to the replay while > I'm running my reports". At least that's how I understand it. max_standby_delay=-1 does that. The documentation needs to be updated to reflect that, it currently says: > There is no wait-forever setting because of the potential for deadlock which > that setting would introduce. This parameter can only be set in the > postgresql.conf file or on the server command line. but that is false, -1 means wait forever. Simon removed that option at one point, but it was later put back and apparently the documentation was never updated. -- 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] visibility maps and heap_prune
Bruce Momjian wrote: > Pavan Deolasee wrote: >> On Fri, Feb 26, 2010 at 8:19 AM, Bruce Momjian wrote: >> >>> Whatever happened to this? It was in the first 9.0 commitfest but was >>> returned with feedback but never updated: >>> >>> >> Though Alex did some useful tests and review, and in fact confirmed that the >> VACUUM time dropped from 16494 msec to 366 msec, I somehow kept waiting for >> Heikki's decision on the general direction of the patch and lost interest in >> between. If we are still interested in this, I can work out a patch and >> submit for next release if not this. > > OK, TODO added: > > Have single-page pruning update the visibility map > * https://commitfest.postgresql.org/action/patch_view?id=75 > > Hopefully Heikki can comment on this. I think I was worried about the possible performance impact of having to clear the bit in visibility map again. If you're frequently updating a tuple so that HOT and page pruning is helping you, setting the bit in visibility map seems counter-productive; it's going to be cleared soon again by another UPDATE. That's just a hunch, though. Maybe the overhead is negligible. -- 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
