Postgres in the Sky (well, EnterpriseDB in the Cloud)

EnterpriseDB Corp announced yesterday that they will be joining Amazon in the cloud. So what the heck does that mean?

Amazon's cloud computing is also called the Amazon Elastic Cloud Compute or Amazon EC2. EC2, when tied with Amazon's cloud storage (Amazon S3), gives you cheap and easy scalability for your applications. Your computing power moves from your own data center and hardware to the "cloud". You control the servers but they are virtual servers running somewhere else. You control access, you control what applications are running and who can use them. If you only need a trickle, you pay for a trickle. When you need to scale to huge proportions, you pay for what you need.

Amazon recently announced Amazon SimpleDB which utilizes Amazon EC2 and Amazon S3. With SimpleDB, you can store and query data (i.e. build data web services). I really wasn't all that impressed with SimpleDB when I first read about it as it is a very simple database. It's named well. ;-)

SimpleDB gives you named value sets of data. It is the most basic of data structures. You can assign anything to the "database" by giving it a name and a value. A name can have multiple values. And so on.

EnterpriseDB's announcement, EnterpriseDB to Deliver OLTP Database Using Amazon Cloud, means that instead of the very basic SimpleDB, you will now be able to get a robust, Enterprise-class database in the cloud.

The beta testing for EnterpriseDB Advanced Server Cloud Edition starts in March, 2008. Cool.

You can run EnterpriseDB on a tiny server to build and test your apps and then, in minutes, scale up to huge proportions when you go live. No worries about data centers or buying hardware.

No mention if the vanilla EnterpriseDB PostgreSQL package would be offered. I would think not as EnterpriseDB Advanced Server is more geared towards this kind of solution.

I'm not sure what kind of SLAs Amazon offers. It will be interesting to see what kind of guarantees can be offered on a service like this. OLTP processing requires very reliable computing, much more so than OLAP and reporting.

We live in interesting times.

LewisC

postgresql enterprisedb

Postgres and/or EnterpriseDB User Groups?

Hi all. I'm going to be using Postgres and EnterpriseDB a lot more in the near future. I was wondering if there is a Tampa area, or central Florida, Postgres user group or if there might be interest in starting one. If not, maybe a general database user group. I know the local Oracle user group is very popular (and it's a group I attend and enjoy) and I was hoping to have the same kind of thing for Postgres and EnterpriseDB. I think a general database user group might be a good idea too. It could cover all of the databases and give people who don't normally work with a particular database some exposure to it. For example, I have never worked with SQL Server but wouldn't mind an occasional presentation on SQL Server topics. Any interest in something like this? I don't know how many people in Central Florida read my blog and would be interested in meeting and discussing various databases. Let me know if you are already aware of something like this or would be interested in attending if something was available. Thanks, LewisC

postgresql enterprisedb

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