Joshua Drake's PostgreSQL Blog

Lots of people have been asking me why I am not going to PgCon. Here I list the reasons, just so I can stop answering:

1. I would rather spend money on outreach. I already talk to everyone going almost everyday .
2. It is during my daughters birthday, and not even an invite from god (Tom Lane) would get me to miss that.

I wish PgCon all the success in the world. I am more than sure that my not being there won't effect that .

See everyone at the PostgreSQL Party PostgreSQL Party and OSCON!

So what the heck is LedgerSMB? It is a PostgreSQL based (only) Open Source accounting system. It is a fork of SQL-Ledger making gratuitous use of security and usability fixes over the aforementioned product.

Take a look... it is coming along nicely: http://www.ledgersmb.org/

We are planning a PostgreSQL party for the weekend before OSCON in
Portland Oregon. The 22nd is a Sunday, with OSCON beginning on the 23rd
which is Monday. Although the exact venue has not been decided it will
likely be a hotel near the convention center.

We are also reaching out to other communities, such as Python, Django,
PHP, LedgerSMB etc... If you are a FOSS member with ties to a community
that utilizes or supports our database, you are invited!

Some outstanding questions:

There will be food.
There will be adult entertainment of the liquid variety.

If you are interested in attending please submit an RSVP to:

register <@> postgresqlparty.org

Full Name
Email
Company (if you are representing)

Would be appreciated.

Sincerely,

Joshua D. Drake

P.S. There might be a nominal door charge (5.00) to help offset costs
any excess would of course be donated directly to the PostgreSQL project.

The following is a list of qualifiers, that may make you a geek. In the tradition of you might be a redneck.

Continue reading "You might be a geek if:"

So I got pinged today on IRC to help a customer. They have an active database that all of a sudden was throwing exceptions to PHP. After some initial diagnosis I noticed that the exception was really PostgreSQL running out of connections because of an EXCLUSIVE LOCK. For the life of me I couldn't think of what this customer would be doing with an EXCLUSIVE LOCK so....

Continue reading "It all began with a simple TRUNCATE"

Marc Fournier said it best:
"Woo hoo, you've succeeded in doing something that I've never had knowingly
happen in, got, 10 years ... someone is advocating blacklisting a
PostgreSQL.Org related web site :("

It all began with a simple enough email:

Hello,

I am contacting you because you are listed as a member of a project at
http://gborg.postgresql.org . Although Gborg has served the PostgreSQL
project well over the years, it is time that we begin to think about
shutting the service down.

The PostgreSQL project volunteers have another service at
http://www.pgfoundry.org that you are welcome to use free of charge.

Although Gborg is not going away tomorrow, we will need your help as
community members to make this transistion as painless as possible. At
this time we would like to ask that project leaders do the following:

1. Register your projects at http://www.pgfoundry.org/

2. Invite your project members to join PgFoundry.

3. Let us know on this list, what, if any parts of your Gborg project
will need to be migrated.

It is possible to keep your mailing lists (albeit with a new address),
mail list archives and your CVS repository without any serious trouble.

However, tickets may be a little troublesome and we will likely ask you
to help make sure that your tickets are migrated. This may have to be
done by hand and for this we apologize.

This migration will likely require some downtime for your project and we
do plan on doing this in phases. E.g;

Register new project
Move CVS
Move Mailing lists

Thank you for supporting the PostgreSQL project and we hope that you
enjoy using pgFoundry.

Sincerely,

Joshua D. Drake

Simple enough right? Well that email went to 4500+ email addresses. Ooops.
To be fair... all of those email addresses were pulled from the Gborg database as
people who did at one time register as a Gborg member.

Luckily most bounced and the actual number of complaints was small but as usual, the complaints
are the loudest and productivity drops to nothing. To top it all off, one of the complaints
was from one of the people that I respect the most in this community, Tom Lane.

After the back and forth of several contentious, hot to the touch emails, I relent.

So, I eat crow and I sit here. I am 35 pounds lighter, behind a desk of cheap plastic, apologizing
for sending out the email. Please know that my intentions were good and that I was
hoping that the Gborg community would want to be a part of the migration. Perhaps they
are and my delivery was wrong?

Joshua D. Drake
PostgreSQL SPI Liason

Last week I wrote about a problem with PostgreSQL date handling. So this week I thought I would return the favor to MySQL. Slashdot is having an outage today on some of their boards, upward of 3.5 hours because they have to reindex a 16 million row table. 3.5 hours to reindex a 16 million row table? What?

On PostgreSQL 8.1, on a 20 million row table using a modest 2 CPU, 4 GIG of ram machine with two SATA drives, the index creation takes:

46 minutes.

If you use PostgreSQL 8.2, you can create the index CONCURRENTLY, thus no outage.

If you were to add Tsearch2+GIN to the mix, I bet Slashdot would not only perform better, scale better and provide a better search mechanism, they wouldn't have the type of problems they are having now.

Note that I am not bashing Slashdot. Instead I am noticing a place where they could provide better service to their readers. The recent migration to PostgreSQL 8.1 by Sourceforge (owned by the same company) has proven a huge boon in performance.

Cascade Locks, Oregon -- Command Prompt, Inc., the PostgreSQL company, today announced that it will add the Slony PostgreSQL replication software to its list of officially support PostgreSQL projects. Slony is the leading Open Source replication solution for PostgreSQL. Continue reading "The PostgreSQL Company: Command Prompt, Inc. adds official Slony support"

This was brought to my attention late last night:

postgres=# create table timetest (test date);

CREATE TABLE

postgres=# insert into timetest

postgres-#            values (to_date('4714-01-27 BC', 'YYYY-MM-DD BC'));

INSERT 159911991 1

postgres=# select '4714-01-27 BC'::date;

ERROR:  date out of range: "4714-01-27 BC"

postgres=# select test from timetest;

     test      

---------------

 4714-01-27 BC

(1 row)

Continue reading "Ahhh! PostgreSQL Gotcha's"

Since OSCON:

Josh Berkus has officially been elected to the SPI board and appointed Treasurer.

Joshua Drake (me) has assumed his duties as PostgreSQL SPI Liason per the Fund Raising Group

Robert Treat (not me) has assumed his duties as PostgreSQL SPI Board Advisor per the Fund Raising Group. Yes he actually bothered to show up to the first SPI Board meeting and regardless of what he says, it was him pinching Decibull's nipples.

Command Prompt (you know them right?) has hired DarcyB. Don't worry we are not going to stop him from working on Slony-I. In case you hadn't noticed, we are kind of pro-FOSS.

LinuxWorld Expo West is coming up! PostgreSQL will have a booth. Make sure you stop by.

Usenix has invited PostgreSQL.Org to LISA in December, contact me if you are interested in coming.

Lastly Replicator 1.7 is finally (well mostly) done and we are now testing. Expect to see it very, very soon for 8.0 and 8.1.

With the addition of DarcyB to our team, I should be able to focus even further on PostgreSQL advocacy and community efforts. I will be putting a great deal of my time into advocacy and education. Along with Sales, some very part time consulting and running around the country doing training. I already have 4 classes setup for fall which may turn into 6.

With the high wholesale oil prices, the media has been having a field day with how much money the big, evil, satan worshipping oil companies have been making. At first I started buying into the hype... Why should I be paying 2.95 a gallon!!! That is outrageous! Of course in the back of my mind I always knew how much Europe pays for their gasoline.

This past week, the largest oil companies in the world reported 15.7 Billion dollars in profit. The combined revenue of these companies was 191.5 Billion. That is a lot of zero's and Congress in their, "Oh my god, it is an election year -- let's see how many ways we can lie to people to make them believe we give a crap." mentality has jumped on the Oil company "Scandal" almost as fast as the press. President George Bush stopped shipments to the US Oil reserve to "ease" some of the pain.

The remark he made was, "Every little bit helps" (or something like that). To be true every little bit does help, but he wasn't trying to help the gasoline flow, he was trying to give a public perception that the U.S. government was doing what it could to help the little people.

Let's get back to the numbers shall we?

191.5 Billion dollars revenue
175.8 Billin in expenses
15.7 Billion in profits

That is a combined approximate 12.19 percent profit.

So I ask, where are the massive profits? To give you a comparative:

Command Prompt had approximately the same percentage of profit Q1 of 2006.

But I don't want to pay 2.95 a gallon for gasoline!!! Well, neither do I. So lets take a look at where the costs of gasoline come from.

I paid 2.89 a gallon in Oregon yesterday to fill up 18.5 gallons. It came to just over 50 bucks. But how much of that 2.89 is the cost from the big, bad oil company?

Total gas price: 2.89
State taxes: .24
Federal Taxes: .18
Total Taxes .42

That's right, 42 cents in taxes. To be fair, those taxes are what pay for US roads and it is one of the few taxes that is a direct result tax. E.g; the tax I pay on this good, directly applies to the cost of using the product.

The state of California has made all coastal lands it controls to to be closed forever to petroleum exploration and development. The US Congress has been unable to successfully allow drilling in the Alaskan Arctic reserve. In the end it is the US Citizen that is currently driving the price of gasoline, we are addicted to oil but we don't want to pay the price of being addicted to Oil.

In closing I am not an OIL loving monkey. My family drives fuel efficient vehicles, we conserve water, and grow a lot of our own food (when in season). We recyle and I am personally a firm believer in alternative fuels, bio-diesel being my current favorite. I also agree with California's decision and I don't want us drilling in the Alaskan Artic reserve.

I just get sick and tired of all the whining.

In closing, the US imports the majority of its oil from Canada, Mexico which last I checked is nowhere near any OPEC country.

Joshua D. Drake

Some of you may have noticed that Command Prompt has been working hard on MammothPostgreSQL. Mammoth PostgreSQL is the rebirth of our commercial Mammoth PostgreSQL distribution. It is 100% Open Source and the only complete and supported PostgreSQL distribution available.

We currently have packages for most of the RedHat/Fedora distributions. Ubuntu and Debian are a couple of days away and DarcyB is helping with FreeBSD.

Mammoth PostgreSQL

I have also been following the recent Verizon chest beating about making people companies like Google pay a surcharge for the bandwidth they use. You can see the articles mentioned in various locations including here, and here.

When I first read about this theory, frankly I thought Verizon and its officers were chewing on the live end of some of their phone cables. However as I consider it futher, I think I understand (even though I still don't agree) where Verizon gets there thought process.

Here is some background.

Verizon has one of the largest backbones to the Internet in the United States. Thus Verizon is carrying a great deal of Google's Internet traffic. The common argument against Verizon in this is that I as the Internet user pay for my Internet connection, and Google pays for their Internet connection, thus Verizon should not be able to charge either just to access each other.

Now take into the following:

Joshua Drake purchases his Internet from Sprint.
Google purchases their Internet from TimeWarner

Where is Verizon in this picture? Well there are at about hop 8-10. In other words although I don't pay Verizon, and Google doesn't pay Verizon yet we are both using Verizon's network.

This is where peering agreements come in. Basically a couple of big IP network providers get together, spread butter on each others toast and promise to use approximately the same amount of bandwidth over each others links. As they will be using approximately the same amount of bandwidth, it is a partnership and no dollars are exchanged, or if they are exchanged they are approximately the same amounts.

If Verizon has one of the largest networks in the US, they are carrying a one of the largest amounts of traffic to Google and not getting paid for it.

Where the logic falls apart.

Google is not a Verizon customer (Well they might be I don't know, lets assume they are not). Since Google is not a Verizon customer then Verizon needs to snot sniffing the sink cleaner and start charging their customers for the bandwidth that is being pushed.

If a good portion of Verizon bandwith is being used by people seeking Google then Verizon needs to be charging the connection links that are requesting the information not going after Google.

With the recent release of the PostgreSQL Source Brower I am noticing new things that we can do with this. For example using an RSS reader (Thunderbird for example) I can subscribe to:

http://www-new.commandprompt.com/projects/public/pgsql/log/trunk?limit=100&mode=stop_on_copy&format=rssRSS

Which is the current Trunk revision log in RSS format. I can then, using Thunderbird select a specific revision, click foward and email the link to that revision with questions that I or another developer may have about the specific change.

As with most of the PotgreSQL community I have been following the (Oracle + InnoDB - MySQL) = Oops pretty closely. Here are some of the current
stories abound:

Ittoolbox (our own Josh Berkus)
ZDnet
InfoWorld
Ovum
Webpronews

I am sure this will be driven into the ground. So the question is, what do people think about the possibility of a MySQL that includes the PostgreSQL storage engine?
It could be an interesting twist to a long running opposing relationship.

As I continue to push forward with PostgreSQL: The Definitive Guide, I continually realize just how much there is that needs to be documented for PostgreSQL. I don't get to write articles as much as I would like anymore, between the book, customers and of course family there just isn't that much time.

Here are three articles I have written in the past:

PostgreSQL + Psycopg2
TSearch2
Savepoints

My original goal for the new book was 1000 pages. We may very well get past that but I don't know if O'Reilly will let me as it will delay the book considerably. My current chapter is Database Maintenance and it is amazing to me how much has changed (and hasn't) since Practical PostgreSQL. The book is going to be fairly detailed and will have chapters on Slony-I, Replicator, plPHP, plPerlNG, plJava, and plPGSQL. We may also include plRuby if we can get it ported to 8.1 before the book goes gold.

Oh and just on a side note... If you like Ruby on Rails, I strongly suggest Django. It uses mod_python and is very similar but you get to use Python instead.