Sunday, June 08. 2008
Printer Friendly
The PostgreSQL 8.4 planned release is March 1, 2009 and is outlined in the PostgreSQL 8.4 Development plan.
It has just passed its May 2008 commit fest milestone and is currently in its July 2008 Commit Fest. Lots of PostgreSQL Planet bloggers have started showcasing some of the new features in store.
We will briefly list our favorite planned and already committed patches.
Continue reading "PostgreSQL 8.4 goodies in store"
Friday, April 18. 2008
Printer Friendly
People coming from SQL Server and MySQL often complain about how you can't query other databases
from within a PostgreSQL database. In Microsoft SQL Server, there is a concept of querying across databases
on the same server
with dbname.dbo.sometable and querying across servers (even of different types e.g. Oracle) by setting up a linked server and doing
something such as servername.dbname.dbo.sometable or using the OPENROWSET(..) or OPENQUERY(...) syntax.
MySQL has a similar feature by using syntax dbname.sometable, but MySQL lacks schemas so there is no way to segregate a
database into nice buckets as you can with SQL Server and PostgreSQL.
In this article we shall provide some examples of using the contrib module - dblink to query local PostgreSQL databases and remote PostgreSQL databases.
DbLink is probably most comparable in structure to SQL Server's OpenRowset functionality.
It lacks the power of SQL Server's Linked Server
approach or OPENQUERY that allows for synchronized joins between linked servers/databases and local tables and updates/inserts on linked servers. This makes it not terribly useful
in cases where you need to join lots of data with local data. It does however come in handy for bulk copy operations from one database/server to another.
Continue reading "Using DbLink to access other PostgreSQL Databases and Servers"
Wednesday, February 20. 2008
Printer Friendly
What is FWTools and OGR GDAL?
FWTools GIS Toolkit is a freely available open source toolkit for Windows and Linux that can do more than GIS tricks.
It is a precompiled bundle of Open Source GIS tools.
The FW comes from the initials of Frank Warmerdam,
the originator of the toolkit and current President of the Open Source Geospatial Foundation (OSGEO).
One key component of the GIS Toolkit is the GDAL/OGR library. Parts of the library have been enhanced by several in the OSGEO community.
GDAL is a basic foundation of countless Open source GIS as well as commercial GIS applications. Here are
Listings of commercial and open source software
that use it and GDAL sponsors.
This is a library which historically has been developed and maintained by Frank Warmerdam, but has started to garner quite a few developers.
GDAL is X/MIT licensed (similar to BSD license), therefore the licensing is very generous for commercial use. The toolkit can be downloaded from
http://fwtools.maptools.org/
Continue reading "GDAL OGR2OGR for Data Loading"
Tuesday, February 12. 2008
Printer Friendly
PostgreSQL 8.3 is out
As many have said - PostgreSQL 8.3 was released on February 4th, 2008 and has numerous enhancements.
Listing of features can be found at PostgreSQL 8.3 release notes,
and has been mentioned ad-nauseum by several Postgres bloggers. Robert Treat has provided a nice round-up of blog entries
that demonstrate various 8.3 enhancements in his PostgreSQL Blog's 8.3 Feature Round-Up.
As a side note, the new EnterpriseDb funded Stack Builder feature for windows provides a nice complement for getting add-ons to PostgreSQL.
Horizon of PostgreSQL
Many PostgreSQL contributors are very proud of the fact that PostgreSQL is an open source
project and therefore can not be bought like MySQL which is an open source product made by a commercial company. I'm not sure general PostgreSQL users really care that much
about this. I suspect that many think
- yah - and Microsoft can be bought, Oracle can be bought, IBM can be bought - who is big enough to buy them and like they will kill off their prize cows
- and if Oracle, IBM or
Microsoft one day were to give away non-crippled versions of Oracle 11G, SQL Server 2008, DB2 to leverage their other holdings (perhaps slightly unrealistic),
what would that mean to PostgreSQL or MySQL?
- Can a community grow without money pumping into it and if it is not growing does it F*** matter that it is an open source project?
Continue reading "PostgreSQL 8.3 is out and the Project Moves On"
Thursday, January 24. 2008
Printer Friendly
In the first part of this series, The Anatomy of PostgreSQL - Part 1, we covered PostgreSQL Server object features. In this part, we shall explore
the database and dissect the parts.
Here we see a snapshot of what a standard PostgreSQL database looks like from a PgAdmin interface.
- Catalogs - these hold meta data information and built-in Postgres objects
- Casts - control how Postgres casts from one datatype to another.
- Languages - these are the languages you can define stored functions, aggregates and triggers in.
- Schemas - logical containers for database objects.
- Aggregates - holder for aggregate functions and custom built aggregate functions.
- Conversions
- Domains
- Functions
- Operators
- Operator Classes
- Operator Families - this is not shown in the diagram and is new in PostgreSQL 8.3
- Sequences - objects for implementing autonumbers
- Tables - self-explanatory but we'll cover the various object properties of a table such as indexes, rules, triggers, and constraints.
- Trigger Functions - these are functions you create that get called from a PostgreSQL table trigger body.
- Types - this is one of the key elements that qualifies PostgreSQL as an object relational database, the fact that one can define new data types.
- Views - virtual tables
Continue reading "The Anatomy of PostgreSQL - Part 2 - Database Objects"
Wednesday, January 16. 2008
Printer Friendly
Sun Purchasing MySQL and PostgreSQL advances
MySQL and Sun?
We just read that Sun is purchasing MySQL for a little under a billion. We are a little shocked and not quite sure what to make of it or how this affects
Sun's investment in PostgreSQL. Further comments on the deal on Jignesh Shah's blog and Josh Berkus blogs. Jignesh and Josh both work at Sun and do PostgreSQL work as well.
Couple of random thoughts
- First, better Sun than Oracle. The thought of Oracle eating up MySQL has always been rather disturbing to us especially since we do a fair amount of MySQL consulting and don't care much for Oracle as a company. I suppose it could still happen.
- Given the fact that Sun is a large contributor to the PostgreSQL project, does this mean PostgreSQL fans can't make fun of
MySQL anymore? Are we like friends now? This could take away some fun and add a little fun at the same time.
- Will this mean MySQL will have no qualms of using PostgreSQL underlying storage engine and what would it be called? - MyPost
Overall we think the move should prove positive for both camps.
PostgreSQL 8.3 really around the corner
8.3 is now on release candidate 8.3 RC1 and as Bruce Momjian noted, it looks like there might be an RC2.
We've been playing around with the 8.3 betas and RCs and really like the integrated Full Text Indexing and XML features. The new features
make it possible to do a quickie REST service-based application. In the next issue of this journal, we hope
to demonstrate creating REST services using 8.3 with server side - (PHP and/or ASP.NET) and front-end Adobe FLEX. We would have liked to demonstrate SilverLight/MoonLight as well, but
we want to wait till Silverlight 2.0 hits release. We'll try to use the Pagila
demo database for the upcoming demo app as Robert Treat has suggested.
Friday, December 28. 2007
Printer Friendly
Question: Does PostgreSQL support stored procedures?
Short Answer: Sort Of as Stored functions.
Longer Answer:
By strict definition it does not. PostgreSQL as of even 8.3 will not support the Create Procedure syntax nor the Call Level calling
mechanism that defines a bonafide stored procedure supporting database (this is not entirely true), since
EnterpriseDB does suport CREATE PROCEDURE to be compatible with Oracle. In PostgreSQL 8.4, this may change.
Check out Pavel Stehule: Stacked Recordset
and Pavel Stehule: First Real Procedures on PostgreSQL
for details.
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
FROM brules
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.
Saturday, December 15. 2007
Printer Friendly
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 2001 only UMN Mapserver was available to display PostGIS spatial data.
As time has passed, UMN Mapserver has grown, and other Mapping software both Commercial and Open Source have come on board that can utilize PostGIS spatial data directly.
On the FOSS side there are many, some being UMN Mapserver, GRASS,
uDig, QGIS, GDAL/OGR, FeatureServer, GeoServer, SharpMap, ZigGIS for ArcGIS integration, and on the commercial side you have CadCorp SIS, Manifold, MapDotNet,
Safe FME Data Interoperability and ETL tools.
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.
Note that in near future versions PostGIS is planning to have geodetic support and does provide basic network topology support via the PgRouting project and there are plans to incorporate network topology as part of PostGIS.
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.
Sunday, November 18. 2007
Printer Friendly
Welcome to PostgreSQL OnLine Journal.
PostgreSQL is an extremely rich object relational database system and has a regal lineage that dates back almost to the beginning of the existence of relational databases.
If we were to look at the family tree of PostgreSQL it would look something like this
(Ingres, System-R)
Postgres
Illustra
Informix
IBM Informix
Postgres95
PostgreSQL
In fact PostgreSQL is a cousin of the databases Sybase and Microsoft SQL Server because the people that started Sybase came from UC Berkeley and worked on the Ingres and/or Postgres projects with Michael Stonebraker. Later on the source code of Sybase SQL Server was later licensed to Microsoft to produce Microsoft SQL Server.
Here is an interesting diagram done by Oleg Bartunov that shows the various relational database pedigrees.
The main focus of this journal is to educate users and potential users about the numerous capabilities
and uses of this powerful database management system.
Over the years we have watched PostgreSQL grow and reach a wider audience. Each day brings newer features, more stability, more environments supported
and more Off-the-Shelf (OTS) applications that support this DBMS.
This Journal is a bit of a literary experiment for us. Technology is very fast-paced and we find that most of the new information we ingest these days comes via fast-paced sources such as Blogs and Magazine/Periodical channels. I like the free form of the blog structure and ability to comment, but I also appreciate the more disciplined, carefully categorized, walk away with a booklet format of the Periodical. Our hope is to combine these two literary instruments into a blogo-periodical that has 2 faces:
- the face of a blog
- the face of an online magazine
.
Since this is what we call a blogo-periodical rather than a plain blog, we shall continually make edits to prior entries that are within the span of our editing issue in progress. So you may find if you are viewing it as a blog, that entries you have already read suddenly change.
After we complete each issue, we hope to provide each journal issue as a downloadable PDF magazine/periodical. Issues in progress or completed will always be available as html ebooks.
In each issue of this journal, we hope to cover the following areas
- Editor's Note - We will provide general comments we would like.
- What's New and Upcoming In PostgreSQL - Will outline items targeted for next release as well as new features in current release.
- PostgreSQL Q&A - Common questions and answers we have curled from newsgroups as well as user submitted questions
- Basics - Articles that pose a basic problem or explain something
- PL Programming - Using PL languages in PostgreSQL - e.g. Plpgsql, PLR, PlPython, PLPHP, PLPerl, PLPerlU, PLProxy etc.
- PostgreSQL Contribs Spotlight - Using PostgreSQL contribs or advanced features - e.g. PostGIS, PgCrypto, PgSphere, TSearch etc.
- Application Development - Using PostgreSQL in an Application e.g. with PHP, Python, Perl, Java, Ruby, .NET, MS Access, OpenOffice etc.
- Product Showcase - a commercial or opensource product for managing PostgreSQL databases or that supports PostgreSQL as a backend
|