Tuesday, December 16. 2008
Printer Friendly
In our Product Showcase section of this issue, we introduced Fusion Charts which is a flash-based
charting product that makes beautiful flash charts. It comes in both a free and a non-free more bells and whistles
version.
In this 3-part series article we shall demonstrate using this with a PostgreSQL database, building a simple dashboard
with ASP.NET and PHP. We shall demonstrate both C# and VB.NET both using the PostgreSQL NPGSQL driver.
For this first part we shall simply load the database, do a quick analysis of what we've got to report on and create some views to help
us with our PHP and ASP.NET apps that will follow in parts 2 and 3.
We will be testing this on 8.3, but since the database is an old one, it should work just fine on older versions of
PostgreSQL. We'll try to refrain from using new features of PostgreSQL.
Continue reading "Fusion Charts and PostgreSQL Part 1: Database Analysis of USDA DB"
Monday, December 15. 2008
Printer Friendly
PostGIS 1.3.5 urgent upgrade if you are running 1.3.4
We were forced to release a 1.3.5 PostGIS upgrade as a result of a bug we accidentally introduced in 1.3.4 during our code cleanup.
We apologize for any inconvenience this may have caused people. This bug affects the use of MULTILINESTRINGS and rears its ugly head
by giving errors such as invalid circular line string when calling
ST_Multi or another odd error when doing a Force collection on a MULTILINESTRING. This hits mapserver users using these geometry types the hardest.
More details of the issue can be gleaned from Paul Ramsey's blog.
Warning: PostGIS 1.3.4 + Mapserver
Continue reading "PostGIS 1.3.5 out the door critical patch to 1.3.4 and Testing Enhancements"
Wednesday, December 03. 2008
Printer Friendly
To finish off our PL/PGSQL tutorial series, we are providing a PL/PGSQL cheat sheet.
Below is a Thumbnail view of the PostgreSQL 8.3 PL/PGSQL cheat sheet
that covers both 8.3 new features and past core PL/PGSQL features. This is by no means comprehensive, but are the features we use most often.
PDF Portrait version 8.5 x 11" of this cheatsheet is available at PostgreSQL 8.3 PL/PGSQL Cheatsheet 8.5 x 11 and also available in
PDF A4 format and HTML.
Sunday, November 30. 2008
Printer Friendly
In this third part of our PLPGSQL Quick Guide series, we shall delve into writing recursive functions. Before we do that, we shall demonstrate a very important
but trivial feature in PostgreSQL and that is the RAISE NOTICE feature. There are more elegant ways of debugging, but this is the simple brain dead way of doing so.
Continue reading "Quick Guide to writing PLPGSQL Functions: Part 3 - NOTICES, RECURSION, and more"
Wednesday, November 26. 2008
Printer Friendly
We had the pleasure of doing a fresh install of PostgreSQL 8.3.5 on RedHat EL4 box and when using the Yum repository, we noticed a couple of changes from last time we did this.
This could have been an oversight in our documentation before.
Changes to Yum Install for 8.3.5?
In our April 2008 issue we had An Almost Idiot's Guide to PostgreSQL YUM
and that article still seems to be surprisingly popular.
In the first step we had:
yum install postgresql
and that as I recall installed the postgresql server in addition to some client libraries.
For 8.3.5 fresh install it seems they are separated and to get the postgresql server you need to do:
yum install postgresql
yum install postgresql-server
Continue reading "Yum addendum for 8.3.5 and PgAgent"
Printer Friendly
PostGIS 1.3.4 is finally out the door. This version has:
- Support for 7.3, 7.4, 8.0, 8.1, 8.2, 8.3, 8.4 beta
- GEOS 2.2.3, GEOS 3.0.3, GEOS 3.1 beta
- Numerous bug fixes and speed improvements
- Slightly better documentation
- addition of function comments to help guide new users while in the psql or pgAdmin environment
- One new function ST_AsGeoJSON to support javascript apis such as OpenLayers.
Some advanced GEOS functions such as ST_SimplifyPreserveTopology and ST_CoveredBy (both released
in 1.3.3) will not be installed unless you are running GEOS 3.0 or above.
Continue reading "PostGIS 1.3.4 is finally out the door"
Wednesday, November 19. 2008
Printer Friendly
This product is not specifically a PostgreSQL product but it is one that we use frequently with many of our database apps so we felt our obligation to blog about it in the context of databases.
Lets face it, when you have a database, somebody will come to you
one day and demand to see their data in sparkling colors, because why have data if you can't see it in sparkling colors.
They might not know what the data is telling them, but at least it will look damn good when charted in 3D.
This is when you should whip out something like Fusion Charts. This is just a small part of a three part series. In our application arena, we shall demonstrate using Fusion Charts in PHP as well as ASP.NET
and of course display PostgreSQL data using it. We shall only be demonstrating the Free version. If you really insist on Oracle, MySQL, IBM DBII, SQL Server, SQLite or FireBird or some other flavor of db, you can perform the same trick with slight variation.
You just need data you want to chart.
Continue reading "Fusion Charts for Sprucing up Data"
Monday, November 17. 2008
Printer Friendly
Sometimes when you are testing or setting up a server or just porting things to another server, you just want to install the same set of users as you had before without restoring any databases.
In PostgreSQL, the users (Login Roles) and group roles are stored at the server level and only the permissions to objects are stored at the database level.
Question: How do you restore just the users and roles without having to do a full pg_dumpall of your server?
Continue reading "Backing up Login Roles aka Users and Group Roles"
Wednesday, November 12. 2008
Printer Friendly
PostGIS 1.3.4 is almost out the door - Need testers
Well PostGIS 1.3.4 is almost out the door and we will be releasing an RC3 very shortly. As a developer in the group and also as a user of the product that is near and dear to me
I would be really appreciative if people in the PostgreSQL community interested in PostGIS can test this out. Below is a clip of Mark Cave-Ayland's note to the postgis-dev group.
PostgreSQL 8.1+ - Win32
PostgreSQL 8.1+ - Solaris
PostgreSQL 7.3 - Linux
PostgreSQL 8.1+ - GEOS 2.2.3 - Linux
PostgreSQL 8.1+ - GEOS 3.0.3 - Linux
PostgreSQL 8.1+ - GEOS 3.1 - Linux
The plan here is to check Win32/Solaris both pass the new sed tests in
Makefile.config.in, make sure we haven't broken really old PostgreSQL
builds, and also make sure that GEOS 2.2, GEOS 3.0 and the very new GEOS
3.1 with prepared geometry doesn't break either.
Continue reading "PostGIS 1.3.4 almost out the Door and 8.4 highlights"
Friday, October 31. 2008
Printer Friendly
PostgreSQL 8.4 will have a ROW_NUMBER() windowing function so this little hack will
hopefully be unnecessary when 8.4 is in production.
Getting back to this exercise, this was actually inspired by Hubert's recent article Tips N’ Tricks - setting field based on order. Why this inspired me, I guess because it stirred
up memories about the often forgotten utility of arrays in PostgreSQL and I thought it would answer a question that was haunting
me - How do I assign sequential numbers to a list?. The article just didn't quite read the way I expected it to and actually
was answering another question I cared much less about, but it did get the
juices flowing. So without much further ado.
Continue reading "Simulating Row Number in PostgreSQL Pre 8.4"
Tuesday, October 28. 2008
Printer Friendly
The age old question of why or why is my table index not being used
is probably the most common question that ever gets asked even by expert database users.
In this brief article we will cover the most common reasons and try to order by statistical significance.
Continue reading "Why is my index not being used"
Printer Friendly
In first part Guide to Writing PLPGSQL functions, we covered the plpgsql function anatomy and basic IF and FOR loops. In this second part of our PLPGSQL Quick Guide series, we shall delve more into control flow. As we mentioned in the previous part, the following control flow constructs exist for PLPGSQL.
- FOR somevariable IN (1 ...someendnumber) LOOP .. END LOOP;
- FOR somevariable IN REVERSE someendnumber .. 1 BY somestep LOOP .. END LOOP;
- FOR somevariable IN EXECUTE(somesqlquery) LOOP ..RETURN NEXT; .. END LOOP;
- LOOP ..logic statements EXIT .. EXIT WHEN .. CONTINUE WHEN .. END LOOP;
- WHILE ... LOOP ... END LOOP;
- EXCEPTION WHEN .... WHEN ..
- Introduced in 8.3 RETURN QUERY which can be in any LOOP like structure or stand alone. This is
covered in New Features of PostgreSQL Functions
In this section we shall demonstrate looping thru sets of records and writing a set returning function. In the next section after,
we shall delve a little into recursive functions, doing table updates, and raising notices.
Continue reading "Quick Guide to writing PLPGSQL Functions: Part 2 "
Saturday, October 11. 2008
Printer Friendly
In this series we'll go over writing PLPGSQL stored functions. We shall follow up in a later issue with a one page cheat sheet.
The Anatomy of a PLPGSQL FUNCTION
All PLPGSQL functions follow a structure that looks something like the below.
CREATE OR REPLACE FUNCTION fnsomefunc(numtimes integer, msg text)
RETURNS text AS
$$
DECLARE
strresult text;
BEGIN
strresult := '';
IF numtimes > 0 THEN
FOR i IN 1 .. numtimes LOOP
strresult := strresult || msg || E'\r\n';
END LOOP;
END IF;
RETURN strresult;
END;
$$
LANGUAGE 'plpgsql' IMMUTABLE
SECURITY DEFINER
COST 10;
--To call the function we do this and it returns ten hello there's with
carriage returns as a single text field.
SELECT fnsomefunc(10, 'Hello there');
Continue reading "Quick Guide to writing PLPGSQL Functions: Part 1"
Sunday, October 05. 2008
Printer Friendly
MySQL is turning out to be one big soap opera as far as I can tell and as Bruce Momjian has mentioned.
Lets go over some of the interesting episodes of this saga:
- First Sun buys MySQL
- Falcon storage engine creator Jim Starkey leaves MySQL/Sun
- Brian Aker heads Drizzle which is a fork of MySQL that hopes to be a stream-lined
implementation of MySQL that leaves out all that nonsense we don't need such as views and stored procs
and targeting it self for running on the cloud, optimizing for massive concurrency, and ease of install. I presume Brian still works for MySQL/Sun though.
- Michael "Monty" Widenius Leaves MySQL and Sun
to help with this Drizzle thing, but will still be working on Maria
according to Brian (see comment below).
- And now Jay Pipes leaves as well to work on Drizzle. He will still be a Sun staff engineer on Drizzle, but not the MySQL community leader.
I'm not sure what all these things say about the stability of the MySQL core. I mean should I stick with MySQL 5 or run for the Drizzle, but I think I'll stick with PostgreSQL
where ever I can. PostgreSQL may not be quite as interesting from a soap opera perspective,
but it seems a tad bit more dependable and I really like my views and stored functions.
Friday, September 26. 2008
Printer Friendly
There are a lot of functions in PostgreSQL and for the most part, they are nicely tucked away in the pg_catalog schema so they don't get mixed with your functions.
There are a lot of contrib modules though and if you use a few of the big ones and just dump them in the public schema, the function list gets overwhelming and you have a hard time finding your own functions.
To keep our sanity we tend to create a schema called util or something like that where we stuff our own personal functions for easy navigation and for larger contribs, we may put these in a
separate schema altogether. Every once in a while, we screw up and put the functions in the wrong schema. Deleting these can become painful if there are a lot to delete.
Problem: How do you delete a butt load of functions without working up a sweat?
Solution:
Use the below code cautiously. We start off with our general hack of writing an sql statement to write lots of sql statements. We had
hoped to use the more generic information_schema for this exercise, but the closest table we could find information_schema.routines, lists the names of the functions
but not the arguments. You need the arguments in your drop since PostgreSQL supports function argument overloading.
SELECT 'DROP FUNCTION ' || ns.nspname || '.' || proname || '(' || oidvectortypes(proargtypes) || ');'
FROM pg_proc INNER JOIN pg_namespace ns ON (pg_proc.pronamespace = ns.oid)
WHERE ns.nspname = 'my_messed_up_schema' order by proname;
The above will generate but not execute code that looks something like below so you can inspect the drops before executing:
DROP FUNCTION my_messed_up_schema.funcabc(int4,int4);
DROP FUNCTION my_messed_up_schema.funcdef(int4,date);
|