For all intents and purposes, PostgreSQL has less of a need for CREATE PROCEDURE than other databases aside from looking more like other databases.
For example in SQL Server -> 2005 - although you can write functions that return tables and so forth, you have to resort to writing
CLR functions marked as unsafe to actually update data in a stored function. This gets pretty messy and has its own limitations so you have no choice but to use a stored procedures, which can not be called from within an SQL query.
In MySQL 5.1 the abilities of functions are even more limiting - they can't even return a dataset.
In PostgreSQL, you can
write a function marked as VOLATILE that updates data and that can do all sorts of wacky things that are useful
but considered by some to be perverse such as the following:
SELECT rule_id, rule_name, fnprocess_rule(rule_id) As process_result
WHERE brules.category = 'Pay Employees'
ORDER BY brules.rule_order
Another thing stored procedures can usually do that functions can not is to return multiple result sets. PostgreSQL can simulate such behavior
by creating a function that returns a set of refcursors. See this .NET example Getting full results in a DataSet object: Using refcursors way down the page,
that demonstrates creating a postgresql function that returns a set of refcursors to return multiple result sets using the Npgsql driver.
Prior to PostgreSQL 8.1, people could yell and scream, but PostgreSQL doesn't support Output Parameters. As weird as it is for a function to support
such a thing, PostgreSQL 8.1+ do support output parameters and ODBC drivers and such can even use the standard CALL interface to grab those values.
At a glance it appears that PostgreSQL functions do all that stored procedures do plus more. So the question is, is there any reason
for PostgreSQL to support bonafide stored procedures aside from the obvious To be more compatible with other databases and not have to answer the philosophical question, But you really don't support stored procedures?.
There must be some efficiency benefits to declaring something as a store procedure and having it called in that way. Not quite sure if anyone has done benchmarks on that.
So for the time being PostgreSQL functions have the uncanny role of having a beak like a duck and the flexibility of a beaver, but having the makeup of a Platypus.
In later issues we'll be covering other PostgreSQL contribs. We would like to start our first issue with introducing, PostGIS, one of our favorite PostgreSQL contribs. PostGIS spatially enables PostgreSQL in an OpenGeospatial Consortium (OGC) compliant way.
PostGIS was one reason we started using PostgreSQL way back in 2001 when Refractions
released the first version of PostGIS with the objective of providing affordable basic OGC Compliant spatial functionality to rival the very expensive commercial offerings.
There is perhaps nothing more powerful in the geospatial world than the succinct expressiveness of SQL married with spatial operators and functions.
Together they allow you to manipulate and analyze space with a single sentence. For details on using Postgis and why you would want to, check out the following links
Just as PostgreSQL has grown over the years, so too has PostGIS and the whole FOSS4G ecosystem. PostGIS has benefited from both the FOSS4G and PostgreSQL growths. On the PostgreSQL, improvements such as improved GIST indexing, bitmap indexes etc and on the FOSS4G side dependency projects such as
Geos and Proj4, and JTS, as well as more tools and applications being built on top of it.
In terms of spatial databases, PostGIS is the most capable open source spatial database extender.
While MySQL does have some spatial capabilities, its spatial capabilities are extremely limited particularly in the selectivity of the spatial relational functions
which are all MBR only, ability to create spatial indexes on non-MyISAM stores, and lack a lot of the OGC compliant functions such as Intersection, Buffering even in its 5.1 product. For details on this check the
MySQL 5.1 docs - Spatial Extensions.
When compared with commercial spatial databases, PostGIS has most of the core functions you will see in the commercial databases such as Oracle Spatial, DB2 Spatial Blade, Informix Spatial Blade, has comparable speed, fewer deployment headaches, but lacks some of the advanced add-ons you will find, such as Oracle Spatial network topology model, Raster Support and Geodetic support. Often times the advanced spatial features are add-ons on top of the standard price of the database software.
Some will argue that for example Oracle provides Locator free of charge in their standard and XE versions,
Oracle Locator has a limited set of spatial functions.
Oracle's Locator is missing most of the core spatial analysis and geometric manipulation functions like centroid, buffering, intersection and spatial aggregate functions; granted it does sport geodetic functionality that PostGIS is currently lacking. To use those non-locator features requires Oracle Spatial and Oracle Enterprise which would cost upwards of $60,000 per processor. Many have heard of SQL Server 2008 coming out and the new spatial features it will sport which will be available in both the express and the full version. One feature that SQL Server 2008 will have that PostGIS currently lacks is Geodetic support (the round world model so to speak). Aside from that SQL Server 2008 has a glarying omission from a current GIS perspective - and that is the ability to transform from one spatial reference system to another directly in the database and is Windows bound so not an option for anyone who needs or is thinking of cross-platform or in a Unix environment.
SQL Server 2008 will probably come closest to PostGIS in terms of price / functionality.
The express versions of the commercial offerings have many limitations in terms of size of database and
usually limited to one processor use. For any reasonably sized deployment in terms of database size, processor utilization, replication, or ISP/Service Provider/Integrator this is not adequate and for any reasonably large deployment that is not receiving manna from heaven, some of the commercial offerings like Oracle Spatial, are not cost-sensible.
There is a rise in the use of mapping and geospatial analysis in the world and it is moving out of its GIS comfort zone to mingle more with other IT Infrastructure, General Sciences, and Engineering. Mapping and the whole Geospatial industry is not just a tool for GIS specialists anymore.
A lot of this rise is driven by the rise of mapping mashups - things like Google Maps, Microsoft Virtual Earth, and Open data initiatives that are introducing new avenues of map sharing and spatial awareness.
This new rise is what many refer to as NeoGeography. NeoGeography is still in its infancy; people are just getting over the excitement of seeing dots in their hometown, and are quickly moving into the next level
- where more detailed questions are being asked about those dots and dots are no longer sufficient. We want to draw trails such as trail of hurricane destruction, avian bird flu, track our movement with GPS, draw boundaries and measure the densities of these based on some socio-ecological factor and we need to store all that user generated or tool generated information,
and have all that transactional goodness, security and ability to query in an easy way that a relational database offers. This is the level where PostGIS and other spatial databases are most useful.
PostgreSQL 8.3 is currently in Beta 4 and promises to offer some whoppingly neat features. First before we go over the new features we are excited about in this upcoming release, we'd like to briefly cover what was added in past releases.
The big 8.0 Highlights
The biggest feature in 8.0 was native support for Windows
Dollar quoting syntax for stored functions which made it a lot easier to write stored functions since instead of having to escape quotes you could use the $something$ body of function $something$ delimiter approach.
Next favorite was - Tablespaces so that people no longer needed to resort to messy symlinks to simulate this needed feature
Save points, and improved buffer management.
Introduced Bitmap indexes which allowed for ability to use multiple indexes on a table simultaneously and less need for compound indexes.
Auto vacuuming. Auto Vacuuming was a huge benefit that all could appreciate. It allowed for automatic cleaning of dead space without human or scheduled intervention.
Also introduced in this release was improved shared locking and two phase commit
change in security - introducing login roles and group roles plus prevention of dropping roles that owned objects.
Constraint exclusion which improved speed of inherited tables thus improving table partitioning strategies.
Improved indexed creation that no longer required blocking concurrent insert, create, delete.
Ability to remove table inheritance from a child table without having to rebuild it.
Aggregates that can take multiple inputs and SQL:2003 statistical functions
Introduction of Fill Factor for tables similar to Microsoft SQL Server's Fill Factor functionality
8.3 upcoming Highlights
8.3 has numerous highlights just as previous versions, but we shall focus on our favorite ones.
Support Security Service Provider Interface (SSPI) for authentication on Windows - which presumably will allow PostgreSQL databases to enjoy the same single signon you get with Microsoft SQL Server 7-2005.
GSSAPI with Kerberos authentication as a new and improved authentication scheme for single signon
One of the features in PostgreSQL designed to enhance index performance is the use of a clustered index. For people coming from MS SQL Server shops, this may look familiar to you and actually serves the same purpose, but is implemented differently and this implementation distinction is very important to understand and be aware of. In PostgreSQL 8.3 the preferred syntax of how you cluster has changed. For details check out 8.3 CLUSTER8.2 CLUSTER8.0 CLUSTER. A lot of what I'm going to say is somewhat of a regurgitation of the docs, but in slightly different words.
First in short - clustering on an index forces the physical ordering of the data to be the same as the index order of the index chosen. Since you can have only one physical order of a table, you can have only one clustered index per table and should carefully pick which index you will use to cluster on or if you even want to cluster. Unlike Microsoft SQL Server, clustering on an index in PostgreSQL does not maintain that order. You have to reapply the CLUSTER process to maintain the order. Clustering helps by reducing page seeks. Once an index search is done and found, pulling out the data on the same page is vastly faster since once you find the start point all successive data nearby is easy picking.
As a corrollary to the above, it doesn't help too much for non-range queries. E.g. if you have dummy ids for records and you are just doing single record select queries, clustering is fairly useless to you. It is only really useful if you are doing range queries like between date ranges or spatial ranges or queries where the neighboring data to an index match is likely to be pulled. For example if you have an order items table, then clustering on a compound index such as order_id,order_item_id may prove useful since neighboring data is something you likely want to pull for range and summations.
Now lets see how we create a clustered index and then talk about the pros and gotchas
--First we create the index
CREATE INDEX member_name_idx
ALTER TABLE member CLUSTER ON member_name_idx;
Once a clustered index is created to force a recluster, you simply do this
To force a cluster on all tables that have clustered indexes, you do this
What is FillFactor and how does it affect clustering?
Again those coming from Microsoft SQL Server will recognize FILLFACTOR syntax. IBM Informix also has a FILLFACTOR syntax that serves the same purpose as the SQL Server and PostgreSQL ones.
For more details here PostgreSQL docs: Create Index.
FillFactor basically creates page gaps in an index page. So a Fill Factor of 80 means leave 20% of an index page empty for updates and inserts to the index so minimal reshuffering of existing data needs to happen as new records are added or indexed fields are updated in the system. This is incorporated into the index creation statement.
CREATE INDEX member_name_idx
ALTER TABLE member CLUSTER ON member_name_idx;
-- note we do this to update the planner statistics information.
Its important since this information helps the planner at selecting indexes and scan approach.
After an index is created on a table, this information is then used in several scenarios
As stated earlier, as new records are added or indexed fields are updated, indexed values are shuffled into the empty slots and when new pages
need to be created, they are created with the specified amount of space left blank.
During vacuuming, reindexing again the specified amount of space is left blank
During CLUSTERING, the clustering process tries to leave records where they are and uses the empty space to shuffle in the new data.
Why should you care?
First for fairly static tables such as large lookup tables, that rarely change or when they change are bulk changes, there is little point in leaving blank
space in pages. It takes up disk space and causes Postgres to scan thru useless air. In these cases - you basically want to set your FillFactor high to like 99.
Then there are issues of how data is inserted, if you have only one index and new data usually resides at the end of the index and the indexed field are rarely updated, again having a low fill factor is probably not
terribly useful even if the data is updated often. You'll never be using that free space so why have it.
For fairly updated data that changes such that you are randomly adding 10% new data per week or so in middle of page, then a fill factor of say 90 is the general rule of thumb.
Cluster approach benefits and Gotchas
The approach PostgreSQL has taken to cluster means that unlike the SQL Server approach, there is no additional penalty during transactions of having a clustered index.
It is simply used to physically order the data and all new data goes to the end of the table. In the SQL Server approach all non-clustered indexes are keyed by the clustered index, which means any change to a clustered field requires rebuilding of the index records for the other indexes and also any insert or update may require some amount of physical shuffling. There are also other consequences with how the planner uses this information that are too detailed to get into.
The Bad and the Ugly
The bad is that since there is no additional overhead aside from the usual index key creation during table inserts and updates, you need to schedule reclustering to maintain your fine order and the clustering causes a table lock. The annoying locking hopefully will be improved in later versions. Scheduling a cluster can be done with a Cron Job or the more OS agnostic PgAgent approach. In another issue, we'll cover how to use PgAgent for backup and other scheduling maintenance tasks such as this.