In this article we shall attempt to perform the same feats that Simon did, but with PostgreSQL instead of
Oracle XMLDB. Note while we are demonstrating this with a GPX file, the same XPath approach can be used to process any XML file.
The database industry is getting way too action packed for my blood.
First we had Sun buying MySQL, then Oracle buying Sun (thus inheriting MySQL and Java)
(recall they already owned
InnoDb (the MySQL main transactional engine) and they also by the way own BerkelyDB
which is the database engine underlying Subversion repository
and they also own all those CRMS and ERPs (Peoplesoft and Seibel),
and now we have IBM integrating EnterpriseDb into their DB so that it can look like Oracle Db.
What next? Perhaps Microsoft will join the party
to integrate EnterpriseDb into their SQL Server offering so SQL Server can look like Oracle and better yet a SQL Server for Linux/Unix to complete the circle.
Different Linux distros have their preferred place of where stuff goes and of course the default location on windows is completely different from that too. So there isn't really one default location where you can find PostgreSQL data cluster. Of course user's can pick their
locations as well. So what is a casual DBA supposed to do?
The pg_settings table
PostgreSQL has a convenient system table view called pg_settings that stores a lot of information. It stores the location of the data cluster, the pg_hbafile and other conf files.
In additon to that you can interogate it to find out information you will find in the postgresql.conf file. Why sift thru that postgresql.conf file (assuming you can already query your postgresql server) when you can find the answers you are looking
for with an SQL query?
This question is one that has come up a number of times in PostGIS newsgroups worded in many different ways. The situation is that if you use a function a number of times
not changing the arguments that go into the function, PostgreSQL still insists on recalculating the value even when the function is marked IMMUTABLE. I have tested this on
8.2 and 8.3 with similarly awful results.
This issue is not so much a problem if function calculations are fast, but spatial function calculations relative to most other functions you will use are pretty
slow especially when dealing with large geometries. As a result your query could end up twice as slow. Even setting the costs of these functions to relatively high does not help the situation.
To demonstrate here is a non-PostGIS version of the issue that everyone should be able to run and demonstrates its not a PostGIS only issue.
PostgresQL 8.4 beta will be out any day and 8.4 official release will hopefully not be too far behind. As we wait patiently for the official release,
Robert Treat has summarized nicely all the new features you can expect in 8.4.
PostgreSQL 8.4 is what I like to call an earth-shattering release because it has so many big ticket items in there, but also some long-needed usability features in it.
While we all know about the Windowing functions and CTEs and Recursive CTEs, there are a couple of usability features that we always get beat up on, which I am glad to see will be in 8.4.
Ability to add new columns to a view with CREATE OR REPLACE without having to drop the view and all the view dependents
Now the other niceties and usuability features which are nice but not quite as top of our list as the aforementioned.
Note this far from an exhaustive list, but Robert Treat's 8.4 slide presentaton is pretty exhaustive:
Variadic functions -- these are functions that have default values defined so can be called with varying arguments. To achieve this before you would have had to create
a separate function that calls the first and passes in the default arg. NOte this can be done with any pl langauge and in fact we demonstrated its use in PL/Python PL/Python and default parameters.
All plpgsql language and other non-sql/non-c proc languages that return sets to be called in the SELECT clause. To get around this problem before, you'd create your sophisticated
set returning function in plpgsql or python or whatever and then wrap it in an SQL function. No need for that hack anymore. Again we demonstrated this feature in PL/Python Pl/Python for loops and returning sets
pg_terminate_backend -- this kills a backend PostgreSQL process instead of just cancelling the query running on it as pg_cancel_backend did
Faster Restore -- now Restore can use parallel threads
RETURN QUERY EXECUTE support in plpgsql
LIMIT clause can take a subquery -- SELECT a.field1, a.field2 FROM a LIMIT (SELECT COUNT(*)/10 FROM a)
Make As alias in column SELECT optional as the ANSI SQL Standard allows. So you can now do - SELECT a field1, b field2 .... This is not something we would suggest since we find it makes
code hard to read, but does make code that used this regrettable syntax more portably converted to PostgreSQL. It would be nice if this were a flag though in the config that can be turned on since
I find it to be bad practice and encouraging bad habits.
Numerours changes to EXPLAIN to show columns used, maintenance improvements such as dead-locking reporting
Today was a very eventful day for PostgreSQL. We'll cover these changes in a bit.
Massive Forking of PostgreSQL project
First in PostgreSQL Announcements - David Fetter announces massive forking of the PostgreSQL project
in several factions. We now have the following -- so take your pick:
Shizzle: High-performance and Feature-Free
MaryMary: Compiled with libhaltingproblem
Narcona: Painless installation and setup
OurThing: Lots of sources, based in Sicily
XPostgres: Everybody who's ever worked on Postgres code, back to UC Berkeley and Illustra.
Moon/PostgreSQL: Corporate support, as long as it lasts.
I feel this may be good for the community
because it is hard to satisfy all these factions in one project. Now perhaps the newsgroups will be a bit calmer.
PgAdmin has come to an end -- make way for OpenPgAdmin
Dave Page announced today that the PgAdmin team received an offer they couldn't refuse from a very
big software company yet to be announced. So they are closing PgAdmin and you will soon be able to purchase
the services and support contracts from this new company.
Not to worry, Devrim Gunduz, has forked PgAdmin to form OpenPgAdmin. You can
check out the site here http://openpgadmin.info.
I must say as much as we are saddened to see the PgAdmin group leave for more fun escapades, We are happy we finally have an administration tool that has the word Open in its name.
If it starts with Open, its got to be open. Now all we need is an OpenPost PostgreSQL fork to go with it. OpenPost I think
will be easier to pronounce than PostgreSQL and Postgres and it has Open in its name.
So to add to the list of PostgreSQL project forks, I would like to see another project fork
OpenPost: Its free, open source, fast, feature-rich, easy to use and best of all you can pronounce it and its open.