Re: [HACKERS] leakproof
On Feb 19, 2012, at 7:24 PM, Tom Lane wrote: > Don Baccus 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] leakproof
On Feb 19, 2012, at 5:42 PM, Tom Lane wrote: > Robert Haas 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] 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 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 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 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] 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] 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
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 simple"not 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] 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
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
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] [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 = 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 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] 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 D&D 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 D&D/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
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] 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] 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] 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] 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] 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] Re: SET variables
At 11:05 AM 5/13/01 -0400, Tom Lane wrote: >mlw <[EMAIL PROTECTED]> writes: >> Obviously this is a very simple example. I guess I am asking for something >> analogous to temporary tables, but on a single datum level. > >What's wrong with a one-row temporary table? Well, the following query would then be a join with that one-row table, which means that you pay the cost of creating the temporary table, optimizing the join (which presumably isn't terribly bad since it's a trivial one), etc. This might not be bad if this were something done rarely. But people in the Oracle world use BIND variables a LOT (and essentially BIND vars are what are being asked for). As was pointed out, the use of BIND variables make Oracle's brain-dead query caching useful (it does source caching, and without BIND variables "select * from foo where foo_key = 1" doesn't match "select * from foo where foo_key = 2", making caching not all that useful). That's not the only reason to use them, though. There are literally tens of thousands of them in OpenACS. We had to work around the fact that PG doesn't offer this capability by hacking the AOLserver driver. If we were working in an application that we didn't control at every level (i.e. a closed-source webserver environment with a closed-source driver) the workaround you suggest would involve the creation and deletion of tens of thousands of temporary tables on a busy website. Not a very scalable workaround in my world ... obviously rewriting the application to remove BIND variables would be the solution we would've chosen if we hadn't been able to hack the functionality into the driver. One reason for the heavy use of BIND variables in the ACS is that you then get type checking in the query, so removing them would require extensive type checking within the application code before submitting dynamic queries to the database to help avoid the "smuggled SQL" problem. (SQL snippets smuggled in via URL arguments). Our driver hack was able to provide the same safeguards against "smuggled SQL" so again, full control over our enviroment means we can live easily without BIND vars. But it's easy for me to see why folks want them. This reminds me a bit of the argument against incorporating the patch implementing the Oracle parameter type mechanism. Folks with a lot of experience with PL/SQL will just scratch their heads bemusedly when they read an statement saying "I don't really see that many people would write functions like this", etc. This patch would greatly simplify the mechanized translation of PL/SQL into PL/pgSQL, even if the feature per se is "useless" (which I happen to disagree with). It's not uncommon for large Oracle applications to include thousands of PL/SQL procedures and functions, since many subscribe to the notion that application logic should reside entirely within the database if possible. So mechanical translation has a certain attraction to the person wanting to port a large-scale application from Oracle to PG. The interesting thing to me doesn't simply lie in the debate over this or that feature. The interesting thing to me is that more and more requests to ease porting from Oracle to Postgres are cropping up. This says that more and more people from the "real" RDBMS world are starting to take Postgres seriously. - 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. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] SAP-DB
Hi guys, > > I've used the open source SAPDB and the performance is pretty damned > impressive. However, 'open source' in application to it is somewhat > deceptive, since you have to make it with SAP's proprietary build > tools/environment. > > In my opinion, however, it would be worth closely auditing SAP DB to see > what postgres can learn. I downloaded it. The directories are two characters in length, the files are numbers, and it is a mixture of C++, Python, and Pascal. Need I say more. :-) I swore I'd never post to the hackers list again, but this is an amazing statement by Bruce. Boy, the robustness of the software is determined by the number of characters in the directory name? By the languages used? Have you considered that the development tools may be abstracting out the directory names in their development environment? Someone else dissed this release because you need their development tools. Well, guess what big boys, the development tools are being released open source, too. And SAP has a history of giving you sources (not fiche or whatever) of their licensed technology so this is a fairly easy step for them. Not by the fact that SAP is a monster company, with a monster customer base, with a DB engine hardly used over here but actually quite popular in Germany? Quite popular in exactly the kind of enterprise environments that PG has yet to crack and, if you dismiss this offering with silly hand-waving, may never crack? Have you ever heard of Adabas? If you don't believe that SAP and SAP-DB are real, go talk to your fellow Great Bridge employee Jan Wieck. OK, I'll unsubscribe now ... I'm still a fan of PG, but not stupid enough to dismiss a robust, industrial-strength RDMBS system based on naive and uneducated criticism. PG has a lot to offer, and the upscale is still amazingly positive judging by the pace of development over the past two years. This is hardly a basis for hand-waving SAP DB into MySQL-land, however. I like PG, I will continue to personally use PG, and I will support SAP DB along with Oracle and PG with OpenACS 4.x. And I would expect most of my clients using that toolkit to use Oracle, with SAP DB coming in second, and PG third ... I'm not trying to demotivate or discourage the PG crowd. However, when you're in a competitive battle the best prescription for getting your bell run is to taunt and tease competitors who actually are in better shape than you. And ... SAP DB is, in many ways important to the enterprise organization. They may have an inferior page-locking concurrency scheme, I need to check on this, but in many enterprise-level commercial environments this isn't such a big deal. Since your (Bruce's) hopes for a wealthy future depends on GB IPO'ing which will only come with significant penetration of the enterprise commercial environment, I humbly suggest you don't write them off quite so quickly. SAP is, after all, a very successful company. - 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 02:47 PM 12/1/00 -0500, Tom Lane wrote: >All we can do is the best we can ;-). In that light, I think it's >reasonable for Postgres to proceed on the assumption that fsync does >what it claims to do, ie, all blocks are written when it returns. >We can't realistically expect to persuade a disk controller that >reorders writes to stop doing so. We can, however, expect that we've >minimized the probability of failures induced by anything other than >disk hardware failure or power failure. Right. This is very much the guarantee that RAID (non-zero) makes, except "other than disk hardware failure" is replaced by "other than the failure of two drives". RAID gives you that (very, very substantial boost which is why it is so popular for DB servers). It doesn't give you power failure assurance for much the same reason that PG (or Oracle, etc) can. If transaction processing alone could give you protection against a single disk hardware failure, Oracle wouldn't've bothered implementing mirroring in the past before software (and even reasonable hardware) RAID was available. Likewise, if mirroring + transaction processing could protect against disks hosing themselves in power failure situations Oracle wouldn't suggest that enterprise level customers invest in external disk subsystems with battery backup sufficient to guarantee everything the db server believes has been written really is written. Of course, Oracle license fees are high enough that proper hardware support tends to look cheap in comparison... Vadim's WAL code is excellent, and the fact that we run in essence with -F performance and also less write activity to the disk both increases performance, and tends to lessen the probability that the disk will actually be writing a block when the power goes off. The dice aren't quite so loaded against the server with this lowered disk activity... - 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] redundancy and disk i/o
At 07:30 PM 11/28/00 -0800, Sandeep Joshi wrote: >Hi, >I have two questions > >1. Is it possible to set up a set of redundant disks for a database? one >of them being remote from the database? If you're talking about replication, PostgreSQL, Inc. will be offering a solution to its $19,000/yr Platinum Partners shortly. It will be released in open source form no more than two years after its release in proprietary form. Check out http://www.erserver.com for more details, and http://www.pgsql.com for more details on the PostgreSQL, Inc. partnership program. Locally, you can use RAID. Are there open-source journaling filesystems that offer filesystem-level replication out 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] 8192 BLCKSZ ?]
At 10:52 AM 12/2/00 +1100, Andrew Snow wrote: > > >> The cost difference between 32K vs 8K disk reads/writes are so small >> these days when compared with overall cost of the disk operation itself, >> that you can even measure it, well below 1%. Remember seek times >> advertised on disks are an average. > >It has been said how small the difference is - therefore in my opinion it >should remain at 8KB to maintain best average performance with all existing >platforms. With versions <= PG 7.0, the motivation that's been stated isn't performance based as much as an option to let you stick relatively big chunks of text (~40k-ish+ for lzText) in a single row without resorting to classic PG's ugly LOB interface or something almost as ugly as the built-in LOB handler I did for AOLserver many months ago. The performance arguments have mostly been of the form "it won't really cost you much and you can use rows that are so much longer ..." I think there's been recognition that 8KB is a reasonable default, along with lamenting (at least on my part) that the fact that this is just a DEFAULT hasn't been well-communicated, leading many casual surveyors of DB alternatives to believe that it is truly a hard-wired limitation. Causing PG's reputation to suffer as a result. One could argue that PG"s reputation would've been enhanced in past years if a 32KB block size limit rather than 8KB block size default had been emphasized. But you wouldn't have to change the DEFAULT in order to make this claim! It would've been just a matter of emphasizing the limit rather than the default. PG 7.1 will pretty much end any confusion. The segmented approach used by TOAST should work well (the AOLserver LOB handler I wrote months ago works well in the OpenACS context, and uses a very similar segmentation scheme, so I expect TOAST to work even better). Users will still be able change to larger blocksizes (perhaps a wise thing to do if a large percentage of their data won't fit into a single PG block). Users using the default will be able to store rows of *awesome* length, efficiently. - 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:11 AM 12/4/00 +, Thomas Lockhart wrote: >We are offering our services and expertise to a community outside >-hackers, as a business formed in a way that this new community expects >to see. Nothing special or sinister here. Other than it seems to have >raised the point that you expected each of us to be working for you, >gratis, on projects you find compelling, using all of our available >time, far into the future just as each of us has over the last five >years. No, not at all. Working gratis is not the issue, as I made clear. There are - despite your rather condescending statement implying otherwise - business models that lead to revenue without abandoning open source. I'm making a decent living following such a business model, thank you very much. I'm living proof that it is possible. ... >A recent example of non-sinister change in another area is the work done >to release 7.0.3. This is a release which would not have happened in >previous cycles, since we are so close to beta on 7.1. But GB paid Tom >Lane to work on it as part of *their* business plan, and he sheparded it >through the cycle. There was no outcry from you at this presumption, and >on this diversion of community resources for this effort. Not sure why, >other than you chose to pick some other fight. There's a vast difference between releasing 7.0.3 in open source form TODAY and eRServer, which may not be released in open source form for up to two years after it enters the market on a closed source, proprietary footing. To suggest there is no difference, as you seem to be doing, is a hopelessly unconvincing argument. The fact that you seem blind to the difference is one reason why PG, Inc worries me (since you are a principle in the company). The reason you heard no outcry from me in the PG 7.0.3 case is because there *is* a difference between it and a semi-proprietary product like eRServer. If GB had held Tom's work on PG 7.0.3 and released it only in (say) a packaged release for purchase, saying "we'll release it to the CVS tree after we recoup our investment", there would've been an outcry from me, bet on it. Probably others, too... >And no matter which fight you chose, you're wasting the time of others >as you fight your demons. Well, I guess I'll have to stay off my medication, otherwise my demons might disappear. I'm a regular miracle of medical science until I forget to take them. - 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:42 PM 12/3/00 -0600, Ross J. Reedstrom wrote: >This paragraph from erserver.com: > >eRServer development is currently concentrating on core, universal >functions that will enable individuals and IT professionals >to implement PostgreSQL ORDBMS solutions for mission critical >datawarehousing, datamining, and eCommerce requirements. These >initial developments will be published under the PostgreSQL Open >Source license, and made available through our sites, Certified >Platinum Partners, and others in PostgreSQL community. > >led me (and many others) to believe that this was going to be a tighly >integrated service, requiring code in the PostgreSQL core, since that's the >normal use of 'core' around here. Right. This is a big source of misunderstanding. There's still the fact that 50% of the PG steering committee that are involved in [partially] closed source development based on PG, though. This figure disturbs me. 50% is a lot. It's like ... half, right? Or did I miss something in the conversion? This represents significant change from the past where 0%, AFAIK, were involved in closed source PG add-ons. >Now that I know it's a completely external implementation, I feel bad about >griping about deadlines. I _do_ wish I'd known this _design choice_ a bit >earlier, as it impacts how I'll try to do some things with pgsql, but that's >my own fault for over interpreting press releases and pre-announcements. IF 50% of the steering committee is to embark on such a task in a closed source or semi-closed source development model, it would seem common courtesy to inform the community of the facts as early as they were decided upon. In fact, it might seem to be common courtesy to float the notion in the community, to gauge reaction and to build support, before finalizing such a decision. AFAIC this arrived out of no where, a sort of stealth "50% of the steering committee has decided to embark on a semi-proprietary solution to the replication problem that you won't see as open source for [up to] two years after its completion". That's a paradigm shift. Whether right or wrong, there's a responsibility to communicate the fact that 50% of the steering committee has decided to partially abandon the open source development model for one that is (in some cases) closed for two years and (in other cases) forever. - 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:59 PM 12/3/00 -0400, The Hermit Hacker wrote: > the sanctity of the *core* server is *always* >foremost in our minds, no matter what other projects we are working on ... What happens if financially things aren't entirely rosy with your company? The problem in taking itty-bitty steps in this direction is that you're involving outside money interests that don't necessarily adhere to this view. Having taken the first steps to a proprietary, closed source future, would you pledge to bankrupt your company rather than accept a large captital investment with an ROI based on proprietary extensions to the core that might not be likely to come out of the non-tainted side of the development house? Would your company sign a contract to that effect with independent parties, i.e. that it would never violate the sanctity of the *core*? Even if it means you go broke? And that your investors go broke? Or would your investors prefer you not make such a formal committment, in order to keep options open if things don't go well? (in the early 80's my company received a total of $8,000,000 in pre-IPO capital investments, so I have some experience with the expectations of investors. It tends to make me a bit paranoid. I'm not the only COO to have such experiences while living the life). What happens in two years if those investors in eRServer haven't gotten adequate return on their investment? Do you have a formal agreement that the source will be released regardless? Can the community inspect the agreement so we can judge for ourselves whether or not this assurance is adequately backed by contract language? Are your agreements Open 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] 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 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 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 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 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 02:58 AM 12/3/00 +, Thomas Lockhart wrote: >> PostgreSQL, Inc perhaps has that as a game plan. >> I'm not so much concerned about exactly what PG, Inc is planning to offer >> as a proprietary piece - I'm purist enough that I worry about what this >> signals for their future direction. > >Hmm. What has kept replication from happening in the past? It is a big >job and difficult to do correctly. Presumably what has kept it from happening in the past is that other things were of much higher priority. Replicating a database on an engine as unreliable as PG was in earlier incarnations would simply replicate your problems, for instance. This statement of yours kinda belittles the work done over the past few years by volunteers. It also ignores the fact that folks in other companies do get paid to work on open source software full-time without having to resort to creating closed source, proprietary products. > It is entirely my fault that you >haven't seen the demo code released; I've been packaging it to make it a >bit easier to work with. OK, good, this part gets open sourced. Still not an open development model. Knowing details about what's going on while code's being developed, not to mention being able to critique decisions, is one of the major benefits of the open development model. >Let me be clear: PostgreSQL Inc. is owned and controlled by people who >have lived the Open Source philosophy, which is not typical of most >companies in business today. We are eager to show how this can be done >on a full time basis, not only as an avocation. Building closed source proprietary products helps you live the open source philosophy on a full-time basis? ... >As soon as you find a business model which does not require income, let >me know. Red herring, and you know it. The question isn't whether or not your business generates income, but how it generates income. Your comment is the classic one tossed out by closed-source, proprietary software advocates who dismiss open source software out-of-hand. Couldn't you think of something better, at least? Like ... something original? > 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. - 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 01:52 PM 12/2/00 -0800, Tom Samplonius wrote: > I doubt that. There is an IB (Interbase) replication option today, but >you must purchase it. That isn't so bad actually. PostgreSQL looks to be >going that way too: base functionality is open source, periphial >companies make money selling extensions. PostgreSQL, Inc perhaps has that as a game plan. Thus far Great Bridge claims to be 100% devoted to the Open Source model. > Besides simple master-slave replication is old news anyhow, and not >terribly useful. Products like FrontBase (www.frontbase.com) have full >shared-nothing cluster support too (FrontBase is commerical). Clustering >is a much better solution for redundancy purposes that replication. I'm not so much concerned about exactly what PG, Inc is planning to offer as a proprietary piece - I'm purist enough that I worry about what this signals for their future direction. If PG, Inc starts doing proprietary chunks, and Great Bridge remains 100% dedicated to Open Source, I know who I'll want to succeed and prosper. - 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] 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] 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] 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] beta testing version
At 11:09 AM 12/1/00 -0800, Nathan Myers wrote: >On Fri, Dec 01, 2000 at 10:01:15AM +0100, Zeugswetter Andreas SB wrote: >> If you need to restore from offsite backup you loose transactions >> unless you transfer the WAL synchronously with every commit. >Currently the only way to avoid losing those transactions is by >replicating transactions at the application layer. That is, the >application talks to two different database instances, and enters >transactions into both. That's pretty hard to retrofit into an >existing application, so you'd really rather have replication in >the database. Of course, that's something PostgreSQL, Inc. is also >working on. Recovery alone isn't quite that difficult. You don't need to instantiate your database instance until you need to apply the archived transactions, i.e. after catastrophic failure destroys your db server. You need to do two things: 1. Transmit a consistent (known-state) snapshot of the database offsite. 2. Synchronously tranfer the WAL as part of every commit (question, do wait to log a "commit" locally until after the remote site acks that it got the WAL?) Then you take a new machine, build a database out of the snapshot, and apply the archived redo logs and off you go. If you get tired of saving oodles of redo archives, you make a new snapshot and accumulate the WAL from that point forward. Of course, that's not a fast failover solution. The scenario you describe leads to being able to quickly switch over to a backup server when the primary server fails. Much better for 24/7/365-style computing. Exactly what is PostgreSQL, Inc doing in this area? I've not seen discussions about it here, and the two of the three most active developers (Jan and Tom) work for Great Bridge, not PostgreSQL, Inc... I should think Vadim should play a large role in any effort to add WAL-based replication to Postgres. - 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: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: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:55 AM 12/1/00 -0800, Nathan Myers wrote: >Many people hope to run PostgreSQL 24x7x365. With vacuuming, you >might just as well shut down afterward; but when that goes away >(in 7.2?), when will you get the chance to take your backups? >Clearly we need either another form of snapshot backup that can >be taken with the database running, and compatible with the >current WAL (or some variation on it); or, we need another kind >of log, in addition to the WAL. Vadim's not ignorant of such matters, when he says "make a copy of the files" he's not talking about using tar on a running database. BAR tools are needed, as Vadim has pointed out here in the past. - 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 07:50 PM 11/30/00 -0600, GH wrote: >On Thu, Nov 23, 2000 at 07:58:29AM -0800, some SMTP stream spewed forth: >> At 09:44 AM 11/21/00 -0700, Tim Uckun wrote: >> >> >What about the php module? Does it take advantage of API? >> >> I don't know. If not, though, there wouldn't be much point in using >> AOLserver, since the simple and efficient database API is the main >> attraction. So I think there's a pretty good chance it does. >> > >Through the course of another thread on the lists we have concluded that >PHP does not support the AOLServer (or any other similar) database API. >The "blockage" is that PHP includes its own database functions, albeit >they are based on the Postgres, MySQL, etc. APIs individually. > >I am considering looking into urging an integration of PHP and >AOLServer's connection pooling (for lack of a better word) stuff. Well, meanwhile I've gotten confirmation from folks in the PHP world (via an openacs forum) that it still isn't threadsafe, though there's an effort underway to track down the problems. I don't know how close to solving this they are. - 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 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] 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] 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 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] How to use nested select statements
At 08:31 PM 11/29/00 +0530, Sanjay Arora wrote: >Well I want to use nested select statements ,is it possible. In PG 7.0 you can use subselects in the "where" clause. >Is there any counterpart for 'Sysdate from dual' as in Oracle You don't need "dual", just "select now()" will work. If you're porting Oracle code, you can make life a lot easier by defining some stuff: create function sysdate() returns datetime as ' begin return ''now''; end;' language 'plpgsql'; create view dual as select sysdate(); Then "select sysdate from dual", "select (any expression) from dual", etc all do what you'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.
Re: [HACKERS] beta testing version
At 03:25 PM 11/28/00 -0700, Ron Chmara wrote: >Mitch Vincent wrote: >> >> This is one of the not-so-stomped boxes running PostgreSQL -- I've never >> restarted PostgreSQL on it since it was installed. >> 12:03pm up 122 days, 7:54, 1 user, load average: 0.08, 0.11, 0.09 >> I had some index corruption problems in 6.5.3 but since 7.0.X I haven't >> heard so much as a peep from any PostgreSQL backend. It's superbly stable on >> all my machines.. > >I have a 6.5.x box at 328 days of active use. > >Crash "recovery" seems silly to me. :-) Well, not really ... but since our troll is a devoted MySQL user, it's a bit of a red-herring anyway, at least as regards his own server. You know, the one he's afraid to put Postgres on, but sleeps soundly at night knowing the mighty bullet-proof MySQL with its full transaction semantics, archive logging and recovery from REDO logs and all that will save him? :) Again ... he's a troll, not even a very entertaining one. - 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:15 PM 11/28/00 +0800, xuyifeng wrote: >no doubt, I have touched some problems PG has, right? if PG is so good, >is there any necessary for the team to improve PG again? See? Troll... The guy worships MySQL, just in case folks haven't made the connection. I'm going to ignore him from now on, suggest others do the same, I'm sure he'll go away eventually. - 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] Full text Indexing -out of contrib and into main..
At 10:52 AM 11/28/00 +0100, Magnus Hagander wrote: >> > b) Check out MSSQL 7's capabilities and weep. >> >> BTW, have you studied MSSQL enough to tell me if it has a >> separate/standalone >> (as a process) fti engine or just another index type. >It is standalone - separate process, data is stored in separate files (not >in db). > >In SQL Server 7.0, you also have to manually update the index. Just updating >the values in the table does *NOT* update the index. (Can be scheduled, of >course, but not live) >In SQL Server 2000 the index can be auto-updated when rows change, but it's >not default. This is similar to Oracle's InterMedia. In practice, using auto-update on a busy, live website is impractical, though how much this is due to InterMedia's being flakey and how much due to the computational expense isn't clear (or rather IM's so flakey one can't really explore enough to see how expensive auto-update on a busy site would be). - 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] 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] 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] 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] 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] Full text Indexing -out of contrib and into main..
At 02:51 PM 11/28/00 +1300, John Huttley wrote: >> >> Maybe asking 'Why isn't the contrib full-text-indexer not in the main >> tree?' would be more productive on that front. > >Well, yes. Why isn't it? > >Full text indexing should be just as much a feature as any other key feature in >PG. >With the advent of unlimited file and record lengths in 7.1, this would be a good >time to >include it. > >FTI is particularly useful in the context of web content engines. Well ... it's pretty inadequate, actually. That might be one reason it's only in contrib. - 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:44 PM 11/27/00 -0500, Tom Lane wrote:
>Mario Weilguni <[EMAIL PROTECTED]> writes:
>> In Oracle, empty strings and null are basicly the same,
>
>Are you sure about that? It'd be a pretty major failure to comply with
>SQL standard semantics, if so.
Thought you'd get a kick out of this:
Connected to:
Oracle8i Enterprise Edition Release 8.1.6.1.0 - Production
With the Partitioning option
JServer Release 8.1.6.0.0 - Production
SQL> create table fubar(some_string varchar(1000));
Table created.
SQL> insert into fubar values('');
1 row created.
SQL> select count(*) from fubar where some_string is null;
COUNT(*)
--
1
SQL>
- 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 06:09 PM 11/27/00 +0100, 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. Go complain to Oracle - their behavior is NON-STANDARD. PG is doing it right. An empty string isn't the same as NULL any more than 0 is the same as NULL for the integer type. Adopting the Oracle-ism would break PG's SQL92-compliance in this area. >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. > >Is there a better way to achieve this? You could rewrite your logic to use the empty string rather than NULL, that's one idea. In the OpenACS project, we ported nearly 10,000 lines of datamodel plus a thousands of queries from Oracle to Postgres and wrote a little utility routine that turned a string returned from a from into either NULL or 'the string' depending on its length. The select queries in the Oracle version were properly written using "IS NULL" so they worked fine. It sounds like you've got a little more work to do if the Oracle queries aren't written as "is null or ..." This is a very nasty misfeature of Oracle, though, because porting from SQL92 to Oracle can be very difficult if the SQL92 compliant code depends on the empty string being different than NULL. Going to SQL92 from Oracle is easier and you can write the Oracle queries and inserts in an SQL92-compliant manner. Benefits of doing so are that your stuff will be easier to port to InterBase, etc as well as Postgres. - 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] 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] Re: [NOVICE] Re: re : PHP and persistent connections
At 12:38 AM 11/27/00 -0700, Ron Chmara wrote: >Don Baccus wrote: >> 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. > >What if you have a server farm of 8 AOL servers, and 12 perl clients, and >3 MS Access connections, leaving things open? Is AOLserver parsing the >Perl DBD/DBI, connects, too? So you're using AOLserver as (cough) a >middleman? Well, no - we'd use the built-in Tcl, Python or nsjava (still in infancy) modules which interface natively to AOLserver's built-in database API. You don't NEED the various connection implementations buried in various languages because they're provided directly in the server. That's the point. That's the main reason people use it. If you're going to run CGI/Perl scripts using its database connectivity stuff, don't use AOLserver. They'll run since AOLserver supports CGI, but they'll run no better than under Apache and probably worse, since no one doing serious AOLserver work uses CGI and therefore the code which implements it has languished - there's no motivation to improve something that no one uses. If you're willing to use a language module which exposes the AOLserver API to your application, then AOLserver's a great choice. >> 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. > >How does AOL server time out access clients, ODBC connections, Perl >clients? I thought it was mainly web-server stuff. Well, for starters one normally wouldn't use ODBC since AOLserver includes drivers for PostgreSQL, Oracle and Sybase. There's one for Solid, too, but no one seems to use Solid since they raised their prices drastically a couple of years ago (if you're going to spend lots of money on a database, Oracle and Sybase are more than willing to help you). Nor does nsjava use JDBC, it encapsulates the AOLserver API into a database API class(es?). AOLserver manages the database pools in about the same way it manages threads, i.e. if a thread can't get the handles it needs (usually only one, sometimes two, more than that usually indicates poorly written code) it blocks until another thread releases a handle. When a thread ends (returns a page) any allocated handles are released. Transactions that haven't been properly committed are rolled back as well (lesser of two evils - the event's logged since it indicates a bug). For each pool you provide the name of the driver (which of course serves to select which RDMBS that pool will use - you can use as many different RDBMSs as you have, and have drivers for), a datasource, the maximum number of connections to open for that pool, minimum and maximum lifetimes for connections, etc. - 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] 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 05:26 PM 11/25/00 -0700, Ron Chmara wrote: >Note: CC'd to Hackers, as this has wandered into deeper feature issues. > >Tom Lane wrote: >> GH <[EMAIL PROTECTED]> writes: >> > Do the "persistent-connected" Postgres backends ever timeout or die? >> No. A backend will sit patiently for the client to send it another >> query or close the connection. > >This does have an unfortunate denial-of-service implication, where >an attack can effectively suck up all available backends, and there's >no throttle, no timeout, no way of automatically dropping these > >However, the more likely possibility is similar to the problem that >we see in PHP's persistant connections a normally benign connection >is inactive, and yet it isn't dropped. If you have two of these created >every day, and you only have 16 backends, after 8 days you have a lockout. > >On a busy web site or another busy application, you can, of course, >exhaust 64 backends in a matter of minutes. Ugh...the more I read stuff like this the more I appreciate AOlserver's built-in database API which protects the application from any such problems altogether. The particular problem being described simply can't occur in this environment. - 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 > >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: [HACKERS] Re: [GENERAL] PHPBuilder article -- Postgres vs MySQL
At 09:44 AM 11/21/00 -0700, Tim Uckun wrote: >What about the php module? Does it take advantage of API? I don't know. If not, though, there wouldn't be much point in using AOLserver, since the simple and efficient database API is the main attraction. So I think there's a pretty good chance it does. - 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.
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.
[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] 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] 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 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] 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] (download ANSI SQL benchmark?) Re: Postgres article
At 10:58 AM 11/21/00 -0500, Tom Lane wrote: >> 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 revised results that are on GB's site now include curves for MySQL >*with* tuning per advice from the MySQL folk. That's good. Have the MySQL folk made any effort to reciprocate? - 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] (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.
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] (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] 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] RE: [GENERAL] PHPBuilder article -- Postgres vs MySQL
At 11:22 AM 11/13/00 -0500, Robert D. Nelson wrote: >Still...Regardless of what database they're running, either their >abstraction layer is shit or their queries really need optimized. Is that >perhaps why, even at 5 clients, the page views he shows never went >significantly above 10/sec? They don't appear to do any client-side query caching, which is understandable from one point of view (you need some sort of handle on which queries are hit frequently enough to warrant devoting RAM to caching the result, or else you risk caching things that don't gain you much and cut down on the amount of the DB cached in RAM which hits you on other queries). On the other hand, you'd think they'd do some analysis... Still, the table-locking of MySQL just gets in the way. If you can cache everything, then you can send a postal worker to the mailbox to retrieve uncached data without significantly impacting throughput (in other words, the MySQL argument that simple select benchmarks are all you need are not relevant). If you can't cache anything but have few users, then perhaps low levels of concurrency don't hurt. If you don't cache anything but have lots of users, scaling well under high levels of load rule. My thinking is that intellegent caching coupled with a highly-scalable database wins. That's the world I'm used to... - 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] Re: [COMMITTERS] pgsql/src/backend/utils/adt (ri_triggers.c)
At 11:37 PM 11/20/00 -0500, Tom Lane wrote: >[EMAIL PROTECTED] writes: >> Update of /home/projects/pgsql/cvsroot/pgsql/src/backend/utils/adt >> Modified Files: >> ri_triggers.c >> keep relations open until they are no longer needed. > >Something that's been bothering me for a good while about ri_triggers >is that it opens the relations without any lock to begin with. >That can't possibly be safe, can it? Hmmm...I only worked within the structure Jan built (to fix/implement semantics) but there are efforts to lock things down with "select for update" where Jan felt it was necessary. Whether or not that's sufficient is another question, but he obviously gave it *some* thought. - 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 06:16 PM 11/13/00 +0100, [EMAIL PROTECTED] wrote: >> Still...Regardless of what database they're running, either their >> abstraction layer is shit or their queries really need optimized. Is that >> perhaps why, even at 5 clients, the page views he shows never went >> significantly above 10/sec? >> >I think this could be because they used real killer pages in the test, >and maybe this also the reason PgSQL fared this good (I've always >been and I'm still a postgres fan, but looking at that results I've >been quite astonished!!). Have you looked the spec? If I remember >well, Tim was talking about executing cuncurrently a page that >joined a dozen tables and another that was doing >update/select/insert on the same tables. Under these condition, 10 >pages/sec it seems lighting to me But much of this could still be cached. I visit my homepage at sourceforge rarely, because my project uses sourceforge for its cvs repository, only. So all those joins are mostly a waste. I never have new postings in my project forums, blah blah. Some level of caching could help (not for me personally, I visit too rarely for a system to want to cache my query returns involved in building my home page, but I'm sure there are many cases where caching would help). Again, you have to balance query cache RAM consumption against the benefits of extra RAM availability to the RDBMS (assuming you have one, which MySQL isn't :) - 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.
[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.
[HACKERS] PG 7.1 pre-beta bug ...
modifying_user integer references users,
parent_group_id integer references user_groups(group_id)
);
-- index parent_group_id to make parent lookups quick!
create index user_groups_parent_grp_id_idx on user_groups(parent_group_id);
create function user_group_add (varchar, varchar, varchar, varchar)
RETURNS integer AS '
DECLARE
v_group_type alias for $1;
v_pretty_name alias for $2;
v_short_name alias for $3;
v_multi_role_p alias for $4;
v_system_user_id integer;
BEGIN
v_system_user_id := 1;
-- create the actual group
insert into user_groups
(group_id, group_type, short_name, group_name, creation_user,
creation_ip_address, approved_p,
existence_public_p, new_member_policy, multi_role_p)
select nextval(''user_group_sequence''), v_group_type, v_short_name,
v_pretty_name, v_system_user_id, ''0.0.0.0'', ''t'', ''f'', ''closed'',
v_multi_role_p
where not exists (select * from user_groups
where upper(short_name) = upper(v_short_name));
RETURN 1;
end;' language 'plpgsql';
insert into users (user_id) values(1);
insert into user_group_types
(group_type, pretty_name, pretty_plural, approval_policy)
values
('group', 'Group', 'Groups', 'open');
select user_group_add('group', 'shortname', 'prettyname', 'f');
[pgtest@gyrfalcon pgtest]$
- 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] 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] 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: [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] PHPBuilder article -- Postgres vs MySQL
At 11:38 PM 11/12/00 -0500, Michael Fork wrote: >Thought this may be of interest to some... > >http://www.phpbuilder.com/columns/tim20001112.php3 I just submitted it to slashdot, what the heck :) - 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.
