Re: [HACKERS] leakproof
On Feb 19, 2012, at 5:42 PM, Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: Having now spent far too much time in bed with that patch, I'm feeling like the concept that we are really looking for there is what some languages call pure - that is, there must be no side effects, whether by throwing exceptions or otherwise. Hmm, pure doesn't sound bad to me. Nice and short. Technically, pure is stronger than has no side effects: http://en.wikipedia.org/wiki/Pure_function Result can't depend on state (for instance, database contents), either. This is the typical definition used in functional programming. gcc extends this to allow use of global variables in a pure function (the stricter definition is met by const functions). PG has immutable, so a slightly weaker pure probably wouldn't be terribly confusing given the gcc precedent (probably across their family of compilers). D adopts the stricter definition of pure. So there's some confusion around the term. But … I picked up this thread after leakproof was settled on and was curious as to what leakproof was supposed to be as I didn't read the earlier posts. I assumed it meant doesn't leak memory, which seems admirable and typical and not needful of an attribute on the function declaration. pure is definitely less confusing IMO, if it's congruent with the weaker sense of pure that's found in some languages/implementations. Don Baccus http://donb.photo.net http://birdnotes.net http://openacs.org -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] leakproof
On Feb 19, 2012, at 7:24 PM, Tom Lane wrote: Don Baccus dhog...@pacifier.com writes: On Feb 19, 2012, at 5:42 PM, Tom Lane wrote: Hmm, pure doesn't sound bad to me. Nice and short. Technically, pure is stronger than has no side effects: http://en.wikipedia.org/wiki/Pure_function Result can't depend on state (for instance, database contents), either. This is the typical definition used in functional programming. Well, that condition is subsumed in our idea of an immutable function. Yes, I said that myself, perhaps you didn't bother to read closely? It's not clear to me whether pure/leakproof functions are meant to be a strict subset of immutable functions Superset, not subset, unless my guessing is wrong. How could pure be a subset of immutable? OK, at this point, proponents will explain why ... But if you're not clear as to what a leakproof function is meant to be. then I suggest the definition must be defined very clearly, so everyone understands what it is meant to be. , but if they are then they meet this stricter definition. On the other hand, if pure/leakproof functions don't have to be immutable but only stable, then the stricter definition corresponds to pure immutable. That still doesn't sound too bad, as long as we define our terms clearly in the docs. Sure, let those making the proposal make things clear. Just speaking as a gadfly who's not posted here for probably close on 10 years … Don Baccus http://donb.photo.net http://birdnotes.net http://openacs.org -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] MySQL search query is not executing in Postgres DB
On Feb 18, 2012, at 12:57 PM, Rob Wultsch wrote: Where first_name is string the queries above have very different behaviour in MySQL. The first does a full table scan and coerces first_name to an integer (so '5adfs' - 5) Oh my, I can't wait to see someone rise to the defense of *this* behavior! Don Baccus http://donb.photo.net http://birdnotes.net http://openacs.org -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] MySQL search query is not executing in Postgres DB
On Feb 18, 2012, at 1:43 PM, Christopher Browne wrote: On Sat, Feb 18, 2012 at 4:12 PM, Don Baccus dhog...@pacifier.com wrote: On Feb 18, 2012, at 12:57 PM, Rob Wultsch wrote: Where first_name is string the queries above have very different behaviour in MySQL. The first does a full table scan and coerces first_name to an integer (so '5adfs' - 5) Oh my, I can't wait to see someone rise to the defense of *this* behavior! I can see a use, albeit a clumsy one, to the notion of looking for values WHERE integer_id_column like '1%' It's entirely common for companies to organize general ledger account numbers by having numeric prefixes that are somewhat meaningful. A hierarchy like the following is perfectly logical: - to 0999 :: Cash accounts [1] I asked earlier if anyone would expect 01 like '0%' to match … Apparently so! Your example is actually a good argument for storing account ids as text, because '' like '0%' *will* match. I'd think it nearly insane if someone was expecting '3%' to match not only the '3000 thru 3999' series, but also '300 to 399' and 30 to 39 and 3. How is PG supposed to know that integers compared to strings are always to be padded out to precisely 4 digits? Don Baccus http://donb.photo.net http://birdnotes.net http://openacs.org -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] MySQL search query is not executing in Postgres DB
On Feb 17, 2012, at 11:28 AM, Christopher Browne wrote: And I have *zero* confidence that for PostgreSQL to rule out LIKE '1%' is preventing those designs from getting built... When confronted by a difficult problem, solve it by reducing it to the question, How would the Lone Ranger handle this? He would turn to Tonto, who undoubtably would advise: If you want to treat an integer like a string, figure out how to convert your integer into a string … The original query strikes me as being similar to expecting the Lone Ranger's six-shooter to be capable of shooting Tonto's arrows. Don Baccus http://donb.photo.net http://birdnotes.net http://openacs.org -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] MySQL search query is not executing in Postgres DB
On Feb 17, 2012, at 4:12 PM, Josh Berkus wrote: On 2/17/12 12:04 PM, Robert Haas wrote: The argument isn't about whether the user made the right design choices; it's about whether he should be forced to insert an explicit type cast to get the query to do what it is unambiguously intended to do. I don't find INTEGER LIKE '1%' to be unambiguous. Prior to this discussion, if I had run across such a piece of code, I couldn't have told you what it would do in MySQL without testing. If someone showed it to me without mention MySQL I'd say: oh, it's an error. What *does* it do in MySQL? And knowing it's MySQL … oh, probably *not* an error, but like you … I'd be mystified. Should 01 like '0%' match? Don Baccus http://donb.photo.net http://birdnotes.net http://openacs.org -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] OOP real life example (was Re: Why is MySQL more
Hannu Krosing wrote: I guess what he meant was that you were arguing for arguments sake (mine is better than yours! Yes it is! Yes it is! ...) That's the dictionary definition of the phrase. and not to get to some solution, and that's the source of the frustration. I only re-subscribed to the list because we at OpenACS had examined PG's OO extensions quite thoroughly before rejecting the current implementation as being not useful for our work, and I thought our reasoning might be of interest. dismissing perfectly good arguments with a simplenot true statements and suggesting people to read heavy books with the claim that the truth is somewhere in there ;) and that's what's I mean when I say he's been arguing from authority. -- Don Baccus Portland, OR http://donb.photo.net, http://birdnotes.net, http://openacs.org ---(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] Inheritance
Bruce Momjian wrote: Christopher Kings-Lynne wrote: 1. The current implementation is broken. 2. We have no proper description of how a fixed implementation should work. Surely 99% of the implementation problems could be solved with an index type that can span tables? Right. Instead of talking in circles, let's figure out how to do it. If the issue is only sequence numbers, can we force a column to _only_ get values from the sequence counter, Even if primary keys were forced to be generated from a sequence (a very artificial restriction), unique constraints are also implemented by index. And people also join on columns other than their primary key so will want indexes on these columns to span tables, also. -- Don Baccus Portland, OR http://donb.photo.net, http://birdnotes.net, http://openacs.org ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] OOP real life example (was Re: Why is MySQL more
Greg Copeland wrote: On Tue, 2002-08-13 at 00:16, Curt Sampson wrote: I will revise my opinion the instant someone shows me something that I can't do relationally, or is easy to implement with inheritance, and difficult with relational methods. The traditional view approach requires unnecessary joins, and there's no getting around it. And yes I know he's not reading my mail and no, don't bother repeating this to him, he'll just continue to ignore the point. -- Don Baccus Portland, OR http://donb.photo.net, http://birdnotes.net, http://openacs.org ---(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] OOP real life example (was Re: Why is MySQL more
Bruce Momjian wrote: It is hard to argue with this logic. If he were actually making a technical argument I might actually agree with you myself. Thus far all he's done is argue from authority, and in tight circles to boot. Which means the term is an accurate description of his behavior ... Here's a lengthier and polite description - he's trying to impress us with his brilliance which several of us are just too dense to recognize on our own. -- Don Baccus Portland, OR http://donb.photo.net, http://birdnotes.net, http://openacs.org ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] OOP real life example (was Re: Why is MySQL more chosen
Curt Sampson wrote: On Sun, 11 Aug 2002, Don Baccus wrote: Oh? Ok, please translate the following into equivalant SQL that does not use a view: ... Granulize GRANT to the table column level. Can you please show me the code for that? After all, I showed you all of my code when doing equivalants. Obviously it would require extending SQL, but since you in part argue that SQL sucks in regard to the relational model this shouldn't matter, right? You're arguing the superiority of the relational model as described by DD over other models, non-relational SQL (which all agree has weaknesses) and most likely God. So don't flip-flop between the oh, SQL sucks think about the relational model and SQL doesn't support that. Pick one or the other. Argue SQL or DD/relational model. It's not hard to propose *extensions* to SQL that would allow granting of perms on a column rather than table level. Or are you saying that it's syntactic sugar only in some imaginary version of postgres that does not exist? Sort of like the idealized relational model that isn't implemented by SQL nor PG, but yet you reference again and again when it suits you to ignore the shortcomings of SQL92? Sure. Sorry, for a moment I thought you were interested in a meaningful discussion rather than a dick-waving contest but I was wrong. I give up. Your right hand waves your dick more frequently and with much more vigor than mine. This has nothing to do with with anything I care about, though. -- Don Baccus Portland, OR http://donb.photo.net, http://birdnotes.net, http://openacs.org ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] OOP real life example (was Re: Why is MySQL more chosen
Tom Lane wrote: Curt Sampson [EMAIL PROTECTED] writes: On Sun, 11 Aug 2002, Don Baccus wrote: Granulize GRANT to the table column level. Can you please show me the code for that? It's required by the SQL spec. PG hasn't got it, but the spec is perfectly clear about how it should be done. I think this is really a bit irrelevant to the thread topic, though. As far as the last goes, not really. Curtis argues from false premises, and this is one. If it were the only false premise he argues from, sure, I'd agree it's irrelevant but sadly Curtis argues from false premises by default. -- Don Baccus Portland, OR http://donb.photo.net, http://birdnotes.net, http://openacs.org ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [SECURITY] DoS attack on backend possible (was: Re:
Gavin Sherry wrote: As to your other point -- that this bug in the data/time code actually *reflects* the quality and reliability of the database itself -- you've really gone too far. The best software has bugs. For example, in the current version of Oracle 9i, if a client (say SQL*Plus) is running on a linux box and talking to Oracle running on a Solaris box, executes the following: create table foo(i integer primary key, bar blob); ... then later does ... update foo set bar=empty_blob() where i = some key value The Oracle server on Solaris crashes. *the whole thing* BANG! Shot-to-the-head-dead. Not the user's client - the server. This means that any user with the right to update a single table with a blob can crash Oracle at will. What does this say about Oracle's overall reliability? As Gavin says all software has bugs. Most of PG's bugs are far less spectacular than the Oracle bug I mention here. Overall I rate PG and Oracle as being about equivalent in terms of bugs. -- Don Baccus Portland, OR http://donb.photo.net, http://birdnotes.net, http://openacs.org ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] OOP real life example (was Re: Why is MySQL more
Curt Sampson wrote: ... the bugs in the postgres implementation of table inheritance, I've found the relational model much easier to use for solving problems. No one has argued that the shortcomings (not bugs, really, just things left out) makes the current implementation of very limited utility. As I mention this is exactly why we choose not to use it at OpenACS. On the other hand at least we took the time to understand how it actually does work before criticizing it. It's a pity, as I pointed out the reduction in joins alone would really be great. -- Don Baccus Portland, OR http://donb.photo.net, http://birdnotes.net, http://openacs.org ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Table Inheritance Discussion
Curt Sampson wrote: The last question comes up because, during the conversation up to this point, we seem to have implicitly accepted that table inheritance is an object-oriented way of doing things. Thinking further on this, however, I've decided that it's not in fact object-oriented at all. It's just type extensibility, really. As to why, again there's an efficiency argument, as I said earlier some joins can be avoided given PG's implementation of this feature: dotlrn=# create table foo(i integer primary key); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'foo_pkey' for table 'foo' CREATE dotlrn=# create table bar(j integer) inherits (foo); CREATE dotlrn=# explain select * from bar; NOTICE: QUERY PLAN: Seq Scan on bar (cost=0.00..20.00 rows=1000 width=8) EXPLAIN ... dotlrn=# create table foo(i integer primary key); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'foo_pkey' for table 'foo' CREATE dotlrn=# create table bar(i integer references foo primary key, j integer); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'bar_pkey' for table 'bar' NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) CREATE dotlrn=# create view foobar as select foo.*, bar.j from foo, bar; CREATE dotlrn=# explain select * from foobar; NOTICE: QUERY PLAN: Nested Loop (cost=0.00..30020.00 rows=100 width=8) - Seq Scan on foo (cost=0.00..20.00 rows=1000 width=4) - Seq Scan on bar (cost=0.00..20.00 rows=1000 width=4) EXPLAIN There's also some error checking (using my inherited example): dotlrn=# drop table foo; ERROR: Relation bar inherits from foo dotlrn=# Which doesn't exist in the view approach in PG at least (I'm unclear on standard SQL92 and of course this says nothing about the relational model in theory, just PG and perhaps SQL92 in practice). -- Don Baccus Portland, OR http://donb.photo.net, http://birdnotes.net, http://openacs.org ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] OOP real life example (was Re: Why is MySQL more chosen
Curt Sampson wrote: The problem is, table inheritance is just syntatic sugar for creating separate tables, and a view that does a UNION SELECT on them all together, projecting only the common columns. I've been wanting to point out that SQL views are really, when scrutinized, just syntactic sugar ... -- Don Baccus Portland, OR http://donb.photo.net, http://birdnotes.net, http://openacs.org ---(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] OOP real life example (was Re: Why is MySQL more chosen
Curt Sampson wrote: On Sun, 11 Aug 2002, Don Baccus wrote: I've been wanting to point out that SQL views are really, when scrutinized, just syntactic sugar ... Oh? Ok, please translate the following into equivalant SQL that does not use a view: CREATE TABLE t1 (key serial, value1 text, value2 text); CREATE VIEW v1 AS SELECT key, value1 FROM t1; GRANT SELECT ON v1 TO sorin; Granulize GRANT to the table column level. Then GRANT SELECT perms for the user on every column from the two tables that happen to be included in the view. Yes, it's awkward. So are the VIEW-based replacements for PG's type extensibility features. -- Don Baccus Portland, OR http://donb.photo.net, http://birdnotes.net, http://openacs.org ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Why is MySQL more chosen over PostgreSQL?
Curt Sampson wrote: On 8 Aug 2002, Hannu Krosing wrote: The main difference (in the inheritance part) is that a relation does not have one fixed set of fields, but can have any additional fields added in inherited tables and still be part of to the base table as well. This is trivial to do with a view. And views of this sort are trivial to do using PG's OO extensions. I think I see a trend in this thread. Why not give it up, dude? -- Don Baccus Portland, OR http://donb.photo.net, http://birdnotes.net, http://openacs.org ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Why is MySQL more chosen over PostgreSQL?
Curt Sampson wrote: On Thu, 8 Aug 2002, Don Baccus wrote: And views of this sort are trivial to do using PG's OO extensions. So long as you don't mind them being broken, yeah. But hell, when someone asks for a unique constraint, they probably don't really mean it, do they? Good grief, we all agree that they're currently broken and need to be fixed someday. Give it up. You're being a boor. -- Don Baccus Portland, OR http://donb.photo.net, http://birdnotes.net, http://openacs.org ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Table inheritance versus views
On Fri, 2002-08-02 at 22:39, [EMAIL PROTECTED] wrote: On 29 Jul 2002 18:27:40 MDT, the world broke into rejoicing as Stephen Deasey [EMAIL PROTECTED] said: Curt Sampson wrote: I'm still waiting to find out just what advantage table inheritance offers. I've asked a couple of times here, and nobody has even started to come up with anything. Table inheritance offers data model extensibility. New (derived) tables can be added to the system, and will work with existing code that operates on the base tables, without having to hack up all the code. But it kind of begs the question of why you're creating the new table in the first place. The new table certainly _won't_ work with existing code, at least from the perspective that the existing code doesn't _refer_ to that table. Since OpenACS has been brought up in this thread, I thought I'd join the list for a day or two and offer my perspective as the project manager. 1. Yes, we use views in our quasi-object oriented data model. They're automatically generated when content types are built by the content repository, for instance. 2. Yes, you can model anything you can model with PG's OO extensions using views. If you haven't implemented some way to generate the view automatically then a bit more work is required compared to using PG's OO extensions. 3. The view approach requires joins on all the subtype tables. If I declare type 'foo' then the view that returns all of foo's columns joins on all the subtype tables, while in the PG OO case all of foo's columns are stored in foo meaning I can get them all back with a simple query on the table. The PG OO approach can be considerably more efficient than the view approach, and this is important to some folks, no matter how many appeals to authority are made to various bibles on relational theory written by Date and Darwen. 4. The killer that makes the current implementation unusable for us is the fact that there's no form of indexing that spans all the tables inherited from a base type. This means there's no cheap enforcement of uniqueness constraints across a set of object types, among other things. Being able to inherit indexes and constraints would greatly increase the utility of PG's OO extensions. 5. If PG's OO extensions included inheritance of indexes and constraints, there's no doubt we'd use them in the OpenACS project, because when researching PG we compared datamodels written in this style vs. modelling the object relationships manually with automatically generated views. We found the datamodel written using PG's OO extensions not only potentially more efficient, but more readable as well. As far as whether or not there's a significant maintenance cost associated with keeping the existing OO stuff in PG, Tom Lane's voice is authorative while, when it comes to PG internals, Curt Sampson doesn't know squat. -- Don Baccus Portland, OR http://donb.photo.net, http://birdnotes.net, http://openacs.org ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] beta testing version
At 11:00 PM 12/2/00 -0800, Vadim Mikheev wrote: There is risk here. It isn't so much in the fact that PostgreSQL, Inc is doing a couple of modest closed-source things with the code. After all, the PG community has long acknowleged that the BSD license would allow others to co-op the code and commercialize it with no obligations. It is rather sad to see PG, Inc. take the first step in this direction. How long until the entire code base gets co-opted? I totaly missed your point here. How closing source of ERserver is related to closing code of PostgreSQL DB server? Let me clear things: (not based on WAL) That's wasn't clear from the blurb. Still, this notion that PG, Inc will start producing closed-source products poisons the well. It strengthens FUD arguments of the "open source can't provide enterprise solutions" variety. "Look, even PostgreSQL, Inc realizes that you must follow a close sourced model in order to provide tools for the corporate world." - Don Baccus, Portland OR [EMAIL PROTECTED] Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
Re: [HACKERS] beta testing version
At 01:06 PM 12/3/00 +0100, Peter Eisentraut wrote: Open source software is a privilege, I admit that I don't subscribe to Stallman's "source to software is a right" argument. That's far off my reality map. and nobody has the right to call someone "irresponsible" because they want to get paid for their work and don't choose to give away their code. However, I do have the right to make such statements, just as you have the right to disagree. It's called the first amendment in my country. - Don Baccus, Portland OR [EMAIL PROTECTED] Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
Re: [HACKERS] beta testing version
At 03:35 PM 11/30/00 -0800, Nathan Myers wrote: On Thu, Nov 30, 2000 at 07:02:01PM -0400, The Hermit Hacker wrote: v7.1 should improve crash recovery ... ... with the WAL stuff that Vadim is producing, you'll be able to recover up until the point that the power cable was pulled out of the wall. Please do not propagate falsehoods like the above. It creates unsatisfiable expectations, and leads people to fail to take proper precautions and recovery procedures. Yeah, I posted similar stuff to the PHPbuilder forum in regard to PG. The logging in 7.1 protects transactions against many sources of database crash, but not necessarily against OS crash, and certainly not against power failure. (You might get lucky, or you might just think you were lucky.) This is the same as for most databases; an embedded database that talks directly to the hardware might be able to do better. Let's put it this way ... Oracle, a transaction-safe DB with REDO logging, has for a very long time implemented disk mirroring. Now, why would they do that if you could pull the plug on the processor and depend on REDO logging to save you? And even then you're expected to provide adequate power backup to enable clean shutdown. The real safety you get is that your battery sez "we need to shut down!" but has enough power to let you. Transactions in progress aren't logged, but everything else can tank cleanly, and your DB is in a consistent state. Mirroring protects you against (some) disk drive failures (but not those that are transparent to the RAID controller/driver - if your drive writes crap to the primary side of the mirror and no errors are returned to the hardware/driver, the other side of the mirror can faithfully reproduce them on the mirror!) But since drives contain bearings and such that are much more likely to fail than electronics (good electronics and good designs, at least), mechanical failure's more likely and will be known to whatever is driving the drive. And you're OK then... - Don Baccus, Portland OR [EMAIL PROTECTED] Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
Re: [HACKERS] beta testing version
At 05:42 PM 12/2/00 +0100, Peter Eisentraut wrote: Don Baccus writes: Exactly what is PostgreSQL, Inc doing in this area? Good question... See http://www.erserver.com/. "Advanced Replication and Distributed Information capabilities are also under development to meet specific business and competitive requirements for both PostgreSQL, Inc. and clients. Several of these enhanced PostgreSQL, Inc. developments may remain proprietary for up to 24 months, with availability limited to clients and partners, in order to assist us in recovering development costs and continue to provide funding for our other Open Source contributions. " Boy, I can just imagine the uproar this statement will cause on Slashdot when the world finds out about it. - Don Baccus, Portland OR [EMAIL PROTECTED] Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
Re: [HACKERS] beta testing version
At 03:51 PM 12/2/00 -0600, Ross J. Reedstrom wrote: "We expect to have the source code tested and ready to contribute to the open source community before the middle of October. Until that time we are considering requests from a number of development companies and venture capital groups to join us in this process." Where's the damn core code? I've seen a number of examples already of people asking about remote access/replication function, with an eye toward implementing it, and being told "PostgreSQL, Inc. is working on that". It's almost Microsoftesque: preannounce future functionality suppressing the competition. Well, this is just all 'round a bad precedent and an unwelcome path for PostgreSQL, Inc to embark upon. They've also embarked on one fully proprietary product (built on PG), which means they're not an Open Source company, just a sometimes Open Source company. It's a bit ironic to learn about this on the same day I learned that Solaris 8 is being made available in source form. Sun's slowly "getting it" and moving glacially towards Open Source, while PostgreSQL, Inc. seems to be drifting in the opposite direction. if I absolutely need something that's only in CVS right now, I can bite the bullet and use a snapshot server. This work might be released as Open Source, but it isn't an open development scenario. The core work's not available for public scrutiny, and the details of what they're actually up don't appear to be public either. OK, they're probably funding Vadim's work on WAL, so the idictment's probably not 100% accurate - but I don't know that. I'd be really happy with someone reiterating the commitment to an open release, and letting us all know how badly the schedule has slipped. Remember, we're all here to help! Get everyone stomping bugs in code you're going to release soon anyway, and concentrate on the quasi-propriatary extensions. Which makes me wonder, is Vadim's time going to be eaten up by working on these quasi-proprietary extensions that the rest of us won't get for two years unless we become customers of Postgres, Inc? Will Great Bridge step to the plate and fund a truly open source alternative, leaving us with a potential code fork? If IB gets its political problems under control and developers rally around it, two years is going to be a long time to just sit back and wait for PG, Inc to release eRServer. These developments are a major annoyance. - Don Baccus, Portland OR [EMAIL PROTECTED] Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
Re: [HACKERS] RI Types
At 06:27 PM 12/2/00 -0500, Michael Fork wrote: I am trying to set the update and delete rules that are returned from the ODBC driver and the spec has the following to say: SQL_NO_ACTION: If a delete of a row in the referenced table would cause a "dangling reference" in the referencing table (that is, rows in the referencing table would have no counterparts in the referenced table), then the update is rejected. (This action is the same as the SQL_RESTRICT action in ODBC 2.x.) What I need to know is if RI_FKey_noaction_del and RI_FKey_restrict_del procedures are functionally the same. The ODBC (which I would hope conforms to SQL 9x) spec has 4 types of RI (CASCADE, NO_ACTION, SET_NULL, SET_DEFAULT), and Postgres appears to have 5 (RI_FKey_cascade_del, RI_FKey_noaction_del, RI_FKey_restrict_del, RI_FKey_setdefault_del, RI_FKey_setnull_del), which leads me to belive that restrict and noaction are the same thing, and the one that is used depends on what the user puts in the REFERENCES line. Am I correct? "RESTRICT" is a SQL3 thing, an extension to SQL92. It appears that the intent is that restrict should happen BEFORE the delete goes chunking its way through the tables, while noaction tries to delete then rolls back and gives an error if necessary. The final table entries are exactly the same for the RESTRICT and NOACTION cases, so the semantics in the sense of the transformation that occurs on the database are equivalent. Currently, PG treats NOACTION and RESTRICT as being the same, they're separated in the code with a comment to that effect, i.e. the code for NOACTION is duplicated for RESTRICT (in part to make it clear that in the future we might want to implement RESTRICT more efficiently if anyone figures out how). - Don Baccus, Portland OR [EMAIL PROTECTED] Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
Re: [HACKERS] beta testing version
At 09:56 PM 12/2/00 -0700, Ron Chmara wrote: ... And I really havn't seen much in the way of full featured products, complete with printed docs, 24 hour support, tutorials, wizards, templates, a company to sue if the code causes damage, GUI install, setup, removal, etc. etc. etc. Want to make money from open source? Well, you have to find, or build, a _product_. Right now, there are no OS db products that can compare to oh, an Oracle product, a MSSQL product. There may be superior code, but that doesn't make a difference in business. Business has very little to do with building the perfect mousetrap, if nobody can easily use it. Which of course is the business model - certainly not a "zero revenue" model as Thomas arrogantly suggests - which OSS service companies are following. They provide the cocoon around the code. I buy RH releases from Fry's. Yes, I could download, but the price is such that I'd rather just go buy the damned release CDs. I don't begrudge it, they're providing me a real SERVICE, saving me time, which saves me dollars in opportunity costs (given my $200/hr customer billing rate). They make money buy publishing releases, I still get all the sources. We all win. It is not a bad model. Question - if this model sucks, then certainly PG, Inc's net revenue last year was greater than any true open source software company's? I mean, let's see that slam against the "zero revenue business model" be proven by showing us some real numbers. Just what was PG, Inc's net revenue last year, and just how does their mixed revenue model stack up against the OSS world? (NOT the .com world, which is in a different business, no matter what Thomas wants to claim). - Don Baccus, Portland OR [EMAIL PROTECTED] Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
Re: [HACKERS] beta testing version
At 04:42 AM 12/3/00 +, Thomas Lockhart wrote: This statement of yours kinda belittles the work done over the past few years by volunteers. imho it does not, Sure it does. You in essence are saying that "advanced replication is so hard that it could only come about if someone were willing to finance a PROPRIETARY solution. The PG developer group couldn't manage it if it were done Open Source". In other words, it is much harder than any of the work done by the same group of people before they started working on proprietary versions. And that the only way to get them doing their best work is to put them on proprietary, or "semi-proprietary" projects, though 24 months from now, who's going to care? You've opened the door to IB prominence, not only shooting PG's open source purity down in flames, but probably PG, Inc's as well - IF IB can figure out their political problems. IB, as it stands, is a damned good product in many ways ahead of PG. You're giving them life by this approach, which is a kind of bizarre businees strategy. I *am* one of those volunteers Yes, I well remember you screwing up PG 7.0 just before beta, without bothering to test your code, and leaving on vacation. You were irresponsible then, and you're being irresponsible now. - Don Baccus, Portland OR [EMAIL PROTECTED] Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
Re: [HACKERS] beta testing version
At 09:29 PM 12/2/00 -0800, Adam Haberlach wrote: Red herring, and you know it. The question isn't whether or not your business generates income, but how it generates income. So far, Open Source doesn't. The VA Linux IPO made ME some income, but I'm not sure that was part of their plan... VA Linux is a HARDWARE COMPANY. They sell servers. "We've engineered 2U performance into a 1U box" is their current line. Dell probably makes more money on their Linux server offerings (I have to admit that donb.photo.net is running on one of their PowerEdge servers) than VA Linux does. If I can show you a HARDWARE COMPANY that is diving on selling MS NT servers, will you agree that this proves that the closed source and open source models both must be wrong, because HARDWARE COMPANIES based on each paradigm are losing money??? The .com'ers are trying it at the moment, and there seems to be a few flaws... ;) That's a horrible analogy, and I suspect you know it, but at least it is original. It wasn't an analogy. Sure it is. Read, damn it. First he makes the statement that a business based on open source is, by definition, a zero-revenue company then he raises the spectre of .com companies (how many of them are open source?) as support for his argument. OK, it's not an analogy, it's a disassociation with reality. Feel better? In any case, can we create pgsql-politics so we don't have to go over this issue every three months? Maybe you don't care about the open source aspect of this, but as a user with about 1500 Open Source advocates using my code, I do. If IB comes forth in a fully Open Source state my user base will insist I switch. And I will. And I'll stop telling the world that MySQL sucks, too. Or at least that they suck worse than the PG world :) There is risk here. It isn't so much in the fact that PostgreSQL, Inc is doing a couple of modest closed-source things with the code. After all, the PG community has long acknowleged that the BSD license would allow others to co-op the code and commercialize it with no obligations. It is rather sad to see PG, Inc. take the first step in this direction. How long until the entire code base gets co-opted? (Yeah, that's extremist, but seeing PG, Inc. lay down the formal foundation for such co-opting by taking the first step might well make the potential reality become real. It certainly puts some of the long-term developers in no position to argue against such a co-opted snitch of the code). I have to say I'm feeling pretty silly about raising such an effort to increase PG awareness in mindshare vs. MySQL. I mean, if PG, Inc's efforts somehow delineate the hopes and goals of the PG community, I'm fairly disgusted. - Don Baccus, Portland OR [EMAIL PROTECTED] Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
Re: [HACKERS] beta testing version
At 11:06 PM 11/30/00 -0800, Vadim Mikheev wrote: As for replaying logs against a restored snapshot dump... AIUI, a dump records tuples by OID, but the WAL refers to TIDs. Therefore, the WAL won't work as a re-do log to recover your transactions because the TIDs of the restored tables are all different. True for current way of backing up - ie saving data in "external" (sql) format. But there is another way - saving data files in their natural (binary) format. WAL records may be applyed to such dump, right? Right. That's what's missing in PG 7.1, the existence of tools to make such backups. Probably the best answer to the "what does WAL get us, if it doesn't get us full recoverability" questions is to simply say "it's a prerequisite to getting full recoverability, PG 7.1 sets the foundation and later work will get us there". - Don Baccus, Portland OR [EMAIL PROTECTED] Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
Re: [HACKERS] beta testing version
At 12:30 AM 12/1/00 -0800, Ian Lance Taylor wrote: For example, I would hope that EMC disk systems handle power loss gracefully. They must, their marketing literature says so :) But if you buy ordinary off the shelf PC hardware, you really do need to arrange for a UPS, and some sort of automatic shutdown if the UPS is running low. Which is what disk subsystems like those from EMC do for you. They've got build-in battery backup that lets them guarantee (assuming the hardware's working right) that in the case of a power outage, all blocks the operating system thinks have been written will in actuality be written before the disk subsystem powers itself down. - Don Baccus, Portland OR [EMAIL PROTECTED] Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
Re: [HACKERS] beta testing version
At 11:02 AM 12/1/00 -0800, Nathan Myers wrote: On Fri, Dec 01, 2000 at 06:39:57AM -0800, Don Baccus wrote: Probably the best answer to the "what does WAL get us, if it doesn't get us full recoverability" questions is to simply say "it's a prerequisite to getting full recoverability, PG 7.1 sets the foundation and later work will get us there". Not to quibble, but for most of us, the answer to Don's question is: "It gives a ~20x speedup over 7.0." That's pretty valuable to some of us. If it turns out to be useful for other stuff, that's gravy. Oh, but given that power failures eat disks anyway, you can just run PG 7.0 with -F and be just as fast as PG 7.1, eh? With no theoretical loss in safety? Where's your faith in all that doom and gloom you've been spreading? :) :) You're right, of course, we'll get roughly -F performance while maintaining a much more comfortable level of risk than you get with -F. - Don Baccus, Portland OR [EMAIL PROTECTED] Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
Re: [HACKERS] beta testing version
At 12:56 PM 12/1/00 -0800, Nathan Myers wrote: (Remember, we're talking about what you could do *now*, with 7.1. Presumably with 7.2 other options will open.) Maybe *you* are :) Seriously, I'm thinking out loud about future possibilities. Putting a lot of work into building up a temporary solution on top of 7.1 doesn't make a lot of sense, anyone wanting to work on such things ought to think about 7.2, which presumably will beta sometime mid-2001 or so??? And I don't think there are 7.1 hacks that are simple ... could be wrong, though. I don't know of any way to synchronously transfer the WAL, currently. Nope. Anyway, I would expect doing it to interfere seriously with performance. Yep. Anyone here have experience with replication and Oracle or others? I've heard from one source that setting it up reliabily in Oracle and getting the switch from the dead to the backup server working properly was something of a DBA nightmare, but that's true of just about anything in Oracle. Once it was up, it worked reliably, though (also typical of Oracle). The "wait to log a 'commit' locally until after the remote site acks that it got the WAL" is (akin to) the familiar two-phase commit. Right. - Don Baccus, Portland OR [EMAIL PROTECTED] Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
Re: [HACKERS] COPY BINARY is broken...
At 03:05 PM 12/1/00 -0800, Alfred Perlstein wrote: How about adding COPY XML? (kidding of course about the XML, but it would make postgresql more buzzword compliant :) ) Hey, we could add a parser and call the module MyXML ... - Don Baccus, Portland OR [EMAIL PROTECTED] Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
Re: [HACKERS] SQL 'in' vs join.
At 08:37 AM 11/30/00 -0500, mlw wrote: mlw wrote: Why is a "select * from table1 where field in (select field from table2 where condition )" is so dramatically bad compared to: "select * from table1, table2 where table1.field = table2.field and condition" Now, given the two components, each with very low costs, it chooses to do a sequential scan on the table. I don't get it. I have have been having no end of problems with Postgres' optimizer. It just seems to be brain dead at times. It is a huge point of frustration to me. I am tied to postgres in my current project, and I fear that I will not be able to implement certain features because of this sort of behavior. But but but ... Not only is the join faster, but it is more readable and cleaner SQL as well. I would never write the query in its first form. I'd change the second one slightly to "select table1.* from ...", though, since those are apparently the only fields you want. The optimizer should do a better job on your first query, sure, but why don't you like writing joins? - Don Baccus, Portland OR [EMAIL PROTECTED] Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
Re: [HACKERS] Please advise features in 7.1 (SUMMARY)
At 05:24 AM 11/30/00 +, Thomas Lockhart wrote: Is "if" clause support in PG? for example: "drop table aa if exist" "insert into aa values(1) if not exists select * from aa where i=1" No. afaict it is not in any SQL standard, so is unlikely to get much attention from developers. The insert, at least, can be written in standard SQL anyway... - Don Baccus, Portland OR [EMAIL PROTECTED] Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
Re: [HACKERS] SQL 'in' vs join.
At 10:52 AM 11/30/00 -0500, Tom Lane wrote: Don Baccus [EMAIL PROTECTED] writes: The optimizer should do a better job on your first query, sure, but why don't you like writing joins? The join wouldn't give quite the same answers. If there are multiple rows in table2 matching a particular table1 row, then a join would give multiple copies of the table1 row, whereas the WHERE foo IN (sub-select) way would give only one copy. SELECT DISTINCT can't be used to fix this, because that would eliminate legitimate duplicates from identical table1 rows. Hmmm...I was presuming that "field" was a primary key of table1, so such duplicates wouldn't exist (and SELECT DISTINCT would weed out duplicates from table2 if "field" isn't a primary key of table2, i.e. if table2 has a many-to-one relationship to table1). For many-to-many relationships yes, you're right, the "in" version returns a different result. Now that the executor understands about multiple join rules (for OUTER JOIN support), I've been thinking about inventing a new join rule that says "at most one output row per left-hand row" --- this'd be sort of the opposite of the LEFT OUTER JOIN rule, "at least one output row per left-hand row" --- and then transforming IN (sub-select) clauses that appear at the top level of WHERE into this kind of join. Won't happen for 7.1, though. Same trick could be used for some classes of queries which do a SELECT DISTINCT on the results of a join, too ... - Don Baccus, Portland OR [EMAIL PROTECTED] Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
Re: [HACKERS] beta testing version
At 07:02 PM 11/30/00 -0400, The Hermit Hacker wrote: v7.1 should improve crash recovery for situations like this ... you'll still have to do a recovery of the data on corruption of this magnitude, but at least with the WAL stuff that Vadim is producing, you'll be able to recover up until the point that the power cable was pulled out of the wall No, WAL won't help if an actual database file is corrupted, say by a disk drive hosing a block or portion thereof with zeros. WAL-based recovery at startup works on an intact database. Still, in the general case you need real backup and recovery tools. Then you can apply archives of REDOs to a backup made of a snapshot and rebuild up to the last transaction. As opposed to your last pg_dump. So what about mirroring (RAID 1)? As the docs tell ya, that protects you against one drive failing but not against power failure, which can cause bad data to be written to both mirrors if both are actively writing when the plug is pulled. Power failures are evil, face it! :) - Don Baccus, Portland OR [EMAIL PROTECTED] Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
Re: [HACKERS] beta testing version
At 05:15 PM 11/30/00 -0800, Nathan Myers wrote: As for replaying logs against a restored snapshot dump... AIUI, a dump records tuples by OID, but the WAL refers to TIDs. Therefore, the WAL won't work as a re-do log to recover your transactions because the TIDs of the restored tables are all different. Actually, the dump doesn't record tuple OIDs (unless you specifically ask for them), it just dumps source sql. When this gets reloaded you get an equivalent database, but not the same database, that you started out with. That's why I've presumed you can't run the WAL against it. If you and I are wrong I'd love to be surprised! - Don Baccus, Portland OR [EMAIL PROTECTED] Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
Re: [HACKERS] beta testing version
At 04:17 PM 11/28/00 +0800, xuyifeng wrote: Hi, how long is PG7.1 already in beta testing? can it be released before Christmas day? can PG7.1 will recover database from system crash? This guy's a troll from the PHP Builder's site (at least, Tim Perdue and I suspect this due to some posts he made in regard to Tim's SourceForge/Postgres article). Since he's read Tim's article, and at least some of the follow-up posts (given that he's posted responses himself), he should know by now that PG 7.1 is still in a pre-beta state and won't be released before Christmas day. I also posted a fairly long answer to a question Tim's posted at phpbuilder.com regarding recoverability and this guy's undoubtably read it, too. Have I forgotten anything, xuyifeng? - Don Baccus, Portland OR [EMAIL PROTECTED] Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
Re: [HACKERS] Question about Oracle compatibility
At 09:59 AM 11/28/00 +, Pete Forman wrote: Mario Weilguni writes: This gets really bad when the actual data is coming from a webinterface, I've to handle 2 different queries for the case empty string and non-empty string. In their documentation both Oracle 7 and 8 state: Oracle currently treats a character value with a length of zero as null. However, this may not continue to be true in future releases, and Oracle recommends that you do not treat empty strings the same as NULLs. Yeah, but this is harder than it sounds! NULL and '' are indistinguishable in queries, so how do you treat them differently? Has to be in the application code, I guess. - Don Baccus, Portland OR [EMAIL PROTECTED] Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
RE: [HACKERS] Constraint names using 'user namespace'?
At 02:18 PM 11/28/00 +0800, Christopher Kings-Lynne wrote: As for the treading-on-user-namespace issue, we already do that for all implicitly created indexes (see UNIQUE, PRIMARY KEY, etc). I'd prefer to treat named constraints consistently with that long-established practice until we have a better idea that can be implemented uniformly across that whole set of constructs. (Once we have schemas, for example, it might be practical to give indexes a separate namespace from tables, which'd help a lot.) Surely the best way to do it would be to make the unique and primary key implicitly created indices totally invisible to the user. Or at least add a 'system' flag to their entries in the pg_indexes table. Create a pg_constraint table instead that people can use to find constraints. Oracle has a "user_constraints" table. Explicitly named contraints have that name entered into the user's namespace, implicitly named constraints get stuffed into "sys" in the form "sys.cn", where "n" is drawn from some system sequence. In Oracle you NEED the user_constraints table, particularly for RI constraint errors, because their wonderful error messages just give you the RI constraint name. If you've not given it a meaningful name yourself, which typically one doesn't ("integer references some_table"), you need to do a select on the user_constraints table to see what went wrong. Keep PG's superior error messages no matter what else is done :) The above is offered as a datapoint, that's all. To support this, dropping unique and pk constraints would no longer be possible (and _should_ no longer be possible) with a CREATE/DROP INDEX command, and instead would be achieved with a functional ALTER TABLE ADD/DROP CONSTRAINT statement. This is essentially the case in Oracle, though I suspect you could dig around, find the name of the unannounced unique index, and drop it by hand if you wanted. - Don Baccus, Portland OR [EMAIL PROTECTED] Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
Re: [HACKERS] Re: [NOVICE] Re: re : PHP and persistent connections
At 10:46 AM 11/27/00 -0600, Ross J. Reedstrom wrote: Uh, Don? Not all the world's a web page, you know. Thatkind of thinking is _so_ mid 90's ;-) Dedicated apps that talk directly the user seem to be making a comeback, due to a number of factors. They can have much cleaner user interfaces, for example. Of course. But the question's been raised in the context of a web server, and I've answered in context. I've been trying to move the discussion offline to avoid clogging the hackers list with this stuff but some of the messages have escaped my machine with my forgetting to remove pg_hackers from the distribution list. I'll try to be more diligent if the discussion continues. - Don Baccus, Portland OR [EMAIL PROTECTED] Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
Re: [HACKERS] Question about Oracle compatibility
At 12:39 PM 11/27/00 -0500, Alex Perel wrote: On Mon, 27 Nov 2000, Mario Weilguni wrote: Sorry if I'm posting to the wrong list, but I don't know which list is appropriate for this question. I've a question concerning compatibilty Postgres - Oracle. In Oracle, empty strings and null are basicly the same, but it does not seem to be under Postgres, making migration a pain. Actually, they aren't the same at all under Oracle or under Postgres. A null represents a lack of data, whereas an empty string is represents data of zero length and zero content. Null is a state and not a value. Unfortunately Mario's entirely correct (I use Oracle...) insert into foo (some_string) values (''); will insert a NULL, not an empty string, into the column some_string. What you are probably seeing is a difference in table layout that sets a default value of '' for the particular column you're touching. You can have postgres do the same by specifying DEFAULT '' when you create your table (or you could ALTER it in..). Using "DEFAULT ''" might help some, but he specifically mentioned inserting form data from a web page, and in this case he'll have to check the string and explicitly insert NULL (or write a trigger for each table that does the check and the resulting massage of the value) or rewrite his queries to treat empty string as being the same as NULL explicitly. Null values are actually quite important because they tell you when you don't have data. An empty tring means something is there, whereas a null in the same place means complete absense of all data. Absolutely right, and Oracle's misimplementation truly sucks. - Don Baccus, Portland OR [EMAIL PROTECTED] Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
Re: [HACKERS] 8192 BLCKSZ ?
At 08:39 PM 11/27/00 -0500, Bruce Momjian wrote: [ Charset ISO-8859-1 unsupported, converting... ] If it breaks anything in PostgreSQL I sure haven't seen any evidence -- the box this database is running on gets hit pretty hard and I haven't had a single ounce of trouble since I went to 7.0.X Larger block sizes mean larger blocks in the cache, therefore fewer blocks per megabyte. The more granular the cache, the better. Well, true, but when you have 256 MB or a half-gig or more to devote to the cache, you get plenty of blocks, and in pre-PG 7.1 the 8KB limit is a pain for a lot of folks. Though the entire discussion's moot with PG 7.1, with the removal of the tuple-size limit, it has been unfortunate that the fact that a blocksize of up to 32KB can easily be configured at build time hasn't been printed in a flaming-red oversized font on the front page of www.postgresql.org. THE ENTIRE WORLD seems to believe that PG suffers from a hard-wired 8KB limit on tuple size, rather than simply defaulting to that limit. When I tell the heathens that the REAL limit is 32KB, they're surprised, amazed, pleased etc. This default has unfairly contributed to the poor reputation PG has suffered from for so long due to widespread ignorance that it's only a default, easily changed. For instance the November Linux Journal has a column on PG, favorable but mentions the 8KB limit as though it's absolute. Tim Perdue's article on PHP Builder implied the same when he spoke of PG 7.1 removing the limit. Again, PG 7.1 removes the issue entirely, but it is ironic that so many people had heard that PG suffered from a hard-wired 8KB limit on tuple length... - Don Baccus, Portland OR [EMAIL PROTECTED] Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
Re: [HACKERS] 8192 BLCKSZ ?
At 09:30 PM 11/27/00 -0500, Bruce Momjian wrote: Well, true, but when you have 256 MB or a half-gig or more to devote to the cache, you get plenty of blocks, and in pre-PG 7.1 the 8KB limit is a pain for a lot of folks. Agreed. The other problem is that most people have 2-4MB of cache, so a 32k default would be too big for them. I've always been fine with the default, and in fact agree with it. The OpenACS project recommends a 16KB default for PG 7.0, but that's only so we can hold reasonable-sized lzText strings in forum tables, etc. I was only lamenting the fact that the world seems to have the impression that it's not a default, but rather a hard-wired limit. - Don Baccus, Portland OR [EMAIL PROTECTED] Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
Re: [HACKERS] Full text Indexing -out of contrib and into main..
At 11:06 PM 11/27/00 -0400, The Hermit Hacker wrote: On Mon, 27 Nov 2000, Bruce Momjian wrote: OK, can someone collect suggestions, add the code, and integrate it for 7.1? too late in cycle ... Yes... - Don Baccus, Portland OR [EMAIL PROTECTED] Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
Re: [HACKERS] Re: [NOVICE] Re: re : PHP and persistent connections
At 12:07 AM 11/26/00 -0500, Alain Toussaint wrote: how about having a middle man between apache (or aolserver or any other clients...) and PosgreSQL ?? that middleman could be configured to have 16 persistant connections,every clients would deal with the middleman instead of going direct to the database,this would be an advantage where multiple PostgreSQL server are used... Well, this is sort of what AOLserver does for you without any need for middlemen. Again, reading stuff like this makes me think "ugh!" This stuff is really pretty easy, it's amazing to me that the Apache/db world talks about such kludges when they're clearly not necessary. My first experience running a website (donb.photo.net) was with Apache on Linux on an old P100 system in 1996 when few folks had personal photo sites with 1000 photos on them getting thousands of hits a day. I have fond memories of those days, and Apache served me (or more properly webserved my website) well. This site is largely responsible for my reputation that lets me freelance nature photography to the national media market pretty much at will. Thus my fondness. But ... for database stuff the release of AOLserver as first Free Beer, and now Free Speech software has caused me to abandon Apache and suggestions like the above just make me cringe. It shouldn't be that hard, folks. - Don Baccus, Portland OR [EMAIL PROTECTED] Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
Re: [HACKERS] Re: [NOVICE] Re: re : PHP and persistent connections
At 10:00 PM 11/25/00 -0800, Mitch Vincent wrote: I've tried quite a bit to use persistent connections with PHP (for over a year) and always the scripts that I try to use them with behave crazy... The last time I tried there were problems all over the place with PHP, variables getting overwritten, certain functions just totally breaking (date() to name one) and so on.. I know I'm not being specific but my point is that I think there are some other outstanding PHP issues that play into this problem as the behavior that I've seen isn't directly related to PostgreSQL but only happens when I use persistent connections.. I've heard rumors that PHP isn't thoroughly threadsafe, could this be a source of your problems? - Don Baccus, Portland OR [EMAIL PROTECTED] Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
Re: [HACKERS] OK, that's one LOCALE bug report too many...
At 07:32 PM 11/24/00 -0500, Tom Lane wrote: Possible compromise: let initdb accept en_US, but have it spit out a warning message: NOTICE: initializing database with en_US collation order. If you're not certain that's what you want, then it's probably not what you want. We recommend you set LC_COLLATE to "C" and re-initdb. For more information see appropriate place in admin guide Thoughts? Are you SURE you want to use en_US collation? [no] (ask the question, default to no?) Yes, a question in initdb is ugly, this whole thing is ugly. - Don Baccus, Portland OR [EMAIL PROTECTED] Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
Re: AW: [HACKERS] Please advise features in 7.1
At 12:28 PM 11/23/00 +0100, Zeugswetter Andreas SB wrote: Reason: I want to know if any of these features are scheduled. 1. Calculated fields in table definitions . eg. Create table test ( A Integer, B integer, the_sum As (A+B), ); This is currently easily done with a procedure that takes a tabletype parameter with the name the_sum returning the sum of a + b. Create table test ( A Integer, B integer ); create function the_sum (test) returns integer as ' begin; return ($1.a + $1.b); end; ' language 'plpgsql'; A select * won't return the_sum create view test2 select A, B, A+B as the_sum from test; will, though. See, lots of ways to do it! - Don Baccus, Portland OR [EMAIL PROTECTED] Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
Re: [HACKERS] Please advise features in 7.1
At 06:00 PM 11/23/00 +1300, John Huttley wrote: 1. Calculated fields in table definitions . eg. Create table test ( A Integer, B integer, the_sum As (A+B), ); ... These are _extraordinarily_ useful for application development. If anyone has a way of bolting on any of these to 7.0, I'd be keen to hear from you. Create a trigger on insert/update for this case... - Don Baccus, Portland OR [EMAIL PROTECTED] Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
[HACKERS] OpenACS datamodel vs. current PG 7.1 sources
After Tom's bug fix, I can now load the data model with no problem. Very cool, I'm pumped! - Don Baccus, Portland OR [EMAIL PROTECTED] Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
Re: [HACKERS] Table/Column Constraints
At 12:18 AM 11/21/00 -0500, Tom Lane wrote: Don Baccus [EMAIL PROTECTED] writes: If this problem is attacked, should one stop at constraints or make certain that other elements like views are dumped properly, too? (or were views fixed for 7.1, I admit to a certain amount of "ignoring pgsql-hackers over the last few months") ... Views do seem to be dumped as views by current sources. Good...definitely a step in the right direction! - Don Baccus, Portland OR [EMAIL PROTECTED] Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
RE: [HACKERS] Crash during WAL recovery?
At 02:01 PM 11/21/00 -0800, Mikheev, Vadim wrote: This snippet in xlog.c makes we wonder... else if (ControlFile-state == DB_IN_RECOVERY) { elog(LOG, "Data Base System was interrupted being in recovery at %s\n" "\tThis propably means that some data blocks are corrupted\n" "\tAnd you will have to use last backup for recovery", str_time(ControlFile-time)); } I thought this was going to be crash safe. WAL doesn't protect against disk block corruption what could be reason of crash (or elog(STOP)) during recovery in most cases. Apart from disk corruption recovery is (or should be -:)) crash safe. Which is why we'll still need BAR tools later. The WAL log can be used to recover from a crash if the database itself isn't corrupted (disk corruption, whatever), but not otherwise because it applies logged data to the database itself. The WAL log doesn't include changes caused by renegade disk controllers, etc :) BAR tools will allow recovery via archives of WAL logs applied to an archive of the database, to recreate the database in the case where the existing database has been corrupted. In Oracle parlance, "WAL" log == "REDO" log, and the BAR tool builds "Archive" logs. Uhhh...I think, anyway. - Don Baccus, Portland OR [EMAIL PROTECTED] Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
RE: [HACKERS] Crash during WAL recovery?
At 09:14 AM 11/22/00 +0800, Christopher Kings-Lynne wrote: Is there any particular reason the spelling and punctuation in the code snippet below is so bad? Vadim's Russian. This impacts his english but not his ability to implement complex features like MVCC and WAL :) - Don Baccus, Portland OR [EMAIL PROTECTED] Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
Re: [HACKERS] query plan optimizer bug
At 10:46 AM 11/22/00 +0800, xuyifeng wrote: Hi, it's obviously there is a query plan optimizer bug, if int2 type used in fields, the plan generator just use sequence scan, it's stupid Have you checked this with real data after doing a VACUUM ANALYZE? - Don Baccus, Portland OR [EMAIL PROTECTED] Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
Re: [HACKERS] Crash during WAL recovery?
At 12:29 AM 11/22/00 -0500, Tom Lane wrote: Is there any particular reason the spelling and punctuation in the code snippet below is so bad? Vadim's Russian. This impacts his english but not his ability to implement complex features like MVCC and WAL :) As someone who can't speak anything but English worth a damn (even though I was raised in Spanish-speaking countries, so you'd think I'd have acquired at least one clue), I have long since learned not to criticize the English of non-native speakers. I think it's certain that the original poster didn't realize Vadim is not a native English speaker, which is why I made my comment (to clue him in). Vadim didn't take my comment as criticism, as his follow-on post made clear (he got the joke). I don't know from your post if you thought I was adding to the criticism or not, but I can say with certainty I wasn't. In my previous life as the founder of a company specializing in optimizing compilers for minicomputers, I employed Dutch (who spoke and wrote English than I or anyone here), Polish, Vietmanese and other nationals who were excellent hackers and who all spoke better English than I spoke their language - or cooked their cuisine or even followed their table customs, for that matter. More generally, a lot of the PG documentation could use the attention of a professional copy editor --- and I'm sad to say that the parts contributed by native English speakers aren't necessarily any cleaner than the parts contributed by those who are not. If you have the time and energy to submit corrections, please fall to! This is very much true. PG needs some good documentation volunteers. I'm not denigrating the current efforts, because PG documention's pretty good all things considered. But some volunteers devoted to improving the docs could accomplish a lot. - Don Baccus, Portland OR [EMAIL PROTECTED] Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
Re: [HACKERS] (download ANSI SQL benchmark?) Re: Postgres article
At 10:19 AM 11/21/00 +, Pete Forman wrote: Don Baccus writes: I also hope that the PG crew, and Great Bridge, never stoop so low as to ship benchmarks wired to "prove" PG's superiority. I thought that Great Bridge's August benchmarks were rather skewed. They only used one particular test from the AS3AP suite. That was the basis for their headline figure of 4-5 times the performance of the competition. I was however impressed by the TPC-C results. MySQL and Interbase were unable to complete them. PostgreSQL showed almost identical performance over a range of loads to Proprietary 1 (version 8.1.5, on Linux) and Proprietary 2 (version 7.0, on NT). Great Bridge didn't do the benchmarking, they hired a third party to do so. And that third party didn't, AFAIK, cherry-pick tests in order to "prove" PG's superiority. The report itself mentioned the testing group's surprise over MySQL's poor showing in the simple, non-TPC-C test. I'm sure it was tossed in so they could answer the question "how much does it cost you to use a transaction-based system rather than MySQL", since avoiding that overhead is the big argument that the MySQL makes in favor of their product. I'm sure the hope was there that the answer would be "not all that much", instead the answer was "gee, you're not that fast after all". Clearly the real target of the benchmark effort was Oracle. However inadequate the benchmarking effort might've been (they're all inadequate, after all) the fact is that Great Bridge at least did run a set of standard benchmarks. The MySQL folk have always cherry-picked their benchmarks, long lied about features in PG, do their benchmarking using default values for PG's shared buffer etc WITHOUT TELLING PEOPLE while at the same time installing MySQL with larger-than-default memory usage limits (the group hired by GB used MySQL's default installation, but EXPLICITLY SAID SO in the report), etc. The GB-financed benchmarks weren't perfect, but they weren't dishonest. The MySQL folks have done things over the years that have been out-and-out dishonest, IMO. - Don Baccus, Portland OR [EMAIL PROTECTED] Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
[HACKERS] building current sources
After reading Vadim's note stating the WAL is enabled by default, I downloaded sources from CVS to rebuild the latest version. There are errors in ecpg's preproc.y grammar that weren't there in the CVS sources I built yesterday. The two rules "createdb_opt_item" and "createdb_opt_list" aren't declared as type string at the front of the file, so bison gets upset. Two other rules, "createdb_opt_encoding" and "createdb_opt_location" were declared as type string but never defined as rules. Bison doesn't like that, either. The first clause for rule CreatedbStmt references a sixth item that doesn't exist in the definition. I've cleaned these up locally (though I just removed the $6 mentioned last because I have no idea what was intended) so I can compile. Someone should clean up the CVS sources... - Don Baccus, Portland OR [EMAIL PROTECTED] Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
Re: [HACKERS] PG 7.1 pre-beta bug ...
At 07:20 PM 11/20/00 -0500, Tom Lane wrote: Don Baccus [EMAIL PROTECTED] writes: All went well except for a handful of occurances of the following error: ERROR: SS_finalize_plan: plan shouldn't reference subplan's variable Fixed, I believe. Your test case now gives regression=# select user_group_add('group', 'shortname', 'prettyname', 'f'); ERROR: ExecAppend: Fail to add null value in not null attribute registration_date but that's correct AFAICT, and 7.0.2 agrees... Yeah, I boiled down my example a bit too far for the case where the RDBMS works, apparently :) There's probably a trigger to fill the registration date that I stripped out, something like that. Thanks ... I'll not be able to get back to testing until later this week (I'm busy with a client site, Oracle-based, boo-hoo). - Don Baccus, Portland OR [EMAIL PROTECTED] Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
Re: [HACKERS] Table/Column Constraints
At 10:49 PM 11/20/00 -0500, Tom Lane wrote: "Christopher Kings-Lynne" [EMAIL PROTECTED] writes: Just to catch up here - does this mean that pg_dump has issues with correctly recreating the contraints? Well, if you examine the pg_dump output, it doesn't really try --- you'll see no sign of any foreign-key constraint declarations in a pg_dump script, for example, only trigger declarations. This is correct as far as reproducing the working database goes, but it's bad news for making a readable/modifiable dump script. Short story, you are both right. Chris - the dumps reload and recreate the constraints (in other words, the answer to your question is "no") Tom's correct in that decyphering the dump output is an ... interesting problem. (Tom, I just want to make sure that Chris undertands that dump/restore DOES restore the constraints. The "it doesn't really try" statement you made, if hastily read without the qualifier, would lead one to believe that a dump/restore would lose constraints). What Tom's saying is the internal implementation of the SQL constraints are exposed during the dump, where it would be much better if the SQL that constructed the constraint were output instead. The implementation isn't hidden from the dump, rather the declaration is hidden. What's worse, this representation ties us down over version updates: we cannot easily change the internal representation of constraints, because the internal representation is what's getting dumped. Which follows up my statement above perfectly. If the implementation were hidden, and the SQL equivalent dumped, we could change the implementation without breaking dump/restore ACROSS VERSIONS. (I capped because WITHIN A VERSION dump/restore works fine). Problem is that there are 5 difference types of constraints, implemented in 5 different ways. Do you want a unifed, central catalog of constraints, or just for some of them, or what? Dunno. Maybe a unified representation would make more sense, or maybe it's OK to treat them separately. The existing implementations of the different types of constraints were done at different times, and perhaps are different "just because" rather than for any good reason. We need investigation before we can come up with a reasonable proposal. I think you hit the nail on the head when earlier you said that representation was driven by the implementation. Of course, one could say this is something of a PG tradition - check out views, which in PG 7.0 still are dumped as rules to the rule system, which no other DB will understand. So I can't say it's fair to pick on newer contraints like RI - they build on a tradition of exposing the internal implementation to pg_dump and its output, they didn't invent it. If this problem is attacked, should one stop at constraints or make certain that other elements like views are dumped properly, too? (or were views fixed for 7.1, I admit to a certain amount of "ignoring pgsql-hackers over the last few months") - Don Baccus, Portland OR [EMAIL PROTECTED] Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
Re: [HACKERS] Table/Column Constraints
At 12:03 AM 11/21/00 -0500, Tom Lane wrote: Peter has remarked that the SQL spec offers a set of system views intended to provide exactly this info. That should be looked at; if there's a workable standard for this stuff, we oughta follow it. This and a BUNCH else. - Don Baccus, Portland OR [EMAIL PROTECTED] Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
Re: [HACKERS] Re: [GENERAL] PHPBuilder article -- Postgres vs MySQL
At 09:43 AM 11/13/00 -0600, [EMAIL PROTECTED] wrote: I made it all the way through the article. I'll summarize it for you: Postgres - hooray! MySQL - boo! Since this is an open source database article linked off of slashdot, I imagine they're getting pounded. Why is all this e-mail showing up so late? (I'm curious because there have been complaints about the mail server here, and the article is old hat). - Don Baccus, Portland OR [EMAIL PROTECTED] Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
Re: [HACKERS] (download ANSI SQL benchmark?) Re: Postgres article
At 10:24 AM 11/13/00 -0800, Limin Liu wrote: >>>> This's great. I have tested Postgres and MySQL with the benchmark shipped with mysql and (of course) MySQL out perform Postgres. So how many simultaneous read/write processes does the MySQL benchmark fire up? Why test a benchmark provided by the mysql folk? That's like trying the benchmark provided by Intel for the initial Pentium 4 announcement and ignoring all the benchmarks they didn't provide you because AMD thunderbird+DDR (AMD 760 chipset) kicks P4 butt on many of them. I should hope you're not so naive as to suppose that the MySQL folk would ship a benchmark showing better performance by PG (or Oracle, or Sybase etc)? I also hope that the PG crew, and Great Bridge, never stoop so low as to ship benchmarks wired to "prove" PG's superiority. They MySQL folk have been liars and cheaters for years, there's no reason to put any faith into their benchmark efforts. >>>> - Don Baccus, Portland OR [EMAIL PROTECTED]> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
Re: [HACKERS] RE: [COMMITTERS] pgsql/src/backend/access/transam (xact.c xlog.c)
At 07:05 PM 11/19/00 +0100, [EMAIL PROTECTED] wrote: Cam I ask what BAR is ? Backup and recovery, presumably... - Don Baccus, Portland OR [EMAIL PROTECTED] Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
Re: [HACKERS] Coping with 'C' vs 'newC' function language namesh
At 05:51 PM 11/16/00 +0200, 'Marko Kreen' wrote: On Thu, Nov 16, 2000 at 09:32:43AM -0600, Ross J. Reedstrom wrote: On Thu, Nov 16, 2000 at 04:16:26PM +1100, Philip Warner wrote: Create Module foo_mod from library 'path-to-lib'; Phil - be careful with the nomenclature. We've got another naming collision, here. SQL9[29] talk about modules, which may or may not be related to what your suggesting here. Do you know any url's where the SQL* standards could be looked up? I have a copy of the SQL92 draft (the one that's circulated among this group in the past) at dsl-dhogaza.pacifier.net. Just use anonymous ftp, it's in the pub directory with an obvious name (sql1992.txt???) - Don Baccus, Portland OR [EMAIL PROTECTED] Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
Re: [HACKERS] RE: [COMMITTERS] pgsql/src/backend/access/transam ( xact.c xlog.c)
At 02:13 PM 11/16/00 -0500, Bruce Momjian wrote: I think the default should probably be no delay, and the documentation on enabling this needs to be clear and obvious (i.e. hard to miss). I just talked to Tom Lane about this. I think a sleep(0) just before the flush would be the best. It would reliquish the cpu slice if another process is ready to run. If no other backend is running, it probably just returns. If there is another one, it gives it a chance to complete. On return from sleep(0), it can check if it still needs to flush. This would tend to bunch up flushers so they flush only once, while not delaying cases where only one backend is running. This sounds like an interesting approach, yes. - Don Baccus, Portland OR [EMAIL PROTECTED] Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
Re: [HACKERS] RE: [COMMITTERS] pgsql/src/backend/access/transam ( xact.c xlog.c)
At 09:32 AM 11/16/00 -0800, Alfred Perlstein wrote: * Bruce Momjian [EMAIL PROTECTED] [001116 08:59] wrote: Ewe, so we have this 1/200 second delay for every transaction. Seems bad to me. I think as long as it becomes a tunable this isn't a bad idea at all. Fixing it at 1/200 isn't so great because people not wrapping large amounts of inserts/updates with transaction blocks will suffer. I think the default should probably be no delay, and the documentation on enabling this needs to be clear and obvious (i.e. hard to miss). - Don Baccus, Portland OR [EMAIL PROTECTED] Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
Re: [HACKERS] Re: [GENERAL] PHPBuilder article -- Postgres vs MySQL
At 01:53 PM 11/15/00 -0500, markw wrote: I'd rather not pollute the application's SQL with postgres-isms. Not that I don't love postgres, but there are always critics looking for a reason to use Oracle or (gasp) MS-SQL. Define some global variable with the name of the database being run (currently only Postgres) and guard the SET statement with a conditional... In the OpenACS project we've got little functions that return query snippets called things like "db_nextval" that return either "sequence_name.nextval" or "nextval('sequence_name')" depending on whether the code's running under Oracle or Postgres. That helps us minimize differences in the source. - Don Baccus, Portland OR [EMAIL PROTECTED] Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
Re: [HACKERS] Re: [GENERAL] PHPBuilder article -- Postgres vs MySQL
At 09:27 AM 11/15/00 -0800, Tom Samplonius wrote: AOLServer isn't the only system that can pool database connections, so can servlets/JSP, ColdFusion, ASP, etc. No doubt AOLServer would be more widely accepted if it used something other than TCL. There are two separate modules that support Java in AOLserver: ns_tomcat which provides an identical interface as Apache tomcat (and no real advantages) and ns_java, which is coming out of the OpenACS project. ns_java exposes AOLserver's pooled, persistent database API to java. There's also support available for Python, though there's still a lot of work to be done to support the full AOLserver API (same's true of ns_java, actually). If you use ADP pages, your use of Tcl is typically restricted to snippets of code anyway, so I've never really understood the complaints about Tcl... - Don Baccus, Portland OR [EMAIL PROTECTED] Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
Re: [HACKERS] Danish database patent
At 06:35 PM 11/11/00 +0200, Hannu Krosing wrote: I took only a brief look at them, so i may be in a state of misunderstanding :) As I understand it, the patent is about generating/composing queries _before_ submitting them to backend, not about query processing in the backend. Yes, it's a query generation technique separate from the database server, generating and executing SQL queries on a set of tables in order to satisfy some goal. Has nothing to do with the RDBMS itself. - Don Baccus, Portland OR [EMAIL PROTECTED] Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
Re: [HACKERS] Text concat problem
At 05:47 PM 11/8/00 -0600, Luis =?UNKNOWN?Q?Maga=F1a?= wrote: insert into employee(title,first_name,start_date,charge) values('Mr. X','Smith',date(now()),'None'); insert into employee(title,first_name,start_date,charge) values('Mr. Y','Smith',date(now()),'None'); insert into employee(title,first_name,start_date,charge) values('Mr. Z','Smith',date(now()),'None'); so far there is no problem at all, the problem comes here: select title || ' ' || first_name || ' ' || last_name as fullname from employee; fullname (3 rows) Doesn't work , I'm thinking it is because of the null value in last_name. Right. NULL means "has no value", it's not the empty string. The result of concatenating with NULL is NULL. Have any idea or suggestion on how to workaround this situation. It's a classic NULL issue. 1+NULL = NULL, too, for instance. Try something like "default ''" in your table definition rather than use null. Then you'll be using the empty string for concatenation. 'abc' || NULL = NULL. 'abc' || '' = 'abc' which appears to be what you want. This is standard SQL92 behavior... - Don Baccus, Portland OR [EMAIL PROTECTED] Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
Re: [HACKERS] Question about reliability?
At 10:43 AM 11/9/00 -0500, Tom Lane wrote: Would there be any potential to avoid these (possibly) unnecessary deaths? No, at least it'll never get my vote. Besides, it's not that difficult for an application to recover from these prophylactic backend deaths. My PG driver for AOLserver does so transparently, retrying queries that get the "sorry I've been asked to shut down because some other backend screwed up and died - please retry your query" but returning an error for the actual query that caused a backend to hose itself. The code using the driver is unaware that anything has happened (other than the thread issuing the query that hosed the backend that died in execution, of course). - Don Baccus, Portland OR [EMAIL PROTECTED] Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
Re: [HACKERS] LIMIT in DECLARE CURSOR: request for comments
At 12:18 PM 10/27/00 -0400, Tom Lane wrote: Hiroshi was a little concerned about this change in behavior, and so the first order of business is whether anyone wants to defend the old way? IMHO it was incontrovertibly a bug, but ... Sure feels like a bug to me. Having it ignored isn't what I'd expect. - Don Baccus, Portland OR [EMAIL PROTECTED] Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
[GENERAL] Re: [HACKERS] My new job
At 01:02 PM 10/10/00 -0400, Tom Lane wrote: Bottom line is we're not sure what to do now. Opinions from the floor, anyone? Yeah, quit worrying and work your collective butts off on 7.1 and 7.2 :) Seriously...the core group is obviously committed to PG, and appear to be folks of integrity. We all will benefit by your working on PG full time while being paid enough so you can eat, drink, and be merry, too. - Don Baccus, Portland OR [EMAIL PROTECTED] Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.
Re: [HACKERS] ALTER TABLE DROP COLUMN
At 07:55 PM 10/9/00 -0300, The Hermit Hacker wrote: I am not convinced that a 2x penalty for DROP COLUMN is such a huge problem that we should give up all the normal safety features of SQL in order to avoid it. Seems to me that DROP COLUMN is only a big issue during DB development, when you're usually working with relatively small amounts of test data anyway. Actually, I could see DROP COLUMN being useful in a few other places ... recently, I spent several hours re-structuring a clients database that had been built by someone else who didn't know what 'relational' means in RDBMS ... or how about an application developer that decides to restructure their schema's in a new release and provides an 'upgrade.sql' script that is designed to do this? This last example is one reason DROP COLUMN would be a great help to the OpenACS development effort. However, upgrades (new releases) are fairly infrequent, and many users of current versions won't bother unless they've run into toolkit bugs (same goes for updating PG). Those who do know that doing an upgrade will require planning, testing on a system that's not running their "live" website, and some amount of downtime. So I don't think a 2x penalty is a huge problem. That would make for a very painful upgrade process if I have to go through the trouble of upgrading my hardware to add more space ... For many folks, if eating 2x the size of a single table runs their system out of disk space, clearly they should've upgraded long, long ago. An OpenACS site has hundreds of tables, I can't imagine running my disk space so tight that I couldn't double the size of one of them long enough to do a DROP COLUMN. Obviously, some folks doing other things will have single tables that are huge, but after all they can always do what they do now - not drop columns. - Don Baccus, Portland OR [EMAIL PROTECTED] Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Service and other goodies at http://donb.photo.net.