473,883 Members | 1,687 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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.
<Controversia l 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 YourEmailAddres sHere" to ma*******@postg resql.org)

Nov 12 '05
29 3540
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(r ather
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

Nov 12 '05 #11
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 "sufficient ly
simple", and it turns out that the spec's constraints on "sufficient ly
simple" eliminate all the doubtful cases.

regards, tom lane

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

Nov 12 '05 #12

kl*****@svana.o rg 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

Nov 12 '05 #13
On Thursday 18 December 2003 03:53, Paul Ganainm wrote:
kl*****@svana.o rg 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

Nov 12 '05 #14
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*******@postg resql.org

Nov 12 '05 #15
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.s ourceforge.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*******@postg resql.org

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

Nov 12 '05 #16
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*******@postg resql.org so that your
message can get through to the mailing list cleanly

Nov 12 '05 #17
> >
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

Nov 12 '05 #18

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

Nov 12 '05 #19
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

Nov 12 '05 #20

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
3666
by: Lothar Scholz | last post by:
Because PHP5 does not include the mysql extension any more is there a chance that we will see more Providers offering webspace with Firebird or Postgres Databases ? What is your opinion ? I must say that i like it to see mysql replaced by a real database (stored procedures etc.)
4
1814
by: Bill L | last post by:
I have some pages that display fine (or at least as I intend them) on most everything except Firebird (including 0.6.1). Is anyone aware of CSS issues specific to Firebird? Example page: http://www.billsaysthis.com/content/misc/view_sayings.phtml Seems to not be respecting the width setting in a style. TIA.
5
4608
by: Maurice LING | last post by:
Hi, I've been using FB1.5 and access the database using Kinterbasdb + Python. My connection is established using kinterbasdb.connect() method and the parameters host, dns, database, user, password are all defaulted to 'None'. On my own machine running Mac OSX 10.3, I can connect using the following: host = 'localhost' database = '<my path to FB database>'
0
1644
by: Raigo | last post by:
I have Firebird SQL 1.0.3 and Visual Web Developer 2005 Express Beta 2 (latest at the moment). Also, I have installed Firebird SQL .NET driver Version 2.0 Alpha 3 for Microsoft .NET 2.0 http://prdownloads.sourceforge.net/firebird/FirebirdClient_2.0-Alpha3.exe my problem is that from Visual Web Developer I cannot see this driver. When
4
5514
by: saif | last post by:
Hi all, I'm working with WinXP and IIS 5.1 and I use Mozilla firebird browser. When I check localhost I am promted for password but that's not the problem. Real problem is I am given this error (after posting me to http://localhost/localstart.asp): ----- Error ------ HTTP 500.100 - Internal Server Error - ASP error Internet Information Services
0
3828
by: Ann Harrison | last post by:
Tom, Actually, I've never had a corrupted firebird database. There were two bugs in an older version of InterBase that produced income for me. The first was a failure to realize that a file had just gone over 4Gb and therefore the word holding the offset had rolled over and the system had begun writing at the beginning of the file. Nasty. The second was a case that allowed a server to open a single database twice, thinking it was two...
10
4486
by: Jennifer Carr | last post by:
I have an Access 2003 application that is split into two .mdb files for the interfaces and data. This app is distributed on a CD to be used on computers that have no internet access. When someone needs an updated dataset the backend .mdb is refreshed from a load of Oracle tables and sent on CD so that the user only needs to replace a single file. The front end links to the tables in the back end. The problem is that the amount of data...
12
3011
by: timothy.williams | last post by:
Hi. I have a Python program that parses a file and inserts records into a database with MySQLdb. I recently upgraded to MySQL 5.0.8, and now my parser runs *really* slow. Writing out to CSV files is fine, but when I try to insert the same records in a MySQL5 database, it slows to a crawl. Using MySQL 4.1 seems fine. The data seems to be inserted correctly, it's just really slow. Has anyone else noticed a similar problem using MySQL5...
1
4475
by: juancho8529 | last post by:
I ned know how set the number of rows obtained from a interbase 2.0 query, hi have tried with instructions has fetch rows, limit, top... but i didn't have found a correct result, Please if somebody can help me! (for example... i need get the rows to 5 to 10 from the sql sentence select field1,field2 from my table)
0
9933
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9781
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11121
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10734
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
7960
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5793
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5982
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4606
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4210
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.