PostgreSQL - Firebird comparison

Google led me to a comparison sheet about PostgreSQL and Firebird by AMSoftwareDesign, as it looks a bit outdated I decided to add some infos about the latest Firebird release, see it in action:

	PostgreSQL vs Firebird feature comparison
Feature	PostgreSQL 8.2.x	Firebird 2.0.x	Firebird 2.5 Alpha
MVCC	Yes	Yes	Yes
Row level Locking Available	Yes	Yes	Yes
Max Database Size	Unlimited*	Unlimited*	Unlimited*
Max table Size	32 TB	~32 TB	~32 TB
Max Row Size	1.6 TB	64 KB	65 KB
Max Rows per Table	Unlimited*	> 16 Billion	> 16 Billion
Max Columns Per Table	50 - 1600 depending on column types	Depends on data types used.	Depends on data types used.
Max Indexes Per Table	Unlimited*	65,535	66,535
Max SQL statement size	Unlimited*	64kb	64kb
Multi Threaded Architecture Available?	No (see "Features we do NOT want" in the TODO list)	Yes (super server) and No (classic server)	Yes (super server), architectures will be unified with full SMP support in 3.0
Ability to re-order table columns without re-creating	No	Yes	Yes
Stores Transaction Information in same file as data	No	Yes	Yes
Auto Increment Columns	Yes (serial type that uses sequences)	Yes (must use a generator and a trigger)	Yes (must use a generator and a trigger)
True Boolean column type	Yes	No	No
Table Inheritance	Yes	No	No
Domains	Yes	Yes	Yes
Table Partitioning	Yes (basic)	No	No
Updateable Views	No (workaround available via rules system)	Yes	Yes
Event/Notification System	Yes	Yes	Yes
Temporary Tables	Yes	No	Yes
Rich Built in Functions	Yes	Yes	Yes
Multi Lang Stored Procedures	Yes (PLPGSQL,PLPerl,PlJava etc)	No	No (support for Java stored procedures is scheduled for 3.0)
Compiled External Function (UDF) Support	Yes	Yes	Yes
Exception handling in stored procedures	Yes	Yes	Yes
2 Phased Commit	Yes	Yes	Yes
Native SSL support	Yes	No	No
Multiple auth methods (i.e. LDAP)	Yes	No	Yes (database auth or integrated windows auth)
Compound Indexes	Yes	Yes	Yes
Unique Indexes	Yes	Yes	Yes
Partial Indexes	Yes	Yes	Yes
Functional Indexes	Yes	Yes	Yes
Multiple Index Storage Types	Yes (btree,hash etc)	No	No
Point in Time Recovery	Yes	No	No
Schema Support	Yes	No	No
Conforms to ANSI-SQL 92/99	Yes	Yes	Yes
Limit/Offset support	Yes	Yes	Yes
Create user defined types	Yes	No	No
Create user defined operators	Yes	No	No
Create user defined Aggregates	Yes	No	No
Log Shipping (for Point In Time Recovery and Log Shipping)	Partial	No	No
Write ahead logging	Yes	No	No
Tablespaces	Yes	No	No
Save Points	Yes	Yes	Yes
Open Source Async Replication	Yes (Slony )	No (Commercial solutions available. Database shadowing is also present.)	No (Commercial solutions available. Database shadowing is also present.)
Online/Hot Backups	Yes	Yes	Yes
File System based backups possible	Yes (Postmaster must be stopped)	Yes	Yes
Require backup/restore to compact	No	Yes	Yes
Fully ACID Compliant	Yes	Yes	Yes
Native Win32 Port	Yes	Yes	Yes
Text/Memo field type	Yes	Yes	Yes
BLOB support	Yes (limited to the max field size of 1 GB)	Yes (Can be up to 32GB)	Yes (Can be up to 32GB)
UTF8 support	Yes	Yes	Yes
Define charactersets/collations per database (default)	Partial (PostgreSQL can also define a characterset for the entire database cluster during the initdb process, it is not recommend to run databases in different encodings than the encoding chosen at initdb time)	Yes	Yes
Define charactersets and collations on a per column level	No	Yes	Yes
Foreign Keys	Yes	Yes	Yes
Check Constraints	Yes	Yes	Yes
Unique Constraints	Yes	Yes	Yes
Not Null Constraints	Yes	Yes	Yes
Multiple Transaction Isolation levels	Yes	Yes	Yes
Fully relational System Catalogs	Yes	Yes	Yes
Information Schema	Yes	No (no schema support) but equivalent system tables	No (no schema support) but equivalent system tables
Native GIS support via GIST or other native means	Yes (PostGIS )	No	No
Open Source Full Text Search	Yes	No	No
Use POSIX Regular Expressions in queries	Yes	No	Yes, through standard predicate SIMILAR TO
Database Monitoring	Yes	No	Yes, through system tables and triggers
Ability to query databases on other servers local or remote.	Yes (Dblink )	No	Yes (through EXECUTE STATEMENT)
Ability to query other databases	Yes (DBI-Link ,DBLink-TDS )	No	No
Read Only Databases	No	Yes	Yes
Regular Version Updates	Yes	Yes	Yes
* Unlimited but still restricted by system resources.

Has them all

A question that pops up frequently on Devshed forums is "How can I get all products that are available in Red and Green colors?" or "How can I find out which customers bought this book and that CD?", solution is simple and I'll provide an example here, it can be made more complicate at your option, but it all boils down to a where and an having condition.
Say we have a table that lists all products and the colors in which those products are available:

CREATE TABLE PRODUCT_COLORS(
  PRODUCT_CODE CHAR(5) NOT NULL,
  COLOR_CODE CHAR(1) NOT NULL,
  CONSTRAINT PRODUCT_COLORS_PK PRIMARY KEY (PRODUCT_CODE,COLOR_CODE)
); 

Data looks like:

Code:

XXXXX                R
XXXXX                B
YYYYY                Y
YYYYY                G
ZZZZZ                G
ZZZZZ                R   

First column is the product code, second column is the color code.
Say we want to know which products are available in G(reen) and R(ed), the query is simple, we'll list all products which do have the Red or Green option and then filter out all those that don't have both, getting the desired result (in this case product 'ZZZZZ')
See it in action:

SELECT a.PRODUCT_CODE
FROM PRODUCT_COLORS a
WHERE a.COLOR_CODE IN ('R', 'G')
GROUP BY a.PRODUCT_CODE
HAVING COUNT(a.PRODUCT_CODE) = 2 

See where I implemented the two conditions? One in the where clause and the second pass to filter out all products which don't have both in the having clause.
Example is built on Firebird, but should work in MySQL, PostgreSQL or any other mainstream database too.

Another great new feature of Firebird 2.5

Other than CREATE USER something really valuable has been added, the ability to query other databases.
Using a table structure like my previous post what follows is an example of a query running on an external database:

SET TERM ^ ;
CREATE PROCEDURE GET_MASTER_PROD_ALL_EXT
RETURNS (
P_CODE Char(5),
I_ENABLED Char(1),
P_DESCR Varchar(50) )
AS
declare variable qry varchar(5000);
  BEGIN
qry = 'SELECT pm.PRODUCT_CODE, pm.IS_ENABLED, pm.PRODUCT_DESCR
    FROM product_master pm';
EXECUTE STATEMENT qry  ON EXTERNAL DATA SOURCE 'localhost:c:\pippo2.fdb'
AS USER 'sysdba' PASSWORD 'masterkey'
INTO :p_code, :i_enabled, :p_descr;
    SUSPEND;
  END^
SET TERM ; ^

The trick is the ON EXTERNAL DATA SOURCE clause, which allows you to specify a target database ("localhost:c:\pippo2.fdb" in my case, generally that's time to start using aliases) and the user/password to be used
As you can see , no "dblinks" yet, but of course you can embed it into a view and join it (the stored proc or the view) with other local or remote objects.
The querystring can also be built dynamically passing parameters.
Isn't it great?

Controlling user access to data

A straight and simple question on Devshed prompted me to post this example about using updateable views to limit the ability of users to read and manipulate data both horizontally and vertically.
Before you ask, with "horizontally" I mean restricting access to a subset of rows in a table, with "vertically" I mean restricting access to a subset of columns in a table.
See this scenario, a table holding product data, named product_master and two users, one with full access and another one which we want to limit.
Specifically we want to allow this second user to see only products flagged as "enabled" (horizontal limitation, only a subset of rows) and, for those products, we want to be shure that he can only change (update) the product description.
How can we achieve this?
With a mix of grant management and updateable views, let's see this setup in action:
Structure for table product_master is:

CREATE TABLE PRODUCT_MASTER(
  PRODUCT_CODE CHAR(5) NOT NULL,
  IS_ENABLED CHAR(1),
  PRODUCT_DESCR Varchar(50),
  CONSTRAINT PRODUCT_MASTER_PK PRIMARY KEY (PRODUCT_CODE)
);
 
GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON PRODUCT_MASTER TO SYSDBA WITH GRANT OPTION; 

As you can see only sysdba has access to this table, nothing is granted to user pippo or public role.
We said that pippo should be able to update the product description, so I'll give him appropriate grants for this:

GRANT UPDATE ON PRODUCT_MASTER TO PIPPO; 

Ok, right now pippo can update the product table, then comes the hard part, allowing him to see and update only enabled products, those rows of the product master table that have the enabled field set to 'Y'.

This will be accomplished with an updateable view and proper grants, let's see it in action:

CREATE VIEW ENABLED_PRODUCTS (PRODUCT_CODE, PRODUCT_DESCR)AS  
/* write select statement here */ 
SELECT pm.PRODUCT_CODE, pm.PRODUCT_DESCR 
FROM product_master pm 
WHERE IS_ENABLED = 'Y' 
WITH CHECK OPTION; 

The two important things here are the where condition in the select and the "with check option" clause.
The related grants to complete the magic are:

GRANT SELECT, UPDATE ON ENABLED_PRODUCTS TO PIPPO;
 
GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON ENABLED_PRODUCTS TO SYSDBA WITH GRANT OPTION; 

Hope this helps

Don't silently turn your outer joins into inner joins ...

Surprised by outer joins not returning the expected results? Looks like they behave like an inner join?

You are probably messing with the join and the where clause.

That's also why it's important to use the full join notation for every join instead of the where based one (often used for inner joins) as it allows for a better separation of the join and the where clause.

Remember that the where clause is applied after the join clause, so if you want to restrict results from the table on the "right" of the left outer join but still have the NULLs you'll have to put the restriction in the join condition, putting it in the where clause will turn your outer join into an inner join.

Not convinced? Let's see an example:

I'll query two tables of the Firebird sample database, EMPLOYEE and EMPLOYEE_PROJECT, I'm looking for a list of employees assigned to a specific project and those not assigned (probably I'll pick a few of them and assign those too), let's see the first query

SELECT 
e.EMP_NO,
e.FULL_NAME,
ep.PROJ_ID 
FROM 
 EMPLOYEE e LEFT OUTER JOIN EMPLOYEE_PROJECT ep
  ON e.EMP_NO = ep.EMP_NO; 

and it's output is

2 Nelson, Robert [null]
4 Young, Bruce VBASE
4 Young, Bruce MAPDB
5 Lambert, Kim [null]
8 Johnson, Leslie VBASE
8 Johnson, Leslie GUIDE
8 Johnson, Leslie MKTPR
9 Forest, Phil [null]
11 Weston, K. J. [null]
12 Lee, Terri MKTPR
14 Hall, Stewart MKTPR

So far so good, now I want only those on the VBASE project and the unassigned ones, let's do what instinct suggests, add a where clause, the outer join will take care of the rest (...)

SELECT 
 e.EMP_NO,
 e.FULL_NAME,
 ep.PROJ_ID 
FROM EMPLOYEE e LEFT OUTER JOIN EMPLOYEE_PROJECT ep
 ON e.EMP_NO = ep.EMP_NO
WHERE 
 ep.PROJ_ID = 'VBASE' 

Result is:

4 Young, Bruce VBASE
8 Johnson, Leslie VBASE
15 Young, Katherine VBASE
44 Phong, Leslie VBASE
45 Ramanathan, Ashok VBASE
71 Burbank, Jennifer M. VBASE
83 Bishop, Dana VBASE
136 Johnson, Scott VBASE
138 Green, T.J. VBASE
145 Guckenheimer, Mark VBASE

Eeek, where are all the unassigned gone???
That's because the WHERE is applied after the JOIN and so it throws away all those NULLs, infact it's bound to pick only PROJ_ID = 'VBASE'
Now the right one, we want it executed in the join

SELECT 
 e.EMP_NO,
 e.FULL_NAME,
 ep.PROJ_ID
FROM EMPLOYEE e LEFT OUTER JOIN EMPLOYEE_PROJECT ep
 ON e.EMP_NO = ep.EMP_NO
  AND 
   ep.PROJ_ID = 'VBASE'  

And the result is just what we are looking for:

2 Nelson, Robert [null]
4 Young, Bruce VBASE
5 Lambert, Kim [null]
8 Johnson, Leslie VBASE
9 Forest, Phil [null]
11 Weston, K. J. [null]
12 Lee, Terri [null]
14 Hall, Stewart [null]
15 Young, Katherine VBASE
20 Papadopoulos, Chris [null]
24 Fisher, Pete [null]
28 Bennet, Ann [null]
29 De Souza, Roger [null]

Firefox running for the Guinness record

Let's help Firefox to reach the goal and set the new world record for downloads on June 17, 2008.

BTW: I've tried it as a portable app ... WOW, much better than Firefox 2, fast and doesn't slow down with many open tabs.

The neglected driver ...

Looks like the Firebird team is going to put some effort on the ODBC driver, which has been lying there abandoned for a few years (last activity report is of 2006) despite it's usefulness in windows desktop apps (at least for me ;))

Data load speed test

I've run some data load tests with various databases using DBMonster, so connecting to databases through JDBC on a WindowsXP personal computer.
Here are the results, in both cases I loaded 100 rows in the parent table and 1000 in the child table, with foreign keys enabled.


Firebird 2.1 with Jaybird 2.1.3 and DBMonster 1.0.3 (And Java .6)

Table structure is:

CREATE TABLE GUYS(
GUY_ID Integer NOT NULL,
GUY_NAME Varchar(45) NOT NULL,
CONSTRAINT PK_GUYS PRIMARY KEY (GUY_ID)
);
GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
ON GUYS TO SYSDBA WITH GRANT OPTION;

CREATE TABLE BADS_ATTRIBUTES(
ATTRIBUTE_ID Integer NOT NULL,
GUY_ID Integer NOT NULL,
ATTRIBUTE_NAME Varchar(45) NOT NULL,
CONSTRAINT PK_BADS_ATTRIBUTES PRIMARY KEY (ATTRIBUTE_ID,GUY_ID)
);
ALTER TABLE BADS_ATTRIBUTES ADD CONSTRAINT FK_BADS_ATTRIBUTES_1
FOREIGN KEY (GUY_ID) REFERENCES GUYS (GUY_ID) ON UPDATE CASCADE ON DELETE CASCADE;
GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
ON BADS_ATTRIBUTES TO SYSDBA WITH GRANT OPTION;


D:\dbmonster-core-1.0.3\bin>dbmonster --grab -t guys bads_attributes -o d:/fireb
ird_2_1_schema.xml

D:\dbmonster-core-1.0.3\bin>rem Batch file to run dbmonster under Windows

D:\dbmonster-core-1.0.3\bin>rem Contributed by Peter De Bruycker
2008-05-23 22:43:03,203 INFO SchemaGrabber - Grabbing schema from database. 2 tables to grab.
2008-05-23 22:43:03,265 INFO SchemaGrabber - Grabbing table GUYS. 50% done.
2008-05-23 22:43:03,359 INFO SchemaGrabber - Grabbing table BADS_ATTRIBUTES. 100% done.
2008-05-23 22:43:03,359 INFO SchemaGrabber - Grabbing schema from database comp
lete.

D:\dbmonster-core-1.0.3\bin>dbmonster -s d:/firebird_2_1_schema.xml

D:\dbmonster-core-1.0.3\bin>rem Batch file to run dbmonster under Windows

D:\dbmonster-core-1.0.3\bin>rem Contributed by Peter De Bruycker
2008-05-23 22:49:32,828 INFO DBMonster - Let's feed this hungry database.
2008-05-23 22:49:32,984 INFO DBCPConnectionProvider - Today we are feeding: Fir
ebird 2.1 Beta 2=WI-T2.1.0.16780 Firebird 2.1 Beta 2/tcp (xxx)/P10 W
I-T2.1.0.16780 Firebird 2.1 Beta 2=WI-T2.1.0.16780 Firebird 2.1 Beta 2/tcp (pm-7
071b5d42629)/P10
2008-05-23 22:49:33,187 INFO Schema - Generating schema .
2008-05-23 22:49:33,187 INFO Table - Generating table .
2008-05-23 22:49:33,187 INFO Table - Generating table .
2008-05-23 22:49:33,359 INFO Table - Generation of table finished.
2008-05-23 22:49:39,375 INFO Table - Generation of table finished.
2008-05-23 22:49:39,375 INFO Schema - Generation of schema finished.
2008-05-23 22:49:39,375 INFO DBMonster - Finished in 6 sec. 547 ms.

D:\dbmonster-core-1.0.3\bin>

The same with MySQL 5.1.23 Connector/J 5.1.6 (InnoDB tables of course as I wanted to have FK)

Table structure is:

DROP TABLE IF EXISTS `test`.`guys`;
CREATE TABLE `test`.`guys` (
`guy_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`guy_name` varchar(45) NOT NULL,
PRIMARY KEY (`guy_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `test`.`bads_attributes`;
CREATE TABLE `test`.`bads_attributes` (
`attribute_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`guy_id` int(10) unsigned NOT NULL,
`attribute_name` varchar(45) NOT NULL,
PRIMARY KEY (`attribute_id`,`guy_id`),
KEY `FK_bads_attributes_1` (`guy_id`),
CONSTRAINT `FK_bads_attributes_1` FOREIGN KEY (`guy_id`) REFERENCES `guys` (`guy_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=139 DEFAULT CHARSET=utf8;

D:\dbmonster-core-1.0.3\bin>dbmonster --grab -t guys bads_attributes -o d:/mysql
_5_1_23_schema.xml

D:\dbmonster-core-1.0.3\bin>rem Batch file to run dbmonster under Windows

D:\dbmonster-core-1.0.3\bin>rem Contributed by Peter De Bruycker
2008-05-23 22:59:40,515 INFO SchemaGrabber - Grabbing schema from database. 2 tables to grab.
2008-05-23 22:59:40,671 INFO SchemaGrabber - Grabbing table guys. 50% done.
2008-05-23 22:59:40,703 INFO SchemaGrabber - Grabbing table bads_attributes. 100% done.
2008-05-23 22:59:40,703 INFO SchemaGrabber - Grabbing schema from database complete.

D:\dbmonster-core-1.0.3\bin>dbmonster -s d:/mysql_5_1_23_schema.xml

D:\dbmonster-core-1.0.3\bin>rem Batch file to run dbmonster under Windows

D:\dbmonster-core-1.0.3\bin>rem Contributed by Peter De Bruycker
2008-05-23 23:00:02,531 INFO DBMonster - Let's feed this hungry database.
2008-05-23 23:00:02,953 INFO DBCPConnectionProvider - Today we are feeding: MyS
QL 5.1.23-rc-community
2008-05-23 23:00:03,093 INFO Schema - Generating schema .
2008-05-23 23:00:03,093 INFO Table - Generating table .
2008-05-23 23:00:03,125 INFO Table - Generating table .
2008-05-23 23:00:12,812 INFO Table - Generation of table finished.
2008-05-23 23:00:49,000 INFO Table - Generation of table finished.
2008-05-23 23:00:49,000 INFO Schema - Generation of schema finished.
2008-05-23 23:00:49,000 INFO DBMonster - Finished in 14 sec. 187 ms.

D:\dbmonster-core-1.0.3\bin>

The difference is quite large!
You can compare my results to those obtained by the SQLite team, hope that these numbers make sense to you.

I'll try with PostgreSQL too, just don't know when

Integrated auth in Firebird 2.1

Just a quick sample of integrated windows auth in Firebird 2.1
Logon to my Firebird server as a user with administrative privileges on the machine:

C:\Programmi\Firebird\Firebird_2_1\bin>isql localhost:employee
Database:  localhost:employee
SQL>

So I'm logged. let's check who am I for Firebird:

SQL> select current_user from rdb$database;

USER

===============================================================================

SYSDBA


SQL>

That's right, as an administrator on the machine, I'm SYSDBA on the database, now let's try with an unprivileged user I have on this machine:
I'll open another command line client as user postgres (guess what other database is installed on this machine?)

C:\Programmi\Firebird\Firebird_2_1\bin>runas /user:postgres cmd
Immettere la password per postgres:
Tentativo di avvio di cmd come utente "AB-2346789223445\postgres" ...

C:\Programmi\Firebird\Firebird_2_1\bin>

Now I have another command line window open

C:\Programmi\Firebird\Firebird_2_1\bin>isql localhost:employee
Database:  localhost:employee
SQL> select current_user from rdb$database;

USER

===============================================================================

AB-2346789223445\POSTGRES


SQL>

Ok, I'm in as postgres, let's try a select

SQL> select * from country;

COUNTRY         CURRENCY
=============== ==========
USA             Dollar
England         Pound
Canada          CdnDlr
Switzerland     SFranc
Japan           Yen
Italy           Lira
France          FFranc
Germany         D-Mark
Australia       ADollar
Hong Kong       HKDollar
Netherlands     Guilder
Belgium         BFranc
Austria         Schilling
Fiji            FDollar

SQL>

This works as expected, table country is available for SYSDBA and for PUBLIC, my current user is in PUBLIC by default.
Let's check a different table, on which PUBLIC has no grants

SQL> select * from tbl_stock_warehouse;
Statement failed, SQLCODE = -551
no permission for read/select access to TABLE TBL_STOCK_WAREHOUSE
SQL>

My request has been rejected as expected, great!
If this is not enough for you a lot of improvements in the user management area are currently in the works for Firebird 2.5 (Core 696 and CORE 1660) and in derived databases like RedSoft's one which will hopefully be merged in the official Firebird server.

Loading data from files

Having already blogged about loading data from flat files to MySQL, it's time to post a similar case for PostgreSQL, as the manual seems to lack a real life example ...

First of all the table to be loaded

CREATE TABLE target
(
code character(3) NOT NULL,
"name" character varying(50) NOT NULL,
amount numeric,
CONSTRAINT pk_1 PRIMARY KEY (code)
)
WITH (OIDS=FALSE);

as you can see one of the column names is a reserved word! Bad practice, but it's here to add some spice.

Now the data

code;name;amount
12A;Pippo;12.5
13B;Topolino;45
23D;Pluto;NULL

as you can see it's delimited by ";", has an header row and contains NULLs, which we want to preserve in our target table.
This file is named 2beloaded.csv and it's placed in c:\

After logging in to PostgreSQL and creating the table with the statement provided above it's time to load it

copy
--target table with columns listed
 target(code, "name", amount)
from
--source file
 'C:/2beloaded.csv'
with
--list of options
      csv
      --switches on csv mode
      header
      --ignores first line as an header line
 delimiter ';'
      --sets the delimiter
 null as 'NULL'
      --preserves nulls by telling the database what represents a NULL

Quite nice, isn't it?
Hope this helps.

Two basic indexing tips ...

Here are two basic tips for proper indexing ...

1. Don't mess with datatypes, too often people refer to an attribute defining it as one datatype in a table and as another in different tables, this actually prevents index usage in joins (forget about FKs for this time ;)) See an example here. You could declare a function based index as a workaround, but why don't we all try to make it right?
   
2. Put indexes where the database can really use them, if a table is to be fully scanned anyway, it's indexes are unlikely to be used, unless you can compare those index entries with other indexes on tables that won't be fully scanned. Ordering is another game ;). See here for an example.

Easy, isn't it? But these mistakes are still very common ...