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.

Less than two weeks ago, I posted my Database Survey. As of just a few minutes ago, I have had 215 responses. That’s pretty awesome. I’d like to get at least twice that though.

I haven’t looked deeply at it yet to see if there are any trends. I think it will be best to wait until the survey is closed. I did look at some of the responses, kind of as a quality check. Looks like MySQL is fairly well represented. I didn’t see any DB2 responses (for primary database).

Infoworld published the 2008 Bossies, Best Of Open Source Software. There are 8 categories and none of them are database:

• Collaboration
• Developer tools
• Enterprise applications
• Networking
• Platforms and middleware
• Productivity applications
• Security
• Storage

I had to look through several of them before I found the database category under Platforms and middleware. Slide 4 is the magic slide:

It says:

I have added quite a few new words to the glossary and fixed several misspellings around the site.
My next steps will be to complete the Postgres and MySQL installation guides.  I’m also taking requests for what anyone would like to see written next.
LewisC

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

Have you heard of Drizzle? It was announced at OSCON yesterday and is all over the blogosphere. From the Drizzle FAQ:

* So what are the differences between is and MySQL?

No modes, views, triggers, prepared statements, stored procedures, query cache, data conversion inserts, ACL. Fewer data types. Less engines, less code. Assume the primary engine is transactional.

Also from the FAQ is that, right now at least, there is no intention to make this run natively on windows and they make the point:

* “This is not a SQL compliant relational…”

Very true, and we do not aim to be that.

It is a fork of MySQL that takes it backward to pre-5.0 in features but hopefully greatly reduces the bugs and instabilities.

EnterpriseDB announced the results of the survey they did a few months ago at OSCON. Now, take the results with a grain of salt as it was done by EnterpriseDB. EnterpriseDB is based on Postgres so there is a vested interest in making Postgres sound good. Results can be skewed depending on how the survey is worded, what options are available as answers and who the respondents are.

The results summary is available for free.

Some key facts:

500 respondents. The download page says “500 corporate IT leaders”. Or maybe, 500 open source developers.

Only 9% of respondents indicated that they preferred commercial solutions over open source solutions.

I was browsing the web on this lazy Sunday afternoon and ran across a good article on the Rarest Words blog. The author was trying to get Django installed and running with Postgres. From the author’s own admissions, he is not a Postgres fanatic.

Well, this and last year I hear everywhere that PostgreSQL is the way to go and that usage of mySQL in 2008 makes people puke… But without any real arguments (besides “Postgres is the way to go”).

After some not so compatible errors with these not so compatible databases, the author did get it working and ran some benchmarks. Postgres did not turn out faster than MySQL.

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.

LewisC’s An Expert’s Guide To Oracle Technology

The datastore in Postgres (which is what Advanced Server is based on) is quite a bit different than the way Oracle stores data. Gone are the usual logical storage components. Data blocks, extents and segments just don’t exist in a Postgres database. Actually, segments do exist when a table gets bigger than 1GB but that’s a story for a different entry.

Postgres stores data as files and directories. Tablespaces are directories and tables (and indexes) are files inside that directory. Postgres ships with two tablespaces installed by default: pg_default and pg_global. The tablespaces cannot be changed or dropped.

LewisC’s An Expert’s Guide To Oracle Technology

Sun just announced MySQL Workbench, a new database design tool for MySQL developers and DBAs. I’m a data modeling tool junkie. I like to play with any I can get my hands on. I’ve used almost every modeling tool that’s been built. My all time favorite is probably Erwin.

I decided to download MySQL Workbench and give it a try. Since I was playing with it, I figured I should write about it and while I am writing about it, I might as well write about a couple of other tools, that I have personally used, that you might like.

TOAD Data Modeler

The TOAD Data Modeler from Quest used to have a free version.

EnterpriseDB News at Blogspot

Chris Keene, the CEO for Wavemaker just blogged that combining Wavemaker and EnterpriseDB just got easier. Wavemaker has a new version that has out of the box support for both Postgres and EnterpriseDB.

I wrote a couple of weeks ago about my first day of Wavemaker training. I still haven’t found time to start day two but now that Wavemaker supports EnterpriseDB, I’ll probably redo day 1 using Advanced Server instead of MySQL. I could have done it before since it already supported Postgres and EnterpriseDB via a manual configuration.

LewisC’s An Expert’s Guide To Oracle Technology

According to an article at Computerworld, Yahoo is running a 2 PB (not GB, not TB, PB – Petabyte) database that processes 24 billion events a day. Let’s put that in perspective. 24 billion events is 24,000 million events; 24,000,000,000 events. 1 petabyte is 1,000,000,000,000 bytes. Yahoo has two of those. Actually, I should be basing this on 1k which is 1024 but when you’re dealing with petabytes, I don’t think we need to be picky. We’re talking really, really big.

Yahoo uses this database to analyze the browsing habits of it half a billion monthly visitors.

LewisC’s An Expert’s Guide To Oracle Technology

An ITToolbox user recently asked a question on the EnterpriseDB discussion group, Oracle equilant UTL_I18N.STRING_TO_RAW IN ENTERPRISEDB.

Basically, Sreenivas asked which functions in EnterpriseDB could be used to implement dbms_crypto, hex_to_raw, string_to_raw, etc. I believe he is using EnterpriseDB Postgres Plus Advanced Server which is the product that gives Oracle Compatibility. The short answer to his question is that right now, there are no compatibility functions for those. The long answer is that you can re-implement that functionality using native PG functionality.

If you look at Sreenivas’s message you can see how his existing code works.

LewisC’s An Expert’s Guide To Oracle Technology

Do you know which open source feature is the most important? Do you know which open source database rocks and which one sucks? Is MySQL better than Postgres? Is Ingres worth considering? How does Firebird compare? Have you used, or have you considered using, an open source database?

Take a survey. It’s only 15 questions so it takes just a few minutes.

I’ll post a link where you can get the results once they have been compiled and prepared.

BTW, this isn’t my survey. I’m just passing on the link.

LewisC

Bind variables are used to ease code maintenance and to save memory and processing on the server. When you save memory and processing power, you improve the overall performance of the server. The inner details of how this saves memory has been enumerated in other places on the web. This article is designed to help developers users bind variables when running queries against Postgres Plus Advanced Server.

What are bind variables?

The easiest way to think of a bind variable is to consider it just another variable. Instead of it being a variable to be used by your application (and whatever language you happen to be using), think of it as a variable to be used by SQL.

I come from an Oracle background so I am used to a few rules when it comes to DDL.

• Don’t perform DDL in a production database until you’ve done it in a test instance.

• DDL is permanent unless you have flashback or want to restore.

That might sound like a limitation but I don’t see it that way. You *SHOULD* test everything before running it in production. And flashback is much more powerful than simply having commit and rollback for DDL.
Today I was trying out some changes to my code (that runs outside of the database) and needed to do a quick test that would delete some rows.

In the past, your basic support for postgres has been mail groups. I’ve never been a big fan of mail groups. You have to subscribe to the group and usually you get tons of stuff you don’t really want. Worst of all, you either have to sort through it or filter it to a folder. I never seem to be able to keep up with the stuff I am interested in due to the clutter.

EnterpriseDB has had forums for a while but recently combined the postgres and advanced server forums into a single location. GridSQL also has it’s own forum.

I’m in Baltimore this weekend at the Postgres East 2008 conference. I’m giving a presentation tomorrow on SQL/XML for developers. Below is the presentation and below that is the code used in the presentation.

Code to follow along:

If you aren’t using javascript, you can get download the presentation or the script from scribd.
Or, to make it easy, just grab this code:

Slide 25

CREATE TABLE EMP
(
  LAST_NAME text,
  EMP_ID integer NOT NULL,
  FIRST_NAME text,
  DEPT_ID integer,
  SALARY integer,
  CONSTRAINT EMP_pkey PRIMARY KEY (EMP_ID)
)
WITH (OIDS=FALSE);
ALTER TABLE EMP OWNER TO xmluser;

Slide 26

INSERT INTO
    EMP(
       LAST_NAME, EMP_ID, FIRST_NAME, DEPT_ID, SALARY)
    VALUES
      ('Blow', 1, 'Joe', 1, 10000),
      ('Head', 2, 'Helmut', 1, 12000),
      ('Jack', 3, 'Noe', 1, 12000),
      ('Hard', 4, 'Blow', 2, 20000),
      ('First', 5, 'Hugo', 2, 21000),
      ('Spaem',6, 'Kingoof', 2, 20000),
      ('Ventura', 7, 'Ace', 3, 35000),
      ('Nickleby', 8, 'Nick', 3, 400000),
      ('Budd', 9, 'Billy', 4, 99000),
      ('Cleaver', 10, 'Wally', 4, 100000) ;

Slide 29

SELECT xmlelement(name main, last_name) from emp;

Slide 30

SELECT xmlelement(name main, last_name),
       xmlelement(name main, first_name)
FROM emp;

Slide 31

SELECT XMLForest(last_name, first_name)
FROM emp;

Slide 32

SELECT xmlelement(name main,
       xmlforest(last_name, first_name) )
FROM emp;

Slide 33

SELECT XMLAgg(
  XMLForest(last_name, first_name) )
FROM emp;

Slide 34

SELECT XMLElement(name main,
       XMLAgg(XMLForest(last_name, first_name) ))
FROM emp;

Slide 35

SELECT XMLElement(name main, XMLAttributes(nextval('t_seq') AS rownum) )
FROM emp;

Slide 36

CREATE TEMP SEQUENCE t_seq;

SELECT xmlelement(name main, xmlattributes(nextval('t_seq') AS rownum),
       xmlforest(last_name, first_name) )
FROM emp;

DROP SEQUENCE t_seq;

Slide 38

SELECT xmlelement(name main,
       xmlforest(last_name || first_name AS fullname, salary) )
FROM emp;

Slide 39

SELECT xmlelement(name main,
       xmlelement(name fullname, xmlattributes(dept_id), last_name || first_name ),
       xmlforest(salary) )
FROM emp;

Slide 40

SELECT xmlelement(name main, xmlcomment('Each Name Goes Here'),
       xmlforest(last_name, first_name))
FROM emp;

Slide 41

SELECT xmlelement(name lastname, last_name),
       xmlelement(name firstname, first_name)
FROM emp;

Slide 42

SELECT xmlconcat(
             xmlelement(name lastname, last_name),
             xmlelement(name firstname, first_name) )
FROM emp;

Slide 43

SELECT xmlelement(name main, xmlconcat(
             xmlelement(name lastname, last_name),
             xmlelement(name firstname, first_name) ) )
FROM emp;

Slide 44

CREATE TABLE xmltab (col1 XML);

INSERT INTO xmltab (
  SELECT xmlelement(name main, xmlconcat(
             xmlelement(name lastname, last_name),
             xmlelement(name firstname, first_name) ) )
FROM emp);

Slide 45

SELECT * FROM xmltab;

Slide 47

SELECT xpath('/main/firstname/text()', col1) FROM xmltab;

Slide 48

SELECT textcol[1] FROM (
  SELECT xpath('/main/firstname/text()', col1) AS textcol
    FROM xmltab ) AS xmlsource;

Slide 49

select query_to_xml('select * from emp', TRUE, TRUE, 'scott');

Slide 50

select table_to_xml('emp', TRUE, TRUE, 'scott');

postgresql conference sql/xml presentation

This post isn’t really PostgreSQL specific. It also happens with MySQL and Ingres (and any other 32 bit driver).

I am running Vista 64 on one of my machines. I installed Postgres 8.3 and the ODBC drivers. When I tried to setup an ODBC connection, the windows ODBC administrator program ODBCAD32.exe did not list Postgres. I noticed that it also did not list MySQL or Ingres even though I knew I had installed drivers for those databases also.

After a bit of research, I tracked down the issue. My ODBC drivers did in fact install. Microsoft, in all their wisdom, has 2 versions of odbcad32.

I just read this in InfoWeek: Database Survey Gives Oracle The Lead In All 13 Categories

The survey compared Oracle with DB2, MySQL, Informix Dynamic Server, PostgreSQL, Microsoft SQL Server, and Sybase Advanced Server Enterprise.

The significant parts of that are that Postgres was included (we don’t see enough of that) and that Postgres performed exceptionally well.
Some important quotes:

In a few categories, the open source system, MySQL, trailed most of the five commercial systems with which it was compared. But high user satisfaction in several categories indicates thatSun Microsystems (NSDQ: JAVA) may have gotten its money’s worth when it paid $1 billion for the database’s parent company, MySQL AB, last month.

I’m just back from a brisk walk around the Marriott World Center Resort in Orlando, FL.

What an amazing place! It is incredibly huge. I asked “How big?” when I was checking in and it has 2004 rooms, 265,000 square feet of meeting space, multiple pools (including some with waterfalls), at least one hot tub and a golf course. Oh yeah, and an alarm clock that doesn’t work. That’s my story and I’m sticking to it.

I’m here manning the EnterpriseDB booth with Derek Rodner and, my boss, Jim Mlodgenski. Jim is giving a presentation on Friday. We’re giving away copies of my book.

Set aside March 11 at 6pm for the first meeting of the Suncoast Postgres User Group. The SPUG serves Tampa, St Pete, Lakeland, Bradenton and the rest of central and western Florida.

You can get additional information at http://pugs.postgresql.org/spug

postgresql postgresql

Postgres 8.3 is out and it contains plenty of new and improved features. Some of my favorites are: sql/xml support, text search, autovacuum improvements, performance improvements (significant) and some additional SQL changes.
You can read the press release.
You can also check out the feature list or review the simpler feature matrix which compares all of the versions since 7.4.
You may also want to read the release notes.
You can read about the release in the press by following along with the discussion at postgresql.org.

postgresql new release

There is a new wiki comparing MySQL to PostgreSQL. Because it’s a wiki, hopefully it can be kept updated so that it’s current AND accurate.
The wiki is MySQL vs PostgreSQL.
Personally, I’d like to see this grow into a universal comparison site that the community could keep updated.
LewisC

There is a new wiki comparing MySQL to PostgreSQL. Because it’s a wiki, hopefully it can be kept updated so that it’s current AND accurate.
The wiki is MySQL vs PostgreSQL.
Personally, I’d like to see this grow into a universal comparison site that the community could keep updated.
LewisC

postgresql mysql

I posted this question on my Postgres Blog. Send me a note if you would be interested.

LewisC

oracle enterprisedb

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.

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.