Re: [HACKERS] Role incompatibilities
* Peter Eisentraut ([EMAIL PROTECTED]) wrote: > Am Samstag, 25. März 2006 17:27 schrieb Stephen Frost: > > I think what Peter would want is for us to track CURRENT_USER (the role > > who logged in) and to always add the CURRENT_USER to the list of roles > > available after a 'SET ROLE'. That would at least get us a little > > closer to the spec though I'm not sure if it matches what other DBs do. > > This is sort of but not really what I meant. What I think would work rather > elegantly is having both a current_user and a current_role and having the > current_role set to current_user on login. (The SQL standard would require > the current role to be empty at login.) Then you get exactly the traditional > group behavior. You can also use SET ROLE to set the current role (but not > the current user) to some other permissible role. This will give you the > applicable privileges of your current user and that new role. The nice thing > about this is that this gives you both traditional PostgreSQL behavior by > default and completely SQL conforming behavior if you ask for it. (If we are > really ambitious, we can let people put their preferred current_role into > their session startup settings to give them exact SQL standard behavior by > default without requiring SET ROLE NONE first.) Note that we don't need any > inherit feature at all for any of that, nor does this require separate user > and role entities. Well, at first blush it would seem like you could use GetSessionUserId() for the 'CURRENT_USER' and modify roles_has_privs_of() to automatically seed the roles_list with the 'CURRENT_USER'. This has the initial issue that SECURITY DEFINER functions would end up including the permissions of the calling user unless some additional checking is done, I believe. I'm also pretty sure the SQL spec considers 'SESSION_USER' different from 'CURRENT_USER' in some situations though I'm not sure which in Postgres our current SESSION/CURRENT_USER arguments are closer to. You'd also have to make sure you don't recurse down from the 'CURRENT_USER' if it's from the seed because otherwise SET ROLE would end up being a no-op. I guess instead of seeding it, you could add it on at the end provided it wasn't already in the list. I'm also concerned about questions along the lines of 'who has permissions on what' and that being stable/sensible. I certainly don't feel we should get rid of 'inherit' though as that's a very useful feature. I'm also not personally terribly interested in trying to implement this, I'm mostly postulating some concrete changes which could be made to give others the opportunity to find issues with them. I also feel that we really can't do what the SQL spec requires without seperating users from roles as I don't beleve you're supposed to be able to grant users to roles or set role to a user. These kinds of issues make me that much less interested in trying to implement the spec verbatim. I'd certainly be much happier to listen to concrete suggestions on specific code changes or even better, patches to implement those changes... Just as I don't feel the spec has been entirely thought through because it hasn't actually been fully implemented in this regard, I don't feel these changes and behaviors are really being defined well enough to discuss very well and consider the ramifications of them. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Some employment changes ...
On Sat, Mar 25, 2006 at 12:01:20 -0800, Josh Berkus wrote: > > As of Monday I'm at Sun Microsystems. Since I'll be officially the > "PostgreSQL Community Guy" there I expect to have a lot more time to devote > to community stuff. Not that GreenPlum hasn't been generous with supporting > me (they have, very much so) but Sun has offered me some opportunities to > promote PostgreSQL which I can't do elsewhere. Is this related to your recent comment that you might be doing some stuff with Open Office again? ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] 8.2 planning features
Marc, > Its relatively easy for a Business to develop a roadmap, since the > managers delegate their programmers to work on specific features ... in > the case of an OSS project, there are no "assignment of features to work > on" happening ... ppl work on what is of interest to them ... so coming > up with anything more then a 'vapor-roadmap' is impossible ... Right. What Luke is saying is that *as a business* GreenPlum is developing a roadmap for what *they* are going to contribute to PostgreSQL and will be soliciting input. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] 8.2 planning features
Its relatively easy for a Business to develop a roadmap, since the managers delegate their programmers to work on specific features ... in the case of an OSS project, there are no "assignment of features to work on" happening ... ppl work on what is of interest to them ... so coming up with anything more then a 'vapor-roadmap' is impossible ... On Sat, 25 Mar 2006, Luke Lonergan wrote: Satoshi, On 3/21/06 3:59 PM, "satoshi nagayasu" <[EMAIL PROTECTED]> wrote: However, we also need to know why business people want to know about the future plan. For the business people, the roadmap is used to know the software is fit to their (growing) business, not only now but in the future. Roadmap can be changed, but still roadmap is necessary for some kind of users. Absolutely. The Bitmap Index and Sort improvements are features that we have been developing for the last 10 months based directly on discussions with business customers. They will be release with Bizgres 0.9 next week, and contributed to Postgres shortly thereafter (sort already is). Others on our list of customer driven roadmap items: - OLAP functions: CUBE, ROLLUP, RANK - Resource management, incl. dynamic memory management and statement queueing - Postgres intrinsic log-shipping replication (we have one to contribute) - Transparent DML on partitions Whether these get into 8.2 or not, I'd welcome developer discussion. We are initiating some planning sessions in mid-April for the next 4-6 months of development. - Luke ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] 8.2 planning features
Satoshi, On 3/21/06 3:59 PM, "satoshi nagayasu" <[EMAIL PROTECTED]> wrote: > However, we also need to know why business people want > to know about the future plan. For the business people, > the roadmap is used to know the software is fit to > their (growing) business, not only now but in the future. > > Roadmap can be changed, but still roadmap is necessary > for some kind of users. Absolutely. The Bitmap Index and Sort improvements are features that we have been developing for the last 10 months based directly on discussions with business customers. They will be release with Bizgres 0.9 next week, and contributed to Postgres shortly thereafter (sort already is). Others on our list of customer driven roadmap items: - OLAP functions: CUBE, ROLLUP, RANK - Resource management, incl. dynamic memory management and statement queueing - Postgres intrinsic log-shipping replication (we have one to contribute) - Transparent DML on partitions Whether these get into 8.2 or not, I'd welcome developer discussion. We are initiating some planning sessions in mid-April for the next 4-6 months of development. - Luke ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Domains as Subtypes
On Sat, Mar 25, 2006 at 07:16:13PM +0100, Jim Nasby wrote: > On Mar 25, 2006, at 4:14 PM, Tom Lane wrote: > > >"Jim C. Nasby" <[EMAIL PROTECTED]> writes: > >>On Fri, Mar 24, 2006 at 10:49:00PM -0500, Tom Lane wrote: > >>>I think we've got that one actually. It's domains as PL-function > >>>output > >>>types that aren't checked. Also plpgsql fails to enforce domain > >>>checks > >>>on its local variables. > > > >>So is this the complete list? > > > >No, I don't think so. IIRC we're also missing domain checks on > >parameter values in Bind messages, and there might be some other > >holes too. See the archives. > > > >I made a suggestion about closing all these holes at once by > >integrating domain checking into the I/O functions for domains, > >but it's not clear how to do that without a big performance hit. > > Performance hit on just domain handling or overall? Personally, I'd > rather see a hit on domain handling that we can work on later rather > than the current state of things which seems to smack of MySQL (Get > the feature 'checked off the list' first, then worry about doing it > the right way). The three issues I've raised regard the type behavior of domains with operators and are completely independent of the input/output checks issues. But I like the idea of centralizing the check in the input/output functions. It seems clearer and cleaner. The procedural language checks are harder, but may be easier to implement if there were a centralized check domain functionality. --elein > -- > Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] > Pervasive Software http://pervasive.comwork: 512-231-6117 > vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 > > > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org > ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [pgsql-advocacy] Some employment changes ...
Josh Berkus wrote: Folks, As of Monday I'm at Sun Microsystems. Since I'll be officially the "PostgreSQL Community Guy" there I expect to have a lot more time to devote to community stuff. Not that GreenPlum hasn't been generous with supporting me (they have, very much so) but Sun has offered me some opportunities to promote PostgreSQL which I can't do elsewhere. Anway, further notes about the change at http://www.powerpostgresql.com/ Also, a lot of PostgreSQL-related companies are currently hiring. If you are an experienced PostgreSQL DBA, developer or hacker and aren't completely satisified by your current job, drop me a line; I've an e-mail folder of people looking. Ah, question answered -- congratulations! Joe ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Some employment changes ...
Hi Josh, On Sat, 2006-03-25 at 12:01 -0800, Josh Berkus wrote: > As of Monday I'm at Sun Microsystems. Congrats! -- The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: PL/php, plPerlNG - http://www.commandprompt.com/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] Some employment changes ...
Folks, As of Monday I'm at Sun Microsystems. Since I'll be officially the "PostgreSQL Community Guy" there I expect to have a lot more time to devote to community stuff. Not that GreenPlum hasn't been generous with supporting me (they have, very much so) but Sun has offered me some opportunities to promote PostgreSQL which I can't do elsewhere. Anway, further notes about the change at http://www.powerpostgresql.com/ Also, a lot of PostgreSQL-related companies are currently hiring. If you are an experienced PostgreSQL DBA, developer or hacker and aren't completely satisified by your current job, drop me a line; I've an e-mail folder of people looking. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] 8.2 planning features
Tom, > BTW, pretty pictures such as Josh draws in the above talk should not be > confused with reality ;-). NOTHING I say should be confused with reality. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Domains as Subtypes
On Mar 25, 2006, at 4:14 PM, Tom Lane wrote: "Jim C. Nasby" <[EMAIL PROTECTED]> writes: On Fri, Mar 24, 2006 at 10:49:00PM -0500, Tom Lane wrote: I think we've got that one actually. It's domains as PL-function output types that aren't checked. Also plpgsql fails to enforce domain checks on its local variables. So is this the complete list? No, I don't think so. IIRC we're also missing domain checks on parameter values in Bind messages, and there might be some other holes too. See the archives. I made a suggestion about closing all these holes at once by integrating domain checking into the I/O functions for domains, but it's not clear how to do that without a big performance hit. Performance hit on just domain handling or overall? Personally, I'd rather see a hit on domain handling that we can work on later rather than the current state of things which seems to smack of MySQL (Get the feature 'checked off the list' first, then worry about doing it the right way). -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] A big thank you to all!
Guys, > I just wanted to take a minute to say a very big thank you to everyone who > has made PostgresQL the outstanding database that it is. So, can I quote you on the PostgreSQL web site? -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Where does the time go?
On Sat, Mar 25, 2006 at 05:38:26PM +, Simon Riggs wrote: > On Sat, 2006-03-25 at 16:24 +0100, Martijn van Oosterhout wrote: > > > I agree. However, if it's the overhead of calling gettimeofday() that > > slows everything down, perhaps we should tackle that end. For example, > > have a sampling mode that only times say 5% of the executed nodes. > > > > EXPLAIN ANALYZE SAMPLE blah; > > I like this idea. Why not do this all the time? I'd say we don't need > the SAMPLE clause at all, just do this for all EXPLAIN ANALYZEs. I was wondering about that. But then you may run into wierd results if a subselect takes a long time for just a few value. But maybe it should be the default, and have a FULL mode to say you want to measure everything. > Something even simpler? First 40 plus 5% random sample after that? I'd > prefer a random sample so we have the highest level of trust in the > numbers produced. Otherwise we might accidentally introduce bias from > systematic effects such as nested loops queries speeding up towards the > end of their run. (I know we would do that at the start, but we are > stuck because we don't know the population size ahead of time and we > know we need a reasonable number of data points). Well, I was wondering if a fixed percentage was appropriate. 5% of 10 million is still a lot for possibly not a lot of benefit. The followup email suggested a sampling that keeps happening less often as the number of tuples increases it a logorithmic based way. But we could add dome randomness that'd be cool. The question is, what's the overhead of calling random()? Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. signature.asc Description: Digital signature
Re: [HACKERS] Role incompatibilities
Am Samstag, 25. März 2006 17:27 schrieb Stephen Frost: > I think what Peter would want is for us to track CURRENT_USER (the role > who logged in) and to always add the CURRENT_USER to the list of roles > available after a 'SET ROLE'. That would at least get us a little > closer to the spec though I'm not sure if it matches what other DBs do. This is sort of but not really what I meant. What I think would work rather elegantly is having both a current_user and a current_role and having the current_role set to current_user on login. (The SQL standard would require the current role to be empty at login.) Then you get exactly the traditional group behavior. You can also use SET ROLE to set the current role (but not the current user) to some other permissible role. This will give you the applicable privileges of your current user and that new role. The nice thing about this is that this gives you both traditional PostgreSQL behavior by default and completely SQL conforming behavior if you ask for it. (If we are really ambitious, we can let people put their preferred current_role into their session startup settings to give them exact SQL standard behavior by default without requiring SET ROLE NONE first.) Note that we don't need any inherit feature at all for any of that, nor does this require separate user and role entities. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Where does the time go?
On Sat, 2006-03-25 at 16:24 +0100, Martijn van Oosterhout wrote: > I agree. However, if it's the overhead of calling gettimeofday() that > slows everything down, perhaps we should tackle that end. For example, > have a sampling mode that only times say 5% of the executed nodes. > > EXPLAIN ANALYZE SAMPLE blah; I like this idea. Why not do this all the time? I'd say we don't need the SAMPLE clause at all, just do this for all EXPLAIN ANALYZEs. > And then in InstrStart have a quick test that skips the gettimeofday > for this interation sometimes. You'd probably need some heuristics > because you always want to catch the first iteration but after the > 10,000th tuple in an indexscan, you're probably not going to learn > anything new. > How does this sound? Something even simpler? First 40 plus 5% random sample after that? I'd prefer a random sample so we have the highest level of trust in the numbers produced. Otherwise we might accidentally introduce bias from systematic effects such as nested loops queries speeding up towards the end of their run. (I know we would do that at the start, but we are stuck because we don't know the population size ahead of time and we know we need a reasonable number of data points). Best Regards, Simon Riggs ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Role incompatibilities
Am Samstag, 25. März 2006 16:10 schrieb Tom Lane: > No, the current implementation is a compromise between exact standards > compatibility and backwards compatibility with our historical "groups" > behavior. I'm not really prepared to toss the latter overboard. My two major sticking points here are the SET ROLE command and the noinherit feature. The SET ROLE command is not required by our historical group behavior (because we didn't have it before) and does not do what the SQL standard says it should do. The noinherit feature is not required by the historical group behavior (because groups are yes-inherit) and is not in the SQL standard either. So these two features were just mistakes as far as I can tell. I'm not passing judgement on whether a command like the currently implemented SET ROLE command or a feature like the currently implemented noinherit feature is useful. They are just not in line with either the historical group behavior or the SQL standard. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [SUGGESTION] CVSync
On Sat, 25 Mar 2006, Joshua D. Drake wrote: Alvaro Herrera wrote: Andrew Dunstan wrote: Tom Lane said: Why in the world is cvsup linked to X libraries? Surely it does not need a GUI. The client has GUI and non-GUI modes. For command-line ops use the -g flag or unset DISPLAY. Apparently not only the CVSup authors chose a weird language, they also wanted to get fancy and wrote a GUI. It's pretty useless if you ask me. I always use the -g switch as well, even when using it interactively. With rsync capability now is CVSup even needed? CVSup is on the main repository, rsync is on the anoncvs repository ... depends on how fast you want your changes :) Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [SUGGESTION] CVSync
On Sat, 25 Mar 2006, Tom Lane wrote: Alvaro Herrera <[EMAIL PROTECTED]> writes: $ ldd bin/cvsup linux-gate.so.1 => (0xe000) libz.so.1 => /usr/lib/libz.so.1 (0xb7f5b000) libXaw.so.7 => /usr/X11R6/lib/libXaw.so.7 (0xb7eff000) libXmu.so.6 => /usr/X11R6/lib/libXmu.so.6 (0xb7ee9000) libXext.so.6 => /usr/X11R6/lib/libXext.so.6 (0xb7edb000) libXt.so.6 => /usr/X11R6/lib/libXt.so.6 (0xb7e8b000) libSM.so.6 => /usr/X11R6/lib/libSM.so.6 (0xb7e82000) libICE.so.6 => /usr/X11R6/lib/libICE.so.6 (0xb7e6a000) libX11.so.6 => /usr/X11R6/lib/libX11.so.6 (0xb7d9e000) libm.so.6 => /lib/tls/i686/cmov/libm.so.6 (0xb7d64000) libc.so.6 => /lib/tls/i686/cmov/libc.so.6 (0xb7c2c000) libXpm.so.4 => /usr/X11R6/lib/libXpm.so.4 (0xb7c16000) libdl.so.2 => /lib/tls/i686/cmov/libdl.so.2 (0xb7c12000) /lib/ld-linux.so.2 (0xb7f71000) Why in the world is cvsup linked to X libraries? Surely it does not need a GUI. it is possible to build a non-gui version as well, that doesn't require X ... that's all I ever buld, but, for me, its simple ... cd /usr/ports/net/cvsup-without-gui; make install :) mercury# ldd /usr/local/bin/cvsup /usr/local/bin/cvsup: libz.so.2 => /usr/lib/libz.so.2 (0x280fa000) libutil.so.3 => /usr/lib/libutil.so.3 (0x28107000) libmd.so.2 => /usr/lib/libmd.so.2 (0x2811) libm.so.2 => /usr/lib/libm.so.2 (0x28119000) libc.so.4 => /usr/lib/libc.so.4 (0x28134000) Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [SUGGESTION] CVSync
Alvaro Herrera wrote: Andrew Dunstan wrote: Tom Lane said: Why in the world is cvsup linked to X libraries? Surely it does not need a GUI. The client has GUI and non-GUI modes. For command-line ops use the -g flag or unset DISPLAY. Apparently not only the CVSup authors chose a weird language, they also wanted to get fancy and wrote a GUI. It's pretty useless if you ask me. I always use the -g switch as well, even when using it interactively. With rsync capability now is CVSup even needed? Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Role incompatibilities
* Tom Lane ([EMAIL PROTECTED]) wrote: > Peter Eisentraut <[EMAIL PROTECTED]> writes: > > My concern here is to arrive at a standards conforming role system. > > Clearly > > we've established that the current one doesn't do it. Moreover, I'm now > > convinced that some aspects of the current implementation arose from an > > attempt to implement the standard but failed because of misunderstandings. > > No, the current implementation is a compromise between exact standards > compatibility and backwards compatibility with our historical "groups" > behavior. I'm not really prepared to toss the latter overboard. I think what Peter would want is for us to track CURRENT_USER (the role who logged in) and to always add the CURRENT_USER to the list of roles available after a 'SET ROLE'. That would at least get us a little closer to the spec though I'm not sure if it matches what other DBs do. I'll try to check on that sometime this weekend. I know that Oracle, at least, gives you all roles which have been granted to you on login on at least the system I've got access to. If you then 'set role' to a given role you *are* dropping privileges, not adding them. I don't know if there's a way to configure Oracle for the standards-compliant method. I'm also not sure what other databases do. The noinherit for 'user' roles is more difficult, of course. We can tell people to set noinherit on the roles that can log in but I'm not sure Peter's happy with that. We could have a server option of "sql_user_noinherit" or some such which automatically set noinherit for roles which have the 'login' attribute and inherit for those without. This would create the artificial distinction between users and roles which the specification has but makes for some very odd problems- do you then disallow roles with 'login' from being granted to others? What about grants done prior to the option being set, ignore them? What if the admin sets 'nologin' for such a role, do the old grants suddenly come back? Anyhow, I'm really not sure it's the best approach in this case to try to follow the spec to the letter and not just because it breaks backwards compatibility for us but also because I don't think any actually implemented database follows it exactly either and as such the spec hasn't been that well thought out in this area. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] [SUGGESTION] CVSync
Andrew Dunstan wrote: > Tom Lane said: > > > Why in the world is cvsup linked to X libraries? Surely it does not > > need a GUI. > > The client has GUI and non-GUI modes. For command-line ops use the -g flag > or unset DISPLAY. Apparently not only the CVSup authors chose a weird language, they also wanted to get fancy and wrote a GUI. It's pretty useless if you ask me. I always use the -g switch as well, even when using it interactively. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Where does the time go?
On Sat, Mar 25, 2006 at 04:24:05PM +0100, Martijn van Oosterhout wrote:
> I agree. However, if it's the overhead of calling gettimeofday() that
> slows everything down, perhaps we should tackle that end. For example,
> have a sampling mode that only times say 5% of the executed nodes.
>
> EXPLAIN ANALYZE SAMPLE blah;
> You could do a non-random sampling fairly easily:
Actually, I thought of a better way to control the sampling that's
probably better than the crude if-then-else structure I gave which also
takes advantage of the fact that the numbers are floating point:
InstrInit:
next_sample = 0;
InstrStart:
if( ntuples < 16 )
dosample = yes;
else if( ntuples > next_sample )
{
dosample = yes;
next_sample += log2(ntuples);
}
else
dosample = no;
This will sample approxiamtly log2 of the actual executions. You could
use log10(), sqrt() or any other function you find reflects the sample
you want.
Have a nice day,
--
Martijn van Oosterhout http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.
signature.asc
Description: Digital signature
Re: [HACKERS] [SUGGESTION] CVSync
Tom Lane said: > Why in the world is cvsup linked to X libraries? Surely it does not > need a GUI. > The client has GUI and non-GUI modes. For command-line ops use the -g flag or unset DISPLAY. My crontab entry looks like this: 3 * * * * /usr/bin/cvsup -g -r 25 /home/cvsmirror/postgres.cvsup cheers andrew ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Where does the time go?
On Sat, Mar 25, 2006 at 10:00:51AM -0500, Tom Lane wrote: > Simon Riggs <[EMAIL PROTECTED]> writes: > > I would like to implement an additional mode for EXPLAIN ANALYZE that > > does no timing instrumentation at all. Most of the time just looking for > > differences between estimated and actual row counts is all you need. > > I don't really agree with that premise ... without timings, you cannot > for instance tell if the planner has over/underestimated the cost of an > index fetch. I agree. However, if it's the overhead of calling gettimeofday() that slows everything down, perhaps we should tackle that end. For example, have a sampling mode that only times say 5% of the executed nodes. EXPLAIN ANALYZE SAMPLE blah; And then in InstrStart have a quick test that skips the gettimeofday for this interation sometimes. You'd probably need some heuristics because you always want to catch the first iteration but after the 10,000th tuple in an indexscan, you're probably not going to learn anything new. You could do a non-random sampling fairly easily: if( ntuples < 16 ) yes else if( ntuples < 16*16 && (ntuples%16) == 0) yes else if( ntuples < 16*16*16 && ntuples%(16*16) == 0) yes else etc etc etc This mean that the more often a node is executed, the less often you actually time it. Note, we store ntuples as a doulbe so the mod operation won't work... How does this sound? -- Martijn van Oosterhout http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. signature.asc Description: Digital signature
Re: [HACKERS] Domains as Subtypes
"Jim C. Nasby" <[EMAIL PROTECTED]> writes: > On Fri, Mar 24, 2006 at 10:49:00PM -0500, Tom Lane wrote: >> I think we've got that one actually. It's domains as PL-function output >> types that aren't checked. Also plpgsql fails to enforce domain checks >> on its local variables. > So is this the complete list? No, I don't think so. IIRC we're also missing domain checks on parameter values in Bind messages, and there might be some other holes too. See the archives. I made a suggestion about closing all these holes at once by integrating domain checking into the I/O functions for domains, but it's not clear how to do that without a big performance hit. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Role incompatibilities
Peter Eisentraut <[EMAIL PROTECTED]> writes: > My concern here is to arrive at a standards conforming role system. Clearly > we've established that the current one doesn't do it. Moreover, I'm now > convinced that some aspects of the current implementation arose from an > attempt to implement the standard but failed because of misunderstandings. No, the current implementation is a compromise between exact standards compatibility and backwards compatibility with our historical "groups" behavior. I'm not really prepared to toss the latter overboard. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [SUGGESTION] CVSync
Alvaro Herrera <[EMAIL PROTECTED]> writes: > $ ldd bin/cvsup > linux-gate.so.1 => (0xe000) > libz.so.1 => /usr/lib/libz.so.1 (0xb7f5b000) > libXaw.so.7 => /usr/X11R6/lib/libXaw.so.7 (0xb7eff000) > libXmu.so.6 => /usr/X11R6/lib/libXmu.so.6 (0xb7ee9000) > libXext.so.6 => /usr/X11R6/lib/libXext.so.6 (0xb7edb000) > libXt.so.6 => /usr/X11R6/lib/libXt.so.6 (0xb7e8b000) > libSM.so.6 => /usr/X11R6/lib/libSM.so.6 (0xb7e82000) > libICE.so.6 => /usr/X11R6/lib/libICE.so.6 (0xb7e6a000) > libX11.so.6 => /usr/X11R6/lib/libX11.so.6 (0xb7d9e000) > libm.so.6 => /lib/tls/i686/cmov/libm.so.6 (0xb7d64000) > libc.so.6 => /lib/tls/i686/cmov/libc.so.6 (0xb7c2c000) > libXpm.so.4 => /usr/X11R6/lib/libXpm.so.4 (0xb7c16000) > libdl.so.2 => /lib/tls/i686/cmov/libdl.so.2 (0xb7c12000) > /lib/ld-linux.so.2 (0xb7f71000) Why in the world is cvsup linked to X libraries? Surely it does not need a GUI. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Where does the time go?
Simon Riggs <[EMAIL PROTECTED]> writes: > I would like to implement an additional mode for EXPLAIN ANALYZE that > does no timing instrumentation at all. Most of the time just looking for > differences between estimated and actual row counts is all you need. I don't really agree with that premise ... without timings, you cannot for instance tell if the planner has over/underestimated the cost of an index fetch. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [SUGGESTION] CVSync
Tom Lane wrote: > Neil Conway <[EMAIL PROTECTED]> writes: > > I agree that investigating alternatives would be a good idea: AFAIK > > there's no easy way to build cvsup on Linux/AMD64 (without patches and > > more pain than I'm willing to endure), so I use cvsup on one machine and > > then periodically rsync a copy of that to my main (AMD64) machine. > > Actually, the last time I looked seriously at cvsup, my requirement was > that it build on HPUX, which it did not (well, maybe it would've with > sufficient investment of pain, but that wasn't happening). I was about > to take another look at whether I couldn't just-install-it on FC4, but > my main FC4 machine is now x86_64, so your remarks are discouraging me > again :-( Don't let'em. I use CVSup on both my ix86 and amd64 machine. (IIRC the latter uses a 32bit binary, but it surely works anyway.) I think these are static binaries, so they would probably work on your machines unchanged. Hmm, well, not quite: $ ldd bin/cvsup linux-gate.so.1 => (0xe000) libz.so.1 => /usr/lib/libz.so.1 (0xb7f5b000) libXaw.so.7 => /usr/X11R6/lib/libXaw.so.7 (0xb7eff000) libXmu.so.6 => /usr/X11R6/lib/libXmu.so.6 (0xb7ee9000) libXext.so.6 => /usr/X11R6/lib/libXext.so.6 (0xb7edb000) libXt.so.6 => /usr/X11R6/lib/libXt.so.6 (0xb7e8b000) libSM.so.6 => /usr/X11R6/lib/libSM.so.6 (0xb7e82000) libICE.so.6 => /usr/X11R6/lib/libICE.so.6 (0xb7e6a000) libX11.so.6 => /usr/X11R6/lib/libX11.so.6 (0xb7d9e000) libm.so.6 => /lib/tls/i686/cmov/libm.so.6 (0xb7d64000) libc.so.6 => /lib/tls/i686/cmov/libc.so.6 (0xb7c2c000) libXpm.so.4 => /usr/X11R6/lib/libXpm.so.4 (0xb7c16000) libdl.so.2 => /lib/tls/i686/cmov/libdl.so.2 (0xb7c12000) /lib/ld-linux.so.2 (0xb7f71000) (this is the ix86 machine, I don't have the other one handy) -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] [HACKERS] Automatically setting work_mem
On Sat, Mar 25, 2006 at 12:24:00PM +, Simon Riggs wrote: > memory. Using too much memory could also impact overall elapsed time > when we have concurrent users, so the question is should we optimise > resources for the multi-user case or for the single user case? Where is > the right balance point? Sounds like what we need is a GUC... I know I certainly have cases where I'll take faster and using more memory over the alternative. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Domains as Subtypes
On Fri, Mar 24, 2006 at 10:49:00PM -0500, Tom Lane wrote: > Josh Berkus writes: > > you missed one. Domains as parameters to functions are not > > enforced. > > I think we've got that one actually. It's domains as PL-function output > types that aren't checked. Also plpgsql fails to enforce domain checks > on its local variables. So is this the complete list? Can we document it somewhere? -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Role incompatibilities
Am Samstag, 25. März 2006 01:02 schrieb Stephen Frost: > If there are use cases where what we're doing is causing problems for > users who are looking for what the SQL spec has exactly, please let us > know.. Given that other databases don't religiously follow the SQL spec > wrt roles either makes me suspect you won't find too cases like that.. My concern here is to arrive at a standards conforming role system. Clearly we've established that the current one doesn't do it. Moreover, I'm now convinced that some aspects of the current implementation arose from an attempt to implement the standard but failed because of misunderstandings. I'd be interested to know what incompatibilities you have found in other database systems, so we know what we're dealing with. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] [HACKERS] Automatically setting work_mem
On Wed, 2006-03-22 at 10:03 +, Simon Riggs wrote: > Recent test results show that with a 512MB test sort we can reclaim > 97% of memory during final merge with only a noise level (+2%) > increase in overall elapsed time. (Thats just an example, your mileage > may vary). So a large query would use and keep about 536MB memory > rather than 1536MB. Large performance test output, credit to Ayush Parashar, Greenplum. We test a very common case for large sorts with high work_mem: High work_mem significantly reduces the number of runs required, whereas high work_mem significantly increases MaxTapes, so there will frequently be the situation that Nruns << MaxTapes and this patch seeks to optimise the final merge (only) for that case. elapsed final merge CPU for final merge with patch 385 s 100.65 s5.48s/71.05u s w/o patch 377 s 84.73 s 4.79s/72.32u s So looking at just the final merge in isolation we have a 19% increase in elapsed time from a 97% reduction in memory usage (based upon the assumption that reducing available slots by 97% will lead to an overall 97% reduction in memory usage from slots+tuples). This uses an earlier result that the optimal merge buffer size for the final merge is 8 times larger than the overall optimal merge buffer size of 32 blocks; altering this ratio would bring down elapsed time at the cost of increasing memory. Using too much memory could also impact overall elapsed time when we have concurrent users, so the question is should we optimise resources for the multi-user case or for the single user case? Where is the right balance point? Resource usage: (resource usage) multiplied by (time in use) with patch: 147,000 MB.secs (512 MB fir 285s, then 15MB for 100s) w/o patch: 189,000 MB.secs (512 MB for 377s) so overall resource consumption reduced to 77% of current usage, or the other way up 45% additional users on a throughput basis. Increase in final merge time is likely due to increased I/O. If this final merge were input to other nodes in a complex query we may not consume the tuples at maximum speed, so the additional time might easily be covered by other actions. Non final merge test results were within 3% of each other; the patch doesn't touch that aspect at all, so from that we can say that the test results are reasonably useful comparison. - - - - With patch: LOG: switching to external sort with 1831 tapes: CPU 2.86s/1.96u sec elapsed 7.58 sec\ LOG: finished writing run 1 to tape 0: CPU 7.36s/27.67u sec elapsed 42.05 sec\ LOG: finished writing run 2 to tape 1: CPU 12.55s/56.85u sec elapsed 79.78 sec\ LOG: finished writing run 3 to tape 2: CPU 17.88s/86.42u sec elapsed 120.94 sec\ LOG: finished writing run 4 to tape 3: CPU 23.06s/116.46u sec elapsed 159.06 sec\ LOG: finished writing run 5 to tape 4: CPU 28.57s/146.25u sec elapsed 201.59 sec\ LOG: finished writing run 6 to tape 5: CPU 33.76s/176.14u sec elapsed 239.87 sec\ LOG: performsort starting: CPU 38.13s/200.71u sec elapsed 272.83 sec\ LOG: finished writing run 7 to tape 6: CPU 38.23s/204.51u sec elapsed 276.76 sec\ LOG: finished writing final run 8 to tape 7: CPU 38.50s/211.93u sec elapsed 284.51 sec\ LOG: shrinking resources to 3% (from 4194304 to 146686 slots): CPU 38.52s/211.93u sec elapsed 284.69 sec\ LOG: performsort done (except 8-way final merge): CPU 38.53s/212.00u sec elapsed 284.85 sec\ LOG: final merge: tape 7 exhausted: CPU 42.70s/270.65u sec elapsed 368.06 sec\ LOG: reassigning resources; each tape gets: +2619 slots, +6770980 mem: CPU 42.70s/270.70u sec elapsed 368.12 sec\ LOG: final merge: tape 2 exhausted: CPU 43.68s/283.05u sec elapsed 385.00 sec\ LOG: final merge: tape 3 exhausted: CPU 43.68s/283.05u sec elapsed 385.00 sec\ LOG: final merge: tape 5 exhausted: CPU 43.68s/283.05u sec elapsed 385.00 sec\ LOG: final merge: tape 0 exhausted: CPU 43.68s/283.05u sec elapsed 385.00 sec\ LOG: final merge: tape 6 exhausted: CPU 43.68s/283.05u sec elapsed 385.00 sec\ LOG: final merge: tape 1 exhausted: CPU 43.68s/283.05u sec elapsed 385.00 sec\ LOG: final merge: tape 4 exhausted: CPU 43.68s/283.05u sec elapsed 385.00 sec\ LOG: external sort ended, 293182 disk blocks used: CPU 44.01s/283.05u sec elapsed 385.50 sec\ Without patch: LOG: switching to external sort with 1873 tapes: CPU 2.72s/2.03u sec elapsed 7.07 sec\ LOG: finished writing run 1 to tape 0: CPU 7.08s/28.42u sec elapsed 39.96 sec\ LOG: finished writing run 2 to tape 1: CPU 12.10s/58.47u sec elapsed 79.37 sec\ LOG: finished writing run 3 to tape 2: CPU 17.35s/89.39u sec elapsed 120.18 sec\ LOG: finished writing run 4 to tape 3: CPU 22.50s/120.55u sec elapsed 161.24 sec\ LOG: finished writing run 5 to tape 4: CPU 27.84s/151.41u sec elapsed 202.11 sec\ LOG: finished writing run 6 to tape 5: CPU 33.15s/182.57u sec elapsed 243.34 sec\ LOG: performsort starting: CPU 37.53s/208.36u sec elapsed 277.51 sec\ LOG: finished writing run 7
Re: [HACKERS] Where does the time go?
On Wed, 2006-03-22 at 21:59 -0500, Tom Lane wrote: > I'm betting that a > big part of your issue is that the EXPLAIN ANALYZE instrumentation > overhead is (1) significant I would like to implement an additional mode for EXPLAIN ANALYZE that does no timing instrumentation at all. Most of the time just looking for differences between estimated and actual row counts is all you need. For long queries, the additional time can make the execution infeasible, yet frequently they are the ones you want to see the output for. EXPLAIN ANALYZE NOTIMING sounds a little dry, any ideas? Best Regards, Simon Riggs ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
