PostgreSQL 8.3 Features To Be Available After Labor Day

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. That doesn't make a lot of sense to me as they are not releasing SPL (their version of PL/SQL) to be included. I guess it's being included because it also supports PL/pgSQL (the postgres procedural language). Bitmap indexes are not making it into this release. That would have been nice for larger installations like data warehouses. Not mentioned in the article but will be there is SQL/X support. I am a huge user of XML and SQL/X makes life so much easier when generating XML. I wonder when XQuery will be supported?

postgresql release 8.3 xml

Nice Little Cheat Sheet

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. The author also includes a pretty long list of cheat sheets available on other sites. LewisC

postgresql cheatsheet

What is included in EnterpriseDB Postgres?

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
• Graphical administration and monitoring (pgAdmin III and phpPgAdmin)
• Replication (Slony-I)
• Geospatial information server (PostGIS)
• Comprehensive documentation

They are also offering support, documentation and forums. LewisC

postgresql enterprisedb

Create a Partitioned Table In PostgreSQL

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. To actually use the new partitions, we need to write either a PostgreSQL Rule or a trigger. I like triggers personally. I'll create the trigger function. I'll have a separate IF for each type of operation (tg_op) and an IF for each partition within that operation. You can simplify this with dynamic SQL but I will save that for a future posting.

CREATE FUNCTION sales_range_handler() 
  RETURNS TRIGGER AS $$
DECLARE
BEGIN
  IF tg_op = 'INSERT' THEN
    IF new.sales_date < cast('2000-02-01' as timestamp)
    THEN
      INSERT INTO sales_jan2000
         (salesman_id, salesman_name, sales_amount, sales_date)
        VALUES (new.salesman_id, new.salesman_name, 
                new.sales_amount, new.sales_date);
    ELSIF new.sales_date < cast('2000-03-01' as timestamp)
    THEN
      INSERT INTO sales_feb2000
         (salesman_id, salesman_name, sales_amount, sales_date)
        VALUES (new.salesman_id, new.salesman_name, 
                new.sales_amount, new.sales_date);
    ELSIF new.sales_date < cast('2000-04-01' as timestamp)
    THEN
      INSERT INTO sales_mar2000
         (salesman_id, salesman_name, sales_amount, sales_date)
        VALUES (new.salesman_id, new.salesman_name, 
                new.sales_amount, new.sales_date);
    ELSIF new.sales_date < cast('2000-05-01' as timestamp)
    THEN
      INSERT INTO sales_apr2000
         (salesman_id, salesman_name, sales_amount, sales_date)
        VALUES (new.salesman_id, new.salesman_name, 
                new.sales_amount, new.sales_date);
    END IF;
  ELSIF tg_op = 'UPDATE' THEN
    -- Do the same for update
    NULL;
  ELSIF tg_op = 'DELETE' THEN
     -- Do the same for delete
    NULL;
  END IF;
  RETURN NULL;
END;
$$   LANGUAGE plpgsql;

Then I create the actual trigger:

CREATE TRIGGER sales_range_handler_trg 
  BEFORE INSERT OR UPDATE OR DELETE 
  ON sales_range
  FOR EACH ROW 
  EXECUTE PROCEDURE sales_range_handler();

Finally, I do an INSERT:

INSERT INTO sales_range (salesman_id, salesman_name, sales_amount, sales_date)
  VALUES (1, 'Lewis', 10.25, cast('2000-03-15' as timestamp) );

If you select from the main table, you will see one record. That record is actually being selected from the MAR2000 table. You can select directly from that table and see that the record is there. To optimize your partitioned queries, check out Constraint Exclusion. LewisC

postgresql partitioning partition plpgsql

10 PostgreSQL versus Everything Else Comparisons

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 have also found that PostgreSQL is most often compared to MySQL and not to one of the large commercial databases such as Oracle or DB2. Update: August 27. A new comparison: PostgreSQL vs Firebird, August 2007 (and hopefully it will remain updated)

1. PostgreSQL or MySQL - Feb 15 2005, a fairly nice comparison actually.
2. PostgreSQL vs. MySQL vs. Commercial Databases: It's All About What You Need - DevX April 12, 2004
3. PostgreSQL vs MySQL: Which is better?, DatabaseJournal Dec 16, 2003
4. PostgreSQL vs. SQL Server: PostgreSQL is right for the Microsoft stack, SearchEntepriseLinux May 15, 2006
5. PostgreSQL vs. Oracle: Users speak out, SeachOracle April 6, 2006
6. PostgreSQL vs MySQL with Rails, June 18, 2005
7. Oracle 10g vs PostgreSQL 8 vs MySQL 5, This one I wrote, Aug 22, 2005
8. Firebird vs Postgres, Forum Post, April 23, 2002
9. PostgreSQL vs. MySQL (Web Techniques, Sep 2001), Dr Dobbs, Jan 1, 2002
10. MySQL vs. PostgreSQL, Aug 9, 2005

Enjoy, LewisC

postgresql list comparison mysql firebird oracle

There is a New PostgreSQL Blog Aggregator In Town

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. Take care and check out the Squidoo All About PostgreSQL page! LewisC

postgresql squidoo blog aggregator database yahoo pipes

Learn PostgreSQL: Newbie Questions

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. He had several reasons but sumed it up as, "...the true features included in 5.1 (as of this moment) are nothing to write home about. The InnoDB stuff is highly advocated but it has its own set of issues,...".

So he was posting some questions to make sure he was heading in the right direction. You can read the actual email here. I am going to sum up some of the emails for you below but if these questions interest you, I would suggest reading the entire thread.

The poster asked four questions but for this entry, I am going to concentrate on his first question. I may add additional entries later to cover his other questions.

His first question regarded performance on DML around a hot table. 10k inserts/day, 500k selects/day and 1M updates/day. He had heard the wrapping the statements in a BEGIN TRANSACTION; and COMMIT; would increase the performance. This generated many great responses.

Here is a partial list of comments and considerations from this question. Many of these I knew but there were a few nuggets that were new to me. Some of these are exact copies of responses and some are slightly paraphrased by me, but the knowledge is actually coming from others.

• A transaction is a bunch of queries which you want to all get committed or aborted together. The expensive step in Postgres is the COMMIT. Postgres does an fsync which forces the data to go to disk (if you're using good hardware, and don't have fsync=off). That takes from 5-15ms depending on how much data and how fast your drives are.
• Grouping more work into a single transaction makes the delay for the fsync at COMMIT time less of a problem. Also having more connections (but not too many, more than a few per processor is probably not helping, more than 100 and it's probably slowing you down) also means it's less important since another process can do some of its work while you're waiting for the fsync.
• You want "fsync=on" on any machine which holds data you care about. And you want hardware which doesn't lie to you so that "fsync is finished" really means the data is on-disk. Else PostgreSQL cannot ensure ACID compliance.
• psql runs in autocommit mode by default. If you want multiple queries in a transaction you have to issue a BEGIN statement. Drivers may do various things by default.
• The rule is, if any query within the transaction errors, then all queries within the transaction are rolled back.
• Build your application around the application needs first, then later look at how to optimize it.
• Remember the two rules of optimization: 1) Don't 2) (for experts only) Don't yet
• The only case where you should consider batching together transactions like that is if you're processing a batch data load of some kind. In that case you have a large volume of updates and they're all single-threaded. But usually in that case you want to abort the whole load if you have a problem.

All in all some really good info. If you are picking up Postgres for the first time, I would suggest you sign up for the general mail list and lurk for a while or just head over to the link above and browse.

LewisC

postgresql newbie question learn

THE Postgres Resource Center

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. The site also hosts community-based forums, enabling EnterpriseDB Postgres users to interact with peers and leverage the collective experience of the EnterpriseDB Postgres community.

The forums are still fairly new so there isn't a lot of traffic yet, but that will come with time. There is a forum for news, postgres beginners, postgres on windows, postgres on linux and postgres on mac. Check it out. LewisC

postgresql support enterprisedb forum

PostgreSQL Books

EnterpriseDB Postgres - Enterprise Grade Database

EnterpriseDB announced at LinuxWorld that they are releasing EnterpriseDB Postgres. This is the open source postgresql with a few items added to make it more of an enterprise class database. This version of Postgres is targeted to new and occassional users. EnterpriseDB is hoping for wide spread acceptance of PostgreSQL which in turn may mean additional acceptance for EnterpriseDB. EnterpriseDB Postgres add an easy to use graphical installer, GUI admin tools (web-based), pgAdmin III (a GUI IDE), replication, forums, and downloads for Linux and Windows. One feature that has had a lot of discussion on the Postgres email list is the installation in Linux. Peter Eisentraut blogged about it on his personal blog, EnterpriseDB and the Professional Grade . Advocates are really pushing PostgreSQL into the mainstream and EnterpriseDB is making one of the biggest pushes. EnterpriseDB also offers support and training for PostgreSQL.

postgresql enterprisedb

Not Much PostgreSQL News Lately

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