Thought I would share a few links that I read this morning. I don’t do a lot of link posts but these three came off my RSS, one after the other, and all three are worth sharing.

The first is Will Oracle kill MySQL? Come on people! I can’t believe people are still stuck on this FUD. Ronald gives some good reasons why that is not likely. The only thing this kind of FUD really does is drive people to Postgres (and that postgres derivative that starts with an E and ends with a DB). I’m glad Ronald was able to set the record straight.

From the database geek.

Postgres 8.4 is just around the corner. I haven’t had much time lately to keep up with Postgres. I’ve had too much to do in the Oracle world (new job, writing, etc). However, I do try to keep up with a few email groups just to keep touch with what’s coming. Here are three things in Postgres 8.4 that I think will be interesting to a lot of people. Two of them bring Postgres that much closer to Oracle and one of them is useful (at times).

SQL Analytics

SQL Analytic Functions, also called Window Functions, allow you to execute functions across multiple sets of data within a single query.

For example, you can query the max value (or sum, average, whatever) of a field for multiple sort orders in a single query.

Yesterday I posted about the results of the EnterpriseDB open source survey. In that post, I said:

I’d like to see the survey again and compare the results to the survey itself.

I discovered that, as of right now, the survey is still online.

Still no information as to when, or if, the entire survey results will be released. That’s what I am most interested in.

LewisC

Technorati : database, enterprisedb, open source, postgres

Well, I am no longer working for EnterpriseDB. It was fun while it lasted but it’s over so I am moving on. I found a new job, locally. It’s pure Oracle and I will get to use Real Application Clusters in a production environment. That’s something I haven’t done in the past so I am looking forward to it. It’s also a java, .net and Oracle Forms shop and they are doing some interesting things with telecommunications and SMS.

From now on, I will only need to travel for conferences. No more trips to New Jersey. That’s kind of a drag as I was almost at elite status on Continental.

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.

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. 

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.

This is just a quick overview of the availability of programming for Postgres. With Oracle, your choice in the database is Java or PL/SQL. In general, PL/SQL is the preferred language.
One of the things that attracted me to Postgres was the ability to choose one of many languages to program in. I usually choose PL/pgSQL but if I can’t do something with that, it’s nice to be able to fall back to TCL, Perl or one of the many other languages supported by Postgres.
Here is a (incomplete, I’m sure) list of supported languages:

• PL/pgSQL – Standard Procedural Language for Postgres
• PL/TCL – TCL, Tool Command Language, easy to learn but powerful
• PL/Perl – Practical Extraction and Report Language, has grown way beyond its origins
• PL/Java – Java, meh
• PL/PHP – PHP: Hypertext Preprocessor, over hyped language
• PL/Python – Object Oriented, dynamic language.

Do you remember Foxpro? xBase? Clipper? I’m an old clipper head from way back. Anyway, I ran across something in my recent internet travels that caught my fancy. It may have the longest name of any utility I have ever seen.
FoxPro PostgreSQL Import, Export & Convert Software will transfer tables to and from PostgreSQL and FoxPro.
I haven’t downloaded it yet so I can say how well it works but it’s a neat idea. I’m guessing it can handle DBF files so that would make it compatible with dBase, Foxpro, Clipper and may other tools.

This is just a little peeve of mine. When you install EnterpriseDB, you can choose either a full production license or the free EnterpriseDB Express version. On my laptop, I want to run the express version. I don’t need it to use more than 1 gig of ram, more than 1 cpu or more than 6gb of disk. But I can’t even install it because my hardware exceeds those specs.

Oracle Express Edition has the same limitations as EnterpriseDB Express (and so does Microsoft SQL Server Express), but Oracle (and MS) limit their hardware usage by building the limits into the software.

I think PL/SQL programmers who move to Postgres all run into the same thing, how do I run the procedure or function once I’ve created it?

Obviously, it’s easy when you are calling it from another stored procedure or function.  What most mean, is how do I call it from the command line?  That’s easy too.

Using my function and procedure from earlier posts, i.e. 10 Steps to Creating a Function In PostgreSQL Using PLpgSQL and Creating a Procedure In PostgreSQL Using PLpgSQL.

To call either the function or the procedure, you can run:

SELECT func_1();

Or

SELECT * FROM func_1();

You must include the ().

Same for the procedure.

SELECT proc_1();

Or

SELECT * FROM proc_1();

Of course, if you are using a GUI tool like PgAdmin III, it’s a somewhat different story.  You need to open the query window but the syntax will be the same.

Technorati Tags: postgres, postgresql, procedure, create, declare, plpgsql, function, code, develop

I recently wrote about creating a function in PL/pgSQL, 10 Steps to Creating a Function In PostgreSQL Using PLpgSQL.

Today, I am going to show you how to create a procedure.  You don’t really create a procedure, you use the same basic syntax as you do for a function.  The RETURNS keyword can get a little tricky as that keyword requirement changes based on what you are trying to do.  To start, I will create a procedure that functions the same as the function in the previous entry.  Then I will make some changes.

CREATE OR REPLACE FUNCTION proc_1(
  OUT out_parameter CHAR VARYING(25) )
AS $$
DECLARE
BEGIN
  SELECT datname
    INTO out_parameter
    FROM pg_database
    LIMIT 1;

  RETURN;
END;
$$ LANGUAGE plpgsql;

If you compare this to the function from the previous entry some things jump out right away.  There is no RETURNS statement.  Postgres is able to determine the return type from the OUT parameter.

The DECLARE keyword is completely optional.

For, I guess, about the last week or so, there has been an ongoing debate on the Advocacy email list about renaming the software from PostgreSQL to Postgres or something else.

Some people have very strong opinions about it. Personally, I don’t see what the big deal is. The only people who really have an issue are the people in that (hacker) community. CEOs don’t care what it’s called. No developer I know cares what it’s called.

Just to clarify, when I say developer, I mean a person who develops software for business use.

It’s actually fairly easy to create a function using PLpgSQL, especially if you are coming from a database background like Oracle or DB2. Both have procedural languages that look a lot like PLpgSQL.

I’ll go ahead and show you the code for a very basic function and then I’ll explain the steps individually.

CREATE OR REPLACE FUNCTION func_1()
  RETURNS CHAR VARYING(25)
AS $$
DECLARE
  local_char_var CHAR(30);
BEGIN
  SELECT datname
    INTO local_char_var
    FROM pg_database
    LIMIT 1;

  RETURN local_char_var;
END;
$$ LANGUAGE plpgsql;

Ok.

InformationWeek has an article with Bruce Momjian, PostgreSQL 8.3 Features To Be Available After Labor Day: Full text search and other features will become available for free download in beta code after Labor Day; with the final release to follow by 8-10 weeks.
Won’t be long now. I probably won’t have time to play with it while it’s in beta but I am looking forward to it. Tsearch2 will be nice and compares to Oracle Text although not quite a feature rich.
For some reason, EnterpriseDB’s PL/SQL debugger is being included.

I stumbled across a nice little cheat sheet by someone names Pete Freitag. It’s a PostgreSQL Cheat Sheet. The sheet contains a quick ref for CREATE DATABASE, CREATE TABLE, Adding a primary key, Creating an INDEX, Backing up a database, Running a SQL script, Selecting using a regular expression, Selecting the first N records, Using Prepared Statements, Creating a Function, Vacuum, Reindexing, Showing a query plan, Importing from a file, and some basic SQL statements. It’s not a bad quick ref for newbies.
The site also has cheat sheets for Coldfusion, Java, Apache, ASCII codes, CSS, XHTML, and even an English Grammer Cheat Sheet.

I have had the question several times now so I thought I would blog about. This info was actually covered in the original press release, EnterpriseDB Announces First-Ever Professional-Grade PostgreSQL Distribution for Linux.
Professional-Grade PostgreSQL Distribution

EnterpriseDB Postgres is an open source, pre-configured, certified binary PostgreSQL distribution that simplifies enterprise deployment, eliminating the need to source multiple software components on the Web and manually assemble them. EnterpriseDB Postgres includes a one-click installer that delivers the most commonly needed PostgreSQL components and add-ons for major operating systems, including:

• The most recent General Availability version of PostgreSQL, plus:

• Secure Sockets Layer (SSL)
• Cryptography (pgCrypto)
• XML Support (libxml)
• Full Text Search (TSearch2)
• Database Linking (DBLink)
• Languages: pl/pgSQL, pl/TCL, pl/Perl
• Database Connectors: ODBC, JDBC

They are also offering support, documentation and forums.
LewisC

postgresql enterprisedb

InformationWeek has a good interview with Bruce: Open Source PostgreSQL Developer Bruce Momjian Spills The DB Beans.

In the interview, Bruce talks about how he spends one day a week at the EnterpriseDB site but most of his time working on Postgres.

There aren’t a lot of quotes in the interview. I wonder if a longer interview is coming.

There are a few anti-comparisons between “bearded sage” Momjian and JBoss’ Marc Fleury and, of course, the obligatory comparison to MySQL.

PostgreSQL sought to match major relational database systems and afterward addressed performance. “We didn’t focus on performance until the end.

First, we create the table structure that will act as the master table.

CREATE TABLE sales_range
(salesman_id  integer,
salesman_name text,
sales_amount  integer,
sales_date    timestamp);

Then we create each of the partitioned tables using OO style syntax. Each partitioned table “inherits” the structure of the parent table. There is a string relation between parent and child. You cannot drop the parent without dropping the child.

CREATE TABLE sales_jan2000
  ( CHECK (sales_date < TO_DATE('02/01/2000','MM/DD/YYYY')) )
  INHERITS (sales_range);

CREATE TABLE sales_feb2000
  ( CHECK (sales_date < TO_DATE('03/01/2000','MM/DD/YYYY')) )
  INHERITS (sales_range);

CREATE TABLE sales_mar2000
  ( CHECK (sales_date < TO_DATE('04/01/2000','MM/DD/YYYY')) )
  INHERITS (sales_range);

CREATE TABLE sales_apr2000
  ( CHECK (sales_date < TO_DATE('05/01/2000','MM/DD/YYYY')) )
  INHERITS (sales_range);

At this point, any inserts against the parent table (sales_range) would be inserted into sales_range.

Feature comparisons, in addition to being somewhat lame and almost always biased, are very dependent on versions. If you throw in performance considerations, they are also dependent on hardware and configuration. Even so, I like to read comparisons, for historical information if nothing else.
Here are some comparisons that I have found. I make no recommendations or even commentary about them. Read through them as if you are a database anthropologist. Dig for the nuggets that increase your existing base of knowledge but remember that a human, probably one with an agenda, put these comparisons together.

I used Yahoo Pipes to create a new PostgreSQL blog aggregator. I am hosting it on squidoo. Check out the Squidoo All About PostgreSQL page.
You can vote for your favorite PostgreSQL book, leave comments and check out postgres and database related products.
BTW, this is the first I have use Yahoo Pipes. It’s very cool and very easy to use. It took me about 10 minutes to use it and about 5 minutes to set up my aggregator. If you have ever used a graphical ETL tool, it looks a lot like that.

A poster on the PostgreSQL pgsql-general mail list asked some good questions that might be important to anyone. The general mail is a:

General discussion area for users. Apart from compile, acceptance test, and bug problems, most new users will probably only be interested in this mailing list (unless they want to contribute to development or documentation). All non-bug related questions regarding PostgreSQL’s version of SQL, and all installation related questions that do not involve bugs or failed compiles, should be restricted to this area. Please note that many of the developers monitor this area.

The poster says he is a long time MySQL user and was switching to PostgreSQL.

Where do you go when you have a question about Postgres? Do you join one of the PostgreSQL.org mail lists? That is so 1990s, isn’t it? Not that I would recommend signing up for a postgres twitter but how about checking out the EnterpriseDB Postgres Resource Center.
You can get news, downloads, documentation and a support forum. The blurb on the site says:

The EnterpriseDB Postgres Resource Center is a community-based site for enterprise application developers and DBAs and provides a rich repository of technical information, tools, and other resources.

This is just too cool. Today, EnterpriseDB announced at LinuxWorld that they are offering GridSQL, a data warehouse and OLAP clustering solution. According to the doc, the EnterpriseDB page for GridSQL, the tool offers:

• Quickly and easily create, maintain, and access very large databases for Business Intelligence and Data Warehousing applications

• Offload reporting from expensive hardware platforms onto an inexpensive grid of commodity hardware, creating significant savings

• Run reporting and other applications with very large datasets, improving organizational decision-making

• Improve organizational productivity and agility by enabling quick access to large amounts of informationThis is supposed to be a true, shared nothing approach to clustering.

I’ve been looking. I’ve been waiting. It’s just not out there. At least nothing I found to be particularly interesting. If you hear any good bits, drop me a comment or email me.

I did want to post a link to my article on my Expert’s Guide to Oracle though. Some waste of valuable bandwidth posted a ridiculous entry about Oracle and PHP. Hope fully you find it worthy of a read.

postgresql news oracle php

EnterpriseDB got the finalist nod for Best Database Solution, for the third year, and for GridSQL For EnterpriseDB. This is the first I have heard of GridSQL and I am looking forward to more info.

All it says in the announcement is:

GridSQL for EnterpriseDB Advanced Server is a new product that will be announced at LinuxWorld on Aug. 7, 2007.

About all I can think is that it is something like pgCluster which is a multi-master, bi-directional replication tool for PostgreSQL. I have a hard believing that’s what it because 1) they already have a replication tool and 2) the naming would be incredibly unfortunate.

I say option 2 because when most people hear grid associated with “an Oracle compatible database”, they think of Oracle RAC and/or Grid (which most people erroneously think is the same thing but that’s a different blog).

So now I wait with baited breath. I wish I had gotten a head’s up at least.

oracle enterprisedb gridsql linux world award pgcluster

Have you ever wondered why you should choose one open source database over another? What features would make the most sense for your Organization? Maybe you’re a developer looking to learn a database and can’t choose where to start?

The folks at PostgreSQL have put together a wiki, Why PostgreSQL instead of MySQL. It’s, by no means, complete at this time but it is a good start.
The wiki is not editable by the public but it is open for reading. The wiki entry compares PostgreSQL 8.1 and MySQL 5.0.

Stefan Arentz posted an entry on his blog, about a hack he’s done for PostgreSQL. He’s added a native UUID to the database. This is pretty slick.
Most of your users will never notice but it can help on databases that need it. He lists some advantages on the google code page:

Database Size – Using a native UUID type on a table that also has an index on the uuid column saves 47% space. This is good because there is less data on disk and in memory. Which really matters if you have potentially hundred of millions of records.

The number of Oracle users is huge and Oracle has the numbers available. The number of EnterpriseDB users is smaller but EnterpriseDB tracks those also. I would like to know how many people are using PostgreSQL in production and what kind of systems they are running.

If you monitor the some of the Postgres email lists, such as General, Jobs, and, maybe the Docs group, you can get a basic feel of some usage but only from the more verbal and connected in the community.

Does Forrester Research or Gartner track open source database usage? I haven’t seen anything.

woodjr over at the sun.blogs.com site that they ran a new SPECjAppServer 2004 with GlassFish v2 and got a better response than what Josh posted a while back.
Check out the post,GlassFish Benchmark Results Keep Climbing.
LewisC

Tags: postgresql sun benchmark