Would you like to win a copy of my EnterpriseDB book?

I've been thinking long and hard about how I want to give away a few copies of my book. I will be at the SOUG meeting Thursday, Jan 24th here in Tampa. If you're in the area, stop by. I'm going to have a drawing for a couple of copies that night. That doesn't really help those people who aren't near by. So, I am going to ask 3 questions and the first two people to answer all three correctly will get a copy. They won't be hard to answer but the answers might require a little work. I will ask the questions and explain how you will need to respond. To get the questions, you need to follow me on twitter. You can find my twitter account in various places on the net, including in my blog at ITToolbox.com.. You will need to be watching at 9am Eastern time, Jan 12, 2008. Follow and be watching for my questions. I will ask the questions and then explain what to do next. This contest is open to anyone. If you are outside the US and you win, we can discuss the best way to get you your book. I hope this is a fun process. That's what I'm shooting for anyway. Let me know what you think. LewisC

postgresql enterprisedb

My Book Has Been Printed

Well, it's taken over a year but it has finally arrived. I started writing the book back in Aug 2006. I finished in late Jan 2007 and the technical editor finished his work in March. Now, In Jan 2008, EnterpriseDB: The Definitive Reference is available. I'm glad I didn't wait for the movie. ;-) It's kind of ironic. Just this morning, I posted that I am working on my second book. My wife called me at work and told me two boxes of books had arrived. 2008 is turning out to be a good year. I'll have some additional news in the near future. Anyway, the book looks good. I've already found a few typos and grammar errors just skimming around. I like the font and the pages look really crisp. All in all, I have to say I am very happy that the book is out. Check out some photos: I think I want to give a couple away. Maybe some kind of contest for the blog readers. Any ideas of something fair? I am willing to pay shipping for a couple but that would only be here in the US. Overseas shipping gets expensive. LewisC

postgresql postgresql

New Postgres Online Magazine

I ran across a new Postgres ezine, Postgres Online Journal. Dec 2007 is complete and Jan 2008 is being constructed. You can read the current, under construction issue as HTML while it is being constructed. It looks like you can also download a PDF when an issue is complete. It will be nice to have an additional resource for Postgres developers and DBAs. This is just a sign that Postgres is becoming more mainstream. In the past all online Postgres information came from a very small group of people. The Dec 2007 issue has some good info:

• PostgreSQL The Road Behind and Ahead
• PostgreSQL 8.3 is just around the Corner
• Converting from Unix Timestamp to PostgreSQL Timestamp or Date Beginner
• Using Distinct ON to return newest order for each customer Intermediate
• How to create an index based on a function Intermediate
• The Anatomy of a PostgreSQL - Part 1 Beginner
• How does CLUSTER ON improve index performance Intermediate
• Language Architecture in PostgreSQL Intermediate
• PostGIS for geospatial analysis and mapping Intermediate
• Database Abstraction with Updateable Views Advanced

The article on updateable views is very good. I have used updateable views A LOT in Oracle but hadn't given that any thought in Postgres. DISTINCT ON is a nice feature also. You can click on About The Authors to see who is writing this. It leads to a company site called Paragon Corporation. They appear to be a database consulting company that works with most databases. This is a nice addition to the Postgres online resource pool. LewisC

postgresql postgres

Best Way to Handle No Data Found in a Procedure?

When it comes to data issues (too many rows, no data found, etc), in Oracle stored procedures, I am used to having exceptions raised that I then handle. PL/pgSQL does not raise exceptions for the same conditions in the same way. The Postgres docs are pretty complete though and through some reading this weekend, I discovered a new keyword. For example, assuming that I have this table (which is empty) in both Oracle and Postgres:

CREATE TABLE empty_table
(
  empty_col integer
);

In Oracle this procedure:

CREATE OR REPLACE PROCEDURE no_data_found_test
AS
  v_int_field INTEGER;
BEGIN

  SELECT empty_col
    INTO v_int_field
    FROM empty_table;

END;

When run:

BEGIN
  no_data_found_test;
END;

Produces an error:

Error starting at line 1 in command:
BEGIN
  no_data_found_test;
END;
Error report:
ORA-01403: no data found
ORA-06512: at "HR.NO_DATA_FOUND_TEST", line 6
ORA-06512: at line 2
01403. 00000 -  "no data found"
*Cause:    
*Action:

However, the equivalent procedure in Postgres:

CREATE OR REPLACE FUNCTION no_data_found_test()
  RETURNS void AS
$BODY$
DECLARE
  v_int_field INTEGER;
BEGIN

  SELECT empty_col
    INTO v_int_field
    FROM empty_table;

END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

Does not raise an exception when run:

postgres=# select * from no_data_found_test();
 no_data_found_test
--------------------

(1 row)

I have been using the FOUND variable to check for a result and raise an exception manually if no data was found. Like this:

CREATE OR REPLACE FUNCTION no_data_found_test()
  RETURNS void AS
$BODY$
DECLARE
  v_int_field INTEGER;
BEGIN

  SELECT empty_col
    INTO v_int_field
    FROM empty_table;

  IF NOT FOUND THEN
    raise exception 'NO_DATA_FOUND';
  END IF;

END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

When run, I do get an exception like I was expecting.

postgres=# select * from no_data_found_test();
ERROR:  NO_DATA_FOUND

However, after a little bit more reading, I see that I can add the STRICT keyword to make the procedure behave like Oracle:

CREATE OR REPLACE FUNCTION no_data_found_test()
  RETURNS void AS
$BODY$
DECLARE
  v_int_field INTEGER;
BEGIN

  SELECT empty_col
    INTO STRICT v_int_field
    FROM empty_table;

END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

And now when run, it does raise the exception:

postgres=# select * from no_data_found_test();
ERROR:  query returned no rows
CONTEXT:  PL/pgSQL function "no_data_found_test" line 5 at SQL statement
postgres=#

Very Nice! I think I prefer this method. It would be nice if STRICT were a database wide configuration parameter. LewisC

postgresql no_data_found

Postgres LiveCD

I read about the Postgres LiveCD at the Blue Gnu. That's such a good idea! I don't know why we don't see more LiveCDs. Even commercial software should be taking advantage of such an easy marketing opportunity. The entry says:

Well, what can I say? The CD is simply Xubuntu with PostgreSQL loaded up and ready to run. And once loaded, you can actually play with PostgreSQL at least three different ways. The Xubuntu CD offers three icons that launch the Command-Line client, PGAdmin3 or phpPGAdmin. Now you can play with the server for as long as you like, goof it up and reboot for a clean, shiny new server. Actually, the CD includes some sample databases and Slony-I, the database replication utility. Now, I didn't notice any documentation lying around, but my guess is one could probably mount the local hard drive and have PostgreSQL create databases on it all day long without ever installing the actual database server. Xubuntu still contains (most of) the other applications normally included - the team has simply loaded PG and it's client applications.

I couldn't find any links about where to get an ISO or anything so I went searching. I found some email list messages from 2006 and two interesting links:

• PGFoundry Live CD Project - Pre-Alpha and no files
• Dru Lavigne at ITToolbox.com - link to a user group with the ISO

As a bonus, Dru links to Joshua Drake's Practical PostgreSQL. This free book is getting a little old but still makes a useful reference. LewisC

postgresql livecd

Postgres Has Exceptional Documentation

Yesterday, Postgres made it to Mashable, today it got a mention in E-Commerce Times, Open Source for Business: Now More Than Ever, Part 1. The article isn't about Postgres specifically. It's about business targeted OSS uptake in the enterprise. There's an "open source roundup" "based on the recommendations of business leaders". The Postgres entry:

Postgresql: PostgreSQL is an open source relational database system. It runs on all major operating systems, including Linux, Unix (AIX, BSD, HP-UX, SGI IRIX, Mac OS X, Solaris, Tru64) and Windows. It is fully ACID (Atomicity, Consistency, Isolation, Durability) compliant, has full support for foreign keys, joins, views, triggers and stored procedures (in multiple languages). It includes most SQL92 and SQL99 data types, including Integer, Numeric, Boolean, Char, Varchar, Date, Interval and Timestamp. It also supports storage of binary large objects, including pictures, sounds or video. It has native programming interfaces for C/C++, Java Latest News about Java, .Net, Perl, Python, Ruby, Tcl and ODBC, among others, and exceptional documentation.

postgresql media

PostgreSQL Site Makes it to Mashable

Mashable posted a SQL TOOLBOX: 20+ Tools and Lessons About SQL. While there are a lot more links to MySQL, at least Postgres made the list. The description isn't exactly the best I have seen though, "PostgreSQL.org - An open source database system developed on SQL." I've been to some of these sites and they aren't exactly the best around. Some are old. There are a couple of really good ones though. The Comparison of different SQL implementations seems pretty well maintained and covers Oracle, Postgres, MySQL, DB2, & SQL Server. Check it out. LewisC

postgresql media