Thread: Firebird and PostgreSQL at the DB Corral.
Hi all, Following up on another thread, here is a comparison between FB and PG from an FB'ers POV. BTW, FB is the love-child of Open-Source-Interbase. BTW, I have tried to be as accurate as I can with the information available to me at this point in time - I was unable to find anything resembling a feature list on the FB site, so most of this is from memory. I do have an email account on the various lists on that site, and I will post this there and see what they have to say. This should get the ball rolling on an FB/PG discussion. Hopefully the primitive formatting that I've applied will work on everybody's machine, I've just just used '> ' (like newsgroups) and ---------'s to separate topics. If you are going to reply, it might be better to do it topic by topic, rather than uselessly copying endless lines of un-replied to postings? Short, staccato, angry responses are what I want! 8-). The architectures of the databases are fundamentally the same MVCC for you, MGA for us Firebirders (Multi Generational Architecture). From an Open-Source-Interbase/Firebird point of view there are several issues where PostgreSQL falls down. Ease of use (particularly on Windows). FB is about as easy as installing Minesweeper. </Controversial remark> PG appears to place a lot of effort in supporting array datatypes. While they are (barely) supported in FB, most advise against using them - if I had my way I'd rip them out - they are the work of the Devil. A well designed schema can get around any need for arrays. They break the relational model and are generally very non-portable. <Controversial remark> I'm not sure exactly where I stand here, but FB has been moved to C++, whereas AFAIK, PostgreSQL is still in C - this is probably a religious war (and beyond me), but I just thought that I'd mention it. From http://advocacy.postgresql.org/advantages/ -------------- > Immunity to over-deployment Ditto for FB. --------------- --------------- > Better support than the proprietary vendors Ditto for FB. ----------------- ---------------------- > Significant saving on staffing costs Ditto for FB. ---------------------- ---------------------- > Legendary reliability and stability Ditto for FB. Although there are reports of corruption from time to time, it is generally because the "server" was actually some end-user's PC. ----------------------- ----------------------- > Extensible Ditto for FB. If you want to extend the code, though, you have to give those changes back to the community - the licence is more GPL than BSD. ------------------------ ----------------------- > Cross platform FB supports approx. 10 major platforms - not quite as many as PG, but still enough to be getting on with. ----------------------- ------------------------ > Designed for high volume environments > We use a multiple row data storage strategy called MVCC to make > PostgreSQL extremely responsive in high volume environments. > The leading proprietary database vendor uses this technology > as well, for the same reasons. Ditto for FB. It uses what is termed variously as MGA (Multi- Generational Architecture - I think that they have you guys beat for the terminology!), Record Shadowing or Record Versioning. It seems to be identical to what PG uses, with a different implementation obviously. -------------------------- --------------------------- > GUI database design and administration tools Ditto for FB. Some free, some proprietary. --------------------------- --------------------------- > A point list for some technical features that PostgreSQL offers: (an X before any of these means that FB is also compliant, an O means not, but see caveats) ----------- > Fully ACID compliant X ----------- ----------- > ANSI SQL compliant X ------------ --------------- > Referential Integrity X (why this should be seen as a bonus is beyond me!) --------------- --------------- > Replication (non-commercial and commercial solutions) allowing > the duplication of the master database to multiple slave machines X caveat: no OS solution at the moment, but there is a form of shadowing that one can do - not great but better than nothing. ---------------- ---------------- > Native interfaces for ODBC, JDBC, C, C++, PHP, Perl, TCL, ECPG, > Python, and Ruby X caveat: I think that FB works natively with the vast majority of these and has its own interfaces as well, but not ECPG obviously. ------------------ ------------------ > Rules ? I don't understand these - can somebody explain exactly what they are? ------------------- ------------------- > Views X (FB's are updateable to boot!) ------------------- ------------------- > Triggers X (puhlease!) -------------------- -------------------- > Unicode X --------------------- --------------------- > Sequences X, in FB parlance Generators! ---------------------- ----------------------- > Inheritance (NO!!!!!!!!!!) ----------------------- ----------------------- > Outer Joins X (This is a big deal?) ------------------------ ------------------------ > Sub-selects X (and this?) ------------------------ ----------------------- > An open API X ----------------------- ---------------------- > Stored Procedures X (another big deal for an RDBMS?) ----------------------- ----------------------- > Native SSL support ? (not sure - there is a thing called Zebedee that allows secure connections over the internet). ----------------------- ----------------------- > Procedural languages X (big deal yet again - though, mind you, not as many as PG. ----------------------- ----------------------- > Hot stand-by (commercial solutions) ? (not sure what is meant by this) ----------------------- ----------------------- > Better than row-level locking X (I assume that what is meant here is MVCC?) ------------------------ ------------------------ > Functional and Partial indexes O -------------------------- -------------------------- > Native Kerberos authentication O -------------------------- -------------------------- > Support for UNION, UNION ALL and EXCEPT queries X -------------------------- -------------------------- > Loadable extensions offering SHA1, MD5, XML, and other functionality O. Caveat, maybe some of this through UDF's? -------------------------- -------------------------- > Tools for generating portable SQL to share with other SQL- > compliant systems. X FB uses fairly standard SQL syntax. There are a couple of freeware utilities as well that help one. --------------------------- --------------------------- > Extensible data type system providing for custom, user-defined > datatypes and rapid development of new datatypes X Caveat. UDT's are a doodle, but cannot AFAIK create row type variables. ---------------------------- ----------------------------- > Cross-database compatibility functions for easing the transition from > other, less SQL-compliant RDBMS O, but many should/would be easy to write using UDF's. ----------------------------- Paul... -- plinehan x__AT__x yahoo x__DOT__x com C++ Builder 5 SP1, Interbase 6.0.1.6 IBX 5.04 W2K Pro Please do not top-post.
On Tue, 16 Dec 2003 21:04:10 -0000 Paul Ganainm <paulsnewsgroups@hotmail.com> wrote:
> Ditto for FB. If you want to extend the code, though, you have to give
> those changes back to the community - the licence is more GPL than BSD.
maybe you need to clarify what you think of when you say "extensible".
postgresql has sql extensions like "create type"; extensibility is possible
w/o going into the source code. it sounds like you're talking about making
changes to the firebird code base here.
richard
--
Richard Welty rwelty@averillpark.net
Averill Park Networking 518-573-7592
Java, PHP, PostgreSQL, Unix, Linux, IP Network Engineering, Security
Paul Ganainm <paulsnewsgroups@hotmail.com> writes: >> Rules > > ? I don't understand these - can somebody explain exactly what they are? Basically like "query macros"--you can use a rule to rewrite part or all of the user query and feed the result to the query optimizer. They're how views are implemented, and you can create updateable views by writing the appriopriate rules. The only reason PG views aren't updateable by default is (AIUI) a lack of agreement on how they should work for complex view definitions. > ------------------- > > ------------------- >> Views > > X (FB's are updateable to boot!) See above. -Doug
On 16/12/2003 21:04 Paul Ganainm wrote: Hi Paul, > ----------------------- > > Better than row-level locking > > X (I assume that what is meant here is MVCC?) > ------------------------ One thing I couldn't find when looking at the FB on-line docs a week or two ago was anything like support for transaction isolation level serializable. Whilst the PG docs states taht its implementation of t.i.l.s is not quite to SQL spec, it is still very good as you don't need to lock rows with select ... for update. How does FB do in this respect? > ------------------------ > > Functional and Partial indexes > > O > -------------------------- No partial indexes? Get them to put it on their TODO list ;) BTW, has FB got an equivalent of PG sequences? -- Paul Thomas +------------------------------+---------------------------------------------+ | Thomas Micro Systems Limited | Software Solutions for the Smaller Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +------------------------------+---------------------------------------------+
Paul Ganainm wrote: > Hi all, > > Following up on another thread, here is a comparison between FB and PG > from an FB'ers POV. BTW, FB is the love-child of Open-Source-Interbase. I would love to have this comparison in a table form and posted on web. Let FB guys chip in and make it more correct.. > The architectures of the databases are fundamentally the same MVCC for > you, MGA for us Firebirders (Multi Generational Architecture). And postgresql has vacuum and FB has automatic sweeps, correct? >From an Open-Source-Interbase/Firebird point of view there are several > issues where PostgreSQL falls down. > > Ease of use (particularly on Windows). FB is about as easy as installing > Minesweeper. Native Postgresql on windows is practially non-exsitent so if you want to compare ease of use, let's talk unix where postgresql is fairly easy to use.. > I'm not sure exactly where I stand here, but FB has been moved to C++, > whereas AFAIK, PostgreSQL is still in C - this is probably a religious > war (and beyond me), but I just thought that I'd mention it. Let's skip it. It is legacy.. >>Legendary reliability and stability > Ditto for FB. Although there are reports of corruption from time to > time, it is generally because the "server" was actually some end-user's > PC. Can that be termed as data corruption due to fault in database server is rare. we should not attribute data corruption due to hardware failure to database software. Postgresql ranks very high on that regard. Except for WAL corruption bug fixed in 7.3.3, there has been no such bug till date(and even for long time) IIRC.. >>Extensible > Ditto for FB. If you want to extend the code, though, you have to give > those changes back to the community - the licence is more GPL than BSD. Postgresql is extensible from an applications POV. Data types, operators, functions, languages, rules, domains and checks etc. How much of it FB supports? >>Cross platform > > > FB supports approx. 10 major platforms - not quite as many as PG, but > still enough to be getting on with. Practiaclly postgresql supports one platform. Unix..:-) Rest is marketing speak. I am not discounting support matrix but I think this is fair to start a database comparison. Windows port in works. That will truely be another platform.. >>ANSI SQL compliant > X Be careful.. You need to list SQL version as well.. >>Native interfaces for ODBC, JDBC, C, C++, PHP, Perl, TCL, ECPG, >>Python, and Ruby > X caveat: I think that FB works natively with the vast majority of these > and has its own interfaces as well, but not ECPG obviously. Does it support writing stored procedures in any of these?.. >>Rules > ? I don't understand these - can somebody explain exactly what they are? This is answered already but I will take another shot. Rules allow you to redirect/add to action of an SQL statement. Check http://developer.postgresql.org/docs/postgres/rules.html >>Views > X (FB's are updateable to boot!) You can do that in postgresql bu postgresql won't do it for you. You have to due the legwork.. >>Hot stand-by (commercial solutions) > ? (not sure what is meant by this) Your database machine/service fails and it is automatically switched over to another database machine/servie without interruption in application availability.. You didn't cover one thing. The on disk layout. AFAIK, FB uses one database per file which makes it hard to support division of data physically. Postgresql has much better disk organisation IMHO.. Good to have such comparison. Let's hope to get a compiled version on web for masses to see.. Shridhar
rwelty@averillpark.net says... > > Ditto for FB. If you want to extend the code, though, you have to give > > those changes back to the community - the licence is more GPL than BSD. > maybe you need to clarify what you think of when you say "extensible". > postgresql has sql extensions like "create type"; extensibility is possible > w/o going into the source code. it sounds like you're talking about making > changes to the firebird code base here. I took the "extensible" bit from the URL that I posted http://advocacy.postgresql.org/advantages/http://advocacy.postgresql.org /advantages/ ------------------ Extensible The source code is available to all at no charge. If your staff have a need to customise or extend PostgreSQL in any way then they are able to do so with a minimum of effort, and with no attached costs. This is complemented by the community of PostgreSQL professionals and enthusiasts around the globe that also actively extend PostgreSQL on a daily basis. ------------------ This seemed to me to be basically a touting of the virtues of PostgreSQL being Open Source, which Firebird is also, but in a different way. FB has the "CREATE DOMAIN url VARCHAR(100)" for example. Paul... > richard -- plinehan x__AT__x yahoo x__DOT__x com C++ Builder 5 SP1, Interbase 6.0.1.6 IBX 5.04 W2K Pro Please do not top-post.
paul@tmsl.demon.co.uk says... > > X (I assume that what is meant here is MVCC?) > One thing I couldn't find when looking at the FB on-line docs a week or > two ago was anything like support for transaction isolation level > serializable. Whilst the PG docs states taht its implementation of t.i.l.s > is not quite to SQL spec, it is still very good as you don't need to lock > rows with select ... for update. How does FB do in this respect? To the best of my knowledge, FB does support serialisable transaction level - I'm 95% sure that it does - will look into this and report back! > > > Functional and Partial indexes > > O > No partial indexes? Get them to put it on their TODO list ;) What, exactly, is a partial index? A functional index is an index on something like ((ColumnX*2)/14)? I think the functional one (is that also an expression index?) is on the way. > BTW, has FB got an equivalent of PG sequences? Yes - "Generators" - easy and simple. Paul... -- plinehan x__AT__x yahoo x__DOT__x com C++ Builder 5 SP1, Interbase 6.0.1.6 IBX 5.04 W2K Pro Please do not top-post.
On Wed, Dec 17, 2003 at 11:08:13AM -0000, Paul Ganainm wrote: > > > > Functional and Partial indexes > > > O > > No partial indexes? Get them to put it on their TODO list ;) > > > What, exactly, is a partial index? A functional index is an index on > something like ((ColumnX*2)/14)? I think the functional one (is that > also an expression index?) is on the way. A partial index is a index on a subset of a table. The case I can think of is a list of transactions, some of which are yet to be billed. They have a BillID field which is NULL. since this is the recent set it is queried quite often, so you can build an index like: CREATE INDEX x ON table ( customerid ) WHERE billid IS NULL Now an index can be used on customer when searching for only unbilled things whereas normally it would also have to search for all historical things as well. So, you get the benefit of a smaller index that is more useful to boot. The other useful situation is it allows you to apply a UNIQUE contraint on only a subset of a table. I havn't used it for that myself. I hope this makes it clear. -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > (... have gone from d-i being barely usable even by its developers > anywhere, to being about 20% done. Sweet. And the last 80% usually takes > 20% of the time, too, right?) -- Anthony Towns, debian-devel-announce
Attachment
shridhar_daithankar@myrealbox.com says... > > Following up on another thread, here is a comparison between FB and PG > > from an FB'ers POV. BTW, FB is the love-child of Open-Source-Interbase. > I would love to have this comparison in a table form and posted on web. Let FB > guys chip in and make it more correct.. That would be nice. > > The architectures of the databases are fundamentally the same MVCC for > > you, MGA for us Firebirders (Multi Generational Architecture). > And postgresql has vacuum and FB has automatic sweeps, correct? The sweeps can be disabled (i.e. set to 0, for manual - mostly late at night or something similar), the default is 20000 - 20K. > > Ease of use (particularly on Windows). FB is about as easy as installing > > Minesweeper. > Native Postgresql on windows is practially non-exsitent so if you want to > compare ease of use, let's talk unix where postgresql is fairly easy to use.. IB/FB is a breeze to use on Unix also - it was developed originally on Unix machines - it's been around since 1981. > >>Legendary reliability and stability > > Ditto for FB. Although there are reports of corruption from time to > > time, it is generally because the "server" was actually some end-user's > > PC. > Can that be termed as data corruption due to fault in database server is rare. Yes. Most issues that crop up on the Interbase and/or Firebird groups appear to me to be of the "Someone tripped over the power lead on my machine, and now the db is corrupt" variety - these people also for some reason appear to have "forced writes" turned off - I think that's like fsync to you guys. > we should not attribute data corruption due to hardware failure to database > software. Postgresql ranks very high on that regard. Except for WAL corruption > bug fixed in 7.3.3, there has been no such bug till date(and even for long time) > IIRC.. The one way in which I see FB as falling down is not having a WAL. It should (and I believe that this is being worked on) - apparently it used to have one, but it got broken (while IB was still commerical I hasten to add). Still, I will just say this, if you have a decent server (i.e. running something other than Wintendo 95 or 98) and look after things like UPS and regular backups, your problems will be *_minimal_*. FB is very much designed to be low maintainance. > >>Extensible > > Ditto for FB. If you want to extend the code, though, you have to give > > those changes back to the community - the licence is more GPL than BSD. > Postgresql is extensible from an applications POV. Data types, operators, > functions, languages, rules, domains and checks etc. > How much of it FB supports? OK, I have to be honest here. Some of the terminology used here can confuse me. Sometimes people use different words for the same thing, and vice versa. What do you mean exactly by extensible data types? And extensible operators - I'm pretty sure that FB doesn't have those. Functions (known in FB as UDF's (User Defined Functions), yes. Extensible languages? Care to elaborate? FB can use UDF's in any language - for example if you write a dll (ARrrhh - Windows again) in any language of your choice, then it can be used by FB. > > FB supports approx. 10 major platforms - not quite as many as PG, but > > still enough to be getting on with. > Practiaclly postgresql supports one platform. Unix..:-) Rest is marketing speak. > I am not discounting support matrix but I think this is fair to start a database > comparison. All I can say is, if you guys can get a nice, easy Windows setup going, that will be real progress. Whether you or I like it or not, W$oze is an important platform. I had a project recently where there is your classic WNT server in the corner - I suggested putting the db on a cheap (2% of project cost) Linux box - you should have seen the guy's eyes glaze over.. it was a non-runner. For the time being, you have to able to offer a *_serious_* Windows solution. This is one area where FB/IB wins hands down. > Windows port in works. That will truely be another platform.. I await it impatiently. > >>ANSI SQL compliant > > X > Be careful.. You need to list SQL version as well.. Of course - I think it's entry level 92 at least, with more, plus their own extensions. From what I've read, FB and PG are about as compliant with the 99 standard as each other. > >>Native interfaces for ODBC, JDBC, C, C++, PHP, Perl, TCL, ECPG, > >>Python, and Ruby > > X caveat: I think that FB works natively with the vast majority of these > > and has its own interfaces as well, but not ECPG obviously. > Does it support writing stored procedures in any of these?.. AFAIK, no. You can write SP's in IB/FB's own language - not too dissimilar from Oracle's PL/SQL. > >>Rules > > ? I don't understand these - can somebody explain exactly what they are? > This is answered already but I will take another shot. Rules allow you to > redirect/add to action of an SQL statement. Check > http://developer.postgresql.org/docs/postgres/rules.html Thanks. > >>Views > > X (FB's are updateable to boot!) > You can do that in postgresql bu postgresql won't do it for you. You have to due > the legwork.. To be honest, I'm not too familiar with them myself. I'll be reading up on them in PG. Hey, the thought's just struck me, that's another area where PG wins hands down - the availability of literature. There's not a single book in English about it (though there is in Russian, Japanese, German and I think Portuguese). This is a real failing, though, I think that there are two in the pipeline. > >>Hot stand-by (commercial solutions) > > ? (not sure what is meant by this) > Your database machine/service fails and it is automatically switched over to > another database machine/servie without interruption in application availability.. Shadows? I'm not sure here. > You didn't cover one thing. The on disk layout. AFAIK, FB uses one database per > file which makes it hard to support division of data physically. It's impossible - the server does it for you. Maybe that's part of the strength *_and_* weakness of IB/FB - it's simplicity. > Postgresql has much better disk organisation IMHO.. Sure, but that leads to greater complexity - there's always a quid pro quo. > Good to have such comparison. Let's hope to get a compiled version on web for > masses to see.. That would be good. Paul... > Shridhar -- plinehan x__AT__x yahoo x__DOT__x com C++ Builder 5 SP1, Interbase 6.0.1.6 IBX 5.04 W2K Pro Please do not top-post.
Paul Ganainm wrote: > rwelty@averillpark.net says... > > > >>>Ditto for FB. If you want to extend the code, though, you have to give >>>those changes back to the community - the licence is more GPL than BSD. > > > >>maybe you need to clarify what you think of when you say "extensible". > > > >>postgresql has sql extensions like "create type"; extensibility is possible >>w/o going into the source code. it sounds like you're talking about making >>changes to the firebird code base here. > > > > I took the "extensible" bit from the URL that I posted > > http://advocacy.postgresql.org/advantages/http://advocacy.postgresql.org > /advantages/ > > ------------------ > Extensible > The source code is available to all at no charge. If your staff have a > need to customise or extend PostgreSQL in any way then they are able to > do so with a minimum of effort, and with no attached costs. This is > complemented by the community of PostgreSQL professionals and > enthusiasts around the globe that also actively extend PostgreSQL on a > daily basis. > ------------------ > > This seemed to me to be basically a touting of the virtues of PostgreSQL > being Open Source, which Firebird is also, but in a different way. I think both of them is true. For me, this kind of bit sets "extensibility" equal to "extensible by extending the source code" _and_ "extensible by using specific database infrastructure" (see the sentence "..extend PostgreSQL in any way....") > > FB has the "CREATE DOMAIN url VARCHAR(100)" for example. PostgreSQL adds CREATE TYPE, CREATE AGGREGATE, CREATE OPERATOR, CREATE OPERATOR CLASS, CREATE CONVERSION, CREATE CAST and CREATE LANGUAGE to this list. > > > Paul...
On Wednesday 17 December 2003 17:07, Paul Ganainm wrote: > shridhar_daithankar@myrealbox.com says... > > Native Postgresql on windows is practially non-exsitent so if you want to > > compare ease of use, let's talk unix where postgresql is fairly easy to > > use.. > IB/FB is a breeze to use on Unix also - it was developed originally on > Unix machines - it's been around since 1981. Given that FB is a multithreaded application, I wonder how it resolves threading issues across platform. Threading has been discussed here before a lot of times and smacked down because cross platform issues are too much for the trouble worth. (OK this is only one of the reason. The major one is process based architecture works flawlessly anyways..:-)) > > Can that be termed as data corruption due to fault in database server is > > rare. > > Yes. Most issues that crop up on the Interbase and/or Firebird groups > appear to me to be of the "Someone tripped over the power lead on my > machine, and now the db is corrupt" variety - these people also for some > reason appear to have "forced writes" turned off - I think that's like > fsync to you guys. Yes. Additionally IDE disks can never provide guaranteed recovery if write cache is enabled. The issue goes beyond which OS and application it is running.. > OK, I have to be honest here. Some of the terminology used here can > confuse me. Sometimes people use different words for the same thing, and > vice versa. > > What do you mean exactly by extensible data types? And extensible > operators - I'm pretty sure that FB doesn't have those. You can create your own data types and operators which you can later use in table creation and SQL functions etc. See http://developer.postgresql.org/docs/postgres/sql-createoperator.html http://developer.postgresql.org/docs/postgres/sql-createtype.html > > Functions (known in FB as UDF's (User Defined Functions), yes. > > Extensible languages? Care to elaborate? FB can use UDF's in any > language - for example if you write a dll (ARrrhh - Windows again) in > any language of your choice, then it can be used by FB. Well, You should check, http://developer.postgresql.org/docs/postgres/sql-createlanguage.html and http://techdocs.postgresql.org/guides/PLLanguages Particularly, when you have a language handler, you don't need to compile it. e.g. in case of pl/sh, you can type a shell script in create function command and it will work You don't need to compile a function in a shared library unless required..:-) I admit postgresql is bit too much extensible in this respect but who's complaining?..:-) > > > > FB supports approx. 10 major platforms - not quite as many as PG, but > > > still enough to be getting on with. > > > > Practiaclly postgresql supports one platform. Unix..:-) Rest is marketing > > speak. I am not discounting support matrix but I think this is fair to > > start a database comparison. > > All I can say is, if you guys can get a nice, easy Windows setup going, > that will be real progress. Whether you or I like it or not, W$oze is an > important platform. I had a project recently where there is your classic > WNT server in the corner - I suggested putting the db on a cheap (2% of > project cost) Linux box - you should have seen the guy's eyes glaze > over.. it was a non-runner. Problem with windows is it is not Unix where postgresql is easy to port across and windows have some truely brain-dead API's like createProcess(rather absence of fork/exec). It is difficult to port a Unix only software to windows. It is cultural shift and it will take it's own time. Furthermore postgresql developers don't want people to judge stability and ability of postgresql from postgresql on windows. It is expected that postgresql on windows will take quie some time to reach same level of stability on unix in general. That is the reason the port is not rushed upon.. > For the time being, you have to able to offer a *_serious_* Windows > solution. It will come. If one is in a hurry,look elsewhere..:-) I mean what's the point of open source if things are released when they are not ready? > This is one area where FB/IB wins hands down. Absolutely.. > > You didn't cover one thing. The on disk layout. AFAIK, FB uses one > > database per file which makes it hard to support division of data > > physically. > > It's impossible - the server does it for you. Maybe that's part of the > strength *_and_* weakness of IB/FB - it's simplicity. Well, as your database grows it presents problem for performance and scalability I believe. I mean if you have 2000 tables and 400GB of data in single file, how an OS is supposed to guess about caching patterns of data and sync. abilities? Postgresql can get away with syncing small part of cluster which it knows been changed. If a single file has to be synced which is large but change is small, I doubt it will remain efficient for long. > > Postgresql has much better disk organisation IMHO.. > > Sure, but that leads to greater complexity - there's always a quid pro > quo. That is correct but the complexity is already built in, it works and it is proven .So the pain part of it is history and it is time to reap the fruits of some good design efforts..:-) Shridhar
Doug McNaught <doug@mcnaught.org> writes:
> The only reason PG views aren't
> updateable by default is (AIUI) a lack of agreement on how they should
> work for complex view definitions.
Actually I think it's more that no one has felt like tackling it. The
SQL spec only requires views to be updatable when they are "sufficiently
simple", and it turns out that the spec's constraints on "sufficiently
simple" eliminate all the doubtful cases.
regards, tom lane
> > The only reason PG views aren't > > updateable by default is (AIUI) a lack of agreement on how they should > > work for complex view definitions. > >Actually I think it's more that no one has felt like tackling it. The >SQL spec only requires views to be updatable when they are "sufficiently >simple", and it turns out that the spec's constraints on "sufficiently >simple" eliminate all the doubtful cases. > > regards, tom lane Sure would be sweet to have it. While I'm here I take this chance to say.. PostgreSQL ROCKS! Thanks to everyone who makes PG great. I'll go back to lurking. G _________________________________________________________________ Get dial-up Internet access now with our best offer: 6 months @$9.95/month! http://join.msn.com/?page=dept/dialup
kleptog@svana.org says... > A partial index is a index on a subset of a table. The case I can think of > is a list of transactions, some of which are yet to be billed. They have a > BillID field which is NULL. since this is the recent set it is queried quite > often, so you can build an index like: > CREATE INDEX x ON table ( customerid ) WHERE billid IS NULL > Now an index can be used on customer when searching for only unbilled things > whereas normally it would also have to search for all historical things as > well. > So, you get the benefit of a smaller index that is more useful to boot. Hmmm... this is kinda like the Oracle thing where tables can be partitioned? Sort of? Paul... -- plinehan x__AT__x yahoo x__DOT__x com C++ Builder 5 SP1, Interbase 6.0.1.6 IBX 5.04 W2K Pro Please do not top-post.
On Thursday 18 December 2003 03:53, Paul Ganainm wrote: > kleptog@svana.org says... > > > Now an index can be used on customer when searching for only unbilled > > things whereas normally it would also have to search for all historical > > things as well. > > > > So, you get the benefit of a smaller index that is more useful to boot. > > Hmmm... this is kinda like the Oracle thing where tables can be > partitioned? Sort of? Not really (other than it's a performance thing). It is very useful when you know most queries are only interested in a certain set of values. -- Richard Huxton Archonet Ltd
On Tue, 2003-12-16 at 16:04, Paul Ganainm wrote:
> > Native interfaces for ODBC, JDBC, C, C++, PHP, Perl, TCL, ECPG,
> > Python, and Ruby
>
I went looking for the native PHP interface to firebird and came up
blank... can you post a link?
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
I've never actually used them but I'm guessing that this is what your looking for. Can anyone verify this? http://us2.php.net/ibase rg ----- Original Message ----- From: "Robert Treat" <xzilla@users.sourceforge.net> To: "Paul Ganainm" <paulsnewsgroups@hotmail.com> Cc: <pgsql-general@postgresql.org> Sent: Thursday, December 18, 2003 1:38 PM Subject: Re: [GENERAL] Firebird and PostgreSQL at the DB Corral. > On Tue, 2003-12-16 at 16:04, Paul Ganainm wrote: > > > Native interfaces for ODBC, JDBC, C, C++, PHP, Perl, TCL, ECPG, > > > Python, and Ruby > > > > I went looking for the native PHP interface to firebird and came up > blank... can you post a link? > > Robert Treat > -- > Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >
Paul Ganainm schrieb: >>ANSI SQL compliant > > > X FB does not support inline views/derived tables, e.g.: SELECT count(*) FROM (SELECT col1, col2 FROM table) Thomas
> >
> > What, exactly, is a partial index? A functional index is an index on
> > something like ((ColumnX*2)/14)? I think the functional one (is that
> > also an expression index?) is on the way.
>
> A partial index is a index on a subset of a table. The case I can think of
> is a list of transactions, some of which are yet to be billed. They have a
> BillID field which is NULL. since this is the recent set it is queried quite
> often, so you can build an index like:
>
Are NULLs even indexed?
Jeff Davis
spam_eater@gmx.net says... > Paul Ganainm schrieb: > FB does not support inline views/derived tables, e.g.: > SELECT count(*) FROM (SELECT col1, col2 FROM table) If you have IB/FB, there is a sample db that comes with it, Employee. There is a view in that db called phone_list. I did select count(*) from phone_list and it worked. Paul... > Thomas -- plinehan x__AT__x yahoo x__DOT__x com C++ Builder 5 SP1, Interbase 6.0.1.6 IBX 5.04 W2K Pro Please do not top-post.
Paul Ganainm schrieb: >>FB does not support inline views/derived tables, e.g.: > >>SELECT count(*) FROM (SELECT col1, col2 FROM table) > > > If you have IB/FB, there is a sample db that comes with it, Employee. > There is a view in that db called phone_list. > > I did select count(*) from phone_list and it worked. > Sure you can do a select from a view, but try the above statement where the view definition is "embedded" diretly into the SELECT. This is not supported by FB 1.5 - though it will be with 2.0 I admit that it is not a very important feature, but I have come to like it, and sometimes it comes in handy :-) Regards Thomas
On Sat, Dec 20, 2003 at 04:14:51PM -0800, Jeff Davis wrote: > > > > > > What, exactly, is a partial index? A functional index is an index on > > > something like ((ColumnX*2)/14)? I think the functional one (is that > > > also an expression index?) is on the way. > > > > A partial index is a index on a subset of a table. The case I can think of > > is a list of transactions, some of which are yet to be billed. They have a > > BillID field which is NULL. since this is the recent set it is queried quite > > often, so you can build an index like: > > > > Are NULLs even indexed? No, but with a partial index you can acheive the same effect. -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > (... have gone from d-i being barely usable even by its developers > anywhere, to being about 20% done. Sweet. And the last 80% usually takes > 20% of the time, too, right?) -- Anthony Towns, debian-devel-announce
Attachment
Martijn van Oosterhout <kleptog@svana.org> writes:
> On Sat, Dec 20, 2003 at 04:14:51PM -0800, Jeff Davis wrote:
>> Are NULLs even indexed?
> No, but with a partial index you can acheive the same effect.
Actually, btree indexes *do* store nulls. This is not really relevant
to the topic at hand, though, since the proposal was to do something
like
CREATE INDEX ind ON tab (foo) WHERE bar IS NULL;
which does not imply anything about storing any actually-null entries
in the index.
Any multicolumn index type must be prepared to store nulls, at least in
columns after the first one (GiST exploits that fine print, btree
doesn't care). Otherwise you couldn't use an index on (a,b) to search
for only a --- if you did, a query like "WHERE a = 42" would effectively
act like "WHERE a = 42 AND b IS NOT NULL", which is wrong.
In principle we could use btree indexes to implement WHERE x IS (NOT)
NULL searches, but the operator-based API for index scans has a problem
representing such searches because IS NULL/IS NOT NULL are not treated
as operators by the parser. Someday someone will get annoyed enough to
fix that.
regards, tom lane
spam_eater@gmx.net says... > >>SELECT count(*) FROM (SELECT col1, col2 FROM table) > > I did select count(*) from phone_list and it worked. > Sure you can do a select from a view, but try the above statement where the > view definition is "embedded" diretly into the SELECT. This is not > supported by FB 1.5 - though it will be with 2.0 OK, so that's what you call an "inline view" is it? What then is a derived table, or is a derived table just a synonym for inline view? > I admit that it is not a very important feature, but I have come to like > it, and sometimes it comes in handy :-) What's wrong with SELECT COUNT(col1) FROM table? Paul... > Thomas -- plinehan x__AT__x yahoo x__DOT__x com C++ Builder 5 SP1, Interbase 6.0.1.6 IBX 5.04 W2K Pro Please do not top-post.
Paul Ganainm schrieb: >>>>SELECT count(*) FROM (SELECT col1, col2 FROM table) > > OK, so that's what you call an "inline view" is it? Yep :-) > What then is a derived table, or is a derived table just a synonym for > inline view? I'm not sure what the "official" name for this is. I have heard both. So from my point of view a derived table and an inline view are the same. > What's wrong with SELECT COUNT(col1) FROM table? > Nothing. But my statement was just an example to show the syntax. But sometimes when things get more complicated it *is* very handy, and I have used it now and then, and wouldn't want to miss it :-) Thomas
spam_eater@gmx.net says... > > What then is a derived table, or is a derived table just a synonym for > > inline view? > I'm not sure what the "official" name for this is. I have heard both. So > from my point of view a derived table and an inline view are the same. OK - I'm fine with the idea that there can be more than one name for the same thing, and that different geographies and/or cultures can mean that people use synonyms. I've recently started looking at Oracle as well - boy oh boy, you'd want to know your FTLA's with that system! > > What's wrong with SELECT COUNT(col1) FROM table? > Nothing. But my statement was just an example to show the syntax. > But sometimes when things get more complicated it *is* very handy, and I > have used it now and then, and wouldn't want to miss it :-) OK, fine. Can you show me an example of where your construct (inline view and/or derived table) behaves differently from and is better than the syntax that I used? Paul... > Thomas -- plinehan x__AT__x yahoo x__DOT__x com C++ Builder 5 SP1, Interbase 6.0.1.6 IBX 5.04 W2K Pro Please do not top-post.
Hi all; Having had some experience with both IB/FB and PgSQL, here is my quick comparison. Disclaimer: I only have basic experience with IB/FB as of 1.0. If these features are now supported, I will happily admit I am wrong ;-) 1: Varchar() is not supported. I.e. all varchar types must have a maximum length defined. This can make certain types of applications support arbitrary limits on input data. 2: I found stored procedures much harder to work with because the end of line delimiter had to be changed. 3: I found that the case sensitivity of the database server to be unintuitive. Basically, I found arbitrary limits difficult to work with for applications requiring lists of text of arbitrary, user (rather than admin) defined lengths. F. ex. a CRM app which needs to store text ranging from a hundred to several thousand characters. I also found the learning curve steeper than that of PgSQL. Other than that, it seems pretty good. I also like the external table features, though you could do something similar with Pl/PerlU.... Best Wishes, Chris Travers
On Sun, 2003-12-21 at 18:49, Paul Ganainm wrote:
> spam_eater@gmx.net says...
> > > What then is a derived table, or is a derived table just a synonym for
> > > inline view?
>
> > I'm not sure what the "official" name for this is. I have heard both. So
> > from my point of view a derived table and an inline view are the same.
>
> OK - I'm fine with the idea that there can be more than one name for the
> same thing, and that different geographies and/or cultures can mean that
> people use synonyms.
>
> I've recently started looking at Oracle as well - boy oh boy, you'd want
> to know your FTLA's with that system!
>
> > > What's wrong with SELECT COUNT(col1) FROM table?
>
> > Nothing. But my statement was just an example to show the syntax.
>
> > But sometimes when things get more complicated it *is* very handy, and I
> > have used it now and then, and wouldn't want to miss it :-)
>
> OK, fine. Can you show me an example of where your construct (inline
> view and/or derived table) behaves differently from and is better than
> the syntax that I used?
>
> Paul...
select distinct * FROM (
select ss.* from (
(
SELECT s.site_id, s.name, r.name AS region_name, e.active,
coalesce(max,0) AS status, match_type
FROM prod1 a, host h,
entity_profile_1 e, site s, region r,
(
SELECT 'Site name' AS match_type, site_id AS search_id FROM site
WHERE name ilike '%abc%'
UNION
SELECT 'prod1 license' AS match_type, findsite(prod1_id) AS
search_id FROM prod1 WHERE prod1_license ilike 'abc%'
UNION
SELECT 'prod2 license' AS match_type, findsite(prod2_id) AS
search_id FROM prod2 WHERE prod2_license ilike 'abc%'
UNION
SELECT 'prod3 license' AS match_type, findsite(prod3_id) AS
search_id FROM prod3 WHERE prod3_license ilike '%abc%'
) AS sr
LEFT JOIN
(
SELECT findsite(entity_id) AS error_id, max(status_id)
FROM current ce
GROUP BY findsite(entity_id)
) AS errors ON (error_id = search_id)
WHERE
search_id = s.site_id AND s.region_id = r.region_id AND search_id =
e.entity_id
AND a.host_id = h.host_id AND h.site_id = s.site_id
)
) AS ss ORDER BY UPPER(ss.name) ASC, region_name asc
) as matches;
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
How about fast loader api ? Pg has COPY. Last time I examined FB (the release prior to 1.5 - 1.02 I think), it did not support any such feature. To be fair - FB prepared statements were considerably faster than the equivalent Pg ones, but faced with loading (say 10,000,000 rows or so) Pg using COPY was more that twice as fast as FB using prepared statements.... best wishes for the flames Mark P.s : I posted the results of this evaluation to the FB list at the time ... without much response...
Having been fairly tough on FB in the previous mail - I should point out that of all the open source databases I have played with, only FB gets performance equivalent to Pg on my Data warehouse style evaluation - so FB ranks as pretty good product as far as I am concerned. see http://sourceforge.net/projects/benchw regards Mark