Firebird and PostgreSQL at the DB Corral.

[Paul Ganainm]

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.
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Richard Welty]

On Tue, 16 Dec 2003 21:04:10 -0000 Paul Ganainm <pa*************@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 rw****@averillpark.net
Averill Park Networking 518-573-7592
Java, PHP, PostgreSQL, Unix, Linux, IP Network Engineering, Security
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Doug McNaught]

Paul Ganainm <pa*************@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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Paul Thomas]

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 |
+------------------------------+---------------------------------------------+

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Shridhar Daithankar]

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...res/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
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Paul Ganainm]

rw****@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/advan...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.
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Paul Ganainm]

pa**@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.
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Martijn van Oosterhout]

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 <kl*****@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

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQE/4D+0Y5Twig3Ge+YRAopUAJ40jFdh4RZACC+6ZWqdFyu8/VQrFACgpbKY
XXjPfOD9czSRXQuRnjO7b10=
=Yc95
-----END PGP SIGNATURE-----

[Paul Ganainm]

sh*****************@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...res/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.
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Bernd Helmle]

Paul Ganainm wrote:

rw****@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/advan...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...

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Shridhar Daithankar]

On Wednesday 17 December 2003 17:07, Paul Ganainm wrote:

sh*****************@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...eoperator.html
http://developer.postgresql.org/docs...reatetype.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...elanguage.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
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Tom Lane]

Doug McNaught <do**@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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Paul Ganainm]

kl*****@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.
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Richard Huxton]

On Thursday 18 December 2003 03:53, Paul Ganainm wrote:

kl*****@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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Robert Treat]

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 ma*******@postgresql.org

[Rick Gigger]

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" <xz****@users.sourceforge.net>
To: "Paul Ganainm" <pa*************@hotmail.com>
Cc: <pg***********@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 ma*******@postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Thomas Kellerer]

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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Jeff Davis]

> >

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


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Paul Ganainm]

sp********@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.
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Thomas Kellerer]

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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Martijn van Oosterhout]

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 thinkof
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 <kl*****@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

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQE/5Z8BY5Twig3Ge+YRAup/AJ99Pny3c0RGY3QqNLYwgH7HOHMHqwCfR+Pe
3eo2whcyb7qvNne8iRVAVoI=
=P8Ov
-----END PGP SIGNATURE-----

[Tom Lane]

Martijn van Oosterhout <kl*****@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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Paul Ganainm]

sp********@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.
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Thomas Kellerer]

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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Paul Ganainm]

sp********@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.
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Chris Travers]

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
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Chris Travers]

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
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Robert Treat]

On Sun, 2003-12-21 at 18:49, Paul Ganainm wrote:

sp********@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
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Mark Kirkwood]

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...


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Mark Kirkwood]

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
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)