Re: [HACKERS] Practical impediment to supporting multiple SSL libraries
On Wed, Apr 12, 2006 at 05:25:47PM +0100, Dave Page wrote: > The driver implements all versions of the wire protocol itself, but if > libpq is available at runtime (it will dynamically load it on platforms > that support it) it can use it for connection setup so features like SSL > can be provided easily. I'm still not overly familiar with how it works > yet, but I'm sure Hiroshi (CC'd) can provide further details if you need > them. Right, so what you're basically doing is setting up the connection via libpq then grabbing the SSL pointer and using that to continue communicating. If it's not SSL you use PQsocket get the socket and continue from there. Unorthodox usage, but it should work. 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] Practical impediment to supporting multiple SSL libraries
On Wed, Apr 12, 2006 at 05:00:17PM -0400, Tom Lane wrote: > > Issuer (name and certificate), validity dates, basic constraints, key > > usage, posslby fingerprint. > > I think that way madness lies --- do we really want to commit to > re-inventing an SSL API that will cover anything someone might want > to do with either underlying library? Indeed. There's also the issue that the underlying system may not be using what you think it is. e.g. GnuTLS can authenticate on PGP keys rather than x509 certificates. There's still the mystery regarding libpq extracting peer DN and CN but passing it to the user. > An idea that just occurred to me is to define PQgetssl as "return SSL* > if we are using OpenSSL for this connection; else return NULL". Then > add a parallel routine (maybe PQgetgnussl?) defined as returning the > equivalent GnuTLS handle, only if we are using GnuTLS for this > connection. (Presumably, in any one build of libpq, one of the pair of > routines would be an always-returns-null stub.) Alternatively, create a new function PQgetsslinfo() that returns both the library name and a (void) pointer. In any case the old interface can never return anything other than a pointer for OpenSSL. > I'd still want to adopt Martijn's idea of declaring both of 'em as > returning void *, to avoid depending on other packages' include files. Ack, at least we can get that out of the way. It doesn't change anything from the user's point of view, other than they know for sure what the signiture is. 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] Control File
Bruce Momjian writes: > Bruno Almeida do Lago wrote: >> After that night, I started to ask myself if PostgreSQL should not have a >> control file to check if expected datafiles are where they should be and >> JUST warn about missing ones? > I don't think this happens frequently enough to add code for it. I think we saw it happen once to Joe Conway's DB. But I see no particular reason why Postgres needs a feature for this --- you can stick a test into your database start script if you need it. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Speaking of pgstats
Tom Lane wrote: > "Magnus Hagander" <[EMAIL PROTECTED]> writes: > > While we're talking about pgstats... There was some talk a while back > > about the whole bufferer/collector combination perhaps being unnecessary > > as well, and that it might be a good idea to simplify it down to just a > > collector. I'm not 100% sure what the end result of that discussion was, > > thouhg, and I can't find it in the archives :-( > > Yeah, I was thinking that same thing this morning. AFAIR we designed > the current structure "on paper" in a pghackers thread, and never did > any serious experimentation to prove that it was worth having the extra > process. I concur it's worth at least testing the simpler method. My research is in the hold queue: http://momjian.postgresql.org/cgi-bin/pgpatches_hold Subject is "Stats collector performance improvement". I am waiting for someone to confirm my tests on other platforms before moving forward, but we really should do something for 8.2. If someone else wants to work on it, go ahead. All my work is in those emails. -- Bruce Momjian http://candle.pha.pa.us EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(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] Control File
Bruno Almeida do Lago wrote: > DBA takes another coffee and finally started the database which... just came > up! Few minutes latter lot off errors being displayed. What is that??? > /mnt/array2 (50% of datafiles and tablespaces were there) was still umounted > and even so PostgreSQL came up. -- PostgreSQL stopped, /mnt/array2 mounted, > started and... - happy end! > > > After that night, I started to ask myself if PostgreSQL should not have a > control file to check if expected datafiles are where they should be and > JUST warn about missing ones? I don't think this happens frequently enough to add code for it. -- Bruce Momjian http://candle.pha.pa.us EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(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] Get explain output of postgresql in Tables
On Wed, Apr 12, 2006 at 07:28:25PM -0400, Alvaro Herrera wrote: > Dave Page escribi?: > > > > Alvaro Herrera <[EMAIL PROTECTED]> writes: > > > > It would be nice to see the "visual explain" tool that Denis wrote -- > > > > did he finish it? Is it available somewhere? Are there any > > > > screenshots? > > > > > Red Hat did one of these some years ago: > > > http://sources.redhat.com/rhdb/visualexplain.html > > pgAdmin also has visual explain capabilities. > > How does it work? Does it parse the text representation? > > I found a screenshot here: > http://www.pgadmin.org/images/screenshots/pgadmin3_explain.png > > Seems nice (but lacking the attributes for each node ...) To get the details you hover over each box. It would be nice if you could have it show that info on the main screen though... -- 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 6: explain analyze is your friend
Re: [HACKERS] Get explain output of postgresql in Tables
Dave Page escribió: > > Alvaro Herrera <[EMAIL PROTECTED]> writes: > > > It would be nice to see the "visual explain" tool that Denis wrote -- > > > did he finish it? Is it available somewhere? Are there any screenshots? > > > Red Hat did one of these some years ago: > > http://sources.redhat.com/rhdb/visualexplain.html > pgAdmin also has visual explain capabilities. How does it work? Does it parse the text representation? I found a screenshot here: http://www.pgadmin.org/images/screenshots/pgadmin3_explain.png Seems nice (but lacking the attributes for each node ...) -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(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] Get explain output of postgresql in Tables
On Wed, Apr 12, 2006 at 03:34:05PM -0700, Josh Berkus wrote: > If we have an XML patch now, I say use it. I know I want it. Certainly; XML is better than nothing. But since it shouldn't be hard to add the ability to output a recordset at the same time... -- 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] Practical impediment to supporting multiple SSL libraries
Martijn van Oosterhout wrote: >On Wed, Apr 12, 2006 at 05:03:32PM +0100, Dave Page wrote: > > > > >>The next version of psqlODBC (that has just gone into CVS tip after >>months of work and debate) uses it, and would break almost completely >>should it be removed, therefore any backwards incompatible change should >>be avoided imho. And 2 or 4 could cause chaos for Windows users if >>different DLL builds get mixed up. >> >> > >Hmm, may I ask what it uses it for? Just to get information, or >something more substantial? > In case of SSL mode, the driver gets the communication path using PQsocket() or PQgetssl() after calling PQconnectdb(). The driver comunicates with the server by itself using the path. In case of non-SSL mode, the driver never calls libpq API at all. regards, Hiroshi Inoue ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Get explain output of postgresql in Tables
"Jim C. Nasby" <[EMAIL PROTECTED]> writes: > Having an SQL format would make it easier to allow for a mode that > captures explain or explain analyze output from every query. Turn that > mode on, run an application's test suite, and now you have a pretty good > idea of how all the queries will run. Or, take a production system and > turn that option on for a single connection. Another option is to have > any queries that take more than X amount of time store an EXPLAIN of the > query. > > Having this info in machine format would make it easier to write > something that sets the various cost estimator values (random_page_cost, > etc). I'm particularly fond of the idea of storing the info in an SQL table. When I first met this in Oracle it seemed awkward and annoying. But as I used it I found more and more reasons why it's useful. I had just such a mode for our application that explained queries before running them (actually just a 1 time in 100 to avoid performance impacts). I could look at an internal administrative web page that listed all queries that showed profiling information, execution counts, explain plan, etc. One advantage this would have is that the SQL table could include much more detailed information than the text output can readably display. Then there could be a function that displays the data from the SQL table in a format similar to the current EXPLAIN output and other functions to display additional information. -- greg ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Get explain output of postgresql in Tables
Jim, > The list goes on. Like I said, you could do all these things with XML, > you just couldn't easily do them within the database. XML --> Table conversion should be relatively easy with PL/Perl, PL/Java, and/or an external language. Heck, if we could expand our XML tools (Peter will have a talk on this at the Summit) we could do it in the database by simple function call. If we have an XML patch now, I say use it. I know I want it. -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Get explain output of postgresql in Tables
-Original Message- From: "Tom Lane"<[EMAIL PROTECTED]> Sent: 12/04/06 23:03:08 To: "Alvaro Herrera"<[EMAIL PROTECTED]> Cc: "Germán Poó Caamaño"<[EMAIL PROTECTED]>, "Jim C. Nasby"<[EMAIL PROTECTED]>, "[EMAIL PROTECTED]"<[EMAIL PROTECTED]>, "pgsql-hackers@postgresql.org" Subject: Re: [HACKERS] Get explain output of postgresql in Tables > Alvaro Herrera <[EMAIL PROTECTED]> writes: > > It would be nice to see the "visual explain" tool that Denis wrote -- > > did he finish it? Is it available somewhere? Are there any screenshots? > Red Hat did one of these some years ago: > http://sources.redhat.com/rhdb/visualexplain.html pgAdmin also has visual explain capabilities. /D -Unmodified Original Message- Alvaro Herrera <[EMAIL PROTECTED]> writes: > It would be nice to see the "visual explain" tool that Denis wrote -- > did he finish it? Is it available somewhere? Are there any screenshots? Red Hat did one of these some years ago: http://sources.redhat.com/rhdb/visualexplain.html I don't see a prebuilt package on that page, but I believe the sources are still available here: http://sources.redhat.com/rhdb/cvs.html regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(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] Get explain output of postgresql in Tables
Ühel kenal päeval, K, 2006-04-12 kell 17:42, kirjutas Alvaro Herrera: > It would be nice to see the "visual explain" tool that Denis wrote -- > did he finish it? Is it available somewhere? Are there any screenshots? IIRC there is a "visual explain" tool pin pgAdmin III --- Hannu ---(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] Get explain output of postgresql in Tables
Alvaro Herrera <[EMAIL PROTECTED]> writes: > It would be nice to see the "visual explain" tool that Denis wrote -- > did he finish it? Is it available somewhere? Are there any screenshots? Red Hat did one of these some years ago: http://sources.redhat.com/rhdb/visualexplain.html I don't see a prebuilt package on that page, but I believe the sources are still available here: http://sources.redhat.com/rhdb/cvs.html regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Get explain output of postgresql in Tables
Ühel kenal päeval, K, 2006-04-12 kell 14:38, kirjutas Jim C. Nasby: > Well, really just about anything you'd want to do with it in an XML > format. The advantage of SQL is that you can do it within the database, > and you don't have to worry about having something around that can > process XML. > > Some possibilities... > > Having an SQL format would make it easier to allow for a mode that > captures explain or explain analyze output from every query. Turn that > mode on, run an application's test suite, and now you have a pretty good > idea of how all the queries will run. Maybe. Depending on how much preprocessing is done before saving, this can be true. Just storing something in "SQL format" (whatever that is) doesn't not magically make it easy to process. And storing an XML string is no more complicated than storing a set of records. > Or, take a production system and > turn that option on for a single connection. Another option is to have > any queries that take more than X amount of time store an EXPLAIN of the > query. OTOH, on a production system, where performance matters, you probably still would prefer a format where collecting data is fast, and storing 1 row per plan will always be faster than storing many, especially with indexes. > Having this info in machine format would make it easier to write > something that sets the various cost estimator values (random_page_cost, > etc). I guess that this needs to be written in C anyhow, and parsing a defined subset of XML is not that hard. > The list goes on. Like I said, you could do all these things with XML, > you just couldn't easily do them within the database. I'm not sure about it, at least without a specific example. Processing tree-structured data is not a thing that SQL is very good at. - Hannu ---(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] Get explain output of postgresql in Tables
Hi, Germán Poó Caamaño escribió: > We can get the best of both worlds. > > For instance, EXPLAIN and EXPLAIN ANALYZE with the usual output; but > also EXPLAIN XML and EXPLAIN ANALYZE XML with an XML syntax to be > used by programs. > > I have a patch for this behavior, but unfortunately this is not > updated. It was made by the time that postgresql 8.0 was beta > without any chance to get feedback (everybody were fixing bugs). > > The strategy was quite simple. It was implemented inside on > explain.c; with an extra parameter. So, if any change could > happen in the normal output of explain, it could be easier to > update the XML one. > > Get it updated should not be so much hours of work. At this > moment I do not have that time :-( I suggest you post it to -patches. If someone is interested, he or she can update it. (Or if you posted it back then, can you provide the link to the archives?) It would be nice to see the "visual explain" tool that Denis wrote -- did he finish it? Is it available somewhere? Are there any screenshots? -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Practical impediment to supporting multiple SSL libraries
"Magnus Hagander" <[EMAIL PROTECTED]> writes: >> Other than DN and CN, what else would people want? > Issuer (name and certificate), validity dates, basic constraints, key > usage, posslby fingerprint. I think that way madness lies --- do we really want to commit to re-inventing an SSL API that will cover anything someone might want to do with either underlying library? Moreover, this does not fix the problem: an existing app that thinks it can pass the returned pointer to an OpenSSL routine will still crash the moment a GnuTLS version of libpq is put under it. Case in point: psql, as currently coded. An idea that just occurred to me is to define PQgetssl as "return SSL* if we are using OpenSSL for this connection; else return NULL". Then add a parallel routine (maybe PQgetgnussl?) defined as returning the equivalent GnuTLS handle, only if we are using GnuTLS for this connection. (Presumably, in any one build of libpq, one of the pair of routines would be an always-returns-null stub.) The advantage of this is that an app knows what it'll get, and an app that's only familiar with one of the two SSL libraries will not be given a pointer it can't use. I'd still want to adopt Martijn's idea of declaring both of 'em as returning void *, to avoid depending on other packages' include files. 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] Get explain output of postgresql in Tables
On Wed, 2006-04-12 at 14:38 -0500, Jim C. Nasby wrote: > On Wed, Apr 12, 2006 at 09:45:41AM -0700, Mischa Sandberg wrote: > > Jim C. Nasby wrote: > > >On Wed, Apr 12, 2006 at 04:53:20PM +0200, Thomas Hallgren wrote: > > > > > >> > > >> > > >> > > >> > > >> > > > > > > > > >Well, the downside is that such a format means explain output is now > > >twice as long. But I'd love to see something like that as an option. I'd > > >also still like to see an SQL-parseable version as well, since I think > > >there's applications for that. > [...] We can get the best of both worlds. For instance, EXPLAIN and EXPLAIN ANALYZE with the usual output; but also EXPLAIN XML and EXPLAIN ANALYZE XML with an XML syntax to be used by programs. I have a patch for this behavior, but unfortunately this is not updated. It was made by the time that postgresql 8.0 was beta without any chance to get feedback (everybody were fixing bugs). The strategy was quite simple. It was implemented inside on explain.c; with an extra parameter. So, if any change could happen in the normal output of explain, it could be easier to update the XML one. Get it updated should not be so much hours of work. At this moment I do not have that time :-( -- Germán Poó-Caamaño http://www.ubiobio.cl/~gpoo/ Concepción - Chile ---(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] GPUSort project
[short] This probably would be an uneasy fit into generic backend code. Was hoping the GPUSort project might have fleeced/sorted out some issues. [long] Simon Riggs wrote: On Wed, 2006-04-12 at 10:00 -0700, Mischa Sandberg wrote: ... Long answer: we're shipping a server (appliance) product built on stock rackmount hardware, that includes an ATI Rage (8MB) with nothing to do. Much of what the box does is a single cpu-bound process, sorting maillog extracts. The GPU is an asset, even at 8MB; the headwork is in mapping/truncating sort keys down to dense ~32bit prefixes; and in making smooth judgements as to when to give the job to (a) the GPU (b) quicksort (c) a tiny bitonic sort in the SSE2 registers. It sounds like its possible, but it would have to give incredible gains before its worth the effort to make it happen. 8MB of video RAM doesn't score much against 256MB of normal RAM, which is pretty cheap these days. A better comparison is 8MB of video RAM vs 512K of L2 cache. GPU's (also) have faster access (>32GB/s) to RAM than the CPU, using AGP/PCI with no contention. Our product uses Xeons instead of Opterons; the 3GHz CPUs are just slogging, waiting >70% for RAM fetch. The hardware dependency would make this extremely sensitive to change, so effort in this area might not give lasting benefit. As it happens, I'm in favour of making code changes to exploit hardware, but this one is too far for me to encourage anybody to pursue it further. Fair comment. I'm using OpenGL, and looking at Glift, so it's not as hardware-specific as you might think. Other projects at gpgpu.org seem to be able to switch among GPU's. That being said, humbly admit that targetting specific hardware tends to give one tunnel vision. Coding "if all these conditions are true, use the fast algorithm, else do it the normal way" is also messier to extend than a nice clean interface layer :-( Any of this would apply to postgres, if tuplesort.c can tolerate a preprocessing step that looks for special cases, and degrades gracefully into the standard case. For other techniques, I think it can, depending upon the cost of the preprocessing step. But the overall improvement from improving small sorts could well be lost in the noise...so maybe not worth it. Agreed. GPU setup makes sorts <1MB not worth it. Small sorts get a boost from bitonic sort in SSE2, which wires into the bottom of a special-case quicksort, where any subrange of 9..16 elements gets done in xmm registers. I think the preprocessing to test and format keys for such sorts is useful anyway. I was trying to make radix sort usable, and that requires the same key prep. Even if the key prep hits its space limit and says, the input is unsuitable for radix sort, it still makes the normal quicksort faster, since some key prefixes are shorter. -- Engineers think that equations approximate reality. Physicists think that reality approximates the equations. Mathematicians never make the connection. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Get explain output of postgresql in Tables
On Wed, Apr 12, 2006 at 09:45:41AM -0700, Mischa Sandberg wrote: > Jim C. Nasby wrote: > >On Wed, Apr 12, 2006 at 04:53:20PM +0200, Thomas Hallgren wrote: > > > >> > >> > >> > >> > >> > > > > > >Well, the downside is that such a format means explain output is now > >twice as long. But I'd love to see something like that as an option. I'd > >also still like to see an SQL-parseable version as well, since I think > >there's applications for that. > > On the plus side, a complex xml document is an easy read in a browser (IE > or Firefox, either way). Hard to picture the representation in relational > tables, though ... did you have some specific idea for what to do with a > plan in SQL, > once it was parsed? Well, really just about anything you'd want to do with it in an XML format. The advantage of SQL is that you can do it within the database, and you don't have to worry about having something around that can process XML. Some possibilities... Having an SQL format would make it easier to allow for a mode that captures explain or explain analyze output from every query. Turn that mode on, run an application's test suite, and now you have a pretty good idea of how all the queries will run. Or, take a production system and turn that option on for a single connection. Another option is to have any queries that take more than X amount of time store an EXPLAIN of the query. Having this info in machine format would make it easier to write something that sets the various cost estimator values (random_page_cost, etc). The list goes on. Like I said, you could do all these things with XML, you just couldn't easily do them within the database. -- 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 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] GPUSort project
On Wed, 2006-04-12 at 10:00 -0700, Mischa Sandberg wrote: > Martijn van Oosterhout wrote: > > On Tue, Apr 11, 2006 at 04:02:07PM -0700, Mischa Sandberg wrote: > > > >>Anybody on this list hear/opine anything pf the GPUSort project for > >>postgresql? I'm working on a radix-sort subcase for tuplesort, and there > >>are similarities. > >> > >>http://www.andrew.cmu.edu/user/ngm/15-823/project/ > > > > I've heard it meantioned, didn't know they'd got it working. However, > > none of my database servers have a 3D graphics anywhere near the power > > they suggest in the article. > > > > Is this of practical use for run-of-the-mill video cards? > > Short answer: maybe. > > Long answer: we're shipping a server (appliance) product built on stock > rackmount hardware, that includes an ATI Rage (8MB) with nothing to do. Much > of > what the box does is a single cpu-bound process, sorting maillog extracts. > The > GPU is an asset, even at 8MB; the headwork is in mapping/truncating sort keys > down to dense ~32bit prefixes; and in making smooth judgements as to when to > give the job to (a) the GPU (b) quicksort (c) a tiny bitonic sort in the SSE2 > registers. There's been talk for the last few years in academic circles about trying to use graphics APIs and/or specialised hardware to improve various aspects of database technology. It sounds like its possible, but it would have to give incredible gains before its worth the effort to make it happen. 8MB of video RAM doesn't score much against 256MB of normal RAM, which is pretty cheap these days. The hardware dependency would make this extremely sensitive to change, so effort in this area might not give lasting benefit. As it happens, I'm in favour of making code changes to exploit hardware, but this one is too far for me to encourage anybody to pursue it further. > Any of this would apply to postgres, if tuplesort.c can tolerate a > preprocessing > step that looks for special cases, and degrades gracefully into the standard > case. For other techniques, I think it can, depending upon the cost of the preprocessing step. But the overall improvement from improving small sorts could well be lost in the noise...so maybe not worth it. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com/ ---(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] Practical impediment to supporting multiple SSL libraries
On Wed, Apr 12, 2006 at 08:14:58PM +0200, Magnus Hagander wrote: > > Other than DN and CN, what else would people want? > > Issuer (name and certificate), validity dates, basic constraints, key > usage, posslby fingerprint. GnuTLS handles this with just one function: gnutls_x509_crt_get_dn_by_oid( cert, oid, index, raw, &data, &length ) And a whole pile of #defines #define GNUTLS_OID_X520_COUNTRY_NAME"2.5.4.6" #define GNUTLS_OID_X520_ORGANIZATION_NAME "2.5.4.10" #define GNUTLS_OID_X520_ORGANIZATIONAL_UNIT_NAME "2.5.4.11" etc... Which is nice because then end users can code in the attributes they want and we don't have to deal with the endless variations. I don't however know enough to know if this (with a function to get OIDs by index) is sufficient to extract all the information from the certificate. Presumably OpenSSL can do this too... -- 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] Practical impediment to supporting multiple SSL libraries
> > There is a more serious issue here though: if we allow more > than one > > SSL library, what exactly can an application safely do with the > > returned pointer? It strikes me as very dangerous for the app to > > assume it knows which SSL library is underneath libpq. It's not at > > all hard to imagine an app getting an OpenSSL struct pointer and > > trying to pass it to GnuTLS or vice versa. To the extent > that there > > are apps out there that depend on doing something with this > function, > > I think that even contemplating supporting multiple SSL > libraries is a threat. > > The only real way to a solution is to work out why people > want the pointer. So far I've found two reasons: > > - People want to hijack the connection after libpq has set it > up to do their own processing. > > - People want to examine the certificates more closely. > > The first would be easily handled by providing a formal > interface for libpq to hijack the connection with, providing > read/write and maybe a few others. The latter is tricker. > You're invariably going to run into the problem where the app > uses one lib and libpq the other. > > Other than DN and CN, what else would people want? Issuer (name and certificate), validity dates, basic constraints, key usage, posslby fingerprint. //Magnus ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Practical impediment to supporting multiple SSL libraries
On Wed, Apr 12, 2006 at 12:32:01PM -0400, Tom Lane wrote: > There is a more serious issue here though: if we allow more than one SSL > library, what exactly can an application safely do with the returned > pointer? It strikes me as very dangerous for the app to assume it knows > which SSL library is underneath libpq. It's not at all hard to imagine > an app getting an OpenSSL struct pointer and trying to pass it to GnuTLS > or vice versa. To the extent that there are apps out there that depend > on doing something with this function, I think that even contemplating > supporting multiple SSL libraries is a threat. The only real way to a solution is to work out why people want the pointer. So far I've found two reasons: - People want to hijack the connection after libpq has set it up to do their own processing. - People want to examine the certificates more closely. The first would be easily handled by providing a formal interface for libpq to hijack the connection with, providing read/write and maybe a few others. The latter is tricker. You're invariably going to run into the problem where the app uses one lib and libpq the other. Other than DN and CN, what else would people want? -- 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] Practical impediment to supporting multiple SSL libraries
On Wed, Apr 12, 2006 at 01:42:51PM -0400, Stephen Frost wrote: > * Andreas Pflug ([EMAIL PROTECTED]) wrote: > > I wonder if there are apps that actually use the ssl pointer, beyond > > detection of encrypted connections. So interpreting the result as bool > > would be sufficient. > > I'm not sure if there are apps out there which use it for anything but a > bool but there's certainly a potential for apps to want to do things > like get the DN of the remote server... Strangly enough, the SSL code in libpq has stored the peer DN and CN except it doesn't appear to be available to the client... 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] Practical impediment to supporting multiple SSL libraries
* Andreas Pflug ([EMAIL PROTECTED]) wrote: > I wonder if there are apps that actually use the ssl pointer, beyond > detection of encrypted connections. So interpreting the result as bool > would be sufficient. I'm not sure if there are apps out there which use it for anything but a bool but there's certainly a potential for apps to want to do things like get the DN of the remote server... Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Practical impediment to supporting multiple SSL libraries
* Tom Lane ([EMAIL PROTECTED]) wrote: > Martijn van Oosterhout writes: > > 1. Changing it to always return (void*), irrespective of SSL > > ... > > Personally, I'm in favour of 1, because then we can get rid of the > > #include for openssl, so users don't have to have openssl headers > > installed to compile postgresql programs. > > I like that too. I've never been very happy about having libpq-fe.h > depending on USE_SSL. I'm all in favor of dropping the dependency on OpenSSL headers from libpq, just to throw my 2 cents in there. > There is a more serious issue here though: if we allow more than one SSL > library, what exactly can an application safely do with the returned > pointer? It strikes me as very dangerous for the app to assume it knows > which SSL library is underneath libpq. It's not at all hard to imagine > an app getting an OpenSSL struct pointer and trying to pass it to GnuTLS > or vice versa. To the extent that there are apps out there that depend > on doing something with this function, I think that even contemplating > supporting multiple SSL libraries is a threat. I'm afraid the way to do this would probably be to have it return a Postgres-defined structure (without depending on if it's compiled with SSL or not) which then indicates if the connection is SSL-enabled or not and then probably other 'common' information (remote DN, remote CA, ASN.1 formatted certificate perhaps, etc...). Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] plpgsql by default
Eric Lauzon wrote: -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Merlin Moncure Sent: 12 avril 2006 12:22 To: Neil Conway Cc: Tom Lane; David Fetter; Jim C. Nasby; Joshua D. Drake; [EMAIL PROTECTED]; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] plpgsql by default On 4/11/06, Neil Conway <[EMAIL PROTECTED]> wrote: On Tue, 2006-04-11 at 17:20 -0400, Tom Lane wrote: No, I'm saying that having access to a PL renders certain classes of attacks significantly more efficient. A determined attacker with unlimited time may not care, but in the real world, security is relative. That's a fair point. Perhaps a compromise would be to enable pl/pgsql by default, but not grant the USAGE privilege on it. This would allow superusers to define One way to circumvent the hassle of having to create the language is to create the database from a template that has the language , hence semi-default plpgsql handler by "default". On the security side, if you implement strong ACLS on the data manipulation if the database is compromised to a level where a low priviliged user database access is compromised there shouldn't be any danger toward having them using SQL or plpgsql. The dark side of this could be some type of privilege escalation scheme present inside postgresql. As example MS-SQL xp_* stored proc, are a vulnerability vector if the compromised user can execute them. So if by default the attacked application is running as the "postgres" user, what will you do to prevent them from manipulating internal's? :) This is just a little safer than surfing the internet with MSSQL installed and the sa user having no password :-) I wonder if a less-privileged user should be present in the database by default, with some advise to use that user instead of postgres for standard connections. I wouldn't be surprised if >80 % of win32 pgsql installations have a single user only... Regards, Andreas ---(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] plpgsql by default
> -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of > Merlin Moncure > Sent: 12 avril 2006 12:22 > To: Neil Conway > Cc: Tom Lane; David Fetter; Jim C. Nasby; Joshua D. Drake; > [EMAIL PROTECTED]; pgsql-hackers@postgresql.org > Subject: Re: [HACKERS] plpgsql by default > > On 4/11/06, Neil Conway <[EMAIL PROTECTED]> wrote: > > On Tue, 2006-04-11 at 17:20 -0400, Tom Lane wrote: > > > No, I'm saying that having access to a PL renders certain > classes of > > > attacks significantly more efficient. A determined attacker with > > > unlimited time may not care, but in the real world, security is > > > relative. > > > > That's a fair point. > > > > Perhaps a compromise would be to enable pl/pgsql by > default, but not > > grant the USAGE privilege on it. This would allow > superusers to define > One way to circumvent the hassle of having to create the language is to create the database from a template that has the language , hence semi-default plpgsql handler by "default". On the security side, if you implement strong ACLS on the data manipulation if the database is compromised to a level where a low priviliged user database access is compromised there shouldn't be any danger toward having them using SQL or plpgsql. The dark side of this could be some type of privilege escalation scheme present inside postgresql. As example MS-SQL xp_* stored proc, are a vulnerability vector if the compromised user can execute them. So if by default the attacked application is running as the "postgres" user, what will you do to prevent them from manipulating internal's? :) -elz AVERTISSEMENT CONCERNANT LA CONFIDENTIALITE Le present message est a l'usage exclusif du ou des destinataires mentionnes ci-dessus. Son contenu est confidentiel et peut etre assujetti au secret professionnel. Si vous avez recu le present message par erreur, veuillez nous en aviser immediatement et le detruire en vous abstenant d'en faire une copie, d'en divulguer le contenu ou d'y donner suite. CONFIDENTIALITY NOTICE This communication is intended for the exclusive use of the addressee identified above. Its content is confidential and may contain privileged information. If you have received this communication by error, please notify the sender and delete the message without copying or disclosing it. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Practical impediment to supporting multiple SSL libraries
Tom Lane wrote: Martijn van Oosterhout writes: 1. Changing it to always return (void*), irrespective of SSL ... Personally, I'm in favour of 1, because then we can get rid of the #include for openssl, so users don't have to have openssl headers installed to compile postgresql programs. I like that too. I've never been very happy about having libpq-fe.h depending on USE_SSL. There is a more serious issue here though: if we allow more than one SSL library, what exactly can an application safely do with the returned pointer? It strikes me as very dangerous for the app to assume it knows which SSL library is underneath libpq. It's not at all hard to imagine an app getting an OpenSSL struct pointer and trying to pass it to GnuTLS or vice versa. To the extent that there are apps out there that depend on doing something with this function, I think that even contemplating supporting multiple SSL libraries is a threat. I wonder if there are apps that actually use the ssl pointer, beyond detection of encrypted connections. So interpreting the result as bool would be sufficient. Regards, Andreas ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Get explain output of postgresql in Tables
Greg Sabino Mullane wrote: I wonder if it would help much just to change EXPLAIN to indent with something other than spaces? I like that. Maybe even decrease the indenting a little more, and compress some of the inner whitespace (such as the 2 spaces after the operator name) Might it be worth checking how many people (and apps) use EXPLAIN output to drive apps? Our (web) reporting has a paging system for long reports, that depends on getting the row/cost estimate from "EXPLAIN somequery" before actually executing "somequery". (Yep, we have pg_autovacuum run ANALYZE a lot :-) Anybody else out there using explain output in an automated way? -- Engineers think that equations approximate reality. Physicists think that reality approximates the equations. Mathematicians never make the connection. ---(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] GPUSort project
Martijn van Oosterhout wrote: On Tue, Apr 11, 2006 at 04:02:07PM -0700, Mischa Sandberg wrote: Anybody on this list hear/opine anything pf the GPUSort project for postgresql? I'm working on a radix-sort subcase for tuplesort, and there are similarities. http://www.andrew.cmu.edu/user/ngm/15-823/project/ I've heard it meantioned, didn't know they'd got it working. However, none of my database servers have a 3D graphics anywhere near the power they suggest in the article. Is this of practical use for run-of-the-mill video cards? Short answer: maybe. Long answer: we're shipping a server (appliance) product built on stock rackmount hardware, that includes an ATI Rage (8MB) with nothing to do. Much of what the box does is a single cpu-bound process, sorting maillog extracts. The GPU is an asset, even at 8MB; the headwork is in mapping/truncating sort keys down to dense ~32bit prefixes; and in making smooth judgements as to when to give the job to (a) the GPU (b) quicksort (c) a tiny bitonic sort in the SSE2 registers. Any of this would apply to postgres, if tuplesort.c can tolerate a preprocessing step that looks for special cases, and degrades gracefully into the standard case. I'm guessing that there are enough internal sorts (on oid, for example) having only small, memcmp-able sort keys, that this is worth adding in. -- Engineers think that equations approximate reality. Physicists think that reality approximates the equations. Mathematicians never make the connection. ---(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] Get explain output of postgresql in Tables
Jim C. Nasby wrote: On Wed, Apr 12, 2006 at 04:53:20PM +0200, Thomas Hallgren wrote: Well, the downside is that such a format means explain output is now twice as long. But I'd love to see something like that as an option. I'd also still like to see an SQL-parseable version as well, since I think there's applications for that. On the plus side, a complex xml document is an easy read in a browser (IE or Firefox, either way). Hard to picture the representation in relational tables, though ... did you have some specific idea for what to do with a plan in SQL, once it was parsed? -- Engineers think that equations approximate reality. Physicists think that reality approximates the equations. Mathematicians never make the connection. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Practical impediment to supporting multiple SSL libraries
Martijn van Oosterhout writes: > 1. Changing it to always return (void*), irrespective of SSL > ... > Personally, I'm in favour of 1, because then we can get rid of the > #include for openssl, so users don't have to have openssl headers > installed to compile postgresql programs. I like that too. I've never been very happy about having libpq-fe.h depending on USE_SSL. There is a more serious issue here though: if we allow more than one SSL library, what exactly can an application safely do with the returned pointer? It strikes me as very dangerous for the app to assume it knows which SSL library is underneath libpq. It's not at all hard to imagine an app getting an OpenSSL struct pointer and trying to pass it to GnuTLS or vice versa. To the extent that there are apps out there that depend on doing something with this function, I think that even contemplating supporting multiple SSL libraries is a threat. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Practical impediment to supporting multiple SSL libraries
> -Original Message- > From: Martijn van Oosterhout [mailto:[EMAIL PROTECTED] > Sent: 12 April 2006 17:15 > To: Dave Page > Cc: pgsql-hackers@postgresql.org > Subject: Re: [HACKERS] Practical impediment to supporting > multiple SSL libraries > > On Wed, Apr 12, 2006 at 05:03:32PM +0100, Dave Page wrote: > > > > The next version of psqlODBC (that has just gone into CVS tip after > > months of work and debate) uses it, and would break almost > completely > > should it be removed, therefore any backwards incompatible change > > should be avoided imho. And 2 or 4 could cause chaos for > Windows users > > if different DLL builds get mixed up. > > Hmm, may I ask what it uses it for? Just to get information, > or something more substantial? The driver implements all versions of the wire protocol itself, but if libpq is available at runtime (it will dynamically load it on platforms that support it) it can use it for connection setup so features like SSL can be provided easily. I'm still not overly familiar with how it works yet, but I'm sure Hiroshi (CC'd) can provide further details if you need them. Regards, Dave. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] plpgsql by default
On 4/11/06, Neil Conway <[EMAIL PROTECTED]> wrote: > On Tue, 2006-04-11 at 17:20 -0400, Tom Lane wrote: > > No, I'm saying that having access to a PL renders certain classes of > > attacks significantly more efficient. A determined attacker with > > unlimited time may not care, but in the real world, security is > > relative. > > That's a fair point. > > Perhaps a compromise would be to enable pl/pgsql by default, but not > grant the USAGE privilege on it. This would allow superusers to define +1 (+10 if I could, and I'm doing my best not to pontificate about security) merlin ---(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] Get explain output of postgresql in Tables
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 > I wonder if it would help much just to change EXPLAIN to indent with > something other than spaces? I like that. Maybe even decrease the indenting a little more, and compress some of the inner whitespace (such as the 2 spaces after the operator name) One other thing I've done in the past that helps a lot is to simplify the text by using "L" for loops, "W" for width, "C" for cost, and "R" for rows, and even "AT" for "actual time". This ends up saveing an enormous amount of horizontal screen space, and is a really easy intuitive one-time learning curve. Normal verbose way: Sort (cost=11383.82..11383.83 rows=1 width=38) (actual time=18942.712..18942.741 rows=9 loops=1) Sort Key: count(*) -> HashAggregate (cost=11383.80..11383.81 rows=1 width=38) (actual time=18942.581..18942.612 rows=9 loops=1) -> Bitmap Heap Scan on turnstep_mail (cost=134.73..11383.79 rows=1 width=38) (actual time=17085.967..18941.677 rows=193 loops=1) Tom + Greg style: Sort (C=11383.82..11383.83 R=1 W=38) (AT=18942.712..18942.741 R=9 L=1) - -Sort Key: count(*) - -->HashAggregate (C=11383.80..11383.81 R=1 W=38) (AT=18942.581..18942.612 R=9 L=1) - >Bitmap Heap Scan on turnstep_mail (C=134.73..11383.79 R=1 W=38) (AT=17085.967..18941.677 R=193 L=1) I use capital letters as it makes it easier to read, especially for things like the common single loop (L=1 vs. l=1) - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200604121213 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFEPSkQvJuQZxSWSsgRAsc3AKDEWkJR6hHr2/Rgwgk49UNhGVtR6ACgo91Z 7Ck46wiCWoVvGW6V/AR7wAo= =UKnc -END PGP SIGNATURE- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Practical impediment to supporting multiple SSL libraries
On Wed, Apr 12, 2006 at 05:03:32PM +0100, Dave Page wrote: > The next version of psqlODBC (that has just gone into CVS tip after > months of work and debate) uses it, and would break almost completely > should it be removed, therefore any backwards incompatible change should > be avoided imho. And 2 or 4 could cause chaos for Windows users if > different DLL builds get mixed up. Hmm, may I ask what it uses it for? Just to get information, or something more substantial? Thanks in advance, -- 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] Practical impediment to supporting multiple SSL libraries
> -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of > Martijn van Oosterhout > Sent: 12 April 2006 16:48 > To: pgsql-hackers@postgresql.org > Subject: [HACKERS] Practical impediment to supporting > multiple SSL libraries > > Just quickly going through what might be needed to support > multiple SSL libraries revealed one big problem in libpq-fe.h. > > #ifdef USE_SSL > /* Get the SSL structure associated with a connection */ > extern SSL *PQgetssl(PGconn *conn); #else extern void > *PQgetssl(PGconn *conn); #endif > > The return type of the function changes depending on whether > SSL is compiled in or not. :( So, libpq exposes to its users > the underlying SSL library, which seems wrong. Now, options include: > > 1. Changing it to always return (void*), irrespective of SSL > 2. Creating a PGsslcontext type that varies depending on what > library you use (or not). > 3. Removing the function entirely because the only user > appears to be psql (in tree anyway). > 4. Only declare the function if the user has #included > openssl themselves. > > Or alternatively we could do nothing because: > > 5. It's not a problem > 6. It's a backward incompatable change The next version of psqlODBC (that has just gone into CVS tip after months of work and debate) uses it, and would break almost completely should it be removed, therefore any backwards incompatible change should be avoided imho. And 2 or 4 could cause chaos for Windows users if different DLL builds get mixed up. Regards, Dave. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] plpgsql by default
On Wed, Apr 12, 2006 at 12:32:52PM +0200, Peter Eisentraut wrote: > Am Dienstag, 11. April 2006 23:20 schrieb Tom Lane: > > In the end it's only one small component of security, but any > > security expert will tell you that you take all the layers of > > security that you can get. > > I think what the security experts are saying is that you need a > thorough evaluation of assets, attackers, risks, and > countermeasures, and I don't see that here. Exactly. One security expert you may have heard of, Bruce Schneier, has laid out a 5-step process, and we haven't gotten to step 1 yet where the proposal is "turn PL/PgSQL off by default." Bruce Schneier's 5-Step Security Evaluation 1. What assets are you trying to protect? 2. What are the risks to those assets? 3. How well does the security solution mitigate those risks? 4. What other risks does the security solution cause? 5. What costs and tradeoffs does the security solution impose? Let's start with step 1 and go forward from there. Cheers, D -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] Practical impediment to supporting multiple SSL libraries
Just quickly going through what might be needed to support multiple SSL libraries revealed one big problem in libpq-fe.h. #ifdef USE_SSL /* Get the SSL structure associated with a connection */ extern SSL *PQgetssl(PGconn *conn); #else extern void *PQgetssl(PGconn *conn); #endif The return type of the function changes depending on whether SSL is compiled in or not. :( So, libpq exposes to its users the underlying SSL library, which seems wrong. Now, options include: 1. Changing it to always return (void*), irrespective of SSL 2. Creating a PGsslcontext type that varies depending on what library you use (or not). 3. Removing the function entirely because the only user appears to be psql (in tree anyway). 4. Only declare the function if the user has #included openssl themselves. Or alternatively we could do nothing because: 5. It's not a problem 6. It's a backward incompatable change Personally, I'm in favour of 1, because then we can get rid of the #include for openssl, so users don't have to have openssl headers installed to compile postgresql programs. Options 2, 3 and 4 have varying levels of evilness attached. However, I can see how 5 or 6 might be attractive. Thoughts? -- 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] Get explain output of postgresql in Tables
Ühel kenal päeval, K, 2006-04-12 kell 10:29, kirjutas Jim C. Nasby: > On Wed, Apr 12, 2006 at 04:53:20PM +0200, Thomas Hallgren wrote: > > > > > > > > > > > > Well, the downside is that such a format means explain output is now > twice as long. You can place end tags differently > But I'd love to see something like that as an option. Me too > I'd also still like to see an SQL-parseable version as well, since I think > there's applications for that. > > As for those who can't manage to post EXPLAIN ANALYZE to the list; as > long as ANALYZE isn't the default I don't see how making a less > human-readable version the default will solve anything, because we'll > still perpetually be asking people for the output of EXPLAIN ANALYZE. If > we want to increase the number of people who provide useful information > in initial performance questions, the answer is to make the information > about what to submit more prominent. We could also default to printing a NOTICE at the end of EXPLAIN, which tells users thus: "If you plan to post this output to pgsql-hackers list, you better post result of EXPLAIN ANALYSE" :P Hannu ---(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] Get explain output of postgresql in Tables
On Wed, Apr 12, 2006 at 04:53:20PM +0200, Thomas Hallgren wrote: > > > > > Well, the downside is that such a format means explain output is now twice as long. But I'd love to see something like that as an option. I'd also still like to see an SQL-parseable version as well, since I think there's applications for that. As for those who can't manage to post EXPLAIN ANALYZE to the list; as long as ANALYZE isn't the default I don't see how making a less human-readable version the default will solve anything, because we'll still perpetually be asking people for the output of EXPLAIN ANALYZE. If we want to increase the number of people who provide useful information in initial performance questions, the answer is to make the information about what to submit more prominent. -- 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 6: explain analyze is your friend
Re: [HACKERS] Get explain output of postgresql in Tables
Richard Huxton wrote: Tom Lane wrote: I dislike the thought of encouraging people to post stuff in a not-easily-readable format. They won't do it anyway, if it's not default; look how we still can't get people to send EXPLAIN ANALYZE output the first time. It certainly needs to be one format for both purposes. One idea that comes to mind is to work up some trivial little script that undoes the more common forms of cut-and-paste damage. I wonder if it would help much just to change EXPLAIN to indent with something other than spaces? Maybe instead of Nested Loop (cost=1.06..40.43 rows=5 width=244) Join Filter: (public.tenk1.unique2 = int4_tbl.f1) -> HashAggregate (cost=1.06..1.11 rows=5 width=4) print Nested Loop (cost=1.06..40.43 rows=5 width=244) --Join Filter: (public.tenk1.unique2 = int4_tbl.f1) > HashAggregate (cost=1.06..1.11 rows=5 width=4) Not sure what would look nice, but this would at least remove the hazard from stuff that thinks whitespace isn't significant. That's the sort of thing I was thinking of, or even something like: 1> Nested Loop ... 1.1> Join Filter... 1.1.1> HashAggregate... 1.2> etc Why not go all the way. Here's the above using Satoshi's suggestion: Easy to copy/paste and whitespace doesn't matter. Easy to read (well, to some at least) and can be even easier if you have access to an XML viewer. Regards, Thomas Hallgren ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] plpgsql by default
> > I wonder if Oracle ever recommended disabling PL/SQL (not to > mention MS Transact-SQL)... > Don't know abiout Oracle, but you can't disable Transact-SQL in SQL Server 7.0 or 2000 (don't know about 2003^h5) because Enterprise Manager and sp_help* require it. And +1 for not installing plpgsql by default. I just had to CREATELANG on 20-odd servers scattered across the US, and it was no big thing. Just rolled it out with the upgrade that needed it. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] plpgsql by default
Dave Page wrote: Keeping PostgreSQL as secure as possible out of the box pretty much requires us to do the same in my mind - if an major feature such as pl/pgsql is easy for the user to enable should they want it, then it should be disabled by default to minimise the number of attack vectors for all those users that do not want it. I wonder if Oracle ever recommended disabling PL/SQL (not to mention MS Transact-SQL)... Regards, Andreas ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Get explain output of postgresql in Tables
Tom Lane wrote: I dislike the thought of encouraging people to post stuff in a not-easily-readable format. They won't do it anyway, if it's not default; look how we still can't get people to send EXPLAIN ANALYZE output the first time. It certainly needs to be one format for both purposes. One idea that comes to mind is to work up some trivial little script that undoes the more common forms of cut-and-paste damage. I wonder if it would help much just to change EXPLAIN to indent with something other than spaces? Maybe instead of Nested Loop (cost=1.06..40.43 rows=5 width=244) Join Filter: (public.tenk1.unique2 = int4_tbl.f1) -> HashAggregate (cost=1.06..1.11 rows=5 width=4) print Nested Loop (cost=1.06..40.43 rows=5 width=244) --Join Filter: (public.tenk1.unique2 = int4_tbl.f1) > HashAggregate (cost=1.06..1.11 rows=5 width=4) Not sure what would look nice, but this would at least remove the hazard from stuff that thinks whitespace isn't significant. That's the sort of thing I was thinking of, or even something like: 1> Nested Loop ... 1.1> Join Filter... 1.1.1> HashAggregate... 1.2> etc -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Get explain output of postgresql in Tables
Richard Huxton writes: > Jim C. Nasby wrote: >> Actually, I've been wondering about better ways to handle this. One >> thought is to come up with a non-human readable format that could easily >> be cut and pasted into a website that would then provide something easy >> to understand. Ideally that website could also produce graphical output >> like pgAdmin does, since that makes it trivially easy to see what the >> 'critical path' is. > I actually started putting something like this together about a year > ago, but the majority of my time was spent reformatting the text rather > than reading the explain. I dislike the thought of encouraging people to post stuff in a not-easily-readable format. They won't do it anyway, if it's not default; look how we still can't get people to send EXPLAIN ANALYZE output the first time. One idea that comes to mind is to work up some trivial little script that undoes the more common forms of cut-and-paste damage. I wonder if it would help much just to change EXPLAIN to indent with something other than spaces? Maybe instead of Nested Loop (cost=1.06..40.43 rows=5 width=244) Join Filter: (public.tenk1.unique2 = int4_tbl.f1) -> HashAggregate (cost=1.06..1.11 rows=5 width=4) print Nested Loop (cost=1.06..40.43 rows=5 width=244) --Join Filter: (public.tenk1.unique2 = int4_tbl.f1) > HashAggregate (cost=1.06..1.11 rows=5 width=4) Not sure what would look nice, but this would at least remove the hazard from stuff that thinks whitespace isn't significant. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] plpgsql by default
> -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of > Peter Eisentraut > Sent: 12 April 2006 11:33 > To: pgsql-hackers@postgresql.org > Cc: Tom Lane; David Fetter; Jim C. Nasby; Joshua D. Drake; > [EMAIL PROTECTED] > Subject: Re: [HACKERS] plpgsql by default > > Am Dienstag, 11. April 2006 23:20 schrieb Tom Lane: > > In the end it's only one small component of security, but > any security > > expert will tell you that you take all the layers of > security that you > > can get. > > I think what the security experts are saying is that you need > a thorough evaluation of assets, attackers, risks, and > countermeasures, and I don't see that here. Regardless of any evaluations, or any proven or thoretical risks in any given code it's Basic Security 101 stuff to disable/remove anything that is not required in a system to immediately reduce the number of potential attacks that could be made. Microsoft are the classic example - they enabled pretty much everything by default in Windows leaving it vulnerable to attack through services many people weren't using (NetBios on a single home user machine for example). You install a modern version of Windows now though and you'll see virtually every network service is disabled, or even uninstalled by default, leaving it up the user to install as required. In addition of course, those services are still subject to the normal bug fixes and updates for those users that do require them. Keeping PostgreSQL as secure as possible out of the box pretty much requires us to do the same in my mind - if an major feature such as pl/pgsql is easy for the user to enable should they want it, then it should be disabled by default to minimise the number of attack vectors for all those users that do not want it. Regards, Dave ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] plpgsql by default
Am Dienstag, 11. April 2006 23:20 schrieb Tom Lane: > In the end it's only one small component of security, but any security > expert will tell you that you take all the layers of security that you > can get. I think what the security experts are saying is that you need a thorough evaluation of assets, attackers, risks, and countermeasures, and I don't see that here. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] RH9 postgresql 8.0.7 rpm
Hi Gaetano, On Tue, 2006-04-11 at 18:31 +0200, Gaetano Mendola wrote: > I'm trying to build the rpms for RH9, > I downloaded the srpm for RH9 but I'm stuck on these errors: > > Attempt a: > > # rpmbuild --rebuild postgresql-8.0.7-1PGDG.src.rpm > Installing postgresql-8.0.7-1PGDG.src.rpm > error: Failed build dependencies: > tcl-devel is needed by postgresql-8.0.7-1PGDG > > why tcl-devel on rh9 version? tcl-devel doesn't exist on rh9 ) We use only one spec file for all platforms. If you are using Red Hat 9, you should consider using the macros that are enabled for RH9 (build89 and/or build9 in the spec file). > Attempt b: > # rpmbuild --nodeps --rebuild postgresql-8.0.7-1PGDG.src.rpm > > checking krb5.h presence... no > checking for krb5.h... no > configure: error: header file is required for Kerberos 5 > error: Bad exit status from /var/tmp/rpm-tmp.73067 (%build) I think rebuilding with --define 'buildrhel3 1' will work here. > ok no kerberos now: > > Attempt c: > # rpmbuild --nodeps --rebuild --define 'kerberos 0' > postgresql-8.0.7-1PGDG.src.rpm > . > checking for zlib.h... yes > checking openssl/ssl.h usability... no > checking openssl/ssl.h presence... no > checking for openssl/ssl.h... no > configure: error: header file is required for OpenSSL > error: Bad exit status from /var/tmp/rpm-tmp.3109 (%build) > > actually I have that file: > > # locate openssl/ssl.h > /usr/include/openssl/ssl.h I have no idea about this and I can't remember right now how I was building RH9 RPMs ... If you provide me a RH9 box, I can help you. Regards, -- 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
Re: [HACKERS] [PATCHES] schema-qualified SET CONSTRAINTS
> >> The attached patch allows SET CONSTRAINTS to take a schema qualified > >> constraint name (myschema.t1_fk_t2) and when given a bare constraint name > >> it uses the search_path to determine the matching constraint instead of > >> the previous behavior of disabling all identically named constraints. > > > > This patch seems egregiously non backwards compatible :-(. > > Yes, it does change the existing behavior, but "egregiously"? How many > applications intentionally defer constraints in multiple schemas at once? intentionally defer "specifically named" constraints in multiple schemas (The default application would imho eighter defer all, or a specific constraint) > Not many. I would guess the more likely situation is that these > applications don't even realize that they are deferring more than one > constraint when it happens. I agree. I think the new behavior is more intuitive, and would even argue the old behavior gets it wrong. Andreas ---(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] Get explain output of postgresql in Tables
Jim C. Nasby wrote: On Mon, Apr 10, 2006 at 10:44:15AM +0100, Richard Huxton wrote: Bruce Momjian wrote: * Allow EXPLAIN output to be more easily processed by scripts Can I request an extension/additional point? * Design EXPLAIN output to survive cut & paste on mailing-lists Being able to paste into a web-form and get something readable formatted back would be very useful on the lists. Sometimes it takes me longer to reformat the explain than it does to understand the problem. Actually, I've been wondering about better ways to handle this. One thought is to come up with a non-human readable format that could easily be cut and pasted into a website that would then provide something easy to understand. Ideally that website could also produce graphical output like pgAdmin does, since that makes it trivially easy to see what the 'critical path' is. I actually started putting something like this together about a year ago, but the majority of my time was spent reformatting the text rather than reading the explain. I've still got a simple perl script that just looks for the most costly steps in an explain and prints their line-number. Lots of false positives but it helps to give a starting point for investigations. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
