[HACKERS] Round Robin Reviewers
Folks, I am very pleased to announce that I have 13 volunteers to be Round-Robin Reviewers in addition to myself. Thanks to all who have stepped up to the plate. However, we have a lot more than 13 patches for this CommitFest, so more reviewers are still needed. In particular, we could use more NEW volunteers who have not reviewed previously; also, without wishing to signal out anyone specifically, if you're hoping to have YOUR patches reviewed by others, then you should really consider volunteering to review some of theirs. I understand that this won't be possible for everyone, but it would be a good goal for us to aim for! Without further ado, here is the list of those who have volunteered so far: Jeff Davis Jaime Casanova Dimitri Fontaine Andres Freund Euler Taveira de Oliveira Stephen Frost Abhijit Menon-Sen Nikhil Sontakke Bernd Helmle Kevin Grittner Joshua Tolley Wolfgang Wilhelm Josh Williams Thanks, ...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] *_collapse_limit, geqo_threshold
On Jul 8, 2009, at 8:26 PM, Tom Lane wrote: Robert Haas writes: That was my first reaction too, but now I'm wondering whether we shouldn't just do #1. #2 is a planner hint, too, just not a very good one. If, as you suggest, it isn't actually useful, then why keep it at all? (On the other hand, if someone thinks they need it, it would be interesting to know the use case, and think about the best way to address it.) Well, I can cite one reasonably plausible use case: when you have an umpteen-way join you need to execute a lot, and you don't want to pay for an exhaustive search, but GEQO doesn't reliably find a good plan. What you can do is let the system do an exhaustive search once to find the best plan, then you rearrange the query to specify that join order via JOINs, and turn off join collapsing. Presto, good plan every time with very little planning time expended. Now, your answer will probably be that we should provide some better mechanism for re-using a previously identified plan structure. No doubt that would be ideal, but the amount of effort required to get there is nontrivial, and I'm not at all convinced it would be repaid in usefulness. Whereas what I describe above is something that costs us nearly nothing to provide. The usefulness might be marginal too, but on the basis of cost/benefit ratio it's a clear win. I don't think that would be my answer because plan caching sounds hard. It would be nice to have, though. :-) But it's clearly a planner hint, however you slice it. It occurs to me that one way to make GEQO less scary would be to take out the nondeterminism by resetting its random number generator for each query. You might get a good plan or an awful one, but at least it'd be the same one each time. DBAs like predictability. Hmm, that doesn't sound appealing to me, but I'm only a DBA at need. I was imagining a GUC that would make the reset optional, in case anyone really does want to have unstable plans. I don't have much doubt about what typical users will prefer though. OK. ...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: Synch Rep: direct transfer of WAL file from the primary to the standby
Hi, On Wed, Jul 8, 2009 at 10:59 PM, Kevin Grittner wrote: > Dimitri Fontaine wrote: > >> 4. sync: slave is no more lagging, it's applying the stream as it >> gets it, either as part of the master transaction or not >> depending on the GUC settings > > I think the interesting bit is when you're at this point and the > connection between the master and slave goes down for a couple days. > How do you handle that? In the current design of synch rep, you have only to restart the standby after repairing the network. The startup process of the standby would restart an archive recovery from the last restart point, and request the missing file from the primary if it's found. On the other hand, WAL streaming would start from the current XLOG position of the primary, which is performed by walsender and walreceiver. If the file required for the archive recovery has gone from the primary (pg_xlog and archive) during a couple of days, and now exists only in a separate archive server, the archive recovery by the standby would fail. In this case, you need to copy the missing files from the archive server to the standby before restarting the standby. Otherwise you need to make a new base backup of the primary, and start the setup of the standby from the beginning. 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] multi-threaded pgbench
On Wed, 8 Jul 2009, Tom Lane wrote: pg_restore doesn't need anything more than a success/failure result from its child processes, but I think pgbench will want more. The biggest chunk of returned state to consider is how each client transaction generates a line of latency information that goes into the log file. -- * Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD -- 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] *_collapse_limit, geqo_threshold
Noah Misch writes: > Describing in those terms illuminates much. While the concepts do suggest 2^N > worst-case planning cost, my artificial test case showed a rigid 4^N pattern; > what could explain that? Well, the point of the 2^N concept is just that adding one more relation multiplies the planning work by a constant factor. It's useful data that you find the factor to be about 4, but I wouldn't have expected the model to tell us 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] *_collapse_limit, geqo_threshold
Joshua Tolley writes: > This sounds like planner hints to me. The argument against hinting, AIUI, is > that although the plan you've guaranteed via hints may be a good one today, > when the data change a bit your carefully crafted plan happens to become a bad > one, but you're no longer around to change the hints accordingly. That's one argument against them. Another one is that time put into developing a planner hints mechanism is time that would be better spent on fixing the underlying planner problem. However, that second argument doesn't apply to something like join_collapse_limit, whose implementation is pretty nearly a one-liner (as are the various existing enable_whatever switches). Again, it's all about cost/benefit ratio. > Do we know that GEQO plans are, in reality, less stable than than usual > planner? Yes, we do. There aren't that many examples in the archives, but that likely is because join_collapse_limit and from_collapse_limit are by default set to try to prevent use of GEQO. The most recent clear-cut example I can find is http://archives.postgresql.org/pgsql-general/2008-10/msg01449.php wherein the guy has apparently written a "flat" FROM of 17 tables, and so neither collapse_limit will kick in. If we get rid of the collapse_limits as proposed in this thread, you'll start seeing those sorts of complaints all over the place, unless we make GEQO deterministic. 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] *_collapse_limit, geqo_threshold
On Wed, Jul 08, 2009 at 09:26:35PM -0400, Tom Lane wrote: > Robert Haas writes: > > That was my first reaction too, but now I'm wondering whether we > > shouldn't just do #1. #2 is a planner hint, too, just not a very good > > one. If, as you suggest, it isn't actually useful, then why keep it > > at all? (On the other hand, if someone thinks they need it, it would > > be interesting to know the use case, and think about the best way to > > address it.) > > Well, I can cite one reasonably plausible use case: when you have an > umpteen-way join you need to execute a lot, and you don't want to pay > for an exhaustive search, but GEQO doesn't reliably find a good plan. > What you can do is let the system do an exhaustive search once to find > the best plan, then you rearrange the query to specify that join order > via JOINs, and turn off join collapsing. Presto, good plan every time > with very little planning time expended. > > Now, your answer will probably be that we should provide some better > mechanism for re-using a previously identified plan structure. No > doubt that would be ideal, but the amount of effort required to get > there is nontrivial, and I'm not at all convinced it would be repaid > in usefulness. Whereas what I describe above is something that costs > us nearly nothing to provide. The usefulness might be marginal too, > but on the basis of cost/benefit ratio it's a clear win. This sounds like planner hints to me. The argument against hinting, AIUI, is that although the plan you've guaranteed via hints may be a good one today, when the data change a bit your carefully crafted plan happens to become a bad one, but you're no longer around to change the hints accordingly. Entire stored plans, or predetermined seeds for GEQO's random number generator all boil down to saying, "I want you to use this plan henceforth and forever." > >> It occurs to me that one way to make GEQO less scary would be to take > >> out the nondeterminism by resetting its random number generator for > >> each query. You might get a good plan or an awful one, but at least > >> it'd be the same one each time. DBAs like predictability. > > > Hmm, that doesn't sound appealing to me, but I'm only a DBA at need. > > I was imagining a GUC that would make the reset optional, in case anyone > really does want to have unstable plans. I don't have much doubt about > what typical users will prefer though. Do we know that GEQO plans are, in reality, less stable than than usual planner? Certainly on paper it appears they could be, but the mailing lists are full of emails about "this query's plan changed and performance suddenly tanked; how do I fix it?" so I'm unconvinced this is a problem unique to GEQO. Which in turn boils down to "we need real world data to look at". -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com signature.asc Description: Digital signature
Re: [HACKERS] multi-threaded pgbench
Andrew Dunstan wrote: > I think you should have it use pthreads if available, or Windows threads > there, or fork() elsewhere. Just a question - which platform does not support any threading? I think threading is very common in modern applications. If there are such OSes, they seem to be just abandoned and not maintained... Regards, --- ITAGAKI Takahiro 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] *_collapse_limit, geqo_threshold
Robert Haas writes: > That was my first reaction too, but now I'm wondering whether we > shouldn't just do #1. #2 is a planner hint, too, just not a very good > one. If, as you suggest, it isn't actually useful, then why keep it > at all? (On the other hand, if someone thinks they need it, it would > be interesting to know the use case, and think about the best way to > address it.) Well, I can cite one reasonably plausible use case: when you have an umpteen-way join you need to execute a lot, and you don't want to pay for an exhaustive search, but GEQO doesn't reliably find a good plan. What you can do is let the system do an exhaustive search once to find the best plan, then you rearrange the query to specify that join order via JOINs, and turn off join collapsing. Presto, good plan every time with very little planning time expended. Now, your answer will probably be that we should provide some better mechanism for re-using a previously identified plan structure. No doubt that would be ideal, but the amount of effort required to get there is nontrivial, and I'm not at all convinced it would be repaid in usefulness. Whereas what I describe above is something that costs us nearly nothing to provide. The usefulness might be marginal too, but on the basis of cost/benefit ratio it's a clear win. >> It occurs to me that one way to make GEQO less scary would be to take >> out the nondeterminism by resetting its random number generator for >> each query. You might get a good plan or an awful one, but at least >> it'd be the same one each time. DBAs like predictability. > Hmm, that doesn't sound appealing to me, but I'm only a DBA at need. I was imagining a GUC that would make the reset optional, in case anyone really does want to have unstable plans. I don't have much doubt about what typical users will prefer though. 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] *_collapse_limit, geqo_threshold
On Jul 8, 2009, at 3:57 PM, Tom Lane wrote: Robert Haas writes: On Tue, Jul 7, 2009 at 6:33 PM, Tom Lane wrote: It's pretty much all-or-nothing now: the GUC does not give you any sort of useful control over *which* joins are reorderable. Yes. So the way I see it, the options are: 1. We can remove join_collapse_limit completely and provide no substitute. In this case, the ability to explicitly specify the join order will be gone. 2. We can remove join_collapse_limit but provide a different, Boolean GUC instead, like enable_join_reordering. In this case, we're not actually reducing the number of GUCs, just the size of the foot-gun. 3. We can remove join_collapse_limit and provide an alternative way to explicitly specify the join order that is more flexible. This both reduces the number of GUCs and arguably provides some useful functionality that we don't have now. It sounds like your vote is for #2, which, as I say, seems like a feature with one arm tied behind its back, but hey, what do I know? Well, the reason I'm not voting for #3 is that it looks like a lot of work to implement something that would basically be a planner hint, which I'm generally against; furthermore, it's a hint that there's been no demand for. (We're not even certain that anyone is using the ability to *fully* specify the join order, much less wanting some undetermined compromise between manual and automatic control.) And anyway I didn't hear anyone volunteering to do it. So the realistic alternatives are #1, #2, or "do nothing"; and out of those I like #2. That was my first reaction too, but now I'm wondering whether we shouldn't just do #1. #2 is a planner hint, too, just not a very good one. If, as you suggest, it isn't actually useful, then why keep it at all? (On the other hand, if someone thinks they need it, it would be interesting to know the use case, and think about the best way to address it.) Accepting that as the consensus in the absence of contrary votes, we still need to decide what to do about from_collapse_threshold and geqo_threshold. I'm pretty sure that we shouldn't eliminate GEQO or geqo_threshold, because the basic algorithm is clearly exponential time and eventually you have to start worrying about that, but we could raise the value. What to do about from_collapse_threshold is less clear to me. I do not think there is a good argument for eliminating geqo_threshold. There might well be an argument for cranking up its default value; but that would take some hard data, which seems lacking at the moment. I'm on the fence about from_collapse_threshold. The argument for having it seems to be that there might be cases where not folding a subquery is preferable to folding it and then taking your chances with GEQO. But I'm not really convinced there are any. Me either. You could probably get the same effect in other ways if you actually needed it, like OFFSET 0 or wrapping the subquery in a SRF. I'm leaning more and more toward thinking we should just nuke it. It occurs to me that one way to make GEQO less scary would be to take out the nondeterminism by resetting its random number generator for each query. You might get a good plan or an awful one, but at least it'd be the same one each time. DBAs like predictability. Hmm, that doesn't sound appealing to me, but I'm only a DBA at need. ...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] *_collapse_limit, geqo_threshold
On Wed, Jul 08, 2009 at 05:46:02PM -0400, Tom Lane wrote: > "Kevin Grittner" writes: > > For a moment it seemed logical to suggest a session GUC for the seed, > > so if you got a bad plan you could keep rolling the dice until you got > > one you liked; but my right-brain kept sending shivers down my spine > > to suggest just how uncomfortable it was with that idea > > If memory serves, we actually had exactly that at some point. But I > think the reason it got taken out was that it interfered with the > behavior of the random() function for everything else. We'd have to > give GEQO its own private random number generator. > > regards, tom lane > A separate random number generator for GECO make a lot of sense. Cheers, Ken -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] modules missing from Application Stack Wizard?
Hello Postgres Hackers, We have begun testing Postgres 8.4 on windows, beginning with the installer. We have noticed that several additional modules which are usually installed through the Application Stack Wizard are missing from the list of available modules like PostGIS or the developer options. Are those available through some other option or an additional installation? Currently we are running on Postgres 8.3.0, and both the Application Stack Wizard ( for PostGIS) as well as the developer options ( include files, library files, tools and utilities) were available on the "Installation Options" dialog, than the installer received a face lift and any additional module was available through the Application Stack Wizard. For PostgreSQL 8.4 those options are missing, all I can see to be available are : Add-ons, tools and utilities EnterpriseDB MySQL Migration Wizard EnterpriseDB pgPhoneHome for Apple iPhone EnterpriseDB Tuning Wizard for PostgreSQL Database Drivers Npgsql pgJDBC PgOleDB psqlJDBC psqlODBC Web Applications Drupal mediaWiki phpBB phpWiki Web Development ApachePHP phpPgAdmin Ruby on Rails Is there some place else from which I need to install PostGIS or the developer options? Thank you, Sincerely, Kasia
Re: [HACKERS] *_collapse_limit, geqo_threshold
"Kevin Grittner" writes: > For a moment it seemed logical to suggest a session GUC for the seed, > so if you got a bad plan you could keep rolling the dice until you got > one you liked; but my right-brain kept sending shivers down my spine > to suggest just how uncomfortable it was with that idea If memory serves, we actually had exactly that at some point. But I think the reason it got taken out was that it interfered with the behavior of the random() function for everything else. We'd have to give GEQO its own private random number generator. 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] *_collapse_limit, geqo_threshold
Noah Misch writes: > With joins between statistically indistinguishable columns, I see planning > times > change by a factor of ~4 for each join added or removed (postgres 8.3). > Varying > join_collapse_limit in the neighborhood of the actual number of joins has a > similar effect. See attachment with annotated timings. The example uses a > single table joined to itself, but using distinct tables with identical > contents > yields the same figures. Hey, some hard data! Thanks for doing that. > The expontential factor seems smaller for real queries. I have a query of > sixteen joins that takes 71s to plan deterministically; it looks like this: > SELECT 1 FROM fact JOIN dim0 ... JOIN dim6 > JOIN t t0 ON fact.key = t.key AND t.x = MCV0 > LEFT JOIN t t1 ON fact.key = t.key AND t.x = MCV1 > JOIN t t2 ON fact.key = t.key AND t.x = MCV2 > LEFT JOIN t t3 ON fact.key = t.key AND t.x = NON-MCV0 > LEFT JOIN t t4 ON fact.key = t.key AND t.x = NON-MCV1 > LEFT JOIN t t5 ON fact.key = t.key AND t.x = NON-MCV2 > LEFT JOIN t t6 ON fact.key = t.key AND t.x = NON-MCV3 > LEFT JOIN t t7 ON fact.key = t.key AND t.x = NON-MCV4 I'm confused here --- I think you must have over-anonymized your query. Surely the ON conditions for the left joins should be referencing t3, t4, etc? > For the real query, removing one join drops plan time to 26s, and > removing two drops the time to 11s. I don't have a good theory for > the multiplier changing from 4 for the trivial demonstration to ~2.5 > for this real query. The rule of thumb that says that an n-way join requires 2^n work is only true if we consider every single combination of possible joins, which normally we don't. The planner prefers join paths that follow join clauses, and will only consider clauseless joins when it has no other choice. I believe that real queries tend to be pretty non-flat in this space and so the number of join paths to consider is a lot less than 2^n. Your synthesized query, on the other hand, allows any relation to be joined to any other --- it might not look that way, but after creating derived equalities there will be a potential join clause linking every relation to every other one. So I think you were testing the worst case, and I'm not surprised that more-typical queries would show a slower growth curve. This is why I don't trust benchmarking the planner on artificial queries. Still, I appreciate your work, because it gives us at least some hard data to go on. 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] *_collapse_limit, geqo_threshold
On Wed, Jul 08, 2009 at 04:13:11PM -0500, Kevin Grittner wrote: > Tom Lane wrote: > > > It occurs to me that one way to make GEQO less scary would be to > > take out the nondeterminism by resetting its random number generator > > for each query. You might get a good plan or an awful one, but at > > least it'd be the same one each time. DBAs like predictability. > > +1 The biggest reason that I've tended to avoid geqo is that I would > never know when it might do something really stupid with a query one > time out of some large number, leading to mysterious complaints which > could eat a lot of time. > > For a moment it seemed logical to suggest a session GUC for the seed, > so if you got a bad plan you could keep rolling the dice until you got > one you liked; but my right-brain kept sending shivers down my spine > to suggest just how uncomfortable it was with that idea > > -Kevin > +1 I like the idea of a session GUC for the random number seed. If we can come up with a way to prune the search space more aggressively, GECO (or GECO2) will be much less prone to generating a bad plan. I also think that a session variable would make it easier to test GECO* by removing the nondeteminism. Ken -- 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] *_collapse_limit, geqo_threshold
Tom Lane wrote: > It occurs to me that one way to make GEQO less scary would be to > take out the nondeterminism by resetting its random number generator > for each query. You might get a good plan or an awful one, but at > least it'd be the same one each time. DBAs like predictability. +1 The biggest reason that I've tended to avoid geqo is that I would never know when it might do something really stupid with a query one time out of some large number, leading to mysterious complaints which could eat a lot of time. For a moment it seemed logical to suggest a session GUC for the seed, so if you got a bad plan you could keep rolling the dice until you got one you liked; but my right-brain kept sending shivers down my spine to suggest just how uncomfortable it was with that idea -Kevin -- 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] *_collapse_limit, geqo_threshold
Robert Haas writes: > On Tue, Jul 7, 2009 at 6:33 PM, Tom Lane wrote: >> It's pretty much all-or-nothing now: the GUC does not give you any sort >> of useful control over *which* joins are reorderable. > Yes. So the way I see it, the options are: > 1. We can remove join_collapse_limit completely and provide no > substitute. In this case, the ability to explicitly specify the join > order will be gone. > 2. We can remove join_collapse_limit but provide a different, Boolean > GUC instead, like enable_join_reordering. In this case, we're not > actually reducing the number of GUCs, just the size of the foot-gun. > 3. We can remove join_collapse_limit and provide an alternative way to > explicitly specify the join order that is more flexible. This both > reduces the number of GUCs and arguably provides some useful > functionality that we don't have now. > It sounds like your vote is for #2, which, as I say, seems like a > feature with one arm tied behind its back, but hey, what do I know? Well, the reason I'm not voting for #3 is that it looks like a lot of work to implement something that would basically be a planner hint, which I'm generally against; furthermore, it's a hint that there's been no demand for. (We're not even certain that anyone is using the ability to *fully* specify the join order, much less wanting some undetermined compromise between manual and automatic control.) And anyway I didn't hear anyone volunteering to do it. So the realistic alternatives are #1, #2, or "do nothing"; and out of those I like #2. > Accepting that as the consensus in the absence of contrary votes, we > still need to decide what to do about from_collapse_threshold and > geqo_threshold. I'm pretty sure that we shouldn't eliminate GEQO or > geqo_threshold, because the basic algorithm is clearly exponential > time and eventually you have to start worrying about that, but we > could raise the value. What to do about from_collapse_threshold is > less clear to me. I do not think there is a good argument for eliminating geqo_threshold. There might well be an argument for cranking up its default value; but that would take some hard data, which seems lacking at the moment. I'm on the fence about from_collapse_threshold. The argument for having it seems to be that there might be cases where not folding a subquery is preferable to folding it and then taking your chances with GEQO. But I'm not really convinced there are any. It occurs to me that one way to make GEQO less scary would be to take out the nondeterminism by resetting its random number generator for each query. You might get a good plan or an awful one, but at least it'd be the same one each time. DBAs like predictability. 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
Fwd: [HACKERS] tsvector extraction patch
Sorry, forgot to reply-all.
-- Forwarded message --
From: Mike Rylander
Date: Wed, Jul 8, 2009 at 4:17 PM
Subject: Re: [HACKERS] tsvector extraction patch
To: Alvaro Herrera
On Wed, Jul 8, 2009 at 3:38 PM, Alvaro
Herrera wrote:
> Mike Rylander escribió:
>> On Fri, Jul 3, 2009 at 3:49 AM, Hans-Juergen Schoenig --
>> PostgreSQL wrote:
>
>> > test=# SELECT * FROM tsvcontent(to_tsvector('english', 'i am pretty sure
>> > this is a good patch'));
>> > lex | rank
>> > +--
>> > good | 8
>> > patch | 9
>> > pretti | 3
>> > sure | 4
>> > (4 rows)
>> >
>>
>> This looks very useful! I wonder if providing a "weight" column would
>> be relatively simple? I think this would present problems with the
>> cast-to-text[] idea that Peter suggests, though.
>
> Where would the weight come from?
>
From a tsvector column that has weights set via setweight().
--
Mike Rylander
| VP, Research and Design
| Equinox Software, Inc. / The Evergreen Experts
| phone: 1-877-OPEN-ILS (673-6457)
| email: mi...@esilibrary.com
| web: http://www.esilibrary.com
--
Mike Rylander
| VP, Research and Design
| Equinox Software, Inc. / The Evergreen Experts
| phone: 1-877-OPEN-ILS (673-6457)
| email: mi...@esilibrary.com
| web: http://www.esilibrary.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] tsvector extraction patch
Mike Rylander escribió:
> On Fri, Jul 3, 2009 at 3:49 AM, Hans-Juergen Schoenig --
> PostgreSQL wrote:
> > test=# SELECT * FROM tsvcontent(to_tsvector('english', 'i am pretty sure
> > this is a good patch'));
> > lex | rank
> > +--
> > good | 8
> > patch | 9
> > pretti | 3
> > sure | 4
> > (4 rows)
> >
>
> This looks very useful! I wonder if providing a "weight" column would
> be relatively simple? I think this would present problems with the
> cast-to-text[] idea that Peter suggests, though.
Where would the weight come from?
--
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
Re: [HACKERS] Does EXEC_BACKEND mode still need to propagate setlocale settings?
Heikki Linnakangas writes: > Tom Lane wrote: >> I am thinking though that the correct fix is to delete them and get >> rid of the overhead of transmitting the postmaster's setlocale settings >> to child processes this way. > Agreed. In EXEC_BACKEND case, main() sets them to the same system > defaults before restore_backend_variables() is called. Right. It would only matter if the postmaster changed them again later, but so far as I can see it does not. The most likely future change that would make it do so would be establishing GUC variables to control them, but we still wouldn't need a special mechanism to transmit the settings. Will go remove those calls. 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] Does EXEC_BACKEND mode still need to propagate setlocale settings?
Tom Lane wrote: > The recent bug report about CheckMyDatabase() failing to use > pg_perm_setlocale() led me to look for other uses of setlocale() that > might be wrong. I found two fishy calls in restore_backend_variables(). I just did the exact same thing but you beat me.. > I am thinking though that the correct fix is to delete them and get > rid of the overhead of transmitting the postmaster's setlocale settings > to child processes this way. A regular backend is going to do > CheckMyDatabase momentarily, and in any case it seems to me that the > postmaster hasn't bothered to change these values off the system defaults > and so there is no need to do anything special to inherit its values. Agreed. In EXEC_BACKEND case, main() sets them to the same system defaults before restore_backend_variables() is called. -- 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
[HACKERS] Does EXEC_BACKEND mode still need to propagate setlocale settings?
The recent bug report about CheckMyDatabase() failing to use pg_perm_setlocale() led me to look for other uses of setlocale() that might be wrong. I found two fishy calls in restore_backend_variables(). I am thinking though that the correct fix is to delete them and get rid of the overhead of transmitting the postmaster's setlocale settings to child processes this way. A regular backend is going to do CheckMyDatabase momentarily, and in any case it seems to me that the postmaster hasn't bothered to change these values off the system defaults and so there is no need to do anything special to inherit its values. Comments? 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] [pgsql-www] commitfest.postgresql.org
Brendan Jurd escribió: > Short answer: I could bring across the old commitfests but it would > take a couple hours at best per commitfest and result in little bits > of data loss here and there. I think we might be better off just > leaving the closed commitfests up on the wiki, and putting a notice on > the app saying "commitfests prior to July 2009 can be found at > wiki.postgresql.org". Agreed; if it requires manual work let's leave them in the wiki. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- 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] 8.4.0 vs. locales vs. pl/perl?
Andrew Gierth wrote: > environment: debian packaged 8.4.0 > > scenario: restoring a dump results in incorrect indexes for some > specific tables (indexscan order fails to match < comparison or > order resulting from explicit sorts). The dump contains plperl > and plperlu language creation and function definitions; excising > these from the dump removes the problem. > > The db locale is en_US.UTF-8 (which appears to be case-insensitive on > this platform: 'C' > 'd' > 'X'), but the incorrect index ordering that > results when the full restore is done is consistent with C locale > (i.e. 'C' > 'X' > 'd'). > > Looking at the code, the obvious thing that glares out is that the > locale setup in CheckMyDatabase is calling setlocale rather than > pg_perm_setlocale... am I missing something, or is this an obvious > bug? Looks like an obvious bug. Looking at the archives, it was present in the collation / per-database locale patches from the beginning, and I missed it during review. I'll go fix it, thanks for the analysis! -- 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] 8.4.0 vs. locales vs. pl/perl?
Andrew Gierth writes: > Looking at the code, the obvious thing that glares out is that the > locale setup in CheckMyDatabase is calling setlocale rather than > pg_perm_setlocale... am I missing something, or is this an obvious > bug? Sigh, it's an obvious bug. Whoever copied xlog.c's "pg_perm_setlocale" calls as "setlocale" needs to get out the sackcloth and ashes. 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] New types for transparent encryption
a...@esilo.com (Andrew Chernow) writes:
> Would the IV be regenerated every time the plaintext is updated, to
> avoid using it twice? For instace: update t set text = 'abc' where id
> = 1 . ISTM that the IV for OLD.text should be thrown away.
>
> Where would the key come from? Where would it be stored? What cipher is
> used?
LDAP authentication systems tend to use SSHA these days...
http://www.openldap.org/faq/data/cache/347.html
With SSHA, the key used for hashing passwords is picked randomly;
often by grabbing a few bytes from /dev/random. It's not important
that it be cryptographically secure, as it is presented directly as
part of the stored password.
In python, SSH hashes thus:
You need two inputs:
1. "password", which is the value that is to be hidden
2. "salt", a seed value.
The point isn't for "salt" to need to be super-secure, just for it to
not be frequently repeated. "Fairly random" seems to be generally
good enough.
import sha from base64
import b64encode
ctx = sha.new( password )
ctx.update( salt )
hash = "{SSHA}" + b64encode( ctx.digest() + salt )
Sort-of-aside:
FYI, I tried implementing SSHA in pl/pgsql, with mixed results.
It interoperated fine with other SSHA implementations as long as the
salt values were plain text.
The SSHA implementation in OpenLDAP (slappasswd) uses 4 byte binary
values (I think it grabs them from /dev/random or /dev/urandom);
unfortunately that wouldn't "play OK" with my pl/pgsql implementation.
I think having that work would be pretty keen, could share code if
anyone is interested...
--
output = reverse("ofni.secnanifxunil" "@" "enworbbc")
http://linuxdatabases.info/info/unix.html
Rules of the Evil Overlord #145. "My dungeon cell decor will not
feature exposed pipes. While they add to the gloomy atmosphere, they
are good conductors of vibrations and a lot of prisoners know Morse
code."
Re: [HACKERS] [pgsql-www] commitfest.postgresql.org
robertmh...@gmail.com (Robert Haas) writes: > I suspect both are true, but in the unlikely event that we decide on > some massive change to the system, we can either run the DBs in > parallel as Tom suggests, or dump out the older data in Wiki markup > and post it on there. But I can't imagine what we'd want to do that > would even make us consider such drastic steps. Your example would > not be a difficult migration, for instance. We had an ancient version of Bugzilla in use for quite a while; it was *SO* vastly different from modern versions that it wasn't remotely plausible to port the data from the old instance to a new one. I went and ran "wget" to pull all the contents of the old instance, turning that into a series of static web pages. No longer updatable, but certainly browsable. Once a CommitFest is complete, I could easily see making a summary of it, as a series of static web pages. No need for a database anymore altogether ;-). -- select 'cbbrowne' || '@' || 'linuxfinances.info'; http://linuxdatabases.info/info/spreadsheets.html I'd give my right arm to be ambidextrous! -- 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] New types for transparent encryption
gsst...@mit.edu (Greg Stark) writes: > However I have a different concern which hasn't been raised yet. > Encrypting lots of small chunks of data with the same key is a very > dangerous thing to do and it's very tricky to get right. Yeah, that's exactly the sort of thing that would be Most Useful for someone trying to do differential cryptanalysis. http://en.wikipedia.org/wiki/Differential_cryptanalysis It would provide an *exact* vector for differential attack if the attacker has the ability to add in a series of bits of data of their choosing before capturing the thus-encrypted dump. If you add some more-or-less-randomish salt, ala SSHA, that could be of some tiny help, maybe, arguably, but I doubt that's usable :-(. http://www.openldap.org/faq/data/cache/347.html -- let name="cbbrowne" and tld="acm.org" in String.concat "@" [name;tld];; http://linuxfinances.info/info/sap.html Why do scientists call it research when looking for something new? -- 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] multi-threaded pgbench
Tom Lane wrote: Andrew Dunstan writes: I think you should have it use pthreads if available, or Windows threads there, or fork() elsewhere. Hmm, but how will you communicate stats back from the sub-processes? pg_restore doesn't need anything more than a success/failure result from its child processes, but I think pgbench will want more. My first reaction is to say "use a pipe." cheers andtrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] 8.4.0 vs. locales vs. pl/perl?
Having been helping someone out on IRC with what looked initially like an index corruption problem, it turns out to be (almost certainly) a locale initialization error. environment: debian packaged 8.4.0 scenario: restoring a dump results in incorrect indexes for some specific tables (indexscan order fails to match < comparison or order resulting from explicit sorts). The dump contains plperl and plperlu language creation and function definitions; excising these from the dump removes the problem. The db locale is en_US.UTF-8 (which appears to be case-insensitive on this platform: 'C' > 'd' > 'X'), but the incorrect index ordering that results when the full restore is done is consistent with C locale (i.e. 'C' > 'X' > 'd'). Looking at the code, the obvious thing that glares out is that the locale setup in CheckMyDatabase is calling setlocale rather than pg_perm_setlocale... am I missing something, or is this an obvious bug? -- Andrew (irc:RhodiumToad) -- 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] multi-threaded pgbench
Andrew Dunstan writes: > I think you should have it use pthreads if available, or Windows threads > there, or fork() elsewhere. Hmm, but how will you communicate stats back from the sub-processes? pg_restore doesn't need anything more than a success/failure result from its child processes, but I think pgbench will want more. 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] *_collapse_limit, geqo_threshold
Hi, When I was first familiarizing myself with PostgreSQL, I took a walk through its documentation on GECO and similar processes in the literature. One big advantage of GECO is that you can trade off planning time for plan optimization. I do agree that it should be updated, but there were definite cases in the literature where the planning time for exhaustive searches could take orders of magnitude more time to execute than the differences in the execution times of the differing plans. My two cents, Ken On Wed, Jul 08, 2009 at 09:43:12AM -0400, Noah Misch wrote: > On Tue, Jul 07, 2009 at 09:31:14AM -0500, Kevin Grittner wrote: > > I don't remember any clear resolution to the wild variations in plan > > time mentioned here: > > > > http://archives.postgresql.org/pgsql-hackers/2009-06/msg00743.php > > > > I think it would be prudent to try to figure out why small changes in > > the query caused the large changes in the plan times Andres was > > seeing. Has anyone else ever seen such behavior? Can we get > > examples? (It should be enough to get the statistics and the schema, > > since this is about planning time, not run time.) > > With joins between statistically indistinguishable columns, I see planning > times > change by a factor of ~4 for each join added or removed (postgres 8.3). > Varying > join_collapse_limit in the neighborhood of the actual number of joins has a > similar effect. See attachment with annotated timings. The example uses a > single table joined to itself, but using distinct tables with identical > contents > yields the same figures. > > The expontential factor seems smaller for real queries. I have a query of > sixteen joins that takes 71s to plan deterministically; it looks like this: > > SELECT 1 FROM fact JOIN dim0 ... JOIN dim6 > JOIN t t0 ON fact.key = t.key AND t.x = MCV0 > LEFT JOIN t t1 ON fact.key = t.key AND t.x = MCV1 > JOIN t t2 ON fact.key = t.key AND t.x = MCV2 > LEFT JOIN t t3 ON fact.key = t.key AND t.x = NON-MCV0 > LEFT JOIN t t4 ON fact.key = t.key AND t.x = NON-MCV1 > LEFT JOIN t t5 ON fact.key = t.key AND t.x = NON-MCV2 > LEFT JOIN t t6 ON fact.key = t.key AND t.x = NON-MCV3 > LEFT JOIN t t7 ON fact.key = t.key AND t.x = NON-MCV4 > > For the real query, removing one join drops plan time to 26s, and removing two > drops the time to 11s. I don't have a good theory for the multiplier changing > from 4 for the trivial demonstration to ~2.5 for this real query. Re-enabling > geqo drops plan time to .5s. These tests used default_statistics_target = > 1000, > but dropping that to 100 does not change anything dramatically. > > > I guess the question is whether there is anyone who has had a contrary > > experience. (There must have been some benchmarks to justify adding > > geqo at some point?) > > I have queries with a few more joins (19-21), and I cancelled attempts to plan > them deterministically after 600+ seconds and 10+ GiB of memory usage. Even > with geqo_effort = 10, they plan within 5-15s with good results. > > All that being said, I've never encountered a situation where a value other > than > 1 or for *_collapse_limit appeared optimal. > > nm > SET geqo = off; > SET join_collapse_limit = 100; > CREATE TEMP TABLE t AS SELECT * FROM generate_series(1, 1000) f(n); ANALYZE t; > > --- Vary join count > -- 242.4s > EXPLAIN SELECT 1 FROM t t00 NATURAL JOIN t t01 NATURAL JOIN t t02 NATURAL > JOIN t > t03 NATURAL JOIN t t04 NATURAL JOIN t t05 NATURAL JOIN t t06 NATURAL JOIN t > t07 > NATURAL JOIN t t08 NATURAL JOIN t t09 NATURAL JOIN t t10 NATURAL JOIN t t11 > NATURAL JOIN t t12; > -- 31.2s > EXPLAIN SELECT 1 FROM t t00 NATURAL JOIN t t01 NATURAL JOIN t t02 NATURAL > JOIN t > t03 NATURAL JOIN t t04 NATURAL JOIN t t05 NATURAL JOIN t t06 NATURAL JOIN t > t07 > NATURAL JOIN t t08 NATURAL JOIN t t09 NATURAL JOIN t t10 NATURAL JOIN t t11; > -- 8.1s > EXPLAIN SELECT 1 FROM t t00 NATURAL JOIN t t01 NATURAL JOIN t t02 NATURAL > JOIN t > t03 NATURAL JOIN t t04 NATURAL JOIN t t05 NATURAL JOIN t t06 NATURAL JOIN t > t07 > NATURAL JOIN t t08 NATURAL JOIN t t09 NATURAL JOIN t t10; > -- 2.0s > EXPLAIN SELECT 1 FROM t t00 NATURAL JOIN t t01 NATURAL JOIN t t02 NATURAL > JOIN t > t03 NATURAL JOIN t t04 NATURAL JOIN t t05 NATURAL JOIN t t06 NATURAL JOIN t > t07 > NATURAL JOIN t t08 NATURAL JOIN t t09; > -- 0.5s > EXPLAIN SELECT 1 FROM t t00 NATURAL JOIN t t01 NATURAL JOIN t t02 NATURAL > JOIN t > t03 NATURAL JOIN t t04 NATURAL JOIN t t05 NATURAL JOIN t t06 NATURAL JOIN t > t07 > NATURAL JOIN t t08; > > --- Vary join_collapse_limit > -- 8.1s > SET join_collapse_limit = 100; > EXPLAIN SELECT 1 FROM t t00 NATURAL JOIN t t01 NATURAL JOIN t t02 NATURAL > JOIN t > t03 NATURAL JOIN t t04 NATURAL JOIN t t05 NATURAL JOIN t t06 NATURAL JOIN t > t07 > NATURAL JOIN t t08 NATURAL JOIN t t09 NATURAL JOIN t t10; > -- 8.0s > SET join_collapse_limit = 11; > EXPLAIN SELECT 1 FROM t t00 NATURAL JOIN t t01 NATURAL JOIN t t02 NATURAL > JOIN t > t03 NATURAL JOI
Re: [HACKERS] multi-threaded pgbench
On Wed, 8 Jul 2009, Itagaki Takahiro wrote: Multi-threading would be a solution. The attached patch adds -j (number of jobs) option to pgbench. Should probably name this -w "numbers of workers" to stay consistent with terminology used on the server side. Is it acceptable to use pthread in contrib module? If ok, I will add the patch to the next commitfest. pgbench is basically broken right now, as demonstrated by the lack of scaling show in your results and similar ones I've collected. This looks like it fixes the primary problem there. While it would be nice if a multi-process based solution were written instead, unless someone is willing to step up and volunteer to write one I'd much rather see your patch go in than doing nothing at all. It shouldn't even impact old results if you don't toggle the option on. I have 3 new server systems I was going to run pgbench on anyway in the next month as part of my standard performance testing on new hardware. I'll be happy to mix in results using the multi-threaded pgbench to check the patch's performance, along with the rest of the initial review here. -- * Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD -- 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] multi-threaded pgbench
Tom Lane wrote: Alvaro Herrera writes: Itagaki Takahiro wrote: Is it acceptable to use pthread in contrib module? We don't have a precedent it seems. I think the requirement would be that it should compile if pthread support is not present. Right. Breaking it for non-pthread environments is not acceptable. The real question here is whether it will be a problem if pgbench delivers significantly different results when built with or without threading support. I can see arguents either way on that ... well pgbench as it is now is now is more ore less unusable on modern hardware for SELECT type queries(way too slow to scale to what the backend can do thses days and the number of cores in a recent box). It is only somewhat usable on the default update heavy test as well because even there it is hitting scalability limits (ie I can easily improve on its numbers with a perl script that forks and issues the same queries). I would even go as far as issuing a WARNING if pgbench is invoked and not compiled with threads if we accept this patch... Stefan -- 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] multi-threaded pgbench
Heikki Linnakangas wrote: Alvaro Herrera wrote: Itagaki Takahiro wrote: Is it acceptable to use pthread in contrib module? We don't have a precedent it seems. I think the requirement would be that it should compile if pthread support is not present. My thoughts as well. But I wonder, would it be harder or easier to use fork() instead? I have just been down this road to some extent with parallel pg_restore, which uses threads on Windows. That might be useful as a bit of a template. Extending it to use pthreads would probably be fairly trivial. The thread/fork specific stuff ended up being fairly isolated for pg_restore. see src/bin/pg_dump/pg_backup_archiver.c:spawn_restore() I think you should have it use pthreads if available, or Windows threads there, or fork() elsewhere. 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] multi-threaded pgbench
Alvaro Herrera writes: > Itagaki Takahiro wrote: >> Is it acceptable to use pthread in contrib module? > We don't have a precedent it seems. I think the requirement would be > that it should compile if pthread support is not present. Right. Breaking it for non-pthread environments is not acceptable. The real question here is whether it will be a problem if pgbench delivers significantly different results when built with or without threading support. I can see arguents either way on 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] multi-threaded pgbench
Alvaro Herrera wrote: > Itagaki Takahiro wrote: > >> Is it acceptable to use pthread in contrib module? > > We don't have a precedent it seems. I think the requirement would be > that it should compile if pthread support is not present. My thoughts as well. But I wonder, would it be harder or easier to use fork() instead? -- 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] Re: Synch Rep: direct transfer of WAL file from the primary to the standby
Fujii Masao wrote: > On Wed, Jul 8, 2009 at 4:00 AM, Heikki > Linnakangas wrote: >>> I would envision the slaves >>> connecting to the master's replication port and asking "feed me WAL >>> beginning at LSN position thus-and-so", with no notion of WAL file >>> boundaries exposed anyplace. >> Yep, that's the way I envisioned it to work in my protocol suggestion >> that Fujii adopted >> (http://archives.postgresql.org/message-id/4951108a.5040...@enterprisedb.com). >> The and values are XLogRecPtrs, not WAL filenames. > > If indicates the middle of the XLOG file, the file written to the > standby is partial. Is this OK? After two server failed, the XLOG file > including might still be required for crash recovery of the > standby server. But, since it's partial, the crash recovery would fail. > I think that any XLOG file should be written to the standby as it can > be replayed by a normal recovery. The standby can store the streamed WAL to files in pg_xlog of the standby, to facilitate crash recovery, but it doesn't need to be exposed in the protocol. -- 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] multi-threaded pgbench
Itagaki Takahiro wrote: > Is it acceptable to use pthread in contrib module? We don't have a precedent it seems. I think the requirement would be that it should compile if pthread support is not present. > If ok, I will add the patch to the next commitfest. Add it anyway -- discussion should happen during commitfest if it doesn't spark right away. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- 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: Synch Rep: direct transfer of WAL file from theprimary to the standby
Heikki Linnakangas wrote: > But more importantly, it can happen by accident. Someone trips on > the power plug of the slave on Friday, and it goes unnoticed until > Monday when DBA comes to work. We've had people unplug things by accident exactly that way. :-/ We've also had replication across part of our WAN go down for the better part of a day because a beaver chewed through a fiber optic cable where it ran through a marsh. Our (application framework based) replication just picks up where it left off, without any intervention, when connectivity is restored. I think it would be a mistake to design something less robust than that. By the way, we don't use any state transitions for this, other than keeping track of when we seem to have a working connection. The client side knows what it last got, and when its reconnection attempts eventually succeed it makes a request of the server side to provide a stream of transactions from that point on. The response to that request continues indefinitely, as long as the connection is up, which can be months at a time. -Kevin "Everything should be made as simple as possible, but no simpler." - Albert Einstein -- 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] *_collapse_limit, geqo_threshold
On Tue, Jul 07, 2009 at 09:31:14AM -0500, Kevin Grittner wrote: > I don't remember any clear resolution to the wild variations in plan > time mentioned here: > > http://archives.postgresql.org/pgsql-hackers/2009-06/msg00743.php > > I think it would be prudent to try to figure out why small changes in > the query caused the large changes in the plan times Andres was > seeing. Has anyone else ever seen such behavior? Can we get > examples? (It should be enough to get the statistics and the schema, > since this is about planning time, not run time.) With joins between statistically indistinguishable columns, I see planning times change by a factor of ~4 for each join added or removed (postgres 8.3). Varying join_collapse_limit in the neighborhood of the actual number of joins has a similar effect. See attachment with annotated timings. The example uses a single table joined to itself, but using distinct tables with identical contents yields the same figures. The expontential factor seems smaller for real queries. I have a query of sixteen joins that takes 71s to plan deterministically; it looks like this: SELECT 1 FROM fact JOIN dim0 ... JOIN dim6 JOIN t t0 ON fact.key = t.key AND t.x = MCV0 LEFT JOIN t t1 ON fact.key = t.key AND t.x = MCV1 JOIN t t2 ON fact.key = t.key AND t.x = MCV2 LEFT JOIN t t3 ON fact.key = t.key AND t.x = NON-MCV0 LEFT JOIN t t4 ON fact.key = t.key AND t.x = NON-MCV1 LEFT JOIN t t5 ON fact.key = t.key AND t.x = NON-MCV2 LEFT JOIN t t6 ON fact.key = t.key AND t.x = NON-MCV3 LEFT JOIN t t7 ON fact.key = t.key AND t.x = NON-MCV4 For the real query, removing one join drops plan time to 26s, and removing two drops the time to 11s. I don't have a good theory for the multiplier changing from 4 for the trivial demonstration to ~2.5 for this real query. Re-enabling geqo drops plan time to .5s. These tests used default_statistics_target = 1000, but dropping that to 100 does not change anything dramatically. > I guess the question is whether there is anyone who has had a contrary > experience. (There must have been some benchmarks to justify adding > geqo at some point?) I have queries with a few more joins (19-21), and I cancelled attempts to plan them deterministically after 600+ seconds and 10+ GiB of memory usage. Even with geqo_effort = 10, they plan within 5-15s with good results. All that being said, I've never encountered a situation where a value other than 1 or for *_collapse_limit appeared optimal. nm SET geqo = off; SET join_collapse_limit = 100; CREATE TEMP TABLE t AS SELECT * FROM generate_series(1, 1000) f(n); ANALYZE t; --- Vary join count -- 242.4s EXPLAIN SELECT 1 FROM t t00 NATURAL JOIN t t01 NATURAL JOIN t t02 NATURAL JOIN t t03 NATURAL JOIN t t04 NATURAL JOIN t t05 NATURAL JOIN t t06 NATURAL JOIN t t07 NATURAL JOIN t t08 NATURAL JOIN t t09 NATURAL JOIN t t10 NATURAL JOIN t t11 NATURAL JOIN t t12; -- 31.2s EXPLAIN SELECT 1 FROM t t00 NATURAL JOIN t t01 NATURAL JOIN t t02 NATURAL JOIN t t03 NATURAL JOIN t t04 NATURAL JOIN t t05 NATURAL JOIN t t06 NATURAL JOIN t t07 NATURAL JOIN t t08 NATURAL JOIN t t09 NATURAL JOIN t t10 NATURAL JOIN t t11; -- 8.1s EXPLAIN SELECT 1 FROM t t00 NATURAL JOIN t t01 NATURAL JOIN t t02 NATURAL JOIN t t03 NATURAL JOIN t t04 NATURAL JOIN t t05 NATURAL JOIN t t06 NATURAL JOIN t t07 NATURAL JOIN t t08 NATURAL JOIN t t09 NATURAL JOIN t t10; -- 2.0s EXPLAIN SELECT 1 FROM t t00 NATURAL JOIN t t01 NATURAL JOIN t t02 NATURAL JOIN t t03 NATURAL JOIN t t04 NATURAL JOIN t t05 NATURAL JOIN t t06 NATURAL JOIN t t07 NATURAL JOIN t t08 NATURAL JOIN t t09; -- 0.5s EXPLAIN SELECT 1 FROM t t00 NATURAL JOIN t t01 NATURAL JOIN t t02 NATURAL JOIN t t03 NATURAL JOIN t t04 NATURAL JOIN t t05 NATURAL JOIN t t06 NATURAL JOIN t t07 NATURAL JOIN t t08; --- Vary join_collapse_limit -- 8.1s SET join_collapse_limit = 100; EXPLAIN SELECT 1 FROM t t00 NATURAL JOIN t t01 NATURAL JOIN t t02 NATURAL JOIN t t03 NATURAL JOIN t t04 NATURAL JOIN t t05 NATURAL JOIN t t06 NATURAL JOIN t t07 NATURAL JOIN t t08 NATURAL JOIN t t09 NATURAL JOIN t t10; -- 8.0s SET join_collapse_limit = 11; EXPLAIN SELECT 1 FROM t t00 NATURAL JOIN t t01 NATURAL JOIN t t02 NATURAL JOIN t t03 NATURAL JOIN t t04 NATURAL JOIN t t05 NATURAL JOIN t t06 NATURAL JOIN t t07 NATURAL JOIN t t08 NATURAL JOIN t t09 NATURAL JOIN t t10; -- 2.2s SET join_collapse_limit = 10; EXPLAIN SELECT 1 FROM t t00 NATURAL JOIN t t01 NATURAL JOIN t t02 NATURAL JOIN t t03 NATURAL JOIN t t04 NATURAL JOIN t t05 NATURAL JOIN t t06 NATURAL JOIN t t07 NATURAL JOIN t t08 NATURAL JOIN t t09 NATURAL JOIN t t10; -- 0.5s SET join_collapse_limit = 9; EXPLAIN SELECT 1 FROM t t00 NATURAL JOIN t t01 NATURAL JOIN t t02 NATURAL JOIN t t03 NATURAL JOIN t t04 NATURAL JOIN t t05 NATURAL JOIN t t06 NATURAL JOIN t t07 NATURAL JOIN t t08 NATURAL JOIN t t09 NATURAL JOIN t t10; -- 0.1s SET join_collapse_limit = 8; EXPLAIN SELECT 1 FROM t t00 NATURAL JOIN t t01 NATURAL JOIN t t02 NATURAL JOIN t t03 NATURAL JOIN t t04 NATURAL JOIN t t05 NATUR
Re: [HACKERS] Re: Synch Rep: direct transfer of WAL file from the primary to the standby
Mark Mielke wrote: > On 07/08/2009 09:59 AM, Kevin Grittner wrote: >> I think the interesting bit is when you're at this point and the >> connection between the master and slave goes down for a couple days. >> How do you handle that? > > Been following with great interest... > > If the updates are not performed at a regular enough interval, the slave > is not truly a functioning standby. I think it's a different problem > domain, probably best served by the existing pg_standby support? If the > slave can be out of touch with the master for an extended period of > time, near real time logs provide no additional benefit over just > shipping the archived WAL logs and running the standby in continuous > recovery mode? Might be easier to set up than pg_standby.. But more importantly, it can happen by accident. Someone trips on the power plug of the slave on Friday, and it goes unnoticed until Monday when DBA comes to work. -- 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] Re: Synch Rep: direct transfer of WAL file from the primary to the standby
On 07/08/2009 09:59 AM, Kevin Grittner wrote: Dimitri Fontaine wrote: 4. sync: slave is no more lagging, it's applying the stream as it gets it, either as part of the master transaction or not depending on the GUC settings I think the interesting bit is when you're at this point and the connection between the master and slave goes down for a couple days. How do you handle that? Been following with great interest... If the updates are not performed at a regular enough interval, the slave is not truly a functioning standby. I think it's a different problem domain, probably best served by the existing pg_standby support? If the slave can be out of touch with the master for an extended period of time, near real time logs provide no additional benefit over just shipping the archived WAL logs and running the standby in continuous recovery mode? Cheers, mark -- Mark Mielke
Re: [HACKERS] Re: Synch Rep: direct transfer of WAL file from the primary to the standby
"Kevin Grittner" writes: > Dimitri Fontaine wrote: > >> 4. sync: slave is no more lagging, it's applying the stream as it >> gets it, either as part of the master transaction or not >> depending on the GUC settings > > I think the interesting bit is when you're at this point and the > connection between the master and slave goes down for a couple days. > How do you handle that? Maybe how londiste handle the case could help us here: http://skytools.projects.postgresql.org/doc/londiste.ref.html#toc18 State| Owner | What is done -++ NULL | replay | Changes state to "in-copy", launches londiste.py copy process, continues with it's work in-copy | copy | drops indexes, truncates, copies data in, restores indexes, changes state to "catching-up" catching-up | copy | replay events for that table only until no more batches (means current moment), change state to "wanna-sync:" and wait for state to change wanna-sync: | replay | catch up to given tick_id, change state to "do-sync:" and wait for state to change do-sync:| copy | catch up to given tick_id, both replay and copy must now be at same position. change state to "ok" and exit ok | replay | synced table, events can be applied Such state changes must guarantee that any process can die at any time and by just restarting it can continue where it left. "subscriber add" registers table with NULL state. "subscriber add —expect-sync" registers table with ok state. "subscriber resync" sets table state to NULL. Regards, -- dim -- 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_migrator mention in documentation
Peter Eisentraut wrote: > On Friday 03 July 2009 02:28:22 Bruce Momjian wrote: > > I looked at that and the problem is that pg_migrator must be built > > against the _new_ source tree, and will issue an error and exit if it > > isn't. The problem with PGXS is it silently chooses the source tree to > > use based on which pg_config it finds in its path first; that seems > > error-prone. Any ideas for a clearer way to specify pg_config, and is > > that really helping things if the user has to specify it? > > The standard way to do that is > > make PG_CONFIG=/some/where/pg84/pg_config > > > As you can > > see, pg_migrator has the requirement of running in a multi-pg_config > > binary environment, so it has extra complexity that might make pg_config > > an undesirable option to be promoted first. > > It's certainly easier to do the above than having to download, configure, and > modify the PostgreSQL source tree, I think. I see you are replying to an old email; the current installer instrutions are: (7) Build pg_migrator For pg_migrator source installs, keep in mind the compile must use the _new_ PostgreSQL source directory and be installed in the new Postgres install directory. The simplest build option is to use PGXS: gmake USE_PGXS=1 PG_CONFIG=/usr/local/pgsql/bin/pg_config install Another option is to point to the top of the new PostgreSQL source tree by running something like: gmake top_builddir=/usr/src/pgsql install Replace '/usr/src/pgsql' with your new source directory. pg_migrator also understands the 'prefix=' specification if you installed Postgres in a custom location. pg_migrator does need a compiled backend source tree to be compiled, e.g. it needs libpgport and access to backend C defines. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + 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] Re: Synch Rep: direct transfer of WAL file from the primary to the standby
Dimitri Fontaine wrote: > 4. sync: slave is no more lagging, it's applying the stream as it > gets it, either as part of the master transaction or not > depending on the GUC settings I think the interesting bit is when you're at this point and the connection between the master and slave goes down for a couple days. How do you handle that? -Kevin -- 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] pgxs and make check message
On Monday 06 July 2009 22:42:54 Peter Eisentraut wrote: > Isn't it a bad idea that this from pgxs.mk does not return a non-zero > status? > > check: > @echo "'make check' is not supported." > @echo "Do 'make install', then 'make installcheck' instead." > > Or is something relying on a nonexisting test suite passing successfully? I suppose nothing is, so I have made this change. -- 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] bytea vs. pg_dump
2009/7/8 Bernd Helmle : > --On Dienstag, Juli 07, 2009 18:07:08 -0400 Tom Lane > wrote: > >> Enum. If we do this then it seems entirely fair that someone might >> want other settings someday. Also, it seems silly to pick a format >> partly on the grounds that it's expansible, and then not make the >> control GUC expansible. Perhaps >> >> SET bytea_output = [ hex | traditional ] > > I like the enum much better, too, but > > SET bytea_output = [ hex | escape ] + 1 Pavel > > looks better to me (encode/decode are using something like this already). > > -- > Thanks > > Bernd > > -- > 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] WIP: Deferrable unique constraints
Jeff Davis wrote: > On Tue, 2009-07-07 at 19:38 +0100, Dean Rasheed wrote: >> This approach works well if the number of potential conflicts is >> small. > > [...] > >> Curing the scalability problem by spooling the queue to disk shouldn't >> be too hard to do, but that doesn't address the problem that if a >> significant proportion of rows from the table need to be checked, it >> is far quicker to scan the whole index once than check row by row. > > Another approach that might be worth considering is to build a temporary > index and try to merge them at constraint-checking time. That might work > well for unique. > I'm not really sure what you mean by a "temporary index". Do you mean one that you would just throw away at the end of the statement? That seems a bit heavy-weight. Also it seems too specific to unique constraints. I think it would be better to cure the scalability issues for all constraints and triggers in one place, in the after triggers queue code. I had hoped that after doing deferrable unique constraints, I might apply a similar approach to other constraints, eg. a deferrable check constraint. In that case, an index doesn't help, and there is no choice but to check the rows one at a time. Unique (and also FK) are special, in that they have potentially more optimal ways of checking them in bulk. ISTM that this is an orthogonal concept to the issue of making the trigger queue scalable, except that there ought to be an efficient way of discarding all the queued entries for a particular constraint, if we decide to check it en masse (perhaps a separate queue per constraint, or per trigger). - Dean > However, there are some potential issues. I didn't think this through > yet, but here is a quick list just to get some thoughts down: > > 1. It would be tricky to merge while checking constraints if we are > supporting more general constraints like in my proposal > ( http://archives.postgresql.org/pgsql-hackers/2009-07/msg00302.php ). > > 2. Which indexes can be merged efficiently, and how much effort would it > take to make this work? > > 3. A related issue: making indexes mergeable would be useful for bulk > inserts as well. > > 4. At the end of the command, the index needs to work, meaning that > queries would have to search two indexes. That may be difficult (but > check the GIN fast insert code, which does something similar). > > 5. The temporary index still can't be enforcing constraints if they are > deferred, so it won't solve all the issues here. > > Regards, > Jeff Davis > -- 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] bytea vs. pg_dump
--On Dienstag, Juli 07, 2009 18:07:08 -0400 Tom Lane wrote: Enum. If we do this then it seems entirely fair that someone might want other settings someday. Also, it seems silly to pick a format partly on the grounds that it's expansible, and then not make the control GUC expansible. Perhaps SET bytea_output = [ hex | traditional ] I like the enum much better, too, but SET bytea_output = [ hex | escape ] looks better to me (encode/decode are using something like this already). -- Thanks Bernd -- 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] *_collapse_limit, geqo_threshold
Tom Lane wrote: > "Kevin Grittner" writes: >> I guess the question is whether there is anyone who has had a contrary >> experience. (There must have been some benchmarks to justify adding >> geqo at some point?) > > The CVS history shows that geqo was integrated on 1997-02-19, which > I think means that it must have been developed against Postgres95 > So while I don't doubt that geqo was absolutely essential when it was > written, it's fair to question whether it still provides a real win. > And we could definitely stand to take another look at the default > thresholds. Well there is a TODO item about implementing an alternative to GEQO (which is being treated more and more as the underdog of the project): http://archives.postgresql.org/message-id/15658.1241278636%40sss.pgh.pa.us Would people be interested in someone working on that item? Cheers, Jan -- 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: Synch Rep: direct transfer of WAL file from the primary to the standby
Hi, Tom Lane writes: > I think this fails the basic sanity check: do you need it to still work > when the master is dead. I don't get it. Why would we want to setup a slave against a dead master? The way I understand the current design of Synch Rep, when you start a new slave the following happen: 1. init: slave asks the master the current LSN and start streaming WAL 2. setup: slave asks the master for missing WALs from its current position to this LSN it just got, and apply them all to reach initial LSN (this happen in parallel to 1.) 3. catchup: slave has replayed missing WALs and now is replaying the stream he received in parallel, and which applies from init LSN (just reached) 4. sync: slave is no more lagging, it's applying the stream as it gets it, either as part of the master transaction or not depending on the GUC settings So, what I'm understanding you're saying is that the slave still should be able to setup properly when master died before it synced. What I'm saying is that if master dies before any sync slave exists, you get to start from backups (filesystem snaphost + archives for example, PITR recovery etc), as there's no slave. Regards, -- dim -- 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] WIP: generalized index constraints
Tom Lane wrote: > ... I think it might be interesting to turn > around Jeff's syntax sketch and provide a way to say that a CONSTRAINT > declaration should depend on some previously added index, eg > something like > > ALTER TABLE tab ADD CONSTRAINT UNIQUE (col1, col2) USING index > Is there any reason to limit UNIQUE constraints to lists of table columns? If you can build a unique index on an expression, why not a unique constraint? A quick test defining an index and manually adding catalog entries for the constraint and depends showed that it appears to work fine (and it's compatible with my deferrable unique constraints patch :-) ) - Dean -- 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] WIP: Deferrable unique constraints
Jeff Davis wrote: > First, I'm happy that you're working on this; I think it's important. I > am working on another index constraints feature that may have some > interaction: > > http://archives.postgresql.org/pgsql-hackers/2009-07/msg00302.php > > Let me know if you see any potential conflicts between our work. > Hi Jeff, Yes, I've been following that other thread with interest. I don't think that there is any conflict between the 2 patches, although there may be some interaction. My stuff is specific to UNIQUE, and my triggers make the assumption that this is enforced by an index. The triggers don't actually care what kind of index, although of course currently it can only be a btree. I guess that your generalised constraints could be made deferrable in a similar way, and would use different triggers to do the final check, so no problem there. > On Tue, 2009-07-07 at 19:38 +0100, Dean Rasheed wrote: >> For potential uniqueness violations a >> deferred trigger is queued to do a full check at the end of the >> statement or transaction, or when SET CONSTRAINTS is called. The >> trigger then replays the insert in a "fake insert" mode, which doesn't >> actually insert anything - it just checks that what is already there >> is unique, waiting for other transactions if necessary. > > What does the deferred trigger do? Do you need a "fake insert" mode or > can you use an index search instead? > Well the point about the "fake insert" mode is that it has to be able to block on another transaction, to see if it commits or rolls back a potentially conflicting value. ISTM that trying to do this with an index search would open up the potential for all sorts of race conditions. - Dean > I'm thinking that you could just store the TID of the tuple that causes > the potential violation in your list. Then, when you recheck the list, > for each potential violation, find the tuple from the TID, do a search > using the appropriate attributes, and if you get multiple results > there's a conflict. > > Regards, > Jeff Davis > > -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
