Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?
On Mon, Oct 31, 2011 at 09:14:48AM -0400, Andrew Dunstan wrote: The fact is that if you have 100 columns and want 95 of them, it's very tedious to have to specify them all, especially for ad hoc queries where the house SQL standards really don't matter that much. It's made more tedious by the fact that there is no real help in constructing the query. This gets particularly bad with views, which developers often seem to stuff with every available column that might be needed by some query instead of creating views tailored to particular queries. Not long ago annoyance with this prompted my to write a little utility function that would give me a query with all the columns specified so I could cut and paste it, and delete the columns I didn't want. (Another advantage is that the result is guaranteed typo free, which my typing certainly is not.) See https://gist.github.com/818490. It's far from perfect, but I still find myself using it several times a month, mainly for the very purpose intended by this suggested feature. As I do the ad hoc query thing more than I'd like to admit, I think there's a place for some form of negation for *. A workaround similar to what you describe here would be to add special tab completion to psql that would expand * to the full list (probably on double tab ...) Ross -- Ross Reedstrom, Ph.D. reeds...@rice.edu Systems Engineer Admin, Research Scientistphone: 713-348-6166 Connexions http://cnx.orgfax: 713-348-3665 Rice University MS-375, Houston, TX 77005 GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] IDLE in transaction introspection
On Tue, Nov 01, 2011 at 10:13:52AM -0400, Andrew Dunstan wrote: On 11/01/2011 09:52 AM, Tom Lane wrote: Simon Riggssi...@2ndquadrant.com writes: Why not leave it exactly as it is, and add a previous_query column? That gives you exactly what you need without breaking anything. That would cost twice as much shared memory for query strings, and twice as much time to update the strings, for what seems pretty marginal value. I'm for just redefining the query field as current or last query. +1 I could go either way on whether to rename it. Rename it please. current_query will just be wrong. I'd be inclined just to call it query or query_string and leave it to the docs to define the exact semantics. +1 on providing the most-recent-query info +1 on the state/query split as a means +1 rename from current_query (i.e. break it) personalbikeshedcolor: query_string Personal opinion on backwards compatability matches Robert's: things that affect admin functionality are less of an issue than those that impact user (i.e. coder) SQL. And definitely break it: I may chose to fix it by bodging in a view for the old behavior myself, but that's my choice. Perhaps provide an example view def in change notes if you really want to. For myself, when making fixes to monitor scripts for this type of change, my reaction is probably Yes, finally, I'm not regexing on the d*mn query string anymore! Ross P.S. though apparently it doesn't bother me enough to create and submit a patch myself. :-( -- Ross Reedstrom, Ph.D. reeds...@rice.edu Systems Engineer Admin, Research Scientistphone: 713-348-6166 Connexions http://cnx.orgfax: 713-348-3665 Rice University MS-375, Houston, TX 77005 GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] sha1, sha2 functions into core?
On Fri, Sep 02, 2011 at 02:05:45PM -0500, k...@rice.edu wrote: On Fri, Sep 02, 2011 at 09:54:07PM +0300, Peter Eisentraut wrote: On ons, 2011-08-31 at 13:12 -0500, Ross J. Reedstrom wrote: Hmm, this thread seems to have petered out without a conclusion. Just wanted to comment that there _are_ non-password storage uses for these digests: I use them in a context of storing large files in a bytea column, as a means to doing data deduplication, and avoiding pushing files from clients to server and back. But I suppose you don't need the hash function in the database system for that. It is very useful to have the same hash function used internally by PostgreSQL exposed externally. I know you can get the code and add an equivalent one of your own... Thanks for the support Ken, but Peter's right: the only backend use in my particular case is to let the backend do the hash calc during bulk loads: in the production code path, having the hash in two places doesn't save any work, since the client code has to calculate the hash in order to test for its existence in the backend. I suppose if the network cost was negligable, I could just push the files anyway, and have a before-insert trigger calculate the hash and do the dedup: then it'd be hidden in the backend completely. But as is, I can do all the work in the client. Ross -- Ross Reedstrom, Ph.D. reeds...@rice.edu Systems Engineer Admin, Research Scientistphone: 713-348-6166 Connexions http://cnx.orgfax: 713-348-3665 Rice University MS-375, Houston, TX 77005 GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] sha1, sha2 functions into core?
On Fri, Aug 12, 2011 at 10:14:58PM +0300, Marko Kreen wrote: On Thu, Aug 11, 2011 at 5:46 PM, Tom Lane t...@sss.pgh.pa.us wrote: Marko Kreen mark...@gmail.com writes: On Wed, Aug 10, 2011 at 9:19 PM, Tom Lane t...@sss.pgh.pa.us wrote: ... which this approach would create, because digest() isn't restricted to just those algorithms. I think it'd be better to just invent two new functions, which also avoids issues for applications that currently expect the digest functions to be installed in pgcrypto's schema. I would suggest digest() with fixed list of algorithms: md5, sha1, sha2. The uncommon/obsolete algorithms that can be used from digest() if compiled with openssl, are not something we need to worry over. In fact we have never supported them, as no testing has been done. Hmm ... they may be untested by us, but I feel sure that if we remove that functionality from pgcrypto, *somebody* is gonna complain. If you dont want to break digest() but do not want such behaviour in core, we could go with hash(data, algo) that has fixed number of digests, but also couple non-cryptographic hashes like crc32, lookup2/3. This would also fix the problem of people using hashtext() in user code. Hmm, this thread seems to have petered out without a conclusion. Just wanted to comment that there _are_ non-password storage uses for these digests: I use them in a context of storing large files in a bytea column, as a means to doing data deduplication, and avoiding pushing files from clients to server and back. Ross -- Ross Reedstrom, Ph.D. reeds...@rice.edu Systems Engineer Admin, Research Scientistphone: 713-348-6166 Connexions http://cnx.orgfax: 713-348-3665 Rice University MS-375, Houston, TX 77005 GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Fwd: Keywords in pg_hba.conf should be field-specific
On Tue, Jun 21, 2011 at 10:15:50AM -0400, Alvaro Herrera wrote:
Excerpts from Pavel Stehule's message of mar jun 21 10:04:26 -0400 2011:
2011/6/21 Alvaro Herrera alvhe...@commandprompt.com:
Excerpts from Pavel Stehule's message of mar jun 21 00:59:44 -0400 2011:
yes - it has a sense. Quoting changes sense from keyword to literal.
But then I see a significant inconsistency - every know keywords
should be only tokens.
else if (strcmp(token, pamservice) == 0)
- {
- REQUIRE_AUTH_OPTION(uaPAM, pamservice, pam);
- parsedline-pamservice = pstrdup(c);
- }
because pamservice - is known keyword, but 'pamservice' is some
literal without any mean. You should to use a makro token_is_keyword
more often.
Yeah, I wondered about this too (same with auth types, i.e. do we accept
quoted hostssl and so on or should that by rejected?). I opted for
leaving it alone, but maybe this needs to be fixed. (Now that I think
about it, what we should do first is verify whether it works with quotes
in the unpatched code).
I tested it and it works: This line
local @dbs +b trust
is accepted and it works in the unpatched code. I don't think we want
to break people's existing pg_hba.conf files for no reason. I doubt
that many people are using pg_hba.conf tokens with quotes, mind you, but
there might be some ...
In any case, if people here thinks we should tighten this, it's easy to
do on top of this patch by changing the strcmp() calls to
token_is_keyword, as you say. Let's not burden this patch with the
responsibility of doing so, because that's likely to get it punted.
Hmm, would it be possible to add some deprecation warnings for this case
without making the code too messy? Perhaps with a macro
token_should_be_keyword. That's the usual path to tightening syntax.
Ross
--
Ross Reedstrom, Ph.D. reeds...@rice.edu
Systems Engineer Admin, Research Scientistphone: 713-348-6166
Connexions http://cnx.orgfax: 713-348-3665
Rice University MS-375, Houston, TX 77005
GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Boolean operators without commutators vs. ALL/ANY
On Fri, Jun 17, 2011 at 10:20:04AM -0400, Alvaro Herrera wrote: Excerpts from Florian Pflug's message of vie jun 17 10:03:56 -0400 2011: How is that worse than the situation with =~ and ~=? With =~ it is to the right, with ~= it is to the left. To throw my user opinion into this ring (as a long time user of regexes in many different systems) I've always taken the ~ to be short hand for the 'approximately' notation (a squiggly equals) which has good semantic match in my mind: a regex match is sort of a fuzzy equality. With that model, the suggested pair is fairly mnemonic - the 'fuzzy' part i(the pattern) is next to the squiggles, the 'concrete' part goes by the equals. I have sometimes needed to look up which is which on ~ and ~~. which has no such directionality, so yeah, no hinting there. Ross -- Ross Reedstrom, Ph.D. reeds...@rice.edu Systems Engineer Admin, Research Scientistphone: 713-348-6166 Connexions http://cnx.orgfax: 713-348-3665 Rice University MS-375, Houston, TX 77005 GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Boolean operators without commutators vs. ALL/ANY
On Fri, Jun 17, 2011 at 05:21:10PM +0200, Florian Pflug wrote: On Jun17, 2011, at 17:15 , Ross J. Reedstrom wrote: On Fri, Jun 17, 2011 at 10:20:04AM -0400, Alvaro Herrera wrote: Excerpts from Florian Pflug's message of vie jun 17 10:03:56 -0400 2011: How is that worse than the situation with =~ and ~=? With =~ it is to the right, with ~= it is to the left. To throw my user opinion into this ring (as a long time user of regexes in many different systems) I've always taken the ~ to be short hand for the 'approximately' notation (a squiggly equals) which has good semantic match in my mind: a regex match is sort of a fuzzy equality. With that model, the suggested pair is fairly mnemonic - the 'fuzzy' part i(the pattern) is next to the squiggles, the 'concrete' part goes by the equals. Hey, that's my mnemonic device! ;-) Ah, good, so since this is almost mathematics, and we have two instances, that's a proof then. :-) Ross -- Ross Reedstrom, Ph.D. reeds...@rice.edu Systems Engineer Admin, Research Scientistphone: 713-348-6166 Connexions http://cnx.orgfax: 713-348-3665 Rice University MS-375, Houston, TX 77005 GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade using appname to lock out other users
On Thu, Jun 16, 2011 at 09:48:12AM -0400, Bruce Momjian wrote: Tom Lane wrote: Ross J. Reedstrom reeds...@rice.edu writes: As an operations guy, the idea of an upgrade using a random, non-repeatable port selection gives me the hebejeebees. Yeah, I agree. The latest version of the patch doesn't appear to have any random component to it, though --- it just expects the user to provide port numbers as switches. Oh, you wanted pg_upgrade to pick a random port number? I can do that, but how would it check to see it is unused? Oh, no! Lost in translation - randomness in this context would be bad. Heebee-jeebees (usual spelling, I guess) (idiom) used to describe a feeling of anxiety, apprehension, depression or illness http://en.wikipedia.org/wiki/Heebie-jeebies Ross -- Ross Reedstrom, Ph.D. reeds...@rice.edu Systems Engineer Admin, Research Scientistphone: 713-348-6166 Connexions http://cnx.orgfax: 713-348-3665 Rice University MS-375, Houston, TX 77005 GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] FOREIGN TABLE doc fix
Right, but I think he needs the it's not easy, here's the whole workflow overview first. Ross -- Ross Reedstrom, Ph.D. reeds...@rice.edu Systems Engineer Admin, Research Scientistphone: 713-348-6166 Connexions http://cnx.orgfax: 713-348-3665 Rice University MS-375, Houston, TX 77005 GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE On Mon, Jun 13, 2011 at 04:54:13PM +0100, Dave Page wrote: On Mon, Jun 13, 2011 at 4:38 PM, Andrew Dunstan and...@dunslane.net wrote: On 06/13/2011 10:25 AM, Dave Page wrote: Don't hold your breath. We'll probably be making enough changes in the FDW infrastructure (particularly planner support) that making an FDW work on both 9.1 and 9.2 would be an exercise in frustration, if it's even possible. Oh joy. There's a GSoC student working on 2 non-trivial FDW's right now, and I have a couple I've been working on. If we're going to make the API incompatible to that extent, we might as well not bother :-( If nobody bothers then there won't be any experience on which to base a stable API. In particular, I think it's crucial that we get working FDWs for MySQL, SQLServer and Oracle ASAP. Yeah - MySQL is one of the ones I've been hacking on. It's hard to be motivated if its going to need a complete rewrite within a year though. I'll still have to work on it, as I've committed to giving talks on it, but others might not bother to even start. I think PostgreSQL has a better track record (especially recently) than most open source projects at supporting the shared incremental creation and improvement of first-class features. Yes, getting the first cut of FDW in place was hard: now it's time for users of that feature to take the leap of faith and write some code. The faith bit is that others _will_ come forward to help with the rewrites necessary to make it work (or work better) for their use cases. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade using appname to lock out other users
On Wed, Jun 15, 2011 at 09:14:16PM -0400, Stephen Frost wrote: Bruce, * Bruce Momjian (br...@momjian.us) wrote: I have researched this and need feedback. In general, I like the whole idea of using random/special ports for the duration of the upgrade. I agree that we need to keep the ability to check the existing clusters. My gut feeling is this: keep the existing port options just as they are, so --check works just fine, etc. Use *only* long-options for the ports to use during the actual upgrade and discourage their use- we want people to let a random couple of ports be used during the upgrade to minimize the risk of someone connecting to one of the systems. Obvioulsy, there may be special cases where that's not an option, but I don't think we need to make it easy nor do I think we need to have a short option for it. As an operations guy, the idea of an upgrade using a random, non-repeatable port selection gives me the hebejeebees. Mr. Murphy will com knocking, sooner or later, with the server picking a port that just happens to be available right now, because it's service is restarting, or is under inet control. Ross -- Ross Reedstrom, Ph.D. reeds...@rice.edu Systems Engineer Admin, Research Scientistphone: 713-348-6166 Connexions http://cnx.orgfax: 713-348-3665 Rice University MS-375, Houston, TX 77005 GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Range Types and extensions
On Mon, Jun 06, 2011 at 12:53:49PM -0400, Robert Haas wrote: I don't have clear feeling on this question in general, but if we're going to break this up into pieces, it's important that they be logical pieces. Putting half the feature in core and half into an extension just because we can will simplify complicate code maintenance to no good end. The snowball effect is something to avoid, and we need to watch out for that, but if the upshot of putting part of it in core is that the core code can no longer be understood or maintained because it depends heavily on a bunch of non-core code, that's not helpful. And concretely, code paths that cannot be exercised easily from core-only code will not get regression tested, and will therefore rot. Ross -- Ross Reedstrom, Ph.D. reeds...@rice.edu Systems Engineer Admin, Research Scientistphone: 713-348-6166 Connexions http://cnx.orgfax: 713-348-3665 Rice University MS-375, Houston, TX 77005 GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Domains versus polymorphic functions, redux
On Fri, Jun 03, 2011 at 11:22:34AM -0400, Robert Haas wrote: On Fri, Jun 3, 2011 at 1:14 AM, Noah Misch n...@leadboat.com wrote: No, there's no need to do that. The domain is an array, not merely something that can be coerced to an array. Therefore, it can be chosen as the polymorphic type directly. Indeed, all released versions do this. Well, as Bill Clinton once said, it depends on what the meaning of the word 'is' is. I think of array types in PostgreSQL as meaning the types whose monikers end in a pair of square brackets. We don't in general have the ability to create a type that behaves like another type. In particular, you can't create a user-defined type that is an array in the same way that a domain-over-array is an array. If we had some kind of type interface facility that might be possible, but we don't. Early on in this thread, one of the users of domains-over-array-type mentioned that he really didn't want to use them that way, he'd be perfectly happy with array-over-domain: i.e.: mydomain[]. How does that impact all this at the rhetorical level under discussion? Ross -- Ross Reedstrom, Ph.D. reeds...@rice.edu Systems Engineer Admin, Research Scientistphone: 713-348-6166 Connexions http://cnx.orgfax: 713-348-3665 Rice University MS-375, Houston, TX 77005 GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] BLOB support
On Thu, Jun 02, 2011 at 01:43:16PM -0400, Tom Lane wrote: =?utf-8?q?Rados=C5=82aw_Smogura?= rsmog...@softperience.eu writes: Tom Lane t...@sss.pgh.pa.us Thursday 02 of June 2011 16:42:42 Yes. I think the appropriate problem statement is provide streaming access to large field values, as an alternative to just fetching/storing the entire value at once. I see no good reason to import the entire messy notion of LOBS/CLOBS. (The fact that other databases have done it is not a good reason.) In context of LOBs streaming is resolved... I use current LO functionallity (so driver may be able to read LOBs as psql \lo_export does it or using COPY subprotocol) and client should get just LO's id. Just to be clear: I do not want to expose a concept of object IDs for field values in the first place. All of the problems you enumerate stem from the idea that LOBs ought to be a distinct kind of field, and I don't buy that. I think you're saying no OIDs exposed to the SQL i.e. actually stored in a field and returned by a SELECT? (Which seems to be the proposal). As I mentioned recently on another list, I've wrapped a block-oriented streaming interface over bytea in python for a web app, specifically to deal with the latency and memory footprint issues of storing 'largish' files directly in the db. I find that with a 64K blocksize, latency is 'good enough' and substr() seems to be constant time for a given size, no matter what part of the bytea value I'm fetching: toast does a fine job of random access. I was musing about providing a way to use the existing client lo streaming interface (rather than the backend bits) for this type of access. The thing called an OID in the client interface is really just a nonce to tell the backend what data to send. With a single generator function: SELECT CASE WHEN is_lo THEN my_loid ELSE make_lo_oid(my_bytea) END FROM my_file_table WHERE id = 34534; Then plugging that back into the lo interface from the client side, would let me use bytea as I currently do for files under 1GB, lo for larger, and gain client side streaming that is transparent to the storage of that particular value. Admittedly, application software would still need to know how to _store_ different values, and manage large objects, with all the pain that entails. But there's some gain in unifying the reading part. Hard to not call it an oid, since that's what the client libraries already document it as (at least, python does) Ross -- Ross Reedstrom, Ph.D. reeds...@rice.edu Systems Engineer Admin, Research Scientistphone: 713-348-6166 Connexions http://cnx.orgfax: 713-348-3665 Rice University MS-375, Houston, TX 77005 GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PERFORM] Hash Anti Join performance degradation
On Wed, Jun 01, 2011 at 04:58:36PM -0400, Robert Haas wrote: On Wed, Jun 1, 2011 at 4:47 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: I guess the real issue here is that m1.id m2.id has to be evaluated as a filter condition rather than a join qual. Well, if you can invent an optimized join technique that works for inequalities, go for it ... but I think you should get at least a PhD thesis out of that. Sounds good, except that so far NOT getting a PhD seems like a much better financial prospect. :-) Yeah, last time I heard of some Uni being so impressed by independent work that they just sort of handed out a Ph.D. it involved a Swiss patent clerk ... Ross -- Ross Reedstrom, Ph.D. reeds...@rice.edu Systems Engineer Admin, Research Scientistphone: 713-348-6166 Connexions http://cnx.orgfax: 713-348-3665 Rice University MS-375, Houston, TX 77005 GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Bug in XPATH() if expression returns a scalar value
On Tue, May 31, 2011 at 04:19:29PM +0200, Florian Pflug wrote: Sorry for the self-reply but I figured it'd be helpful to add information that I discovered only after my initial post. On May30, 2011, at 15:17 , Florian Pflug wrote: The XPath expression 'name(/*)', for example, is supposed to return 'root' when applied to the XML fragment 'rootnested/nested//root'. Postgres, however, currently returns an empty array. In the mean while, I've discovered that this was discussed previously about a year ago here: http://archives.postgresql.org/pgsql-general/2010-07/msg00355.php The basic confusion seems to be whether XPATH() is supposed to work on everything that http://www.w3.org/TR/xpath/ consideres to be an Expression, or only on what that document calls a Location Path. The difference is basically that Location Paths server purely as predicates, i.e. *select* a subset of nodes from an XML fragment, while Expressions can produce node sets *or* arbitrary scalar values (boolean, numeric or string). According to the thread from last summer, XLST handles this by defining *two* constructs which evaluate XPath expressions, one for those which return node sets (xsl:template match=...) and one for those which return scalar values (xsl:value-of select=...). My patch makes XPATH() work for both nodset-returning *and* scalar-value-returning expressions. This has the advantage of being simpler, but it does force the scalar values produced by an XPath expression to be valid XML fragments. For boolean and numeric values this isn't a problem, but it does limit what you can do with string-returning XPath expressions. If people deem this to be a problem, we could instead add a separate function XPATH_VALUE() that returns VARCHAR, and make people use that for scalar-value-returning expressions. However, to avoid confusion, XPATH() should then be taught to raise an error if used for scalar-value returning expressions, instead of silently returning an empty array as it does now. Thoughts, anyone? I think it's important to note here that the nodeset returning nature of XPATH in XSLT is a context setting functionality: these nodes are then further processed by the template. In the postgresql case, the distinction between returning a value and doing further processing isn't so clear. My one use-cases tend toward processing a table full of records with an XML field, using the XPATH to select out fragments and records ids into a secondary table for further processing/analysis. What you describe, making XPATH return something for the scalar functions, is sorely needed. Constraining the return values to be valid XML fragments is the sort of wart that makes XML processing in postgresql seem odd to those familiar with other tools, though. How about naming the other function XPATH_VALUE_OF, just to make it the XSLT connection clear? Ross -- Ross Reedstrom, Ph.D. reeds...@rice.edu Systems Engineer Admin, Research Scientistphone: 713-348-6166 Connexions http://cnx.orgfax: 713-348-3665 Rice University MS-375, Houston, TX 77005 GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] creating CHECK constraints as NOT VALID
On Tue, May 31, 2011 at 11:35:01AM -0500, Kevin Grittner wrote: Alvaro Herrera alvhe...@alvh.no-ip.org wrote: This patch allows you to initially declare a CHECK constraint as NOT VALID, similar to what we already allow for foreign keys. That is, you create the constraint without scanning the table and after it is committed, it is enforced for new rows; later, all rows are checked by running ALTER TABLE VALIDATE CONSTRAINT, which doesn't need AccessExclusive thus allowing for better concurrency. I think it's a valuable feature, not just in terms of timing and concurrency, but in terms of someone starting with less-than-perfect data who wants to prevent further degradation while cleaning up the existing problems. This feature is present in other databases I've used. Yup, the ER triage approach to data integrity: Stop the major bleeding, we'll go back and make it a pretty scar later Follows from one of the practical maxims of databases: The data is always dirty Being able to have the constraints enforced at least for new data allows you to at least fence the bad data, and have a shot at fixing it all. Right now, you may be forced into running with constraints effectively 'off', depending on the app to get new data right, while attempting to catch up. And the app probably put the bad data in there in the first place. One of the thankless, important but seemingly never urgent tasks. Ross -- Ross Reedstrom, Ph.D. reeds...@rice.edu Systems Engineer Admin, Research Scientistphone: 713-348-6166 Connexions http://cnx.orgfax: 713-348-3665 Rice University MS-375, Houston, TX 77005 GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] tackling full page writes
On Wed, May 25, 2011 at 01:29:05PM -0400, Robert Haas wrote: On Wed, May 25, 2011 at 1:06 PM, Greg Smith g...@2ndquadrant.com wrote: On 05/24/2011 04:34 PM, Robert Haas wrote: I've been looking into a similar refactoring of the names here, where we bundle all of these speed over safety things (fsync, full_page_writes, etc.) into one control so they're easier to turn off at once. Not sure if it should be named web_scale or do_you_feel_lucky_punk. Actually, I suggested that same idea to you, or someone, a while back, only I was serious. crash_safety=off. I never got around to fleshing out the details, though. clearly: crash_safety=running_with_scissors -- Ross Reedstrom, Ph.D. reeds...@rice.edu Systems Engineer Admin, Research Scientistphone: 713-348-6166 Connexions http://cnx.orgfax: 713-348-3665 Rice University MS-375, Houston, TX 77005 GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] LOCK DATABASE
On Thu, May 19, 2011 at 04:13:12PM -0400, Alvaro Herrera wrote: Excerpts from Robert Haas's message of jue may 19 15:32:57 -0400 2011: That's a bit of a self-defeating argument though, since it implies that the effect of taking an exclusive lock via LockSharedObject() will not simply prevent new backends from connecting, but rather will also block any backends already in the database that try to perform one of those operations. Well, the database that holds the lock is going to be able to run them, which makes sense -- and you probably don't want others doing it, which also does. I mean other backends are still going to be able to run administrative tasks like slon and so on, just not modifying the database. If they want to change the comments they can do so after you're done with your lock. Tom has a point though and so does Chris. I'm gonna put this topic to sleep though, 'cause I sure don't want to be seen like I'm proposing a connection pooler in the backend. I know I'm late to this party, but just wanted to chime in with support for the idea that access to a particular database is properly in the scope for a DBA, and it would be good for it not to require filesystem/sysadmin action. It seems to me to be a proper serverside support for poolers or shared hosting setups, or other uses cases, without going to whole hog. Arguably would probably require versions of pg_cancel_backend and pg_terminate_backend that operate for the database owner, as well as superuser. Perhaps the approach to restricting connections should not be a database object lock, but rather an admin function that does the equivalent of flipping datallowconn in pg_database? Ross -- Ross Reedstrom, Ph.D. reeds...@rice.edu Systems Engineer Admin, Research Scientistphone: 713-348-6166 Connexions http://cnx.orgfax: 713-348-3665 Rice University MS-375, Houston, TX 77005 GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Pull up aggregate subquery
On Mon, May 23, 2011 at 11:08:40PM -0400, Robert Haas wrote: I don't really like the idea of adding a GUC for this, unless we convince ourselves that nothing else is sensible. I mean, that leads to conversations like this: Newbie: My query is slow. Hacker: Turn on enable_magic_pixie_dust and it'll get better. Newbie: Oh, yeah, that is better. Why isn't this turned on by default, anyway? Hacker: Well, on pathological queries, it makes planning take way too long, so we think it's not really safe to enable it by default. Newbie: Wait... I thought you just told me to enable it. It's not safe? Hacker: Well, sort of. I mean, uh... hey, look, an elephant! ROTFL! This needs to go on the wiki somewhere discussing why HACKERs rejects tunable knobs as often as happens. Ross -- Ross Reedstrom, Ph.D. reeds...@rice.edu Systems Engineer Admin, Research Scientistphone: 713-348-6166 Connexions http://cnx.orgfax: 713-348-3665 Rice University MS-375, Houston, TX 77005 GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Collation mega-cleanups
On Mon, May 09, 2011 at 03:57:12PM -0400, Robert Haas wrote: On Mon, May 9, 2011 at 2:58 PM, Bruce Momjian br...@momjian.us wrote: Tom this collation stuff has seen more post-feature-commit cleanups than I think any patch I remember. Is there anything we can learn from this? How about don't commit all the large patches at the end of the cycle? My take home from following this is: 'Even Tom can get caught in the just one more little change trap' :-) Ross -- Ross Reedstrom, Ph.D. reeds...@rice.edu Systems Engineer Admin, Research Scientistphone: 713-348-6166 Connexions http://cnx.orgfax: 713-348-3665 Rice University MS-375, Houston, TX 77005 GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Collation mega-cleanups
On Tue, May 10, 2011 at 07:21:16PM +0200, Andres Freund wrote: On Tuesday, May 10, 2011 07:08:23 PM Ross J. Reedstrom wrote: On Mon, May 09, 2011 at 03:57:12PM -0400, Robert Haas wrote: On Mon, May 9, 2011 at 2:58 PM, Bruce Momjian br...@momjian.us wrote: Tom this collation stuff has seen more post-feature-commit cleanups than I think any patch I remember. Is there anything we can learn from this? How about don't commit all the large patches at the end of the cycle? My take home from following this is: 'Even Tom can get caught in the just one more little change trap' I don't think any of the changes from Tom deserves that categorization. No disrespect intended, far from it. The trap is that something at seems at a distance as relatively small can grow on closer inspection. Which I think is exactly what Tom said (paraphrased) The pre-commit review was insufficent i.e. the remaining problems seemed little, but were not. In addition, little is relative to who's doing the changes, over what domain. Things that are little for Tom on PostgreSQL would not be so for me. Presumably the inverse is true over other domains. So perhaps it was more of the This code is less ready than I thought it was, but now that I've spent the time understanding it and the problem, the shortest way out is forward. I think we've all been in that swamp, at one time or another. Ross -- Ross Reedstrom, Ph.D. reeds...@rice.edu Systems Engineer Admin, Research Scientistphone: 713-348-6166 Connexions http://cnx.orgfax: 713-348-3665 Rice University MS-375, Houston, TX 77005 GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Formatting Curmudgeons WAS: MMAP Buffers
On Thu, Apr 21, 2011 at 11:16:45AM -0400, Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: On Thu, Apr 21, 2011 at 2:43 AM, Peter Eisentraut pete...@gmx.net wrote: I think to really address that problem, you need to think about shorter release cycles overall, like every 6 months. �Otherwise, the current 12 to 14 month horizon is just too long psychologically. I agree. I am in favor of a shorter release cycle. I'm not. I don't think there is any demand among *users* (as opposed to developers) for more than one major PG release a year. It's hard enough to get people to migrate that often. In fact, I predict that the observed behavior would be for even more end users to start skipping releases. Some already do - it's common not to upgrade unless there's a feature you really need, but for those who do stay on the 'current' upgrade path, you'll lose some who can't afford to spend more than one integration-testing round a year. Ross -- Ross Reedstrom, Ph.D. reeds...@rice.edu Systems Engineer Admin, Research Scientistphone: 713-348-6166 Connexions http://cnx.orgfax: 713-348-3665 Rice University MS-375, Houston, TX 77005 GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [COMMITTERS] pgsql: Document the all-balls IPv6 address.
On Fri, Mar 18, 2011 at 11:00:19PM -0400, Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: On Fri, Mar 18, 2011 at 10:19 PM, Andrew Dunstan and...@dunslane.net wrote: On 03/18/2011 09:18 PM, Robert Haas wrote: all balls seems like a colloquialism best avoided in our documentation. It's already there, although I agree it's infelicitous. I vote for taking it out. I think that could be interpreted as inappropriate. IIRC, the pre-existing usage refers to time 00:00:00. It does not seem especially useful to adopt the same terminology for network addresses; that's more likely to confuse people than anything else. And just as a historical etymological note for the list, in case anyone finds this in the archives: all balls referring to all zeros setting shows up as NASA speak in Apollo era transcripts, for any sort of all zeros setting - the one I remember off hand was actually a angle setting for an engine firing for Apollo 13. It may have been milspeak at one time as well. The more modern interpretation seems to be a contraction of all balls, no brains, so would in fact be a little off for a changelog entry. Ross etymologically yours Reedstrom -- Ross Reedstrom, Ph.D. reeds...@rice.edu Systems Engineer Admin, Research Scientistphone: 713-348-6166 Connexions http://cnx.orgfax: 713-348-3665 Rice University MS-375, Houston, TX 77005 GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Sync Rep v19
On Fri, Mar 11, 2011 at 09:03:33AM -0500, Robert Haas wrote: On Fri, Mar 11, 2011 at 8:21 AM, Fujii Masao masao.fu...@gmail.com wrote: In that case, the last write WAL timestamp would become equal to the last replay WAL timestamp. So we can see that there is no lag. Oh, I see (I think). You're talking about write/replay lag, but I was thinking of master/slave transmission lag. Which are both useful numbers to know: the first tells you how stale queries from a Hot Standby will be, the second tells you the maximum data loss from a meteor hits the master scenario where that slave is promoted, if I understand all the interactions correctly. Ross -- Ross Reedstrom, Ph.D. reeds...@rice.edu Systems Engineer Admin, Research Scientistphone: 713-348-6166 Connexions http://cnx.orgfax: 713-348-3665 Rice University MS-375, Houston, TX 77005 GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [DOCS] Sync rep doc corrections
On Mon, Mar 07, 2011 at 03:45:17PM -0300, Alvaro Herrera wrote: Excerpts from Tom Lane's message of lun mar 07 15:16:31 -0300 2011: If we do that then it becomes worth wondering what the -docs list is for at all. Maybe we *should* get rid of it; I dunno. I see your point about how the factual issues involved in a docs change ought to be vetted on -hackers, but on the other hand the traffic on -hackers is so high already that I'm not happy about folding another list with a reasonably distinct charter into it. Searching for doc changes/suggestions is helped tremendously by having them in a separate list. -1 for merging it with -hackers. I have no opinion on the -hackers crossposting issue as it doesn't affect me at all. Definitely don't merge. Crossposting, I think, should be reserved for significant factual changes to docs of features that are in current development, like ... syncrep. :-) Basically, keep docs work on docs, but cross-post to hackers when a cross-check is needed. Don't routinely crosspost, lest that train hackers to ignore the docs posts. After all, we insist that developers of patches submit docs: correct documentation is a valid concern for all developers. Ross P.S. Everyone is aware of the settings for majordomo, so you only get one of any crosspost, right? -- Ross Reedstrom, Ph.D. reeds...@rice.edu Systems Engineer Admin, Research Scientistphone: 713-348-6166 Connexions http://cnx.orgfax: 713-348-3665 Rice University MS-375, Houston, TX 77005 GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ALTER EXTENSION UPGRADE, v3
On Thu, Feb 03, 2011 at 10:21:28AM +0200, Anssi Kääriäinen wrote:
On 02/02/2011 08:22 PM, Dimitri Fontaine wrote:
Either one line in the Makefile or a new file with the \i equivalent
lines, that would maybe look like:
SELECT pg_execute_sql_file('upgrade.v14.sql');
SELECT pg_execute_sql_file('upgrade.v15.sql');
So well… I don't see how you've made it less gross here.
Chaining the upgrade files should be relatively easy, if something like
pg_execute_sql_file would be available (actually it would need to be
pg_execute_extension_file so that @extschema@ would be substituted
correctly).
Example:
upgrade_from_1_0 = '1.0 = upgrade_from_1.0.sql'
upgrade_from_2_0 = '2.0 = upgrade_from_2.0.sql'
upgrade_from_3_0 = '3.0 = upgrade_from_3.0.sql'
Hmm, how about allowing a list of files to execute? That allows the
developer to create modularized sql, and composite it in the config:
for a mythical version 4.0:
1.0 = add_foobar_table.sql new_method_baz.sql
2.0 = drop_method_baz.sql add_quuz_table.sql
# oops, we still needed this
3.0 = new_method_baz.sql
I know when I'm developing such upgrades, the code looks like that,
until I need to shoehorn them into the upgrade systems idea of version
numbers matching names to find scripts to run.
The advantage of this is that it keeps the logic for mapping version
to upgrades in the config: the upgrade scripts mearly handle the actual
SQL for doing a specific task, not a collection of tasks only related by
virtue of being released at the same time.
Ross
--
Ross Reedstrom, Ph.D. reeds...@rice.edu
Systems Engineer Admin, Research Scientistphone: 713-348-6166
Connexions http://cnx.orgfax: 713-348-3665
Rice University MS-375, Houston, TX 77005
GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ALTER EXTENSION UPGRADE, v3
On Thu, Feb 03, 2011 at 04:31:08PM +0100, Dimitri Fontaine wrote: Ross J. Reedstrom reeds...@rice.edu writes: Hmm, how about allowing a list of files to execute? That allows the Sure. I still don't see why doing it in the control file is better than in the Makefile, even if it's already better than in the SQL script, at least in terms of code to write to support the idea. Because that's two places to touch that have to worry about mapping versions to actions. Inside the config file I'm already going to have to do that, and in mostly a trivial one-to-one mapping. The proposed make rules are an example of the kind of 'make my code match what the system wants' that I complained of. Speaking about which, using Make rules to prepare your upgrade files from other pieces means no development at all on the backend side. You can hardly beat that. Yes, from the backend-developer's perspective. But not from the extension-developer's perspective :-) And seriously, make is one of those things that is supremely capable of doing lots of stuff, but is so difficult to use correctly that everyone keeps reinventing newer wheels. Seems this one isn't round enough. In fact, doing it via make rules would still be available, if that's what floats the particular developer's boat. more choices is good. Ross -- Ross Reedstrom, Ph.D. reeds...@rice.edu Systems Engineer Admin, Research Scientistphone: 713-348-6166 Connexions http://cnx.orgfax: 713-348-3665 Rice University MS-375, Houston, TX 77005 GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Allowing multiple concurrent base backups
On Tue, Jan 11, 2011 at 11:06:18AM -0800, Josh Berkus wrote: It makes it very convenient to set up standbys, without having to worry that you'll conflict e.g with a nightly backup. I don't imagine people will use streaming base backups for very large databases anyway. Also, imagine that you're provisioning a 10-node replication cluster on EC2. This would make that worlds easier. Hmm, perhaps. My concern is that a naive attempt to do that is going to have 10 base-backups happening at the same time, completely slamming the master, and none of them completing is a reasonable time. Is this possible, or is it that simultaneity will buy you hot caches and backup #2 - #10 all run faster? Ross -- Ross Reedstrom, Ph.D. reeds...@rice.edu Systems Engineer Admin, Research Scientistphone: 713-348-6166 Connexions http://cnx.orgfax: 713-348-3665 Rice University MS-375, Houston, TX 77005 GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch to add a primary key using an existing index
On Fri, Dec 03, 2010 at 05:16:04PM -0500, Robert Treat wrote: On Fri, Dec 3, 2010 at 4:41 PM, Josh Berkus j...@agliodbs.com wrote: However, I don't see why we need (column_list). Surely the index has a column list already? ALTER TABLE table_name ADD CONSTRAINT pk_name PRIMARY KEY USING index_name ... seems like the syntax most consistent with the existing commands. Anything else would be confusingly inconsistent with the way you add a brand-new PK. Uh, the syntax I posted was based on this currently valid syntax: ALTER TABLE table_name ADD PRIMARY KEY (column_list); The constraint bit is optional, which is why I left it out, but I presume it would be optional with the new syntax as well... Also, I'm not wedded to the idea of keeping the column list, but if you are arguing to make it super consistent, then I think you need to include it. If you consider that an index basically is, in some sense, a pre-canned column list, then: ALTER TABLE table_name ADD PRIMARY KEY (column_list); ALTER TABLE table_name ADD PRIMARY KEY USING index_name; are parallel constructions. And it avoids the error case of the user providing a column list that doesn't match the index. Ross -- Ross Reedstrom, Ph.D. reeds...@rice.edu Systems Engineer Admin, Research Scientistphone: 713-348-6166 Connexions http://cnx.orgfax: 713-348-3665 Rice University MS-375, Houston, TX 77005 GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] contrib: auth_delay module
On Fri, Nov 19, 2010 at 04:57:03PM +0900, KaiGai Kohei wrote: (2010/11/18 2:17), Robert Haas wrote: If KaiGai updates the code per previous discussion, would you be willing to take a crack at adding documentation? P.S. Your email client seems to be setting the Reply-To address to a ridiculous value. OK, I'll revise my patch according to the previous discussion. Please wait for about one week. I have a big event in this weekend. I'll take a crack at the docs, though I might need hand-holding for the new git stuff (I'll hit the wiki) Ross -- Ross Reedstrom, Ph.D. reeds...@rice.edu Systems Engineer Admin, Research Scientistphone: 713-348-6166 Connexions http://cnx.orgfax: 713-348-3665 Rice University MS-375, Houston, TX 77005 GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] contrib: auth_delay module
On Tue, Nov 16, 2010 at 09:41:37PM -0500, Robert Haas wrote: On Tue, Nov 16, 2010 at 8:15 PM, KaiGai Kohei kai...@ak.jp.nec.com wrote: If we don't need a PoC module for each new hooks, I'm not strongly motivated to push it into contrib tree. How about your opinion? I'd say let it go, unless someone else feels strongly about it. I would use this module (rate limit new connection attempts) as soon as I could. Putting a cap on potential CPU usage on a production DB by either a blackhat or mistake by a developer caused by a mistake in configuration (leaving the port accessible) is definitely useful, even in the face of max_connections. My production apps already have their connections and seldom need new ones. They all use CPU though. Ross -- Ross Reedstrom, Ph.D. reeds...@rice.edu Systems Engineer Admin, Research Scientistphone: 713-348-6166 Connexions http://cnx.orgfax: 713-348-3665 Rice University MS-375, Houston, TX 77005 GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
On Thu, Jul 15, 2010 at 04:20:12PM +0100, Simon Riggs wrote: Just for the record, I've never ever met anyone that said Oh, this \d syntax makes so much sense. I'm a real convert to Postgres now you've shown me this. The reaction is always the opposite one; always negative. Which detracts from our efforts elsewhere. Ah, that's true, we've never met in person ... Let me say that I recall finding the clean separation of what the client implements vs. what the server implements very useful when I was new to postgresql. Anything that doesn't start with a backslash works equally well from psql and from python/psycopg2, for example. If you make SHOW variants that are actually client side \d commands, you break that. Ross -- Ross Reedstrom, Ph.D. reeds...@rice.edu Systems Engineer Admin, Research Scientistphone: 713-348-6166 The Connexions Project http://cnx.orgfax: 713-348-3665 Rice University MS-375, Houston, TX 77005 GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Admission Control
On Sat, Jun 26, 2010 at 01:19:57PM -0400, Robert Haas wrote: I'm not sure. What does seem clear is that it's fundamentally at odds with the admission control approach Kevin is advocating. When you start to run short on a resource (perhaps memory), you have to decide between (a) waiting for memory to become available and (b) switching to a more memory-efficient plan. The danger of (b) is that using less memory probably means using more of some other resource, like CPU or disk, and now you've just switched around which release you're overloading - but on the other hand, if the difference in CPU/disk is small and the memory savings is large, maybe it makes sense. Perhaps in the end we'll find we need both capabilities. I can't help feeling like some good instrumentation would be helpful in answering some of these questions, although I don't know where to put it. One issue with this is that no matter how expensive you make a query, it will run - it just may take a very long time (if the cost is a reasonable estimate) This is also an implied suggestion for a dynamically self-modifying cost param, since the memory cost isn't absolute, but rather relative to free memory. In addition, as Robert points out, the tradeoff between resources is dynamic, as well. Hmm, I'm suddenly struck by the idea of having a max_cost parameter, that refuses to run (or delays?) queries that have too high a cost. That might have some interactive-SQL uses, as well: catch the cases you forgot a join condition, so have an unintended cartesian explosion, etc. Could also be a belt-and-suspenders last defense for DB admins who aren't sure the client software completely stops the users from doing something stupid. Clearly, default to current behavior, -1 (infinity). Ross -- Ross Reedstrom, Ph.D. reeds...@rice.edu Systems Engineer Admin, Research Scientistphone: 713-348-6166 The Connexions Project http://cnx.orgfax: 713-348-3665 Rice University MS-375, Houston, TX 77005 GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] New PGXN Extension site
On Tue, Jun 15, 2010 at 01:25:33PM -0700, David E. Wheeler wrote: On Jun 15, 2010, at 1:12 PM, Bruce Momjian wrote: This was just posted to announce. Seems the community now has to compete with another extension-based infrastructure if we ever get around to developing one of our own. I personally had no knowledge of this, which is fine, but don't expect me to get excited about it, except to consider it a threat to a community-lead extension site. This *is* for the community, Bruce. There was extensive discussion of my original proposal back in January: http://www.mail-archive.com/pgsql-hackers@postgresql.org/msg143645.html I welcome all contributions from the community. I want it to be a success for PostgreSQL. But note that it doesn't have to be started as a -hackers project (any more than pg_upgrade did). CPAN, for example, was created because Jarkko had an itch. He scratched it. I'm doing the same here. One issue that will come up: this is clearly a more commercial enterprise than Jarkko's CPAN (and the internet is a different place than it was in 1995) You pushed money right to the front with this, so that will lead to certain questions concerning ownership of what arguably should be community resources: the IP of the aggregate index, for example. I'm a big believer in JFDI as well - just be aware that toes will get stepped on, and require some bandages. Ross -- Ross Reedstrom, Ph.D. reeds...@rice.edu Systems Engineer Admin, Research Scientistphone: 713-348-6166 The Connexions Project http://cnx.orgfax: 713-348-3665 Rice University MS-375, Houston, TX 77005 GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] New PGXN Extension site
On Tue, Jun 15, 2010 at 03:42:59PM -0700, David E. Wheeler wrote: On Jun 15, 2010, at 3:22 PM, Bruce Momjian wrote: I totaly agreed you need funding, and you are very well qualified to do this, and it is a badly needed facility. Thanks. The problem I had is that the effort appeared to be I am creating my own sandbox, fund me (particularly the FAQ), which is probably not what you wanted to convey. I understand adjustments are being made and if you can clarify how this is going to relate to the community in terms of input, oversight, and management, it might be something the entire community can get behind, and help fund. Agreed. How's this? http://pgxn.org/faq.html +1 Excellent, actually. Ross -- Ross Reedstrom, Ph.D. reeds...@rice.edu Systems Engineer Admin, Research Scientistphone: 713-348-6166 The Connexions Project http://cnx.orgfax: 713-348-3665 Rice University MS-375, Houston, TX 77005 GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] no universally correct setting for fsync
On Mon, May 10, 2010 at 01:35:32PM -0700, Josh Berkus wrote: deleted, or on a reporting read-only clone of your database which gets recreated very night and is not used for failover. High quality hardware alone s/very/every/ or s/very night/periodically/ Ross -- Ross Reedstrom, Ph.D. reeds...@rice.edu Systems Engineer Admin, Research Scientistphone: 713-348-6166 The Connexions Project http://cnx.orgfax: 713-348-3665 Rice University MS-375, Houston, TX 77005 GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Add column if not exists (CINE)
On Tue, Apr 27, 2010 at 08:18:13PM -0400, Robert Haas wrote: On Tue, Apr 27, 2010 at 6:45 PM, Kjell Rune Skaaraas kjell...@yahoo.no wrote: [snip] I saw some indications that this might be a minority opinion, well I would like to cast a vote FOR this functionality. The workarounds are ugly, the solution simple and while I agree it's possible to misuse it, my opinion is that you shouldn't become a surgeon if you can't handle a scalpel. In this case I get the feeling I'm reading instructions on how to do surgery with a butter knife because we don't dare hand out anything sharper. I've already said my piece on this, but I couldn't agree more. Well said, and your use case is exactly the one I want it for. +1 (Scribbles down the phrase instructions on how to do surgery with a butter knife because we don't dare hand out anything sharper for future repurposing) Ross -- Ross Reedstrom, Ph.D. reeds...@rice.edu Systems Engineer Admin, Research Scientistphone: 713-348-6166 The Connexions Project http://cnx.orgfax: 713-348-3665 Rice University MS-375, Houston, TX 77005 GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] psql: Add setting to make '+' on \d implicit
On Fri, Apr 23, 2010 at 10:58:40AM -0500, Terry Brown wrote: I asked on IRC if there was any way to make \d behave like \d+ by default, and davidfetter said no but suggest it here. endpoint_david pointed out you could use \d- to get the old behavior if you wanted to temporarily negate the setting. So the proposal would be: \d+ does as it has always done, no change \d- (new) always behaves like 'old' \d \d acts as 'old' \d or as \d+, depending on the setting of 'verbose_describe', set via \pset. Default setting of verbose_describe would presumably yield 'old' behavior. Motivation is that I like to see comments when they exist. Probably useful for other reasons too. Hmm, what about all the other + variants? Would this setting affect them? I'd suggest perhaps it should. Ross -- Ross Reedstrom, Ph.D. reeds...@rice.edu Systems Engineer Admin, Research Scientistphone: 713-348-6166 The Connexions Project http://cnx.orgfax: 713-348-3665 Rice University MS-375, Houston, TX 77005 GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Alias to rollback keyword
On Wed, Mar 31, 2010 at 10:34:41PM -0400, Bruce Momjian wrote: Matthew Altus wrote: Hey, After dealing with a production fault and having to rollback all the time, I kept typing a different word instead of rollback. So I created a patch to accept this word as an alias for rollback. Obviously it's not part of the sql standard, but could be a nice extension for postgresql. See the patch for more details. Applied. Hmm, a careful review of the patch leads me to believe that this is one of the few times that a keyword might benefit from localization. Seems l10n efforts in this area have mostly focused on the filtering case, but I'm sure we can repurose such lists. Licensing might be an issue. Clearly this needs to be controlled by the client locale, not the server. Any need for a guc? Ross -- Ross Reedstrom, Ph.D. reeds...@rice.edu Systems Engineer Admin, Research Scientistphone: 713-348-6166 The Connexions Project http://cnx.orgfax: 713-348-3665 Rice University MS-375, Houston, TX 77005 GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] function to display ddl
On Sun, Feb 14, 2010 at 05:08:05PM +0100, Yeb Havinga wrote: Little, Douglas wrote: Hi, Is there a PG command or fuction that will return table ddl? If you just want the definition,in psql type \d tablename. To dump ddl the pg_dump with proper arguments can dump just the ddl of a single table. (see pg_dump --help) more of a pg-users question, but since we've got a partial answer here, might as well complete it. Try psql -E, then \d sometable to see the commands psql issues to generate its display. Ross -- Ross Reedstrom, Ph.D. reeds...@rice.edu Systems Engineer Admin, Research Scientistphone: 713-348-6166 The Connexions Project http://cnx.orgfax: 713-348-3665 Rice University MS-375, Houston, TX 77005 GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Output configuration status after ./configure run.
On Wed, Feb 10, 2010 at 07:01:19PM +0200, Priit Laes wrote: It might avoid the 'UU, I forgot to enable python support.', after you have waited a while for the build to finish... +1 from me, for that very reason! Ross -- Ross Reedstrom, Ph.D. reeds...@rice.edu Systems Engineer Admin, Research Scientistphone: 713-348-6166 The Connexions Project http://cnx.orgfax: 713-348-3665 Rice University MS-375, Houston, TX 77005 GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] MySQL-ism help patch for psql
On Mon, Jan 25, 2010 at 06:06:53PM +0200, Alastair Bell Turner wrote: .. without having to add a switch to their command lines. It's not going to have anything to say to experienced psql users anyway so it would probably not bug anyone enough to turn it off. I would so use this feature going the other way: fire up comfortable psql and see what mysql command I need to type ... Having it in the interface (behind \help [mysql|oracle|firebird|mssql|..] seems reasonable to me, given how much info we already have in \help. I find the basic BNF help for SQL syntax still useful reminder, and know to go to the actual docs when there's not enough there. So a quick mapping of most-needed commands, and a pointer to the docs for the full ramifications and subtle differences seems to fit the existing documentation module. Ross -- Ross Reedstrom, Ph.D. reeds...@rice.edu Systems Engineer Admin, Research Scientistphone: 713-348-6166 The Connexions Project http://cnx.orgfax: 713-348-3665 Rice University MS-375, Houston, TX 77005 GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] MySQL-ism help patch for psql
On Mon, Jan 25, 2010 at 10:49:55AM -0600, Ross J. Reedstrom wrote: On Mon, Jan 25, 2010 at 06:06:53PM +0200, Alastair Bell Turner wrote: .. without having to add a switch to their command lines. It's not going to have anything to say to experienced psql users anyway so it would probably not bug anyone enough to turn it off. I would so use this feature going the other way: fire up comfortable psql and see what mysql command I need to type ... Having it in the interface (behind \help [mysql|oracle|firebird|mssql|..] seems reasonable to me, given how much info we already have in \help. I find the basic BNF help for SQL syntax still useful reminder, and know to go to the Well, behind \migrate or some other word as others have, since I see \help is just the SQL help I so praised. Ross -- Ross Reedstrom, Ph.D. reeds...@rice.edu Systems Engineer Admin, Research Scientistphone: 713-348-6166 The Connexions Project http://cnx.orgfax: 713-348-3665 Rice University MS-375, Houston, TX 77005 GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] primary key display in psql
On Wed, Jan 13, 2010 at 05:03:33PM -0500, Robert Haas wrote: On Wed, Jan 13, 2010 at 4:47 PM, Tom Lane t...@sss.pgh.pa.us wrote: Spelling out primary key would seem to be more in keeping with existing entries in that column, eg we have not null not NN. I think this is a sensible proposal for a single-column PK, but am less sure that it makes sense for multi-col. The modifiers column is intended to describe column constraints; which a multi-col PK is not, by definition. Yeah, IIRC, MySQL shows PRI for each column of a multi-column primary key, and I think it's horribly confusing. I wouldn't even be in favor of doing this just for the single-column case, on the grounds that it makes the single and multiple column cases asymmetrical. IMO, the \d output has too many bells and whistles already; the last thing we should do is add more. How about spelling it as so: Table public.test Column | Type | Modifiers +-+--- a | integer | primary key b | integer | Indexes: test1_pkey PRIMARY KEY, btree (a) Table public.test2 Column | Type | Modifiers +-+--- a | integer | primary key (compound) b | integer | primary key (compound) Indexes: test2_pkey PRIMARY KEY, btree (a, b) As to Tom's point that a compound primary key is a table level restriction, by definition, participating in such a key is still a restriction on what values that column can take. When introspecting someone else's schema, with a very wide table, seeing '(compound)' is a nice strong hint to go looking for the other members of the PK. Ross -- Ross Reedstrom, Ph.D. reeds...@rice.edu Systems Engineer Admin, Research Scientistphone: 713-348-6166 The Connexions Project http://cnx.orgfax: 713-348-3665 Rice University MS-375, Houston, TX 77005 GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] New PostgreSQL Committers
On Mon, Dec 07, 2009 at 10:49:13AM +, Dave Page wrote: On behalf of the core team, I'm pleased to announce that the Congratulations! +1 Congrats to you all, and thanks for the contributions, both past and future. As an aside, this sort of thing is one of the best signs to an external user of the health of the PostgreSQL project: the 'orderly transfer of power' as it were. I'm always cautious about adopting a project with a limited set of core developers (often one) no matter how good the software. Ross -- Ross Reedstrom, Ph.D. reeds...@rice.edu Systems Engineer Admin, Research Scientistphone: 713-348-6166 The Connexions Project http://cnx.orgfax: 713-348-3665 Rice University MS-375, Houston, TX 77005 GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SE-PgSQL patch review
On Tue, Nov 24, 2009 at 03:12:43PM +0900, KaiGai Kohei wrote: Itagaki Takahiro wrote: * CREATE TABLE tbl (col integer AS SECURITY_CONTEXT = '...') Is the syntax AS SECURITY_CONTEXT natural in English? We need to put a reserved token, such as AS, prior to the SECURITY_CONTEXT to avoid syntax conflicts to DEFAULT b_expr option. Does WITH work? Seems to read better to me: CREATE TABLE tbl (col integer WITH SECURITY CONTEXT [...]) -- Ross Reedstrom, Ph.D. reeds...@rice.edu Systems Engineer Admin, Research Scientistphone: 713-348-6166 The Connexions Project http://cnx.orgfax: 713-348-3665 Rice University MS-375, Houston, TX 77005 GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] plruby code and postgres ?
On Thu, Nov 19, 2009 at 05:15:05PM -0700, u235sentinel wrote: Does anyone have a link for pl/ruby? I found a link under the postgres documentation and found a web site from there talking about the code. However when I clicked on the link to download it I noticed ftp wouldn't respond on their site. Debian's got a copy of the original tarball or the most recently release version: http://packages.debian.org/source/lenny/postgresql-plruby And links there in. Ross -- Ross Reedstrom, Ph.D. reeds...@rice.edu Systems Engineer Admin, Research Scientistphone: 713-348-6166 The Connexions Project http://cnx.orgfax: 713-348-3665 Rice University MS-375, Houston, TX 77005 GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 'TID index'
On Sat, Sep 25, 2004 at 11:14:53AM +0100, Simon Riggs wrote: Jim C. Nasby Fair questions. MVCC has been tightly locked into Postgres/SQL for the whole of its history. There is much written on this and you should search some more - references are in the manual. Well, not quite it's whole history: MVCC showed up in 6.5. Vacuum's been there since before SQL. Actually, is a bit of a historical accident. My understanding of the squence of events is that Hewlett-Packard donated an early WORM optical drive to the Stonebraker lab. Since it's write once, it had the beavior that you could only append to files. Someone thought it might be useful for auditing, etc., so they wrote the first storage mananger for postgres to accommodate that drive. The other storage manager at the time was for battery-backed, persistent RAM. So, all this append-only writing leads to files with lots of dead tuples, so the vacuum command was added to reclaim space. I think on the WORM drive, this was supposed to mark blocks 'invisible' in some sense. I don't know if the WORM drive ever actually got used with postgres. Ross -- Ross Reedstrom, Ph.D. [EMAIL PROTECTED] Research Scientist phone: 713-348-6166 The Connexions Project http://cnx.rice.edu fax: 713-348-3665 Rice University MS-375, Houston, TX 77005 GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Open items
On Wed, Aug 18, 2004 at 10:12:53PM -0400, Bruce Momjian wrote: Magnus Hagander wrote: Another discussion was about binary files in the tree (not being source files - the source is a binary .AI file (AFAIK that's Adobe Illustrator)). The question was raised wether ImageMagick could do this conversion - it can't. Doesn't support AI. Also, it would introduce yet another build dependency in order to create a single file. I don't see much other way than stuffing the icon in there (possibly along with the .AI file if you'd need to change it manually). It is, after all, just a resource and not code. These binary files are almost never going to be changed so I see no problem with adding them to CVS, and putting whatever source we can into CVS. If we can't we just document how we created the binary. Can you export the image to tiff format or something so we can modify it laster if we need to, or perhaps gimp format? Actually, you'll find that Adobe Illustrator AI files are not a 'binary' file at all: they're a particular flavor of PostScript. In fact, I just used the pstoedit tool to convert this particular one (the PostgreSQL elephant head logo) into an xfig file, then from there back to postscript. It's not ideal: apparently, Illustrator and Xfig have slightly different spline curve implementations, so the lines get a bit bumpy after the pass through xfig. So, it's not really a set of tools I'd recommend to attempt to automate the 'build some icons' step: that still takes an artist. However, the AI file _is_ an editable source doc. I've hand hacked Postscript in the past. I'd recommend keeping it in CVS, along side the hand-build icons. If someone wants to hack on icons at some later date, the AI/postscript file is a useful starting point (preferred, actually: it's vector). Eventually, an SVG doc will probably be the way to go. Further investigation show that pstoedit can use GNU libplot to generate _lots_ of different formats, including SVG and fig, via a different path. From _that_ fig file, xfig can generate a postscript file that renders _identically_ to the AI file. So, the AI is a useful source. Ross -- Ross Reedstrom, Ph.D. [EMAIL PROTECTED] Research Scientist phone: 713-348-6166 The Connexions Project http://cnx.rice.edu fax: 713-348-3665 Rice University MS-375, Houston, TX 77005 GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: Postgres development model (was Re: [HACKERS] CVS comment)
On Sun, Aug 08, 2004 at 01:18:02AM -0400, Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: Maybe a better SCM could help with this, but I doubt it. I haven't seen any particular reason why we should adopt another SCM. Perhaps BitKeeper or SubVersion would be better for our purposes than CVS, but are they enough better to justify the switchover costs? I doubt it. CVS has it problems, and there are other SCM packages available that address those, but the but the main problem for postgresql is shortage of uber-hackers, as Tom says. Once that problems solved (more good coders seasoned in the codebase) then it might be time to switch. By then, they'll be even easier to use. ;-) Ross -- Ross Reedstrom, Ph.D. [EMAIL PROTECTED] Research Scientist phone: 713-348-6166 The Connexions Project http://cnx.rice.edu fax: 713-348-3665 Rice University MS-375, Houston, TX 77005 GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE ---(end of broadcast)--- TIP 3: 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] Error codes revisited
On Tue, Mar 04, 2003 at 11:04:03PM -0500, Tom Lane wrote: There is still barely enough time to do the long-threatened protocol revision for 7.4, if we suck it up and get started on that now. I've been avoiding the issue myself, because it seems generally boring and thankless work, but maybe it's time to face up to it? Given the repeatedly-asked-for functionalities (like error codes) for which the stopper has been the long-threatened protocol revision, I'd think it might be boring, but would hardly be thankless. Heck, I'd expect a few whoops of joy around the lists. Ross ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Simplifying timezone support
According to my sent folder, this went out Monday afternoon, but I haven't seen it yet, so I'm resending to the list only, without the attached patch. I'll send the patch over to patches. Any comment on the behavior, specifically, the heuristic for deciding tzset() failed, and the proposed order of application of tzset() vs. table lookup? Ross On Mon, Feb 24, 2003 at 03:34:56PM -0600, Ross J. Reedstrom wrote: On Fri, Feb 21, 2003 at 08:39:12PM -0600, Ross J. Reedstrom wrote: Every other validly formatted TZ variable that returns GMT should be caught be the datetktbl check. I'll play with it this weekend, see how hard it is to make it work. O.K., the weekend's over, And I've created two different version of this. Both work, ipass all the regression test, and solve the 'CST is just a funny way to say GMT' problem. I was able to make use of DecodePosixTimezone (DPT) from Thomas's datetime parsing code in assign_timezone. However, the order of application of this vis. tzset is unclear. I had proposed doing the DPT first, then tzset, then a NOTICE if it looked like tzset didn't. Got that working, but discovered a change of behavior: for some of those who have a timezone in the zoneinfo database that is a three letter abbreviation, the current code (tzset only) will provide daylight savings time transitions, so that a timestamp in July returns a different timezone than one in February. This is not true for our internal values of set time zone: there, we convert to a numerical offset, which is constant no matter when the timestamp occurs. This is still a win for those who's timezone abbreviation is _not_ in the zoneinfo DB, (such as CST), which currently is silently interpreted as an odd spelling of GMT. Second solution - try tzset() first, and apply the following heuristic to see if it took: tzname[0]==$TZ and tzname[1]== and timezone=0 and daylight=0 In other words, _all_ the timezone related information remains the default. I tested this against the 1607 zoneinfo files on my system: every one was filtered out, even things that _are_ GMT with no DST (they all had a non-null tzname[1] == tzname[0]) If this succeeds (i.e. tzset didn't recognize the TZ), go ahead and look it up in our big table'o date/time strings. This also works, fixing the bogus GMT spellings, without changing current behavior for any string that is not bogus. Note that the sysadmin can always tell if tzset or the table was used, by looking at the format of the 'show time zone' result. If tzset was called, this is the string that was passed to 'set time zone'. If the table was used, it will be an hours west of GMT offset. The problem with this approach is that it does nothing to reduce our dependency on the OS timezone functionality. Comments? I've attached the second patch for discussion. Ross ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Simplifying timezone support
On Fri, Feb 21, 2003 at 08:39:12PM -0600, Ross J. Reedstrom wrote:
Every other validly formatted TZ variable that returns GMT should be
caught be the datetktbl check.
I'll play with it this weekend, see how hard it is to make it work.
O.K., the weekend's over, And I've created two different version
of this. Both work, ipass all the regression test, and solve the
'CST is just a funny way to say GMT' problem. I was able to make use
of DecodePosixTimezone (DPT) from Thomas's datetime parsing code in
assign_timezone. However, the order of application of this vis. tzset
is unclear.
I had proposed doing the DPT first, then tzset, then a NOTICE if it
looked like tzset didn't. Got that working, but discovered a change of
behavior: for some of those who have a timezone in the zoneinfo database
that is a three letter abbreviation, the current code (tzset only) will
provide daylight savings time transitions, so that a timestamp in July
returns a different timezone than one in February. This is not true for
our internal values of set time zone: there, we convert to a numerical
offset, which is constant no matter when the timestamp occurs.
This is still a win for those who's timezone abbreviation is _not_ in the
zoneinfo DB, (such as CST), which currently is silently interpreted as
an odd spelling of GMT.
Second solution - try tzset() first, and apply the following heuristic
to see if it took:
tzname[0]==$TZ and tzname[1]== and timezone=0 and daylight=0
In other words, _all_ the timezone related information remains the
default. I tested this against the 1607 zoneinfo files on my system:
every one was filtered out, even things that _are_ GMT with no DST (they
all had a non-null tzname[1] == tzname[0])
If this succeeds (i.e. tzset didn't recognize the TZ), go ahead and look
it up in our big table'o date/time strings. This also works, fixing the
bogus GMT spellings, without changing current behavior for any string
that is not bogus.
Note that the sysadmin can always tell if tzset or the table was used, by
looking at the format of the 'show time zone' result. If tzset was called,
this is the string that was passed to 'set time zone'. If the table was
used, it will be an hours west of GMT offset.
The problem with this approach is that it does nothing to reduce our
dependency on the OS timezone functionality.
Comments? I've attached the second patch for discussion.
Ross
Index: src//backend/commands/variable.c
===
RCS file: /projects/cvsroot/pgsql-server/src/backend/commands/variable.c,v
retrieving revision 1.73
diff -c -r1.73 variable.c
*** src//backend/commands/variable.c2003/02/01 18:31:28 1.73
--- src//backend/commands/variable.c2003/02/24 21:29:44
***
*** 243,252
const char *
assign_timezone(const char *value, bool doit, bool interactive)
{
! char *result;
! char *endptr;
double hours;
/*
* Check for INTERVAL 'foo'
*/
--- 243,257
const char *
assign_timezone(const char *value, bool doit, bool interactive)
{
! char*result;
! char*endptr;
! chartztmp[sizeof(tzbuf)];
! char*lp;
! const char *cp;
double hours;
+ int tzval,i;
+
/*
* Check for INTERVAL 'foo'
*/
***
*** 337,348
else
{
/*
!* Otherwise assume it is a timezone name.
*
!* XXX unfortunately we have no reasonable way to check
whether a
!* timezone name is good, so we have to just assume that it
!* is.
*/
if (doit)
{
strcpy(tzbuf, TZ=);
--- 342,360
else
{
/*
!* Otherwise assume it is a timezone name.
!* Try tzset() first. If that fails, see if our internal
!* table of timezone names, can handle it.
*
!* XXX unfortunately we have only an approximate way to check
!* whether a timezone name is good: if the value of TZ is
returned
!* as the canonical tzname, there is no daylight savings time
!* (flag or canonical second name) _and_ the offset is GMT,
!* tzset() punted, i.e., returned all default values.
!* If that happens, try the internal table - if _that_ fails,
!* throw a notice.rjr 2003/02/23
*/
+
if (doit
Re: [HACKERS] Simplifying timezone support
On Thu, Feb 20, 2003 at 04:19:21PM -0500, Tom Lane wrote: Ross J. Reedstrom [EMAIL PROTECTED] writes: On Thu, Feb 20, 2003 at 03:21:09PM -0500, Tom Lane wrote: Provide a portable way of getting libc to tell us whether it likes TZ, and I'll be glad to fix this. Dang that lovely word 'portable'. However, given your proposed change, perhaps the hurdle for portable time handling is now lower: it seems we've not been exposed to as broad a range of broken systems as in the past. On this particular point my threshold of 'portable' is actually pretty low, as long as it's fail-soft. Failure to detect bad TZ on some systems would leave them no worse off than before, right? But I haven't seen *any* published API that directly tells you whether tzset liked TZ or not --- AFAICT it's supposed to just silently substitute GMT. Which would be okay if GMT were the only allowed spelling of GMT, but it ain't ... I've been digging in the date and time code a bit, and now have a proposal for dealing with SET TIME ZONE 'someunknownstring'. First, we use the time token table from the time constant parser in utils/adt/datetime.c to see if we've got a recognized time zone abbreviation. If it is, we generate a canonical POSIX timezone name for use in setting TZ, call tzset(), and we're done. If the time zone came back UNKOWN, we go ahead and see if tzset() can interpret it. Criteria for failure: if the timezone offset came back 0, and the reported tzname[0] is the same as the string that we passed in. If it does, we fire a NOTICE about an unknown spelling of GMT. Note that we would have already caught all _known_ spellings of GMT in the first step, so we won't be spamming the DBA with warnings about 'GMT' and 'UTC', etc. An extension to this would be to use the tzset() trick above directly in the datetime constant parser, as a fallback after not matching the table. In that case, we'd probably want to treat the unknown spelling of GMT as an error, though (as it currently does). Thoughts? If this seems acceptable, I can implement it this weekend. Ross ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Simplifying timezone support
On Fri, Feb 21, 2003 at 05:45:53PM -0600, Ross J. Reedstrom wrote: On Fri, Feb 21, 2003 at 06:15:31PM -0500, Tom Lane wrote: Ross J. Reedstrom [EMAIL PROTECTED] writes: snip I'm worried about cases like Africa/Benin for places that just happen to be on the prime meridian, but don't call their time GMT or UTC. Looking at a globe, it also seems possible that there are places an hour west of Greenwich, for which this could fail during daylight-savings season. Well, that'll either get caught by the existing table (we've got six different spellings of GMT, currently) or by the 'string in != string out' case - the zoneinfo format requires a 3 or more character abbreviation for the time zone. For every case I'v looked at in my zoneinfo directory, it's either 3 or 4 uppercase characters, and _never_ matches the filename path string used to set it. I'll do an exhaustive test after dinner. O.K., I've run the test: of the 1108 files in my zoneinfo database, only 11 have matching filenames to the canonical name returned after setting TZ. Of those 11, 4 are some version of GMT (GMT, UCT, UTC, WET), of which, one is in fact missing from our table - UCT. At minimum, I'll add that. Every other validly formatted TZ variable that returns GMT should be caught be the datetktbl check. I'll play with it this weekend, see how hard it is to make it work. Ross ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Simplifying timezone support
On Fri, Feb 21, 2003 at 06:15:31PM -0500, Tom Lane wrote: Ross J. Reedstrom [EMAIL PROTECTED] writes: snip I'm worried about cases like Africa/Benin for places that just happen to be on the prime meridian, but don't call their time GMT or UTC. Looking at a globe, it also seems possible that there are places an hour west of Greenwich, for which this could fail during daylight-savings season. Well, that'll either get caught by the existing table (we've got six different spellings of GMT, currently) or by the 'string in != string out' case - the zoneinfo format requires a 3 or more character abbreviation for the time zone. For every case I'v looked at in my zoneinfo directory, it's either 3 or 4 uppercase characters, and _never_ matches the filename path string used to set it. I'll do an exhaustive test after dinner. An extension to this would be to use the tzset() trick above directly in the datetime constant parser, as a fallback after not matching the table. In that case, we'd probably want to treat the unknown spelling of GMT as an error, though (as it currently does). I think tzset() is probably much too slow to consider calling on every pass through timestamptz_in ... It wouldn't happen on every call - only with funky timezone representations. We could NOTICE use of tzset(), as well, to alert the DBA about something fishy, if you'd like. Ross ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Simplifying timezone support
On Thu, Feb 20, 2003 at 03:21:09PM -0500, Tom Lane wrote: Ross J. Reedstrom [EMAIL PROTECTED] writes: question about pgsql's time zone parsers. It appears there's at least two, since SET TIME ZONE accepts strings like 'US/Eastern', while general timestamp parsing doesn't: The TIME ZONE string is fed to libc (via TZ environment variable); the other cases are not. SET TIME ZONE will silently accept any string at all, and fall back to providing GMT when a timestamptz is requested. Provide a portable way of getting libc to tell us whether it likes TZ, and I'll be glad to fix this. Dang that lovely word 'portable'. However, given your proposed change, perhaps the hurdle for portable time handling is now lower: it seems we've not been exposed to as broad a range of broken systems as in the past. I'll look at it. but no promises. Ultimately we should probably get rid of our dependence on the libc time routines altogether ... but I have no intention of opening that can of worms right now. See past discussions in the archives. Agreed. I see we're inheriting the actually misleading case from the OS/libc, as well: wallace$ unset TZ wallace$ date Thu Feb 20 15:00:04 CST 2003 wallace$ export TZ=US/Central wallace$ date Thu Feb 20 15:00:16 CST 2003 wallace$ export TZ=US/Zanzibar wallace$ date Thu Feb 20 21:00:33 US/Zanzibar 2003 wallace$ export TZ=CST wallace$ date Thu Feb 20 21:00:42 CST 2003 wallace$ Ross ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Simplifying timezone support
On Wed, Feb 19, 2003 at 10:35:58PM -0500, Tom Lane wrote: snip Tom discussion backend internal tracking of timezone Any objections? Not to your suggestion per se, but looking at the bug report raises a question about pgsql's time zone parsers. It appears there's at least two, since SET TIME ZONE accepts strings like 'US/Eastern', while general timestamp parsing doesn't: test=# select TIMESTAMP WITH TIME ZONE '2003/02/18 09:36:06.00933 CST'; timestamptz -- 2003-02-18 09:36:06.00933-06 (1 row) test=# select TIMESTAMP WITH TIME ZONE '2003/02/18 09:36:06.00933 EST'; timestamptz -- 2003-02-18 08:36:06.00933-06 (1 row) test=# select TIMESTAMP WITH TIME ZONE '2003/02/18 09:36:06.00933 US/Eastern'; ERROR: Bad timestamp external representation '2003/02/18 09:36:06.00933 US/Eastern' Further testing says it's even worse that that: SET TIME ZONE will silently accept any string at all, and fall back to providing GMT when a timestamptz is requested. This includes the TLA TZ abbreviations that the constant parsing code understands, like CST and EST. test=# set TIME ZONE 'CST'; SET test=# select TIMESTAMP WITH TIME ZONE '2003/02/18 09:36:06.00933 EST'; timestamptz -- 2003-02-18 14:36:06.00933+00 (1 row) test=# set TIME ZONE 'FOOBAR'; SET test=# select TIMESTAMP WITH TIME ZONE '2003/02/18 09:36:06.00933 EST'; timestamptz -- 2003-02-18 14:36:06.00933+00 (1 row) Here's an especially fun one: with DATESTYLE set to 'Postgresql,US', whatever string is handed to SET TIME ZONE comes out the other end, if it can't be parsed: test=# set TIME ZONE 'FOOBAR'; SET test=# select TIMESTAMP WITH TIME ZONE '2003/02/18 09:36:06.00933 EST'; timestamptz --- Tue Feb 18 14:36:06.00933 2003 FOOBAR (1 row) Leading to this erroneous pair: test=# set TIME ZONE 'US/Central'; SET test=# select TIMESTAMP WITH TIME ZONE '2003/02/18 09:36:06.00933 EST'; timestamptz Tue Feb 18 08:36:06.00933 2003 CST (1 row) test=# set TIME ZONE 'CST'; SET test=# select TIMESTAMP WITH TIME ZONE '2003/02/18 09:36:06.00933 EST'; timestamptz Tue Feb 18 14:36:06.00933 2003 CST (1 row) test=# Tom, since you're in (or near) that code right now, how painful would it be to unify the time zone parsing? What's the correct behavior? Certainly SET TIME ZONE should at leat NOTICE about invalide time zone names? Ross ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] psql and readline
On Sat, Feb 15, 2003 at 03:10:19PM -0600, Ross J. Reedstrom wrote: On Fri, Feb 14, 2003 at 11:32:02AM -0500, Tom Lane wrote: Patrick Welche [EMAIL PROTECTED] writes: On Thu, Feb 13, 2003 at 10:25:52AM -0500, Tom Lane wrote: Well, is that a bug in your wrapper? Or must we add a configure test for the presence of replace_history_entry()? Good question. Easiest for now for me would be add a configure test. Okay with me --- Ross, can you handle that? O.K., I found the 'editline' wrapper around 'libedit' that provides a subset of readline functionality, and used that for testing. On my Debian Linux systems, editline installs readline compatability headers (readline.h, history.h) into /usr/include/editline/, so I added tests for those into configure.in, and src/include/pg_config.h.in, and usage in src/bin/psql/input.h I added a test for replace_history_entry() to configure.in, and usage of it to src/bin/psql/command.c. As you may recall, the original purpose of this patch was to deal with the interaction of '\e' with the history buffer. I implemented replacing the '\e' entry in the buffer with the query as returned by the edit session and sent to the backend. If the replace_history_entry function is missing, I now just push the edited entry onto the history stack, leaving the '\e' in place as well. Tested on systems with readline, editline, and --without-readline. Since the patch has now grown to 25 lines, I've posted it over to PATCHES. Ross ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] psql and readline
On Mon, Feb 17, 2003 at 12:05:20AM +0100, Peter Eisentraut wrote: Ross J. Reedstrom writes: I don't think this is what we were out for. We've certainly been running with libedit for a long time without anyone ever mentioning /usr/include/editline. I suggest this part is taken out. Well, I found a set of systems that install libedit (and editline) in that location (i.e. Debian Linux). I couldn't test on the standard version of that system without either this, or hacking a symlink into /usr/include. Yes, BSD systems that install libedit directly in /usr/include (or into readline), like Patrick's, don't need it, but mine do. Is there some reason we _shouldn't_ support this configuration? Ross ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Todo claim: psql tab completion on schema names
On Sun, Feb 16, 2003 at 01:34:34AM +0100, Ian Barwick wrote: On Sunday 16 February 2003 01:10, Rod Taylor wrote: I've been debating a mechanism which could build tab completion tables based on the documentation for a while now -- and was going to give it a try next week. If it works, that file would essentially disappear. Hmm, from slash commands, or from SQL grammar? Or both? How impossible would it be to generate the tab-completion from the _grammar_ (for the SQL) rather than from docs? Something I was musing about when adding cases to this a month or so ago. Feel free to send in patches for ones that have been missed. ..what I meant is that after entering \d, TAB will produce a list of tables, but \di does not produce a list of indexes, same for \dv etc. I see no particular reason why this is so and can provide patches if relevant. Send in the patches: won't hurt, even if they get overridden by alter work - who knows, Rod may get distracted and not complete the grand plan above. In general, it's better to capture existing work than hold of for future, better, implementations, for things like this (no backwards compatability issues) Ross ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Call for objections: put back OIDs in CREATE TABLE AS/SELECT INTO
On Thu, Jan 23, 2003 at 10:03:28AM -0500, Tom Lane wrote: Curt Sampson [EMAIL PROTECTED] writes: I object. I personally think we should be moving towards not using OIDs as the default behaviour, inasmuch as we can, for several reasons: All these objections are global in nature, not specific to CREATE TABLE AS. The argument that persuaded me to do something here is that CREATE TABLE AS should not be different from CREATE TABLE's default behavior. I have no problem with moving towards lack-of-OIDs as the default behavior for both statements, in the long run, if we can get past the compatibility issues. But I don't think OIDs in user tables are costing us anything much, so I'm not prepared to take any big compatibility hit to change the default ... Agreed as to taking the compatability hit in the 7.3 branch (you _were_ talking about changing 7.3, weren't you?) But I think Curt and D'Arcy have a point: what OIDs are costing the DBAs and PostgreSQL developing community is the pain of having an 'almost' solution in place. OIDs have always been the unwanted child in PostgreSQL: the 'pure relational' people don't want them, and the Object people are misled into thinking we've got a _real_ object id. On the relational side, they've stood in for proper use of primary keys (as D'Arcy points out), partly because it's so _easy_ to misuse them that way: the wire protocol returns the OID for free in some cases, and the interface libraries make it easy to get at. So the immediate case, changing the default (in 7.3) to match the CREATE TABLE case makes sense. However, we need to wean developers off using OIDs. I've been working with Diedrich Vorberg on a thin python object relational mapping interface (his Object Relational Membrane - ORM) and this was a central problem: you _need_ a unique id for an object, and the oid seemed so natural ... So in the longer term, we need to provide a replacement. Arguably, the primary key for a table is the right replacement, but we don't _require_ a pkey, so what to do in cases where this isn't one? Also, the pkey can be _any_ column(s), of _any_ type, which could be inconvenient for returning as the result of an insert, for example (imagine a text field as pkey, with a _huge_ block of text just written into it ...) Ross ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Generate user/group sysids from a sequence?
On Fri, Jan 17, 2003 at 11:38:24AM -0500, Bruce Momjian wrote: Tom Lane wrote: A small difficulty is that explicitly-specified sysids could conflict with sysids generated later by the sequence. We could perhaps fix this by forcing up the sequence setting to be at least as large as an explicitly-given ID (compare the handling of explicitly loaded OIDs). A sequence sounds like a good idea. When we create a user, we can use MAX() to find the maximum, and if that is less than the sequence value, bump up the sequence to equal max and add the row, again incrementing the sequence. Another idea would be to put a trigger on the column so that any INSERT/UPDATE would automatically bump up the sequence with setval(). Hmm, unlike the OID case, I'd think there's unlikely to be many 'preused' userids. Why not just retry if the sequence hits an existing entry? Ross ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Oracle rant
On Thu, Jan 16, 2003 at 11:17:42AM -0500, Jeff wrote: On Wed, 15 Jan 2003, mlw wrote: So with all that, you gotta appreciate both sides - hte fact pg just works and the tunability of bigger db's (Oh yeah - and we've actually had informix on the horn about the problem - their solution was upgrade to 9.4 - it'll be out in march). Here lies the real secret to why Open Source of all types keeps the techies (like us) maximally happy: I know I've seen Tom Lane (and others) often suggest an upgrade as the real fix for a problem, but the suggestion to upgrade to a not yet released version invariably includes the option of applying the patch yourself. Not something Oracle can offer. Ross ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] copying perms to another user
On Tue, Jan 14, 2003 at 12:23:59PM +0800, Christopher Kings-Lynne wrote: Christopher Kings-Lynne [EMAIL PROTECTED] writes: No, I mean that we don't drop the user. You go: ALTER USER chriskl COPY PERMISSIONS FROM blah; That seems cleaner to me than the DROP thingy. You could only easily implement this in the current database --- but since it's not a DROP, one could repeat it in each database as needed. Could someone perhaps add it to TODO then (so I don't forget about it)? I can't promise that I can implement it... In this scenario, 'blah' is the user who will eventually be dropped, and chriskl is taking over ownership of his 'stuff' right? How about doing it the other way: ALTER USER blah COPY PERMISSIONS TO chriskl; Hmm, in fact, I can imagine uses for both forms: creating a 'template' user who you COPY PERMISSIONS FROM when creating a new user of that type, who will then be customized, so you can't use GROUPs. Hmm, what about GROUP membership? Those get copied as well? Ross ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] pg_get_constraintdef
On Mon, Jan 13, 2003 at 11:59:33AM +0800, Christopher Kings-Lynne wrote: Tom Lane writes: Feel free to contribute some code. I will, but unfortunately the damage has already been done...since I have to support 7.3 anyway, fixing the above problem will actually make my life harder, not easier... Yeah, but never let that stop you from doing the right thing. Heck, isn't that almost a diagnostic for 'the right thing', Hmm, this is easy: I must be doing something wrong. ;-) Ross ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] PostgreSQL site, put up or shut up?
On Mon, Jan 13, 2003 at 10:01:38AM -0500, Vince Vielhaber wrote: On Mon, 13 Jan 2003, Dan Langille wrote: On 13 Jan 2003 at 9:45, Vince Vielhaber wrote: On Mon, 13 Jan 2003 [EMAIL PROTECTED] wrote: FTP is just over 800MB, plan for growth. WEB is just over 90MB, can't tell you what to plan for there. Sorry to be dense, but what time period is this for? Any given day. It's disk space, not traffic. I think anyone thinking of putting up a mirror will want to know traffic volumes. The only info I could give was what I already did. My above statement was to clarify the above numbers. And there was a statement upthread from someone (Marc?) indicating that the bandwidth was down in the noise for them (as an ISP). Ross ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] psql and readline
On Fri, Jan 10, 2003 at 11:02:55PM +0100, Peter Eisentraut wrote: Ross J. Reedstrom writes: I already posted a one-line patch to implement this, but it doesn't seem to hve come through to the list. Here it is inline, instead of as an attachment: We need this to work without readline as well. (Of course there won't be any history, but it needs to compile.) blush Even after slogging my way through the nesting #ifdefs for readline and win32, I forgot! Let's make that a three line patch, then. Index: src/bin/psql/command.c === RCS file: /projects/cvsroot/pgsql-server/src/bin/psql/command.c,v retrieving revision 1.84 diff -u -r1.84 command.c --- src/bin/psql/command.c 2002/10/23 19:23:56 1.84 +++ src/bin/psql/command.c 2003/01/10 22:06:07 @@ -1639,6 +1639,9 @@ error = true; } +#ifdef USE_READLINE + replace_history_entry(where_history(),query_buf-data,NULL); +#endif fclose(stream); } Ross ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] psql and readline
On Thu, Jan 09, 2003 at 10:49:33PM +0100, Peter Eisentraut wrote: Christopher Kings-Lynne writes: Is there any way of making the 'up' arrow retrieve all of the last multiline query, instead of just the last line? There is nothing technical that should prevent you from implementing it. But you need to come up with a reasonable system to keep the history straight if meta commands are mixed with multiline commands, such as select * \x from foo; Peter, what do you think about pushing the query buffer onto the history stack at execution time, any time it's a multiline query? That way, after the above sequence, you'd have: (1) select * (2) \x (3) from foo; (4) select * from foo; Yeah, this wastes a history slot, but it does solve the problem. It's also dead simple to code. ;-) BTW, the behavior in bash for multiline interactive entered commands is to concatenate them into a single line and dump them in the history, replacing the individual parts. The individual parts are in the history while you're at the secondary prompt, then go away after execution. In psql, we'd probably just lose interstitial metacommands. Ross ---(end of broadcast)--- TIP 3: 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] psql and readline
On Fri, Jan 10, 2003 at 07:15:34AM +, Peter Mount wrote: On Thu, 9 Jan 2003, Peter Eisentraut wrote: Tom Lane writes: The case I find interesting is where you're using plain \e to re-edit a query interactively. If this query never gets into the history buffer then you're lost: you won't be able to pull it back for re-editing a second time. If you call \e again immediately then you edit the previous command. Yes, but it's not always the last command you want :-( I already posted a one-line patch to implement this, but it doesn't seem to hve come through to the list. Here it is inline, instead of as an attachment: Index: src/bin/psql/command.c === RCS file: /projects/cvsroot/pgsql-server/src/bin/psql/command.c,v retrieving revision 1.84 diff -u -r1.84 command.c --- src/bin/psql/command.c 2002/10/23 19:23:56 1.84 +++ src/bin/psql/command.c 2003/01/09 22:18:49 @@ -1639,6 +1639,7 @@ error = true; } + replace_history_entry(where_history(),query_buf-data,NULL); fclose(stream); } Ross ---(end of broadcast)--- TIP 3: 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] Upgrading rant.
On Thu, Jan 02, 2003 at 07:26:06PM -0500, Tom Lane wrote: Lamar Owen [EMAIL PROTECTED] writes: replies I will rather quickly redirect to /dev/null, as it isn't Red Hat's fault we can't do a sane upgrade. I think you're wasting your time trying to hold us to a higher standard of backwards-compatibility than is maintained by the OSes and tools we must sit on top of. Case in point: even though the Debian upgrade scripts have occasionally given me a near-heart attack by claiming that they didn't succcessfully upgrade when they did, I've never had this problem. Is this because Oliver is smarter than you? Or Debian is 'superior'? No, it's because _incremental upgradability_ is _the_ design goal for the Debian distribution. Lots of other stuff may work better on RedHat (auto hardware detection, etc.) but this is the design case for Debian, so the facilities are mostly there for Oliver to use to do incremental, rollbackable, upgrades. What does that mean for PostgreSQL? Perhaps Tom's right: you can't fix it in the program if the underlying system doesn't support it. Ross ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] SQL/MED spec for cross-database linkages
On Fri, Dec 06, 2002 at 01:52:01PM -0500, Tom Lane wrote: Rod Taylor [EMAIL PROTECTED] writes: Found SQL/MED, Section 21 of ISO 9075-9. That's the old version, though. The new draft is at (digs out article) http://sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-14-XML-2002-03.pdf according to this article, but I'm not having any luck accessing that URL. Let me email the authors and see if I can get a copy. Hmm, seems you need to change protocols: ftp://sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-14-XML-2002-03.pdf Ross ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] SQL/MED spec for cross-database linkages
On Fri, Dec 06, 2002 at 01:52:01PM -0500, Tom Lane wrote: Rod Taylor [EMAIL PROTECTED] writes: Found SQL/MED, Section 21 of ISO 9075-9. That's the old version, though. The new draft is at (digs out article) http://sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-14-XML-2002-03.pdf according to this article, but I'm not having any luck accessing that URL. Let me email the authors and see if I can get a copy. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? Having pulled that one, I discover it's actually the XML related spec. The MED one is at: ftp://sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-09-MED-2002-01.pdf There's a text version, as well (always nice for grepping): ftp://sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-09-MED-2002-01.txt Ross ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Propose RC1 for Friday ...
I've tested this under 7.3, and it works beautifully for the cases I've built over the last 2 days. I can no longer bugger a plan up mearly by reordering the WHERE clauses. Note that 2 of the five parts won't patch in (involving constantqual). Looks to be code refactoring between here and planmain.c on the 7.4 branch? I tried to hand-patch it in, and gave up. it _seems_ to work without it, but I probably haven't covered that codepath. Ross On Thu, Nov 14, 2002 at 01:33:05PM -0500, Tom Lane wrote: I said: Well, we could define it as a bug ;-) --- that is, a performance regression. I'd be happier about adding a dozen lines of code to sort quals by whether or not they contain a subplan than about flip-flopping on the original patch. That would actually solve the class of problem you exhibited, whereas the other is just a band-aid that happens to work for your particular example. The attached patch does the above. I think it's a very low-risk change, but am tossing it out on the list to see if anyone objects to applying it in the 7.3 branch. (I intend to put it in 7.4devel in any case.) regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] performance regression, 7.2.3 - 7.3b5 w/ VIEW
On Wed, Nov 13, 2002 at 02:40:40AM -0500, Mike Mascari wrote: Ross J. Reedstrom wrote: For this query, the difference is 160 ms vs. 2 sec. Any reason for this change? I could be way off base, but here's a shot in the dark: http://groups.google.com/groups?hl=enlr=ie=UTF-8oe=UTF-8threadm=3D0885E1.8F369ACA%40mascari.comrnum=3prev=/groups%3Fq%3DMike%2BMascari%2Bsecurity%2BTom%2BLane%26ie%3DUTF-8%26oe%3DUTF-8%26hl%3Den At the time I thought PostgreSQL was doing something naughty by allowing user functions to be invoked on data that would ultimately not be returned. Now I know how Oracle uses VIEWS for row security: Oracle functions invoked in DML statements can't record any changes to the database. So if the above is the cause, I wouldn't have any problems with the patch being reversed. Maybe separate privileges for read-only vs. read-write functions are in order at some point in the future though... Bingo, that solved it. I'm back to 160 ms. What does Tom feel about removing this? Is there some way the planner could have known which was the smarter/faster order of application? Ross ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] performance regression, 7.2.3 - 7.3b5 w/ VIEW
You're right, I should remove that (cruft left over from when the subselect wasn't). However, it has no impact on the planner at hand: removing it does trim 25% from the execution time, but getting the WHERE clauses used in the right order gains an order of magnitude. Both apply. Thanks, I'll fix it. Ross On Wed, Nov 13, 2002 at 09:28:38AM +0100, Tommi Maekitalo wrote: Am Mittwoch, 13. November 2002 07:22 schrieb Ross J. Reedstrom: Hey Hackers - ... CREATE VIEW current_modules AS SELECT * FROM modules m WHERE module_ident = (SELECT max(module_ident) FROM modules WHERE m.moduleid = moduleid GROUP BY moduleid); ... I just wonder if you really need the GROUP BY. The subselect should return exactly one row and so max does without GROUP BY: CREATE VIEW current_modules AS SELECT * FROM modules m WHERE module_ident = (SELECT max(module_ident) FROM modules WHERE m.moduleid = moduleid); ---(end of broadcast)--- TIP 3: 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] performance regression, 7.2.3 - 7.3b5 w/ VIEW
On Wed, Nov 13, 2002 at 08:58:04AM -0500, Tom Lane wrote: Ross J. Reedstrom [EMAIL PROTECTED] writes: Bingo, that solved it. I'm back to 160 ms. What does Tom feel about removing this? Is there some way the planner could have known which was the smarter/faster order of application? As I said in the previous thread, I don't have a lot of patience with the notion of expecting the planner to promise anything about evaluation order of WHERE clauses. I wasn't thrilled with adding the patch, but I'm even less thrilled with the idea of backing it out now. Having read the previous thread, I realized you wouldn't be thrilled about it, that's why I asked. While I agree in principle (don't promise a particular order), the pragmatic corollary of that principle would say if you don't favor a particular order, then don't change the order from previous stable releases. Unlike the previous thread, I'm not looking for a particular order: there're no side-effects I'm trying to exploit, I just want the best possible performance. There has been some discussion of reordering WHERE clauses based on estimated cost --- a simple form of this would be to push any clauses involving subplans to the end of the list. I haven't done anything about that yet, mainly because I'm unsure if there are cases where it would be worse than not doing it. Me either, though my gut says subplans are expensive. I _can_ trivially write queries that do the wrong thing (suboptimal order of WHERE clauses) with or without this patch. It's clearly the wrong time to try to do anything fancier, but the conservative thing to do (in my unbiased opinion ;-) is put it back the way it was for the last stable release, on the principle of least surprise - there seems to be no bug fixed or functionality gained by keeping the change. Seems like this is at least worth a TODO: * Examine WHERE clause order optimization possibilities, particularly with subplans Ross ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] performance regression, 7.2.3 - 7.3b5 w/ VIEW
Hey Hackers - I was testing beta5 and found a performance regression involving application of constraints into a VIEW - I've got a view that is fairly expensive, involving a subselet and an aggregate. When the query is rewritten in 7.2.3, the toplevel constraint is used to filter before the subselect - in 7.3b5, it comes after. For this query, the difference is 160 ms vs. 2 sec. Any reason for this change? Here's the view def., and explain analyzes for the view, and two hand rewritten versions (since the explain analyze in 7.2.3 doesn't display the filter parameters) Ross CREATE VIEW current_modules AS SELECT * FROM modules m WHERE module_ident = (SELECT max(module_ident) FROM modules WHERE m.moduleid = moduleid GROUP BY moduleid); repository=# explain analyze select * from current_modules where name ~ 'Fourier'; QUERY PLAN -- Seq Scan on modules m (cost=0.00..116090.23 rows=1 width=135) (actual time=18.74..1968.01 rows=37 loops=1) Filter: ((module_ident = (subplan)) AND (name ~ 'Fourier'::text)) SubPlan - Aggregate (cost=0.00..25.57 rows=1 width=13) (actual time=0.41..0.41 rows=1 loops=4534) - Group (cost=0.00..25.55 rows=6 width=13) (actual time=0.08..0.37 rows=10 loops=4534) - Index Scan using moduleid_idx on modules (cost=0.00..25.54 rows=6 width=13) (actual time=0.06..0.27 rows=10 loops=4534) Index Cond: ($0 = moduleid) Total runtime: 1968.65 msec (8 rows) repository=# explain analyze select module_ident from modules m where m.name ~ 'Fourier' and m.module_ident = (SELECT max(modules.module_ident) as max from modules where (m.moduleid=moduleid) group by modules.moduleid); QUERY PLAN Seq Scan on modules m (cost=0.00..116090.23 rows=1 width=4) (actual time=2.46..158.33 rows=37 loops=1) Filter: ((name ~ 'Fourier'::text) AND (module_ident = (subplan))) SubPlan - Aggregate (cost=0.00..25.57 rows=1 width=13) (actual time=0.35..0.35 rows=1 loops=270) - Group (cost=0.00..25.55 rows=6 width=13) (actual time=0.07..0.31 rows=9 loops=270) - Index Scan using moduleid_idx on modules (cost=0.00..25.54 rows=6 width=13) (actual time=0.06..0.22 rows=9 loops=270) Index Cond: ($0 = moduleid) Total runtime: 158.81 msec (8 rows) repository=# explain analyze select module_ident from modules m where m.module_ident = (SELECT max(modules.module_ident) as max from modules where (m.moduleid=moduleid) group by modules.moduleid) and m.name ~ 'Fourier'; QUERY PLAN -- Seq Scan on modules m (cost=0.00..116090.23 rows=1 width=4) (actual time=18.66..1959.31 rows=37 loops=1) Filter: ((module_ident = (subplan)) AND (name ~ 'Fourier'::text)) SubPlan - Aggregate (cost=0.00..25.57 rows=1 width=13) (actual time=0.41..0.41 rows=1 loops=4534) - Group (cost=0.00..25.55 rows=6 width=13) (actual time=0.08..0.37 rows=10 loops=4534) - Index Scan using moduleid_idx on modules (cost=0.00..25.54 rows=6 width=13) (actual time=0.06..0.27 rows=10 loops=4534) Index Cond: ($0 = moduleid) Total runtime: 1959.84 msec (8 rows) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] protocol change in 7.4
On Wed, Nov 06, 2002 at 05:02:14PM +0900, Satoshi Nagayasu wrote: Hannu Krosing [EMAIL PROTECTED] wrote: Exactly. When user send the COMMIT command to the master server, the master.talks to the slaves to process precommit-vote-commit using the 2PC. The 2PC cycle is hidden from user application. User application just talks the normal FE/BE protocol. But _can_ client (libpq/jdbc/...) also talk 2PC FE/BE protocol, i.e. act as master ? Not for now. The current libpq/jdbc can talk only normal FE/BE protocol. But it can be implemented. Because my (experimantal)libpq can talk 2PC FE/BE protocol. :-) snip Because the postgres backend must detect a type of incomming connection (from the client app or the master). If it is comming from the client, the backend relays the queries to the slaves (act as the master). But if it is comming from the master server, the backend must act as a slave, and does not relay the queries. So, your replication is an all-or-nothing type of thing? you can't replicate some tables and not others? If only some tables are replicated, then you can't decide if this is a distributed transaction until it's been parsed. Also, if we want to cascade, then one server can be both master and slave, as it were. For full-on-2PC, I'm not sure cascading is a good idea, but it's something to consider, especially if there's provisions for partial replication, or 'optional' slaves. I think there are several types of connection in the sync replication or the distributed transaction. Especially, the bulk transfer of tables or indexes will be neccesary for the distributed query in future. So, I think embedding the connection type information in the startup packet is a good idea. Is there some fundamental reason that the slave backends can't just wait and see if the first commit command is PRECOMMIT or COMMIT and then act accordingly on for each transaction ? Are two commit commands required on the clustered postgres? And is one commit command required on the single postgres? I think Hannu is suggesting that COMMIT could occur from either of two states in the transaction state diagram: from an open transaction, or from PRECOMMIT. There's no need to determine before that moment if this particular transaction is part of a 2PC or not, is there? So, no you don't _require_ PRECOMMIT/COMMIT because it's clustered: if a 'bare' COMMIT shows up, do what you currently do: hide the details. If a PRECOMMIT shows up, report status back to the 'client'. So, it seems to me that the minimum protocol change necessary to support this model is reporting the current transaction status to the client. I think it will confuse the application programmer. I think your mental image of an application programmer needsto be expanded: it should also include middleware vendors, who very much want to be able to control a distributed transaction, one part of which may be a postgresql replicated cluster. Ross ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] protocol change in 7.4
On Tue, Nov 05, 2002 at 08:54:46PM +0900, Satoshi Nagayasu wrote: Hannu Krosing [EMAIL PROTECTED] wrote: In protocol-layer 2PC, no new SQL command is required. A precommit-vote-commit phase will be called implicitly. It means an user application can be used without any modification. An application can use a traditional way (BEGIN...COMMIT). If application continues to use just BEGIN/COMMIT, then the protocol level must parse command stream and recognize COMMIT in order to replace it with PRECOMMIT, COMMIT. If the communication library has to do that anyway, it could still do the replacement without affecting wire protocol, no ? No, I think Satoshi is suggesting that from the client's point of view, he's embedded the entire precommit-vote-commit cycle inside the COMMIT command. In my implementation, 'the extended(2PC) FE/BE protocol' is used only in the communication between the master and slave server(s), not between a client app and the master server. libpq --Normal FE/BE-- (master)postgres --Extended(2PC)FE/BE-- (slave)postgres --Extended(2PC)FE/BE-- (slave)postgres --Extended(2PC)FE/BE-- (slave)postgres A client application and client's libpq can work continuously without any modification. This is very important. And protocol modification between master and slave server(s) is not so serious issue (I think). Ah, but this limits your use of 2PC to transparent DB replication - since the client doesn't have access to the PRECOMMIT phase (usually called prepare phase, but that's anothor overloaded term in the DB world!) it _can't_ serve as the transaction master, so the other use cases that people have mentioned here (zope, MOMs, etc.) wouldn't be possible. Hmm, unless a connection can be switched into 2PC mode, so something other than a postgresql server can act as the transaction master. Does your implementation cascade? Can slaves have slaves? Ross ---(end of broadcast)--- TIP 3: 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] protocol change in 7.4
On Mon, Nov 04, 2002 at 08:10:29PM -0500, Mike Mascari wrote: Actually, I was thinking along the lines of a true CREATE DATABASE LINK implementation, where multiple databases could participate in a distributed transaction. That would require the backend in which the main query is executing to act as the coordinator and each of the other participating databases to act as cohorts. And would require a protocol change to support the PREPARE, COMMIT-VOTE/ABORT-VOTE reply, and an ACK message following the completion of the distributed COMMIT or ABORT. Right, you need TPC in order for pgsql to participate in transactions that span anything outside the DB proper. A DB link is one example, or an external transaction manager that coordinates DB and filesystem updates, for example. Zope could use this, to coordinate the DB with it's internal object store. Ross ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Turning the PLANNER off
On Wed, Oct 30, 2002 at 10:59:39PM -0500, Tom Lane wrote: What's the basis for your assertion that it's planning things that don't need it? Given a JOIN-constrained query I do not believe the planner will look at any cases other than the intended join order. Well, that was a loose choice of words - let's say the planner seems to be taking awfully long to build an execution tree with only one choice available. He can do whatever he wants, as long as he has no illusions about getting it accepted back into the sources ;-). Understood - this would be a hacking tool only. What would probably be more useful is to do some profiling to understand why the planner is taking longer than he wants even with a JOIN-constrained query. I should think this would be pretty quick. Yup, that targets the same question as above - anything 'uneeded' actually happening in the planner? I'll send him off with this suggestion. Ross ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Turning the PLANNER off
Ah, so Ben finally got around to posting here. Ben's a CS Grad student here at Rice. His (current) project involves taking some interesting results from constraint satisfaction and implementing them on a database: one of the CS faculty has demonstrated that one class of highly joined DB queries maps to a solved problem in constraint satisfaction. The end goal would be an optimizer module or setting that recognizes this class of query, and spits out a mathematically optimized join order. So, in the interim, Ben's trying to do it by hand: preorder the joins and demonstrate that the 'best' order is in fact the best. Then move on to looking into integrating this, if possible: part of the problem is recognizing the structure of the query, of course. Right now, the planner is getting in the way - although he can extract the needed timing info, he's wasting CPU cycles planning things that don't need it, limiting the number of cases he can try. As mentioned before, there's currently no interface to feed in a Plan, so he's out of luck. Is there, programmatically, a way to do it? Serialize a plan tree to a file, and feed it in latter, purely for development purposes. How painful would that be? Should I send him in to see if he can implement one quickly, or are there dragons hiding in there? Ross On Mon, Oct 28, 2002 at 07:55:02PM -0500, Bruce Momjian wrote: That is a good question. The planner does more than just analyse the query. It generates the Plan used by the executor, so that can't be removed. It is always a pain when the optimizer/planner takes longer than the executor. We do have PREPARE/EXECUTE in 7.3beta for you to use. --- Ben McMahan wrote: I'm looking at different ways of optimizing queries with a large number of joins. I write the same query in a number of different ways and compare the running times. Now the problem is I do not want the optimizer changing the queries. So I explicit state the order of the joins in the FROM clause. I also turn off everything I can except for one type of join (say hash join), and I've turned off geqo. But I find that the PLANNER still takes an enormous amount of time for some queries. It doesn't look like the Planner is actually optimizing (changing) anything, but just in case, I was wondering if there was a way to turn off the PLANNER. Note, when I say an enormous amount of time, I mean at least double the time the EXECUTOR takes to actually answer the query. Thanks for your help, Ben McMahan ps. here is a small example of what my queries look like (so you can see if there is something else it might be deciding on): SELECT DISTINCT c0.x1 , c1.x2 , c0.x3 , c0.x4 , c2.x5 FROM r1 c4 (x4,x2,x5) JOIN (r0 c3 (x2,x3,x5) JOIN (r2 c2 (x3,x1,x5) JOIN (r1 c1 (x4,x1,x2) JOIN r1 c0 (x1,x3,x4) ON ( c0.x4 = c1.x4 AND c0.x1 = c1.x1 )) ON ( c0.x3 = c2.x3 AND c0.x1 = c2.x1 )) ON ( c1.x2 = c3.x2 AND c0.x3 = c3.x3 AND c2.x5 = c3.x5 )) ON ( c0.x4 = c4.x4 AND c1.x2 = c4.x2 AND c2.x5 = c4.x5 ); A quick reminder, FROM r1 c4 (x4,x2,x5) just renames a table r1 into c4 where it also renames the columns to x4, x2, and x5 respectively. ---(end of broadcast)--- TIP 3: 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 -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Web site
On Tue, Sep 24, 2002 at 03:59:33AM -0400, Vince Vielhaber wrote: On Tue, 24 Sep 2002, Gavin Sherry wrote: Hi all, It occurs to me that opening web page on www.postgresql.org, asking the user to select the mirror, is rather unprofessional. I am sure this has been discussed before but I thought I would bring it up again anyway. Your point? So, why not just redirect people to one of the mirrors listed? This could be done based on IP (yes it is inaccurate but it is close enough and has the same net effect: pushing people off the main web server) or it could be done by simply redirecting to a random mirror. Been there, done that, didn't work. Too much of a job to keep track of that many IP blocks too. I'd suggest setting a cookie, so I only see the 'pick a mirror' the first time. And provide a link to 'pick a different mirror' that resets or ignores the cookie. Ross ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Web site
On Tue, Sep 24, 2002 at 11:26:55AM -0400, Vince Vielhaber wrote: On Tue, 24 Sep 2002, Ross J. Reedstrom wrote: I'd suggest setting a cookie, so I only see the 'pick a mirror' the first time. And provide a link to 'pick a different mirror' that resets or ignores the cookie. Or choose the mirror that works best for you and bookmark it. Of course, that's what _I_ do, but the dicussion was how to make the frontpage 'user friendly' and 'professional'. Lots of global corps. do the 'pick your geographical region' thing, but mainly for sales reasons, so it must be professional, right? Ross ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Proposal for resolving casting issues
On Thu, Sep 19, 2002 at 04:57:30PM +0200, Zeugswetter Andreas SB SD wrote: Have you seen my example ? If calculated in float4 the result of 1.01*1000.0-1000.0 would be 0.0, no ? So? If you are storing one input as float4, then you cannot rationally say that you know the result to better than 6 digits, because you don't know the input to better than 6 digits. Claiming that 1000.001 is a more accurate answer for the product than 1000.0 is simply wishful thinking on your part: nothing to the right of the sixth digit actually means a darn thing, because you don't know whether the input was really exactly 1000, or should have been perhaps 1000.001. I still see 1E-10 as a better answer to above calculation than your 0, and my snapshot 9/11 does return that 1E-10. Well, then you'd be wrong. Numerical analysis says you _can't_ get more information out than went in to the _least_ precise part of a calculation. What your suggesting is the equivalent of wanting to put up a shelf, so you estimate the length of the wall by eyeballing it, then measure the wood for the shelf with a micrometer, to be sure it fits exactly right. We teach this in intro science classes all the time: if you calculate with 3.14 as an approximation to pi, you better not report the circumference of a circle as 2.45678932 cm, I'll take off points! I do think I grasp the problem :-) Hmm, I'm not so sure. ;-) I give up now. I voiced my concern, and that is as far as my interest goes on this actually. I still think fielding what other db's do in this area would be a good thing before proceeding further. Ah, sorry to drag this on, then. But this is one of those clear cases were we must fo the right thing, not follow the crowd. PostgreSQL gets used by a lot of scientific projects (Have you noticed all the big bioinformatics databases being mentioned on the lists?). Partly because we're always underfunded, partly because we're academics who like to have the code. If we start getting basic maths wrong, that'll be a huge balck eye for the project. Ross -- Ross Reedstrom, Ph.D. [EMAIL PROTECTED] Executive Director phone: 713-348-6166 Gulf Coast Consortium for Bioinformatics fax: 713-348-6182 Rice University MS-39 Houston, TX 77005 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Proposal for resolving casting issues
On Thu, Sep 19, 2002 at 10:30:51AM -0500, Ross J. Reedstrom wrote: Ah, sorry to drag this on, then. But this is one of those clear cases were we must fo the right thing, not follow the crowd. PostgreSQL gets do used by a lot of scientific projects (Have you noticed all the big bioinformatics databases being mentioned on the lists?). Partly because we're always underfunded, partly because we're academics who like to ^^(scientific projects) ^^ have the code. If we start getting basic maths wrong, that'll be a huge ^^(PostgreSQL) balck eye for the project. black Clearly, it's time for an early lunch for me. I need sugar for my brain. Ross ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Proposal for resolving casting issues
On Thu, Sep 19, 2002 at 06:00:37PM +0200, Zeugswetter Andreas SB SD wrote: What if he must display 9 digits and says the result is approximately 2.45678932 would that be worse than 2.4600 ? Yup. Trailing zeros are not significant. That's why scientific notation is nice: you don't fill in all those insignificant placeholders. For above calculation pg will in the future return 0. as an answer to 1.01*1000.0-1000.0 when used in my example context, while it currently returns 0.0010 ... You both are saying, that 0. is a better answer. That's right. And correct, as well. Ross ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Win32 rename()/unlink() questions
On Wed, Sep 18, 2002 at 08:01:42PM -0400, Bruce Momjian wrote: Second, when you unlink() a file on Win32, do applications continue accessing the old file contents if they had the file open before the unlink? I'm pretty sure it errors with 'file in use'. Pretty ugly, huh? Ross ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] MySQL wins award - makes amusing statement
On Thu, Sep 12, 2002 at 01:56:19PM +0800, Christopher Kings-Lynne wrote: *sigh* Well, at least they have an easy and fast upgrade process ;) Right, fewer pesky features to get in the way of the upgrade ;- Ross ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Rule updates and PQcmdstatus() issue
On Mon, Sep 09, 2002 at 11:30:52AM -0700, Dann Corbit wrote: I suspect it'll be several more major releases before we begin to consider it approaching completely functional. I believe that the surprise is at the focus, when it comes to a release. With commercial products (anyway) if you have any sort of show-stopper bug (crashing, incorrect results, etc.) you do not release the tool until the bug, and all others like it, are fixed. Bugs that have to do with appearance or convenience can be overlooked for a release as long as they are documented in the release notes. Now, it is not unlikely that there are unintentional show-stopper bugs that get through Q/A. But intentionally passing them through would be incompetent for a commercial enterprise. Hmm, you don't have any drinking buddies who work QA, do you? _Lots_ of known, eat your harddrive bugs get classified as to be fixed in future release in commercial software, when the release date pressure grows. With open source projects, the empasis tends to be on features, with far less regard for correcting known problems. Even bugs that can cause a crash seem to be viewed as acceptable if they happen rarely. Huh? I tend to see exactly the opposite. Actual crash and wrong answer bugs tend to get very prompt attention on all the open source projects I know and use. What _does_ get delayed or even ignored are bug compability problems, like this one. That is, software that relies on the affected rows count is in fact broken, since it's making assumptions about that number that were never promised in any standard or interface docs. snip silly comparison to commercial software house All kidding aside, I would like to see increased emphasis on stability and correctness. But I will admit that it is a lot less fun than adding new features. And this has got to be trolling: PostgreSQL is one of the _most_ stability and correctness focused software projects I've ever known. In this particular case, the complaints about this issue where Your bugfix broke my tool! make it better! The answer was We can't just put it back, that's an actual bug in there (rules firing in an unpredicatable order). What's the _correct_ behavior? The people with the complaints then did not come up with a compelling, complete description of what the correct behavior should be. There's always been vague parts to the desired behavior like the phrase Tom pointed out: in the context of the view which was clarified to mean viewable by the view, which is nearly impossible to code, if not an example of the halting problem. PostgreSQL as a project errs on the side of not coding the quick fix, in favor of waiting for the right answer. Sometimes too long, but this case isn't one of those, IMHO. Ross -- Ross Reedstrom, Ph.D. [EMAIL PROTECTED] Executive Director phone: 713-348-6166 Gulf Coast Consortium for Bioinformatics fax: 713-348-6182 Rice University MS-39 Houston, TX 77005 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Inheritance
On Thu, Sep 05, 2002 at 10:23:02AM -0400, Tom Lane wrote: I really like Hannu's idea of storing an entire (single-inheritance) hierarchy in a single file. Wouldn't this require solving the ALTER TABLE ADD COLUMN (to parent) column ordering problem? I guess the question we need to ask ourselves is if we're prepared to abandon support of multiple inheritance. Personally I am, but... No opinion - I've not used the inheritance much, since I'm not willing to give up referential integrity. Ross ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] pgaccess - where to store the own data
On Fri, Aug 30, 2002 at 02:43:38PM -0400, Matthew T. OConnor wrote: As someone else mentioned (I think), even using a separate schema is not always an acceptable option. If you are using a packaged application (whether commercial or open source), you usually don't want *any* changes to the vendor provided database. Particularly with commercial software, that can mean loss of, or problems with, technical support, or problems when upgrading. Agreed, but if the information is to be stored using the database server at all, then I think this option should be left in since some users probably don't mind the clutter, and will not be allowed to create a new database or schemea. I'm a bit late on this discussion, but I, for one, have liked having some of the pgaccess info stored with the database. That way, no matter what machine I connect to the DB from, I get the same set of functions, queries, and schema-documents. BTW, has the 'schema' tab been renamed yet? With actual schema in 7.3, that'll get confusing. Ross ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Proposed GUC Variable
On Tue, Aug 27, 2002 at 06:08:40PM -0400, Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: But we should have some default to print some of the query, Why? So far you've been told by two different people (make that three now) that such a behavior is useless, and no one's weighed in in its favor ... I agree that a 'trimmed' query is likely to be useless, but the idea of printing the query on ERROR is a big win for me: right now I'm logging _all_ queries on our development machine (and sometimes on our production machine. when there's trouble) so my logs would get considerably smaller. A settable trim length would probably be a good idea, I suppose, for those slinging 'bytea' and toasted texts around. Ross ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] @(#) Mordred Labs advisory 0x0001: Buffer overflow in
On Tue, Aug 20, 2002 at 11:28:32AM -0400, Tom Lane wrote: Nigel J. Andrews [EMAIL PROTECTED] writes: But going back to the idea that it seems that the only problem being publicised in the 'outside world' is the cash_out(2) version can we not do the restriction on acceptable input type in order to claim that the fix? Totally pointless IMHO, when the same problem exists in hundreds of other functions. Also, there really is no way to patch cash_out per se; the problem is a system-level problem, namely failure to enforce type checking. cash_out has no way to know that what it's been passed is the wrong kind of datum. Basically, we've used opaque as a substitute for accurate type declarations; that's got to stop. Hmm, are there _any_ cases where it's appropriate to call an 'opaque' function directly from user code? cash_out() and it's kin are type output functions that are called under controlled conditions, with backend controlled parameters. Trigger functions also are called with backend controlled parameters. Is there a 'hack' fix that doesn't allow opaque returning functions in user-defined locations? Ross ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Open 7.3 items
On Fri, Aug 16, 2002 at 10:21:12AM -0400, Vince Vielhaber wrote: RPMs aren't a good enough reason to put it in. All features aren't installed in an RPM, why would this need to? Besides, anything that is runtime configurable can end up getting its default changed on a whim. Then again as long as 7.2.1 is stable enough for me there's no reason to upgrade 'cuze I damn sure ain't going back and changing all sorts of programs and scripts that have global users. So, Vince, do you have problems with the various GUC based optimizer hooks getting set to other than the default? I'd think you'd notice if suddenly indexscans all went away, or any of these: #enable_seqscan = true #enable_indexscan = true #enable_tidscan = true #enable_sort = true #enable_nestloop = true #enable_mergejoin = true #enable_hashjoin = true My point is that your resistance to a GUC controlled runtime configurable on the basis of 'it might get changed accidently' makes little sense to me, given all the other runtime config settings that never do get changed. What makes you think this one will be more susceptible to accidental flipping? I'm not sure who's 'whim' it is that your afraid of: perhaps you have a paticularly sadistic DBA to deal with? ;-) And of course, this being free software and all, noone is forcing an upgrade on you. Ross ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] tsearch bug in 7.2.1?
On Thu, Aug 15, 2002 at 11:59:20AM +0300, Oleg Bartunov wrote:
tsearch has compiled-in stop-list, it's currently just not flexible
as OpenFTS does. We plan to move most functionality to tsearch but
currently have no time. Feel free to join us to speedup tsearch
development.
Oleg -
I think Chris's issue might be the same one I ran into just last night.
(BTW, thanks for tsearch and the OpenFTS work, it's really great)
My problem is that queries with only stopwords throw an ERROR, rather
than a WARNING or NOTICE. This means We've got to deal with catching an
exception so our middleware doesn't spew ugly errors and tracebacks at
our endusers, and I've got to deal with cleaning up the transaction.
Having the behavior be issue a notice and return no match would give
us a reasonably functional interface: if I don't implement reading the
NOTICE, I get confused users ('huh? the doesn't match anything?')
rather than irate users ('Your search interface sucks! It keeps
crashing!')
Oh, well, off to implement some try: catch: logic.
Ross
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Companies involved in development
On Thu, Aug 15, 2002 at 11:05:07AM -0400, Bruce Momjian wrote: I think we are going to see more company-funded developers working on PostgreSQL. There are a handful now, but I can see lots more coming. I am going to work on getting those funding companies more visibility. We originally were concerned that such involvement may harm the development process, but history has shown that it has only been a huge benefit for the community. I agree, and it's also true that while the visibility of all you paid-to-hack types is high, the fact that you _are_ paid, and by wha companies, is not. Just recently, one of the GnuE developers has been running on about SapDB, and mentioned how they've got '100 paid developers at SAP' and seemed to think that since GreatBridge died, pgsql is back to all volunteer. The fact that GB wasn't the first nor the only nor the last corp. funding pgsql development was clearly missed, even among a relatively PG savvy user community (GnuE is the GNU/Enterprise middleware system, and is is pgsql as its primary developement backend http://www.gnuenterprise.org/ ) Ross ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Inheritance
On Wed, Aug 14, 2002 at 09:39:06AM -0500, Greg Copeland wrote: On Tue, 2002-08-13 at 23:43, Curt Sampson wrote: Just my opinion of course, but I think it would be best to have a detailed description of how everything in inheritance is supposed to work, write a set of tests from that, and then fix the implementation to conform to the tests. And I think a detailed description comes most easily when you have a logical model to work from. I completely agree. This is why I want/wanted to pursue the theory and existing implementations angle. In theory, it sounds like a good idea. In practice ... ;-) Seems like everyone trying to jump on index spanning is premature. Seems like some people haven't looked at the history of the OO implementation in PostgreSQL. Actually, I think you'll find that once a PostgreSQL DBA gets to the point of designing a sufficently complex schema that inheritance might be useful, they quickly bump up against the lack of index and constraint spanning (most notably, referential integrity), and stop right there. This means that there is little community experience with the existing implementation, beyond the OO die hards. ;-) I'm not sure, but Bruce's suggestion of getting index spanning working first might move the existing implementation over the hump from 'interesting toy' to 'less than perfect implementation'. Then, the community can get some real world experience. Bruce has archived some of the emails - check your local pgsql source tree, under $PGSQLHOME/doc/TODO.detail/inheritance There was also some theoretical OO discussion, back when the change for default SELECT behavior on an inhertiance tree was made. (You used to have to say: SELECT foo from parent* to get foo from the parent and all children) Take a look at the archives and see if there's anything in that discussion that interests you: providing summary posts of old discussions is often a good way to restart and move an unresolved topic along. Ross ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] need assignment
On Wed, Jul 17, 2002 at 11:19:40PM +0800, Christopher Kings-Lynne wrote: i spoke w/jan some time ago (in a hurry now -- have to call salvation army to have them pick up my couch!). i need to jump in an discuss/get an assignment off the todo list. i am a cs doctoral student at gmu in va. i am the best programmer in the world. Wow. Yeah, my reaction as well. Here's hoping there's an invisible smiley after that statement. Especially since there's no other content. Ross ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
