Re: [GENERAL] PostgresSQL vs. Informix
In answering the question, it might help to have information on two background items. The first item is the politics of the situation. I gather from Chad Hendren's post that an individual in the top-8-OEM-customer enterprise is advocating for Postgresql. The second item is the existing technical situation. What is unsatisfactory about Informix at this time? What is mission critical about Informix's performance that Postgresql will have to meet? (Postgresql has yet to conquer the computer cluster/grid arena and this frustrates many would-be power users.) Does the customer have mission critical applications that are closely coupled or dependent on Informix features or disfeatures? On Wednesday 2007-11-28 08:32, Chad Hendren wrote: > PostgreSQL Team, > > I have a large OEM customer (one of the top 8 for Sun worldwide) that is > considering embedding PostgreSQL into the core of their primary product > instead of Informix. He is trying to build his case for this change. > Do we have anything that I can forward to him (something like the > presentation you did at CEC?) to help him build his case? > > This is an urgent request from my customer given that his timeline is > relatively short. Any help you can give me will be very appreciated. > > Thanks, > > Chad Hendren > > Original question: > > Have you seen any studies (either by Sun or others) that compares > PostgresSQL to other commercial database software (Informix, Oracle, > Sybase,etc.)? I am interested seeing a feature by feature comparison of > PostgresSQL and Informix. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Bigtime scaling of Postgresql (cluster and stuff I suppose)
On Monday 2007-08-27 08:04, Andrew Sullivan wrote: > On Sat, Aug 25, 2007 at 11:13:45AM -0400, Tom Lane wrote: > > In case you hadn't noticed the disconnect between these statements: > > if they have to be that close together, there *will* be a single point > > of failure. Fire in your data center, for instance, will take out every > > copy of your data. So as a "high availability" solution I don't find > > it all that compelling. > > Indeed. There's another issue, too, which you have to spend some > time reading the manual to get. The clustering stuff is _yet > another_ table type, with subtly different semantics from other table > types. As usual, this means that you can blow off your foot by > mixing table types in a transaction. As near as I can tell, the > cluster table type (I disremeber the name of it) cannot be run in > strict mode, either. > > To answer the OP's question, you can do some "cluster-like" things by > doing hardware clustering -- two machines attached to a RAID with > some sort of hardware fail-over in place. > > I think that the MySQL cluster stuff wasn't intended as an HA > feature, though (although they might well be selling it that way). > It was a way to scale many small systems for certain kinds of > workloads. My impression is that in most cases, it's a SQL-ish > solution to a problem where someone decided to use the SQL nail > because that's the hammer they had. I can think of ways you could > use it, and I'm not surprised that Some Giant Corp is doing so. But > I'd be astonished if someone used it for truly valuable data. I > would think very hard about the qualifications of someone who > proposed using it for financial data. > If it was developed by Ericson for Telco purposes then it would be designed to be wicked fast for OLTP (billing-switching is an OLTP application) with VERY high up time and reliable (customers and on occasion regulators get angry when the phones dont work). It wouldn't matter if it can be geographically distributed. If the switching center catches fire you're hosed anyway. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Blobs in Postgresql
On Wednesday 2007-08-15 05:52, Gregory Stark wrote: > "Ron Olson" <[EMAIL PROTECTED]> writes: > > Hi all- > > > > I am evaluating databases for use in a large project that will hold image > > data as blobs. I know, everybody says to just store pointers to files on > > the disk... > > Well not everyone. I usually do, but if you're not handling these blobs > under heavy load independent of the database (like web servers) then either > approach works. I've always wondered how you keep transactions working when you only store pointers to large data. Do you need an external transaction manager to insure that the file doesn't get deleted when you "delete" the data via the pointer? Do you need an external application that handles all deletes, inserts, and updates? ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] postgres vs. oracle for very large tables
On Tuesday 2006-06-13 16:19, Jim C. Nasby wrote: > On Mon, May 15, 2006 at 12:24:51PM -0700, TJ O'Donnell wrote: > > I've written some extensions to postgres to implement > > chemical structure searching. I get inquiries about > > the performance of postgres vs. oracle. This is a huge > > topic, with lots of opinions and lots of facts. But, > > today I got some feedback stating the opinion that: > > "Postgres performance diminishes with large tables > > (we?ll be going to upwards of hundreds of millions of rows)." > > > > Is this pure speculation, opinion, known fact? > > Does anyone know of measured performance of postgres > > vs. oracle, specifically with very large tables? > > You're more likely to run into problems with large fields being toasted > than plain large tables. IIRC Oracle's large object support is better > than PostgreSQL's. There's more to it that that. If the huge tables grow, VACCUMING for XID maintenance could put Postgres at a disadvantage relative to Oracle. There are "behavioral" variables involved. Furthermore, it may be possible to trade DBA tricks for initial cost of ownership. Usually the accounting doesn't work out (DBA salaries are even more expensive than Oracle licenses) ... but grad students work cheap. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Fabian Pascal and RDBMS deficiencies in fully implementing
On Tuesday 2006-06-13 09:26, David Fetter wrote: > On Tue, Jun 13, 2006 at 09:18:17AM -0600, Scott Ribe wrote: > > > What say we just stop right there and call Date's Relational Model > > > what it is: a silly edifice built atop wrong premises. > > > > SQL was a quick and dirty hack (Systems R and R* needed some way to > > interface with data) with multiple deficiencies recognized and > > documented right within the very first paper by its own authors. > > Perfection isn't a human attribute. There isn't a whole lot of > convincing evidence that it's a divine attribute. Did you have a > point to make? > > > To hold it up as any kind of paradigm is really misinformed. > > SQL had something that relational algebra/relational calculus did not > have, which is that somebody without a math degree can stare at it a > short while and *do* something with it right away. That it also has > other properties that are extremely useful and powerful (the ability > to specify states of ignorance using NULL, do arithmetic, use > aggregates, etc.) is what has made it such a smashing success. > > Now, there's another thing that makes it amazingly hard to displace: > imagining what would be better *enough* to justify the many millions > of people-years and even more billions of dollars needed to move away > from it. Despite Date's many whines over the decades, his > still-vaporware Relational Model doesn't even vaguely approximate that > criterion. > > Cheers, > D COBOL and VisualBasic are better than Haskell by the same argument. (SQL always reminds me a lot of COBOL.) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Fabian Pascal and RDBMS deficiencies in fully
On Friday 2006-06-09 09:50, Martijn van Oosterhout wrote: > On Fri, Jun 09, 2006 at 12:01:07PM -0400, A.M. wrote: > > So you should normalize and add relations to represent the state > > adequately. NULL doesn't give you enough information anyway- does NULL in > > a birthday header mean "no birthday", "n/a" (a business doesn't have a > > birthday), "not born yet", etc... Using real data, you can represent any > > of these states. > > What's makes you think I'm interested in storing the distinctions? > Either I know the birthday or I don't. If I want to work out why I > don't know it, yeah, it's worth storing the state. There are other ways > of finding that out (for example you can readily tell if you're looking > at a business and I know the system doesn't have people that aren't > born yet). But mostly I just need to track that it's not known. > > Have a nice day, More importantly, it is usually not possible to list all the reasons information might be absent. Nullity is a semantically empty residual category. It can and does mean all unspecified (or accidental) forms of information absence. You need null when you model data because real data is not always amenable to an exhaustive partition. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Any *real* reason to choose a natural, composite PK over a surrogate, simple PK?
On Thursday 2006-06-08 05:48, [EMAIL PROTECTED] wrote: > What am I missing? Why use a composite key *ever* aside from > "familiarity?" Could someone give a real-world example where > "familiarity" is a compelling reason to choose a composite PK, and > trumps stability and simplicity? Another "familiarity" translates into "self-documentation" and thus is a major software engineering desideratum. For some designers that might be reason enough to use a composite key. Using a surrogate key is arguably not a gain in simplicity. It adds a column to the table design. It is populated with a non-intuitive sequence number. The table now has a surrogate primary key and the alternate composite key. The only gain in simplicity is for some machine operations. Furthermore the surrogate key allows pseudo-uniqueness. If composite key over rows ABC has two identical values, abc and abc', the composite key must still have a unique constraint (trading back much efficiency that might be gained with the surrogate key) to insure that the identical values are not masked by the primary key constraint on the surrogate key. Likewise, the stability provided by a surrogate key is arguably illusory. If N is the primary key and the values in composite key ABC change then the surrogate key N simply masks poor design. If ABC is not stable then the initial analysis was flawed and ABC was not a valid candidate for a primary key. N only provides stability if the contents of ABC change in such a way that ABC remains unique. > Stability seems to be the single-most important factor to consider. If > the database can't uniquely identify a row, what's the point? Choosing > a surrogate key guarantees stability. > > Dana > > ---(end of broadcast)--- > TIP 1: if posting/reading through Usenet, please send an appropriate >subscribe-nomail command to [EMAIL PROTECTED] so that your >message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Fabian Pascal and RDBMS deficiencies in fully implementing the relational model
On Thursday 2006-06-08 15:14, David Fetter wrote: > On Thu, Jun 08, 2006 at 05:21:07AM -0700, [EMAIL PROTECTED] wrote: > on bag theory[1] and 3-value logic[2]. Until they come up with a > testable system, or Hell freezes over, whichever comes first, Pascal's > book will make a good companion on your shelf to books on > Phlogiston[3] theory, or a decent doorstop, whichever you prefer. I have encountered at least two commercial database products that declared every column "NOT NULL". I have always assumed that this was defensive, preventing stupid programmer mistakes. I recall reading somewhere that Codd proposed multiple flavors of nullity. Are there theoretical proposals for databases with logical systems having more than three values? ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Oracle purchases Sleepycat - is this the "other shoe" for MySQL AB?
On Wednesday 2006-02-15 18:42, Leonard Soetedjo wrote: > On Wednesday 15 February 2006 01:38, Tom Lane wrote: > > merlyn@stonehenge.com (Randal L. Schwartz) writes: > > > Oracle purchases Sleepycat. From what I understand, BerkeleyDB was the > > > "other" way that MySQL could have transactions if Oracle decided to > > > restrict InnoDB tables (after purchasing Innobase last year). > > > > > > Does this mean the other shoe has dropped for MySQL AB? > > > > The deal's not gone through yet, but it sure does look like they want to > > put a hammerlock on MySQL ... > > Is it possible that Oracle is trying to buy MySQL to kill off other open > source competitor, e.g. PostgreSQL? MySQL has a strong number of users and > therefore it is a good deal for Oracle to buy MySQL. Then by doing that, > Oracle will market MySQL as the low-end alternative to their own database > to give a full solution to the customer. And this would slow down the take > up rate for other database competitor. > > I just hope not > > > > Regards, > > Leonard Soetedjo Given Sleepycat's position in the embedded database market, I think Oracle's move to acquire the company stands on it's own without the need to assume it is part of some wider defense against free software. MySQL's current merchandizable market position can't be that desirable from Oracle's point of view. MySQL is best in the lower middle part of the database market. You have to sell lots of units and endure much headache to make money there. Furthermore you don't need MySQL to do it. It would be easy to just hobble and rebrand Oracle to do the same thing. MySQL's only interesting technology is decoupling the MySQL front end from the core database engine. (Which makes me wonder why so many on this list say PosgreSQL couldn't be coopted. Wouldn't MySQL just have to change the PostgreSQL parser?) The real threat to Oracle from the free software community is that faced by Microsoft with BSD *nix and Linux: COMMODIFICATION. Commodification is already a real threat to Oracle and of the three big commercial databases it is the least diversified. For IBM with DB2 database commodification would be the same mixed blessing as OS commodification. With 8.1 and autovacuum PostgreSQL finally became partially independent of a full-time DBA. This means that in terms of numbers, probably 80% of the big three database installations could be replaced with PostgreSQL with little or no loss of functionality (with some proviso for the fact that SQL Server has a much deeper and more user friendly interface). The remaining 20% of installations would obviously be larger and might account for something like 80% of revenue, but the fact remains that the BASIS for database commodification is well in place. There is good reason to expect FOSS database market share to increase considerable over the next 5 to 15 years. Over the medium term Oracle stands to be challenged hard by database commodification. It has two viable strategic options. First it can try to buy time for it's database offerings by slowing the rate of commodification. Second, and more important, it can try to "own" as much of the inevitable commodification on Oracle's terms as it can. Note, the gratis deployment of BSD/Linux from a commercial perspective is not interesting. Enterprise budgets are never so tight. The 20% of accounts that generate 80% of revenues will pay as much or more for Linux as for Windows if it results in meeting critical business needs. Google could pay for Windows or Solaris if it wanted to. Buying Innobase, and especially Sleepy cat immediately hedges Oracle AGAINST commodification ESPECIALLY if they maintain BDB's dual license structure. On the other hand, Oracle acquires some ability to throttle commodification by slowing development of dual license software products that it controls. Furthermore it has put itself in a no-downside position vis-a-vis MySQL and MySQL's dominant market share of modest web applications. In the best case Oracle acquires MySQL. It continues to offer MySQL on GPL terms but manages the product's future to best insultate the company from near-term FOSS commodification. (Commodification defense actually implies *gaining* market share against FOSS competitors. Oracle wants to own the dual-license commodity standard if it can.) If Oracle can't buy MySQL then it can starve it for database backends, and MySQL may fold. In one scenario Oracle still buys MySQL. At worst, MySQL is orphaned until a group picks up the GPL code. Or MySQL may be denied backend engines and soldier on. In this case, commodification is still slowed down as MySQL scrambles and its evident role as the emerging commodity standard (based on market share) is called into question. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] RAID-50
On Wednesday 2006-01-18 17:38, Michael Crozier wrote: > > I once read in an O'Reilly book on tuning Solaris about RAID-50. That's > > right multiple RAID-5 disk sets each treated as a single logical drive > > then striped with RAID-0. The book said that if built correctly load > > balancing could make performance approach RAID-10 at considerably lower > > cost for VERY large storage arrays. > > > > Have any list members had experience with RAID-50? > > There have been a few recent discussions on the pg-performance mailing list > that touched on RAID-50. I'm sorry, but I don't remember the conclusions > (if there were any). I'd suggest a peek at the archives. > > Are you researching this for Postgresql Solaris? I would certainly > appreciate hearing about any of your findings, especially if this is for a > PG/Solaris/LVM combination. > > Regards, > > Michael I wish I had some practical motivation. Alas, I am asking out of idle curiosity. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] RAID-50
I once read in an O'Reilly book on tuning Solaris about RAID-50. That's right multiple RAID-5 disk sets each treated as a single logical drive then striped with RAID-0. The book said that if built correctly load balancing could make performance approach RAID-10 at considerably lower cost for VERY large storage arrays. Have any list members had experience with RAID-50? ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Relational Inheritance Features.
Relational Inheritance Supporting Features Perhaps the most important deficit in Postgresql's current INHERITS model is hard to detect. That deficit is the inability to name inheritance classes themselves. One has to refer to the _per se_ class by referencing an associated table. While it is nice to have anonymous classes, it is highly desirable that classes be first-class objects, and at a minimum a proper object should be able to take a name in its own right.[name] Since a class' interface is the tuple for the associated table, classes should not be directly instantiated unless the class owns (a possibly anonymous) table. As first-class objects, however, one should be able to DECLARE or DEFINE relational inheritance classes (or rather, the root for a tree or branch) [define].A define function is useful when creating a relational inheritance analog to a pure abstract class; that is, a class that cannot be directly instantiated. (An pure abstract class is not to be confused with an abstract class table.) Actually, lack of DEFINE is a major gap in SQL itself. Most modern programming languages make a distinction between *defining* a “multi-variable precursor” (sort of a light weight type) and *instantiating* a variable. In SQL, definition is always conflated with instantiation using a CREATE expression. It should be possible to DEFINE database objects and then CREATE the objects; create them after an indefinite interval, and perhaps repeatedly. One should not have to repeatedly run verbose scripts. Even more than completing than use in some still theoretical relational inheritance model, DEFINE would be of immediate practical use: CREATE temporary tables, that often occur as clones and that are repeatedly created and dropped. CREATE views that are often dropped then recreated. Creating indexes that are repeatedly dropped. Managing frequently dropped constraints. If one could define a free-floating or “immaculate” index, it could be repeatedly instantiated and attached to columns in different tables. If one could define an immaculate constraint, it could be repeatedly created and attached to tables in a schema. 1.1 Some operations on relational classes. With fully developed relational inheritance it would be highly desirable to re-order columns in descendant tables. The first reason is a strong aesthetic argument. One might, for example, always want some audit columns to be the last five values in any table in a schema. A practical argument is that one could create distinct class signatures by arbitrarily recombining the order of columns. Naturally, it would be convenient, if one could also refer to columns by name in procedural languages. Relational inheritance classes should also support a form of polymorphism. Specifically, the name of an ancestor table (or the table itself) and the fully or sufficiently specified columns of the row's ordered form can be regarded as a distinct signature [signature]. When selecting, inserting, or updating signatures should be polymorphic. Note that an incautious user (or overly permissive implementation, depending on perspective) could create ambiguous queries. These could either be resolved by a tree traversal rule or detected and errored out. Making ambiguous structures or ambiguous queries errors is obviously required by good design. 1.2 Extending relations or classes As I have mentioned elsewhere multiple inheritance requires merging multiple parent classes. Merging multiple ancestries implies a mathematical order of operations. It follows that when an INHERITS clause specifies more than one table, the designer should be able to use parentheses to explicitly specify the order for merging parent tables. Of more general interest is that table attributes have scope. This is a new dimension of complexity that does not exist in pure relational databases. For practical purposes, I anticipate that it will be sufficient only to specify local and subclass scope with phrases using the words LOCAL and CLASS. Columns, constraints, indexes, and rules and triggers (event actions) can have either local or (sub)class scope. Local scope implies that the property (acts as if) it were created an managed independently for the top of the class, and possibly for each descendant. Note that how local properties are treated by inheritance is somewhat ambiguous. Should these dependent objects be created or changed in descendants? Since the answer is not clear I propose that the behavior must be explicitly specified. When a local column, constraint, index, or event action is created, altered, or dropped one must specify whether or not the action cascades to descendants. (It should *not* cascade by default.) Likewise, when a table inherits from a class with local properties one should specify whether local properties are inherited. They should be inherited by default. Class constraints do not
[GENERAL] Relational Inheritance Thoughts
Relational Inheritance Thoughts The most fundamental property of relational inheritance is that it creates hierarchies of relations that act as composite relations. That is, relational inheritance produces a tree of relations (presumably tables) that itself can be treated as a relation. The tree can be queried as if it were a single table, and provided that the signature of the tuple is distinct, inserts and updates can also be polymorphically executed against the hierarchical composite relation. Obviously, relational inheritance is one way to realize an object-relational feature in a database. What is almost as obvious is that inheritance introduces a non-relational database element into the underlying database model. Depending on whether or not the implementation supports single or multiple inheritance the resulting product supports not only a relational database model but also either a hierarchical or an acyclic network [acdg]. At the risk of belaboring the point, relational single inheritance resembles a hierarchical database, like a traditional computer file system. A relational multiple inheritance database resembles a network database, like the once promising CODASYL standard or the GROVE or DOM representation of an XML hyper-document. I recall reading somewhere that network databases can realize all the functional power of a relational database and the reverse is also true (thus, network and relational databases are functional equivalents). Translating between the two models is, unfortunately, not trivial. The reader will know from experience that normalized relational databases (and more so, some strategic denormalized data warehouse forms) are relatively easy to query. On the other hand, algorithmic data structures naturally become trees and directed graphs. It is easy to do application programming for a network database. A database with relational multiple inheritance is a hybrid between a relational database and network database that ought to support any mixture of the data models without prejudice. The ability to handle a network data model has the advantage of eliminating the need to provide a translation layer that marshals data to and from each application that uses the database. The networked object oriented data from an application can (theoretically) go directly into the network composite relation without the translation needed to put it in relational form. More generally, the ability to work with a dual system supporting both relational and network models simultaneously will give database designers another degree of freedom in expressive power. A good designer will be that much better with relational inheritance options. (On the other hand, novice and poor designers will have more rope with which to hang themselves.) On the downside, one expects that a data design cannot be optimized both for write operations and for queries. Relational inheritance allows database to be designed for the convenience of application programmers at the expense of report writers. Given the typical design process for databases and the relative power and prestige of application and report developers one indeed expects that relational inheritance *will* be used to the advantage of application programmers and the detriment of report writers. Furthermore, relational multiple inheritance databases have to encompass the complexity of Relational X Network. A relational multiple inheritance database system should be a degree of magnitude more complex than its relational (or network) relative. The reward will be more expressive convenience for database designers. The resulting system, however, will have no more functional power than either a relational or network database system. Also interesting is that a table (or relation) may have an “attitude” toward parenthood. The table may be preapted. A preapted table is explicitly and completely ready for use as the parent of a class hierarchy. It is fully class aware. One should be able to explicitly make a preapted stand-alone table Adhamic, ready for use as the first ancestor of a hierarchy. Of course, you should also be able to declare that a preapted table is FINAL. Furthermore, declaring any class property on a table should implicitly make the table itself a member of a relational inheritance network. Note that strict preaptation does not allow for opportunistically finding a purely relational table and using the relational table as the first ancestor of a relational inheritance class. Another attitude toward parenthood is indifference. There are various ways that a table may be purely relational, thus indifferent to being used as a class ancestor. The table may be implicitly ignorant. As far as a found Adhamic table is concerned there is no class. It is absolutely unconstrained by any objects dependent on it and it would be utterly ignorant that it is implicate
Re: [GENERAL] WAL logs multiplexing?
On Wednesday 2005-12-28 05:38, Martijn van Oosterhout wrote: > On Wed, Dec 28, 2005 at 03:17:40PM +0300, Dmitry Panov wrote: > > I'm currently considering setting up online backup procedure and I > > thought maybe it would be a useful feature if the online logs could be > > written into more than one place (something like oracle redo logs > > multiplexing). > > > > If I got it right if the server's filesystem crashes completely then the > > changes that haven't gone into an archived log will be lost. If the logs > > are written into more than one place the loss could be minimal. > > So you think PostgreSQL should reimplement something that RAID > controllers already do better? > > These are reasons you have backups and PITR and other such things. I > don't think having the server log to multiple places really gains you > anything... What if one is off-site? ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Inheritance Algebra
On Wednesday 2005-12-21 07:50, Karsten Hilbert wrote: > On Wed, Dec 21, 2005 at 01:52:34PM +0100, Martijn van Oosterhout wrote: > > On Sun, Dec 04, 2005 at 10:59:10PM -0700, Trent Shipley wrote: > > > Relational Constraint Inheritance Algebra > > > With regard to class and attribute uniqueness > > > > It's taken a while to digest this and sorry for the delay. While I find > > the ideas intreguing there is a little voice in the back of my head > > asking: practical applications? > > I would assume quite a few people would use table > inheritance in a simple way were it available in a more > convenient fashion: to transport fields, primary and foreign > keys to child tables. I am not clear on why this sort of scenario benefits more from CREATE TABLE's "INHERITS" clause than the "LIKE" clause (assuming that LIKE copied the appropriate table properties). Indeed, the recursive SELECT associated with INHERITS might be undesirable. If I understand you [Karsten] correctly then the really elegant way to do this is with a "DECLARE" or "DEFINE TABLE|INDEX|FOREIGN KEY|... definition_name (definition_clause)" (The choice of DECLARE or DEFINE would depend on the SQL list of reserved words.) Then instantiate the declared object with something like: CREATE TABLE|INDEX|... object_name USING definition_name. Changes in definition (ALTER DEFINITION)should optionally cascade to instantiated objects. Use ALTER TABLE to create variant tables. Very useful for creating things that often get quashed and re-created, like temporary tables and indexes. Also very useful for things that should be uniform but get attached to many tables, like annoying ubiquitous check constraints, indexes, or foreign keys. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Inheritance Algebra
[ This post is theory oriented, so it can't go in HACKERS nor can it go in SQL so it gets posted to GENERAL. I would polish this more. Unfortunately, it is at the point were I'd seek feedback were I in a seminar. ] Relational Constraint Inheritance Algebra With regard to class and attribute uniqueness 0 Intro: Postgresql inheritance and uniqueness Postgresql's INHERITS is one of the most intriguing features of the at-liberty, open-source database. At the same time, most observers regard INHERITS as an incomplete implementation of a fully object-oriented, or better, class-aware, database function. The most glaring omission is that primary key and unique constraints are not inherited by children. Nevertheless, the implementation of INHERITS has not changed much through the last several revisions of Postgresql. Bizgres' partitioning scheme, constraint based exclusion [?], relies on the current default behavior of inheritance in Postgresql. No doubt other consumers have taken advantage of the feature's current behavior, so any extension must preserve existing behavior by either developing sub-clauses that further specify the behavior of the INHERITS or they must develop an entirely new lexis for building inheritance based relational classes. When a constraint is declared in a database that supports relational inheritance, the constraint necessarily has scope. In the simplest cases, constraint scope is local, applying only to the table where the constraint was declared, or the scope is to the subclass, applying to this table and all descendants unless over-ridden. According to the Postgresql 8.0 documentation, all constraints are automatically inherited unless over-ridden (the subclass model) except for foreign and unique constraints that are unsupported at the class level. In effect, under Postgresql 8.0 foreign and unique constraints have local scope. Another notable quirk of Postgresql's inheritance model is that no table is explicitly aware it could become a parent. There is no “abstract” or “final” clause nor any other clause restricting the behavior or potential children exists in “CREATE TABLE”. Indeed, the top of any inheritance hierarchy necessarily begins as a strictly relational table. One side effect of the current model is that implementing class-wide uniqueness is problematic. Either the parent model would need to be abstract (a nonexistent clause) or a child's inheritance of a unique constraint would change the behavior of the parents heretofore table-local unique (or even non-unique) column. Postgresql's current hybrid implementation of inheritance, having both implicitly local and subclass scope for different kinds of constraints, points to a powerful hybrid model where columns can have constraints that are explicitly declared with table-local or subclass-wide scopes. The rest of this essay examines the interaction of localism-class cross plurality-uniqueness[1]. It seems obvious that the distinctions have theoretical discussion (and hopefully acceptance). More important is whether the supporting these distinctions would be useful in any real-world product. I believe that supporting such fine distinctions would be of some use, but will make no further effort to argue the case. 1 Types of relational inheritance models Relational inheritance of a constraint feature has scope [2]. Levels of scope include absent (necessarily local), table-local, subclass, class-wide, mixed, and dual. Obviously, support for relational inheritance can simply be absent. This is the norm. Any such table is strictly relational and all constraints are necessarily local. Tables in this essay are explicitly not under the “absent” relational inheritance scope. Another family of models for relational inheritance scope might be called local (table-local or relation-local). If Postgresql's CREATE TABLE ... LIKE clause allowed for “inheritance” of all constraints, triggers, and so on, it would be an implementation of the local model. In particular, unique constraints are checked for each table in the class but are not enforced over the whole of an entire class or subclass. Presumably, if table-local scope were the default behavior across a database, queries would not recurse into descendant tables by default. Note that this used to be Postgresql's default behavior. SQL developers had to ask the engine to recurse into descendant tables. Mixed scope models extend the local model, allowing for class-like treatment of some relational aspects. (In this essay we are particularly concerned with plurality-uniqueness.) Arguably (and unfortunately), Postgresql currently implements a mixed model. Some constraints have subclass scope and some have local scope. A traditional, strictly hierarchical inheritance of constraints from object-aware tables by descendants is a powerful scoping model. Strictly speaking, every table belo
Re: [GENERAL] 3 x PostgreSQL in cluster/redunant
On Tuesday 2005-11-15 13:06, Joshua D. Drake wrote: > Michelle Konzack wrote: > > Am 2005-11-14 16:54:41, schrieb Jim C. Nasby: > >> On Mon, Nov 14, 2005 at 07:36:44PM +0100, Michelle Konzack wrote: > >>> Hello *, > >>> > >>> I have three Sun Server where I have reserved on each Server a Raid-5 > >>> of 1 TByte for my PostgreSQL. The first PostgreSQL is up and running > >>> with a database of 150 GByte. > >> > >> Keep in mind that databases and RAID5 generally don't mix very well. > > > > Can you explain me why? > > RAID 5 is very expensive for writes. > > > Unfortunatly the Controllers in the three SUN-Servers do not support > > 300 GByte SCSI-Drives, so I have to continue with the Raid-5 of 16x > > 76 GByte. > > Could you do RAID 10? > > Sincerely, > > Joshua D. Drake I've seen books on tuning recommend RAID-5 into the low terrabyte range for read-dominated databases (notably small data warehouse applications). For very large multi-terrabye applications the suggestion is that RAID-50 along with streaming to and from stochastically accessed distributed storage can partially hide the expense of writing to storage while bringing the money cost of storage down considerably. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] FOSS Reporting tools (was Oracle 10g Express - any danger for Postgres?)
On Wednesday 2005-11-02 13:11, Jim C. Nasby wrote: > On Wed, Nov 02, 2005 at 01:25:31PM -0600, James Thompson wrote: > > > Yes, sqlplus looks especially bad once you're used to banging around > > > in psql. Although, I recently discovered rlwrap (a generic readline > > > wrapper) which makes sqlplus almost tolerable. It's the best thing to > > > happen to sqlplus since... well, since "quit" I suppose. > > > > I just wish pgsql had something similar to sqlplus's built in formatting > > tools for output. Being able to set titles, row lengths, and breaks made > > sqlplus a very nice reporting tool. A rather large majority of "reports" > > at my old job consisted of sqlplus commands to set the format output, and > > a sql statement redirected to our line printer. > > > > I haven't used Oracle since the mid 90s so I don't have a working example > > but a description of some of the commands can be found here > > > > http://www.siue.edu/~dbock/cmis564/otext3.htm > > Given the choice, I'd *MUCH* rather have a good, easy-to-use CLI than a > reporting tool. I'm not a GUI person, so I always hate working with > Oracle and MSSQL in that regard. Of course db2's CLI is just horrid, but > luckily it's easy to just substitute your shell for it's editing > features, ie: > > db2 'select * from table' > db2 'update ...' > > Believe it or not it very quickly becomes second nature to wrap > everything in db2 '', so it's not nearly as bad as you'd think. > > In any case, how much user demand is there for a reporting tool for > PostgreSQL? Either a seperate tool or better functionality in psql. My > guess is that this isn't something that interests most of the > developers, so the only way it's going to happen is if a lot of users > speak up and ask for it. Of course speaking up with patches is far > better. I never really used SQL*Plus as a command line tool. I tended to use it as a weak SQL scripting language. Granted report generators can't be part of core PostgreSQL, they are still a critical part of any database workshop. === A) Are there any FOSS SQL scripting tools that output data ready for reporting (like SQR, but better)? B) Are there any FOSS tools that will take data and build pretty output. C) Are there any FOSS tools of type B that will take streaming input from some tool of type A. D) Are there any FOSS tools that combine both A and B into one low learning curve package like Crystal Reports. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
