::Planet PostgreSQL::

Friend from my previous employer told me that plans of execution of prepared statement, and the same statement run “as it” are different. Well, I checked and this is what I found (it’s not shocking, it’s actually quite obvious, but You have to think about it for a while to “get it”). Let’s assume we have pretty [...]

May 10, 2008 07:09 PM

Hi, all!!

It's a great pleasure to say I'm going to attend PGCon2008 this year!! :)

I'm going to Canada tomorrow (May, 11) and hope to enjoy Ottawa and Canada before conference starts.

If you live in Ottawa and would like to talk about Npgsql, Mono, .Net or anything else, please drop me a mail.

About Npgsql, I posted on npgsql-devel list about an Npgsql2 beta4 release this week. But unfortunately it won't be possible. Josh is fixing some last bugs and will be doing a release next week. Sorry for any problems this announce may have caused.


So, that's it!

Stay tuned!

May 10, 2008 07:15 PM

I have just come back home from a very long day at PyCon2, the second edition of the Italian Python Conference which has been held in Florence.
ITPUG, the Italian PostgreSQL Users Group, has been invited by the organisers of the conference, the national association for the promotion of Python (Associazione Python Italia).

It's been a terrific chance for us to setup a small booth and to show our new promotional flyers (in Italian) and our brand new PostgreSQL stickers, made by our fellow Valentino.
The technical liason between PostgreSQL and Python is very strong, and we have found so many people that were eager to hear news about PostgreSQL and about our association.
We are looking forward to return the favor to the Python association during our European PostgreSQL Day, which will be held in Prato on October 17 and 18.
I believe it is important to strengthen this sort of links between non profit organisations that share the same principles and goals of free software promotion. I am sure there will be more chances to collaborate and cooperate with our fellow "Pythonists".
This is another important step along the short life so far of ITPUG, the non profit organisation that has been founded in November 2007.
Now we can concentrate on our next conference: next week, ITPUG's Chris Mair will deliver a workshop on PostgreSQL at the "National Conference about Free Software".
Today, Simone Martelli setup the italian website of PGDay 2008. Another small step for a better PostgreSQL community!

May 10, 2008 05:48 PM

One of the great selling points of PostgreSQL is its pluggable PL language architecture. MySQL is known for its pluggable storage and PostgreSQL is known for its pluggable PL language architecture. From Monty's notes on slide 12 looks like MySQL may be working on a pluggable PL language architecture of their own. The most common of these languages are the all-purpose languages SQL and C (these are built-in and not really PLs like the others, but we'll throw them in there), PLPgSQL which is also built-in but not always enabled, PL/Perl, PL/Python, and the domain specific languages PL/R, PL/SH and gaining popularity Skype released PL/Proxy. There are others in the family such as PL/Tcl, PL/PHP, PL/Ruby, PL/Scheme (a dialect of Lisp), PL/Java, PL/Lua and PL/LOLCode (for kicks and as a reference implementation. Think of LOLCode as PostgreSQL Pluggable PL equivalent of MySQL's BLACK HOLE storage engine.) .

The other interesting thing about the PostgreSQL PL language architecture is that it is a fairly thin wrapper around these languages. This means the kind of code you write in those languages is pretty much what you would write if you were doing general programming in those languages minus some spi calls. Since the handler is a just a thin wrapper around the environment, the language environment must be installed on the database server before you can use the PL language handler. This means you can have these functions utilized in your SQL statements and you can write in a language you feel comfortable with if you can get the darn PL compiled for your environment or someone has already kindly compiled it for your environment or that it is even compilable for your environment. The pluggable PL architecture means you can write a PL Handler for your favorite language or invent your own language that you can run in the database. In the end the barrier between code,data, and semantic constructs is more of a constraint imposed by compilers. If you have any doubts about the above statement, you need only look at some javascript injection attacks to bring the statement home. One of my fantasies is developing a language that morphs itself, that utilizes the database as its morphing engine and its OS and that breaks the illusion of data being data, code being code, and lacks rigid semantics. Of the languages we have worked with, SmallTalk comes closest to a language that satisfies these ideals and Lisp to a much lesser extent. Lisp lacked the semantic elegance of SmallTalk among other things.

Most people are used to having their procedural language push their data around. PL code living in PostgreSQL allows your data to push your procedural code around in a set-based way. This is a simple but pretty powerful feature since data is in general more fluid than code. For interpretated/just-in time compiled languages it can live in the database, for compiled it has to call compiled functions.

May 10, 2008 10:58 AM

The End of Moore's Law

I was reading "The Problem with Threads" by Prof. Ed Lee, and noticed the following claim right on the first page:

Many technologists predict that the end of Moore’s Law will be answered with increasingly parallel computer architectures (multicore or chip [multiprocessors], CMPs) [15].

This quote confuses me, because, to the best of my knowledge, Moore's Law has not ended, and the industry's move to multicore/manycore processors is not directly related to the imminent demise of Moore's Law. Moore's Law is the claim that transistor density in integrated circuits approximately doubles every two years. As far as I know, that remains basically true for the time being, and current speculation is that it will continue to hold for at least 10 years.

What is driving the move to multicore designs is that we can no longer effectively use those extra transistors to increase the speed of a single sequential instruction stream. Ramping up clock speed increases heat dissipation, and doesn't improve performance very much if memory latency doesn't significantly change. Techniques like caching, pipelining, and superscalar execution help, but only to an extent. Hence the move to multicore designs and chip-level parallelism.

That said, I'm definitely not a hardware guy, and doubtless Prof. Lee has forgotten more about processor design than I am ever likely to know. And when Moore's Law ends, that may well encourage the multicore trend even more—but my understanding is that the eventual demise of Moore's Law and the current move to multicore architectures are not directly related. I'm curious to know if I'm mistaken.

(As an aside, text quoted above cites "Multicore CPUs for the Masses" in ACM Queue as support for the claim that the industry is moving toward multicore designs. While that is true, the article makes no mention of Moore's Law.)

May 10, 2008 06:02 AM

Today I will describe 2 new patches which modify psql: First patch was written and committed by Bruce Momjian. Commit message describes the thing pretty simply: Have psql output tab as the proper number of spaces, rather than \x09. What it means? In 8.3 and earlier when Your string contained tab characters it was printed as “\x09″, thus rendering [...]

May 09, 2008 05:12 PM

Index bloat can be a major pain in heavy OLTP databases. With Reindex being a blocking operation in Postgres, you can not reasonably reclaim index bloat in a large database by hand without going offline. Thus enter concurrentReindex.php. This PHP cli script will concurrently reindex all non-unique, non-system catalog indexes in a given database. I have only tested this against a Postgres 8.2 database, but it should work in theory against 8.2 or higher. Feedback, enhancements and patches welcome.

May 09, 2008 03:57 PM

MySQL has this nifty/annoying feature/bug of special data type “TIMESTAMP”. It is like a DATETIME, but it gets automatically updated whenever you modify the row. I’ll try to add the same feature to PostgreSQL. This is how it works in MySQL: mysql> create table test (x varchar(10), y timestamp); Query OK, 0 rows affected (0.03 sec) mysql> insert into test [...]

May 08, 2008 03:16 PM

In prior articles of this series, we covered the following:

1. Showcasing REST in PostgreSQL - The PreQuel we went over what REST is and isn't
2. REST in PostgreSQL Part 1 - The DB components we loaded the Pagila database and created a db plpgsql search function to support our rest server service
3. REST in PostgreSQL Part 2 A - The REST Server service with ASP.NET we demonstrated a REST web service using Mono.NET, MS.NET both in C#, VB.Net/Monobasic
4. REST in PostgreSQL Part 2 B - The REST Server service with PHP 5 we demonstrated a REST web service using PHP 5

May 07, 2008 07:45 PM

I briefly mentioned in my earlier post that OpenSolaris binary is probably just a chapter of a book. Lets look more at it

[Read More]

May 07, 2008 05:49 PM

The May commit-fest is going well, with lots of wiki and committer activity.

May 07, 2008 04:00 AM

PostgreSQL 8.3 brought us many great new features. Among them were concurrent autovacuum, enums, tsearch in core. On of the changes though made a lot of people dislike 8.3. It was removal of implicit casts. What do I mean? In PostgreSQL 8.2, You could easily do something like this: # select 1::int4 = '1'::text; ?column? ---------- t (1 row) In PostgreSQL 8.3 [...]

May 05, 2008 11:11 AM

3 new functionalities from 3 people: First, patch from George Gensure, committed by Tom Lane. New function - pg_conf_load_time() returns timestamp when PostgreSQL reread its configuration last time. In trivial situation it will be the same as pg_postmaster_start_time(), but if someone issued pg_ctl reload Then it will differ: => psql -c "select pg_postmaster_start_time(), pg_conf_load_time()" pg_postmaster_start_time [...]

May 05, 2008 07:32 AM

There are lots of cool things about PostgreSQL, but one of my favorites is Joshua Tolley’s PL/LOLCODE implementation. He says he’s about to have more time to work on it, so watch out for updates.

In the meantime, I gave a presentation on lolcode to the Portland PerlMongers in April:

May 05, 2008 06:24 AM

I’m still buzzing from BarCampPortland. I loved every minute of it, and managed to learn a ton from all the amazing people in Portland. I met a ton of new people, and started several more projects

Over the course of two days, I ran two sessions. The first was about PostgreSQL:

We had a fantastic discussion, totally filled the room up with people curious about what PostgreSQL can do. We had several requests for MySQL -> PostgreSQL “rosetta stone” documentation.

The second session was How to raise or eat chickens sustainably:

We had an incredible conversation - ranging from getting and raising chickens for the first time, to digging up your lawn and planting food, to gentrification, to obsessive local eating.

I also attended these others: DSLR, Bikes and Geeks Collide, Project Management, My Other Thing, Women in Tech, and a session at WordCamp.

I think it’s finally time to go to sleep. Check out a ton of pictures. Thanks so much everyone who attended, led sessions and volunteered this weekend!

May 05, 2008 06:23 AM

PostgreSQL Weekly News - May 04 2008
The May commit-fest has begun, and as predicted, is going even more
smoothly than March's.
Continue reading "PostgreSQL Weekly News - May 04 2008"

May 05, 2008 05:03 AM

Today another two new additions to PostgreSQL - as You can see may commit-fest seems to work pretty good First change is new functionality in pl/pgsql: RETURN QUERY EXECUTE. Pavel Stehule, and Tom Lane committed patch which lets You use new command, or to be more specific extend existing command. “RETURN QUERY” was added in PostgreSQL [...]

May 03, 2008 12:52 PM

Man... I used to cry every time I thought about the beauty VMWare is about virtualization. But now, I have another one to cry for: VirtualBox.

Why?

First of all it is OpenSource. Only this is already a motivation by itself.

Second: It flies!!! I know VMware is very fast, but VirtualBox is a piece of magic. I don't know how to describe it. It is very fast, runs very smoothly and consumes very little resources!! I think there is no need for saying that I'm very excited with VirtualBox, is there? :)

Third: Starting with 1.6.0 It has support for Seamless integration for GNU/Linux and Solaris guests!!

Man!! Seamless integration is the next step in the way of virtualization... It is really nice!!

I'm doing some tests here and it works very well!

I'd like to thank you VirtualBox developers. And I also would like to say that Sun did a big step in the right direction buying VirtualBox. I hope many more features come and that 1.6 version is just the first of many excellent releases!

UPDATE:
P.S.: Please, don't take me wrong. I'm a big fan of VMWare, I even have a 4.x license I bought to be able to compile Npgsql on MS .Net. But now that I have VirtualBox, I think that license will not be used anymore soon.

May 02, 2008 09:20 PM

There has been a lot of talk lately about PostgreSQL and what MySQL can learn from the PostgreSQL clan. We would like to look at the reverse of that. This article is a bit of a complement to Joshua Drake's What MySQL (and really, Sun) can learn from PostgreSQL.

First of all a lot of staunch advocates of PostgreSQL wonder what exactly is it that MySQLers see in that beast of a database or as Martin Mickos likes to call it The Ferrari of databases?

May 02, 2008 08:16 PM

Hi all!

I'm right now looking for accommodations in Ottawa, Canada. I'm going to do a presentation about Npgsql at PGCon2008 and as I never went to Ottawa I don't know of any good place to stay. If you have any tips about cheap hotels, hostels or anything , please, let me know.

Thanks in advance.

Update: I'd really really thank you all of those who gave me tips about places in Ottawa. I already did my reservation and hope to see you there at PGCon2008!!

May 02, 2008 08:54 PM

SciDBMS

I noticed that the final report from the Science Database Research Meeting was released a little while ago. Worth reading if you're interested in how database technology can be applied to managing scientific data — they have some interesting ideas about both what problems need to be solved, but also how to develop those solutions into a product that scientists can use (via both an open source project and a startup company).

May 02, 2008 06:00 PM

Today, we have 2 new changes in psql. One of them is not actually new feature, but I decided to write about it anyway, because of its implications. First (which was actually committed second), is patch from Dickson S. Guedes, committed by Heikki Linnakangas Commit message was quite small, but it does explain what the new code [...]

May 02, 2008 03:41 PM

As I digg and slashdot my way through the weary set of tubes that connects us all, I have stumbled across a number of interesting (good and bad) posts in the last week. The first of which comes from our friends doing Ruby on Rails development.

May 01, 2008 07:23 PM

Yesterday, Alvaro Herrera committed patch written by Pavel Stehule which adds generate_subscripts(…) function. Commit message: Add generate_subscripts, a series-generation function which generates an array's subscripts. Pavel Stehule, some editorialization by me. This function is basically nicer way to iterate over array elements. For example, to convert array of integers to record set with these integers You would have to do something [...]

April 29, 2008 12:32 PM

I did quite a bit of work for 8.3 on closing holes that allowed improperly encoded data into the database, so it's now quite hard, if not impossible, to get Postgres to store invalidly encoded text data. However, I've never had to deal with that much in real life until today - luckily all my clients and work assignments have used encodings where that wasn't an issue. Today, on the other hand, my new client gave me a database dump to deal with that is in UTF8 and contains invalid data (the client currently uses 8.1.x - part of my job will be to get them to 8.3). "Just use iconv to remove the bad data'" the client told me.

Unfortunately, this doesn't quite work.

Iconv will remove byte sequences that don't obey the UTF8 sequence rules. The problem is, that's not enough to ensure that the sequence actually designates an actual UTF8 character. I found the following sequence left by iconv that Postgres quite rightly refused to allow: 0xf7b09280. I have no idea what it's meant to be. I suspect some client program simply took some input bytes and escaped it regardless of sanity. To understand why this is not valid, see the third table here: http://en.wikipedia.org/wiki/UTF-8#Description

So, if iconv leaves some bad stuff, how can I get rid of it? Enter the admin's Swiss Army chainsaw: Perl. A little experimentation found that this would work:


perl -spi.bak -e 's/[\xf5-\xff\xc0\xc1][\x80-\xbf]*//g' output-from-iconv


The resulting file loaded without error. So now I can go to work on solving the client's real problem

April 28, 2008 08:32 PM

I often get questions from PostgreSQL employers about where they should post jobs to hire a PostgreSQL DBA, designer or hacker. Here's some advice.

April 28, 2008 02:21 PM

People have asked us how to calculate running totals a number of times; not a lot but enough that we feel we should document the general technique. This approach is fairly ANSI-SQL standard and involves using SELF JOINS. In a later article we shall describe how to calculate moving averages which follows a similar technique but with some extra twists.

Note that the below examples can also be done with a correlated sub-select in the SELECT clause and in some cases that sometimes works better. Perhaps we shall show that approach in a later issue. We tend to prefer the look of the SELF JOIN though and in practice it is generally more efficient since its easier for planners to optimize and doesn't always result in a nested loop strategy. Just feels a little cleaner and if you are totaling a lot of columns (e.g number of items, products) etc, much more efficient.

April 28, 2008 07:49 AM

PostgreSQL Weekly News - April 27 2008
Simon Riggs proposed a way of implementing MERGE. Discussion is
on-going.

Continue reading "PostgreSQL Weekly News - April 27 2008"

April 28, 2008 05:53 AM

We would like to thank Jeff Crumbley of IILogistics for providing many of these steps and informing us that Microsoft has finally released a 64-bit OLEDB for ODBC driver.

For those who have not experienced the torture of this situation - let me start with a little background. First if you are running SQL Server 2005 32-bit and wished to create a linked server to a PostgreSQL server, everything is hunky dory. If however you had a SQL Server 2005 64-bit server, you ran into 2 very annoying obstacles.

1. Obstacle 1: There for a long-time was no 64-bit ODBC driver nor native driver for PostgreSQL. This obstacle was somewhat alleviated when Fuurin Kazanbai made experimental compiled 64-bit PostgreSQL ODBC drivers available which work for AMD and Intel based processors.
2. Obstacle 2: All looked good in the world until you tried this in SQL Server 2005 64-bit and low and behold - you needed a 64-bit OLEDB provider for ODBC to use it in SQL Server 2005 64-bit. Yes we waited patiently for years for this piece to be available. We still love you Microsoft. Then as Jeff Crumbley pointed out - Microsoft released an OLEDB 64-bit provider for ODBC in early April 2008.

April 25, 2008 05:28 AM

Well, I asked for a wiki almost three years ago, but it is finally here in official capacity. Thanks! In the few weeks of its existence, we have already seen great progress in documenting many development issues that were previously undocumented or hidden in strange places. I have started to keep my personal notes and todo items, which were previously spread around various files and pieces of papers, under User:Petere.

I have been observing the wikification in the Debian project for a few years now. I expect that in the near future, all of the contents relevant to developers will have migrated from the main web site to the wiki.

April 24, 2008 11:16 PM

I have signed up for the Ottawa Marathon on the weekend after PGCon. If anyone is still around on the Sunday, join me on or at the side of the track.

April 24, 2008 11:09 PM

First we'd like to thank Devrim of Command Prompt for working hard on making this new YUM repository available. In this article we will go over using the new PostgreSQL YUM repository for Redhat Fedora, Enterprise Linux and CentOS distros that is available at http://yum.pgsqlrpms.org/.

We are gearing the content of this article to the described user profile

• Person has SSH root access to their PostgreSQL box
• Person is new to using Red Hat Linux
• Person may be new to using Linux in general

April 24, 2008 07:30 AM

One common discussion we see on the postgresql mailing lists is that of how to track changes and do versioning of schema within a database. One of the common solutions offered is the idea of grabbing schema from the system catalogs, writing it to a file, and then committing that to svn. In theory you could write an elegant tool for such a task, but given the number of times someone has asked me to send them a copy of the script we use at OmniTI, I'm guess that even the hacky script we use will probably be helpful. To that end, I've finally gone ahead and put a public copy of our getddl tool up on labs. Be aware that the script has some issues; I've include a TODO of some items that I'd like to see improved, but it meets our current needs, so you're better off writing patches than waiting for a new release (;-)), but hopefully it will still be of some value.

• Getddl Project Page


• getddl.pl script

April 23, 2008 10:16 PM

Hi all!

FISL 9.0 was very, very nice!! I enjoyed very much to be able to talk to so many people about FLOSS!

I had the great pleasure to meet Josh Berkus and David Fetter! I took a picture with Josh I will post here later. Josh is a very nice person! I enjoyed very much to be able to talk to him. Unfortunately I talked almost nothing with David Fetter. I hope when I'm at PGCon I can talk to him a little bit more :)

I also met for the first time Everaldo Canuto on this presentation about Mono. It was very funny. Suddenly on his presentation, Everaldo stops and asks: "Is Francisco here?" After I put my hand up, he asks again: "Are you the Francisco I'm thinking you are?" and everybody started to laugh... :)
Anyway, Everaldo is a very funny person. He has a very particular way of doing presentations...

I also found my old friend Alessandro Binhara! I didn't know he would be there! It was a very nice surprise. We talked a little bit but unfortunately he said he wouldn't be able to stay much time there. :( I hope we can talk more later on another opportunity.

That's all for while. I will post some pictures I took at FISL later when I transfer them to my computer.

Stay tuned.

April 23, 2008 09:55 PM

There are few things I like better than when a customer says to the team, "I want the best machine I can buy for XXX dollars". It inspires a certain sense of joy not unlike the feeling an average Slashdot reader gets when they walk into the local gadget store. It is particularly special because you know as much as you could make use of such a machine, you know you would never justify the expense.

April 23, 2008 05:31 PM

PDXPUG and USPgA are headed to LinuxFest NW in Bellingham, WA this weekend. We have a booth we’ll be staffing 9:30AM-5:00PM Saturday and 9:30AM-4:00PM Sunday. Mark Wong will be presenting ptop Saturday April 26th, at 10am in room Haskell-111. Don’t miss it!

Seems like a great lineup this year - tons of Drupal, how to use open source in your business, and ROBOTS!

Come meet us and find out more about PostgreSQL.

April 22, 2008 05:37 PM

English disclaimer: this post will be in Polish only, as it’s use for other nationalities is somehow limited jakiś czas temu pisałem jak skonfigurować polskiego tsearcha pod postgresem 8.2. od tamtej pory trochę się zmieniło, pojawił się 8.3, w którym tsearch jest wbudowany w bazowego postgresa, więc instalacja jest prostsza. zakładam, że postgres jest skonfigurowany tak [...]

April 22, 2008 04:30 PM

We finished our first 8.4 commit-fest a few weeks ago, and it was a painful process. The commit-fest represented ten months of outstanding patches and feature ideas.

Moving forward, the new idea is to track items using a wiki that can be updated by anyone. More importantly, it looks like our developers are getting back into the habit of reviewing patches so many patches are being dealt with before the next commit-fest in May.

April 22, 2008 04:00 AM

Well, I booked my tickets this morning for PGCon 2008. Against my better judgement, I've booked tickets through Dulles, an airport which I really do not like, but which offers direct flights to Ottowa, something I can't get from BWI (Although BWI did offer a connecting flight through Atlanta...). I'll be arriving Monday night if anyone is in town that early, if not guess I will have time to work on my slides.

On an administrative note, the lightning talks are looking good, we'll have talks from guys named Greg, Robert, and Gavin, just to name a few. I do have 1 or 2 slots that I could squeeze someone into if your interested; I'd really like to see a quick talk on PostgreSQL use in other open source ecosystems.

One last thing, Dan showed me the nifty conference map with pointers to all the things around the conference, if you haven't seen it you'll want to bookmark for the upcoming trip. See you soon!

April 22, 2008 02:59 AM

PostgreSQL Weekly News - April 20 2008
Many patches have been arriving on the -patches list.
The PostgreSQL booth at FISL was another success, this time with a new
training outfit, new faces staffing, and some tragedy at the end.
FISL had almost 7500 participants this year, making it the world's
largest FLOSS conference.
Continue reading "PostgreSQL Weekly News - April 20 2008"

April 21, 2008 04:34 AM

A while back i blogged about a bug with PDO and prepared statements. After digging a little it appears that as of libpq.so.4 (PostgreSQL 8.0 tree) native prepared statement support was added. If PDO detects you have libpq.so.4 at compile time it will use the libpq prepare instead of its own internal prepare. What then happens is random prepared statements fail with heavy use. Again the only way around this right now is to use libpq.so.3 from PostgreSQL 7.4. Now to figure out who to bug about it. I have a feeling the PDO folks will point to libpq and the libpq maintainers will point to PDO.

April 19, 2008 01:47 AM

Truviso announced today that they were open sourcing some great work that Alvaro Herrera of Command Prompt Inc., has been working on. You can get the high-level details from this press release published in Forbes Magazine:

http://moourl.com/truvisoPgsql

If you're interested in what's happening in the code, you can find a message from Alvaro here:

http://archives.postgresql.org/message-id/20080325202645.GA15520%40alvh....

And the patches themselves (in their current form) are here:

http://archives.postgresql.org/pgsql-patches/2008-03/msg00394.php
http://archives.postgresql.org/pgsql-patches/2008-04/msg00190.php
http://archives.postgresql.org/pgsql-patches/2008-03/msg00245.php

From Alvaro today:

"All in all, I expect to be able to remove dead rows created by
transactions that are no longer in progress, but which started more
recently than some currently-open long-running transaction."

Great work, and we're all looking forward to the finished product, sometime in early 2009.

April 18, 2008 09:30 PM

Command Prompt made news today at the grace of our very good partners in the PostgreSQL Community, Truviso. We are working with Truviso to implement some very cool vacuum features that will help with long running transactions.

April 18, 2008 08:54 PM

Colin Charles blogged about my talk at MySQLCon. I wanted to clear a few things up that he mentioned. I noted in my talk: EnterpriseDB is the opposite, they’re closing up more and more. I spoke with Bob Zurek who is the CTO of EnterpriseDB. I was not correct in my EnterpriseDB comment. My reference came from this page which is very difficult to tell which is Open Source and which is not. I still think they would benefit more if they would be to just open everything.

April 18, 2008 05:27 PM

Proving that, in this new age, old rivalries are largely forgotten, MySQL just gave PostgreSQL one of their Google Summer of Code slots. Thanks, guys!

April 18, 2008 03:41 PM

At least it's new for me, and it's not on the list. Bugged a colleague of mine today, and we were both very surprised for a while until we figured it out. Ok, it's not very complex, but the deal was that:
SELECT CAST(1 as CHAR)
works, but
SELECT CAST (1 as CHAR)
does not (syntax error!)

Now, CAST is already horribly limited in MySQL (who came up with the idea that you shouldn't be able to cast to all available types if it's reasonable to do the conversion?! Like from int to text...), but this is just too much. Since when does whitespace before a parenthesis matter in SQL?! Sheesh...

Yet another reason to prefer PostgreSQL...

April 18, 2008 02:17 PM

Tom Lane committed his patch which modifies output of “EXPLAIN VERBOSE” command. Log message: Cause EXPLAIN's VERBOSE option to print the target list (output column list) of each plan node, instead of its former behavior of dumping the internal representation of the plan tree. The latter display is still available for those who really want it (see debug_print_plan), but [...]

April 18, 2008 12:11 PM

Hi, all!!

I'm right now at FISL (Fórum Internacional de Software Livre) 9.0 at Porto Alegre - RS - Brazil!

FISL is an annual conference about FOSS where we have a very nice opportunity to talk to many people about FOSS.

I will post more info about FISL later in another post.

April 18, 2008 11:22 AM

People coming from SQL Server and MySQL often complain about how you can't query other databases from within a PostgreSQL database. In Microsoft SQL Server, there is a concept of querying across databases on the same server with dbname.dbo.sometable and querying across servers (even of different types e.g. Oracle) by setting up a linked server and doing something such as servername.dbname.dbo.sometable or using the OPENROWSET(..) or OPENQUERY(...) syntax.

MySQL has a similar feature by using syntax dbname.sometable, but MySQL lacks schemas so there is no way to segregate a database into nice buckets as you can with SQL Server and PostgreSQL.

In this article we shall provide some examples of using the contrib module - dblink to query local PostgreSQL databases and remote PostgreSQL databases. DbLink is probably most comparable in structure to SQL Server's OpenRowset functionality. It lacks the power of SQL Server's Linked Server approach or OPENQUERY that allows for synchronized joins between linked servers/databases and local tables and updates/inserts on linked servers. This makes it not terribly useful in cases where you need to join lots of data with local data. It does however come in handy for bulk copy operations from one database/server to another.

April 18, 2008 06:40 AM

I spent April 17th, 2008 flying to San Jose, Ca only to arrive 30 minutes before my talk, "What MySQL can learn from PostgreSQL", jump in a cab and literally walk into the door of the room I was assigned, "right on time". I think the talk went over fairly well. I opened with the statement, "This is not about MySQL AB, this is about MySQL and the community." I think it help set the tone for the presentation. I didn't want people to feel like I was attacking a profit model or a company.

April 18, 2008 03:23 AM

As you now can see on my sidebar, I'm now a twitter

So, now you can see, or better, read to know what I'm doing. This is a youtube video a friend of mine Osny ( who is also my first twitter contact ) sent me about twitter: http://www.youtube.com/watch?v=ddO9idmax0o

Good twitting for you!

April 18, 2008 12:46 AM

Version 2.6.0 of DBD::Pg, the Perl interface to Postgres, has just been released. I've been trying to keep to a "release early, release often" system for DBD::Pg this year. In addition to some fixes to make compiling on Strawberry Perl and other Perl Windows distributions happier, here's the major changes in the last few versions:

• pg_notifies is now a true database handle method, so you can simply use $dbh->pg_notifies();
• $dbh->column_info() now shows enum values (thanks to Dave Rolsky)
• Added three new statement handle attributes, mostly to support a (very cool) future project I'm working on:
  • $sth->{pg_bound} : returns a hash of all placeholders, indicating whether each one has been bound yet.
  • $sth->{pg_numbound} : returns the number of placeholders currently bound.
  • $sth->{pg_current_row} : returns the number of the last tuple fetched.
• Various performance improvements, mostly to streamline the do() and execute/prepare() calls as much as possible

Give it a whirl, and please consider using CPAN::Reporter, which is invaluable to Perl module authors. It's similar to the Postgres Build Farm in that it allows your software to be tested on a wide variety of platforms and versions. The current reports for DBD::Pg shows we're doing well, but need more testers.

April 17, 2008 04:09 PM

Spoke with our Attorney today (Greg Hitchcock) he says, "I will try to get you a draft of the IRS application by the last week of April". We have already filed with the State of Oregon, this is the for the Federal 501c3 classification. This is great news because it means we could potentially be done with all of this before OSCON or LWE. At a maximum we will be done by West!

April 16, 2008 03:24 AM

Inspired by the old meme about the top shell commands, I thought I'd see what the most common SQL commands I run are. When you are using psql, (the command-line interface to Postgres), it keeps a history of all the commands you enter inside the file ~/.psql_history. It only keeps 500 lines by default, which I find way too low, so I adjust it by setting the HISTSIZE variable. Here's the usual contents of my .psqlrc file:

\pset null '\\N'
\set HISTSIZE 5000
\timing
\set G '\\x\\g\\x'

The first line gives a better visual output to nulls. The second tells psql to allow the .psql_history file to grow to 5000 lines, while the third line times all the commands you enter. The final line allows you to run a single command in expanded mode like so:

greg=# SELECT * FROM pg_class  :G

Interestingly, it looks like a variant of the above may make its way into the next version of psql...

On to the counting meme, however. Here's the command I used:

awk '{print $1}' ~/.psql_history | sort | uniq -c |sort -rn | head -10

The output on one of the production boxes I watch over gives:

   1356 select
    386 \d
    347 \q
    186 vacuum
    141 \c
    126 update
     84 \x
     59 SELECT
     46 begin;
     41 \dt

Note that older versions of psql did not wrap multi-line commands into a single line inside the file, so you may see odd results when using 8.2

April 15, 2008 06:27 PM

I found a couple of posts over on Planet MySQL today about some new announcement from the dolphin-herders. Apparantly, the Sun/MySQL combination is now starting to push at least some features for Enterprise customers only. AFAIK this isn't entirely new, since they've had some tools previously only available to enterprise customers, but I guess this would be a first for actual server code. (Though the way patches are moved between Enterprise and Community never really made sense to me, that's probably because I didn't study it in enough detail)

From what I can tell, this puts MySQL more towards the situation that PostgreSQL has with EnterpriseDB (and their now renamed product Postgres Plus Advanced Server) and some other companies. This is a proprietary product that sits on top of an open source foundation that is PostgreSQL. Now, EnterpriseDB initially marketed this product as opensource - which was incorrect, and they got quite a bit of bad press about it. The question is, what will Sun/MySQL do now. Will they continue to market their Enterprise product as Open Source, which it obviously isn't, or will they change that?


Continue reading "Not quite open source"

April 15, 2008 11:41 AM

Kickfire and "Stream Processing"

I noticed Robert's post about the Kickfire launch. He mentioned Truviso — for whom I work — so I thought I'd add my two cents.

Kickfire is the company previous known as "C2App". I'm not familiar with the details of their technology, but the basic idea is to use custom hardware to accelerate data warehousing queries (this blog post has some more details). Using custom hardware is not a new idea — Netezza have been doing something superficially similar for years, with considerable success. In addition to custom hardware, Kickfire apparently use a few other data warehousing techniques that have recently come back in vogue (e.g. column-wise storage with compression, coupled with the ability to do query execution over compressed data). As an aside, I think that building a data warehousing product using MySQL is a fairly surprising technical decision.

One thing I did notice is that Kickfire's PR mentions "stream processing" repeatedly, and Robert's post suggests that the sort of stream processing done by Kickfire is similar to what Truviso does. This is not the case: the two companies and their products are very different. I'd guess that Kickfire are using the term because it's become something of a buzzword.

I'd like to talk more about Truviso on this blog in the future, but the basic idea behind data stream processing is to allow analysis queries to be performed over live streams of data, as the data arrives at the system. In traditional databases, in order to apply a query to a piece of data, you first need to insert the data item into the database, wait for it to be committed to disk (force-write the write-ahead log), and then finally run a query on it from scratch. When data arrives at a rapid pace and you need low-latency query results, this "store-and-query" model has terrible performance; it's also an unnatural way to structure a client application (you're essentially polling for results). Instead, a data stream query processor allows the user to define a set of long-running continuous queries that represent the conditions of interest over the incoming data streams. As new live data arrives, the data is applied to the queries to incrementally update their results; client applications can simply consume new query results as soon as they become available. This allows you to get query results that are always up-to-date, without the need to first write data to disk (the data can either be discarded, or else written to disk asynchronously). For certain domains, such as algorithmic trading, network and environment monitoring, fraud detection, and real-time reporting, the data stream approach often yields much better performance and a more natural programming model. For more info, see the talk on data stream query processing I gave at last year's PgCon.

So what does this have to do with using custom hardware to accelerate data warehousing queries? Not a whole lot. I'm guessing that Kickfire have co-opted the "stream processing" label because they push analysis queries down to the custom chip, and then "stream" the stored data over the chip, to compute multiple queries in a single pass. If you squint at it right, there are some similarities to stream query processing (in both cases, you only want to take one pass over the data), but fundamentally, Kickfire is trying to solve a very different problem, and using a very different set of technologies. Data warehouse engines like Kickfire (and Greenplum) are complements to data stream systems like Truviso (and Streambase, Coral8, and others), not supplements or competitors.

April 15, 2008 08:35 AM

Over the weekend I had a chance to catch up on some of my favorite database oriented blogs, and I noticed a number of them were mentioning the upcoming Kickfire engine/appliance based around MySQL. Always a sucker to read about Yet Another MySQL Engine, I looked through what little details there were (Kickfire had previewed their system to some of the more popular MySQL blogs in order to create some initial buzz ahead of the MySQL users conference; good ploy). I think the breakdown goes like this:

• Designed for Data Warehousing


• Uses Hardware Based Acceleration


• Appliance System


• Does "Stream Processing"


• Involves lots of buzzwords like "ground breaking" and "revolutionary"


• They have brought Web 2.0 Marketing to the Database World

even if this product isn’t what it claims to be, they’ll prove the concept and there will be a competitive rush into this space

Again, I’m surprised it hasn’t been done before. Particularly given the money big vendors like Oracle and IBM sink into their DB development every year.

April 15, 2008 07:36 AM

PostGIS 1.3.3 is out

PostGIS 1.3.3 has been released and is already in the PostgreSQL RPM and soon will be in Yum repository. PostgreSQL 8.3 users who are using PostGIS are encouraged to upgrade because this release contains a fix for a major bug that affected spatial aggregates in 8.3. The windows Application Stack Builder version with the update should be out within this or next week.

Shp2PgSQL Loader now can load plain DBFs

We've been working on the 2007 Topologically Integrated Geographic Encoding and Referencing (Tiger) data recently. For those who are familiar with the US Census current Tiger format, this is the first version to be released in Environmental Systems Research Institute (ESRI) Shape file format. We ran into one small problem. The Tiger data includes related data with no geometry. These come as plain old DBase (DBF) files. Prior versions of Shp2PgSQL could not deal with DBF files with no corresponding Shape (SHP) geometry files, but the version packaged with 1.3.3 can.

I would like to thank Paul Ramsey for checking over my DBF-only patch and squeezing it into this release. Now that I have gotten my hands dirty again with C code, I almost feel like a real programmer. As a side note, even if you don't use PostGIS, this should come in handy for loading any DBF file into PostgreSQL.

New PostgreSQL Yum Repository

We recently had the pleasure of trying out the new PostgreSQL YUM repository for Fedora/RedHat Enterprise/Cent OS distros that is maintained by Devrim GÜNDÜZ. It made the process of installation on Redhat Enterprise Linux a lot simpler.

In this issue we shall provide step by step instructions on using it geared toward the non-Red Hat Linux/CentOS indoctrinated folk (AKA the misguided Microsoft Windows people). The reason we feel this is necessary is that a lot of people develop on Windows and then deploy on Linux. The Linux experience can be somewhat intimidating, so we hope to make this an easier process by assuming you know little if anything about Linux. So stay tuned for that article.

April 14, 2008 06:30 PM

Reinhard Max has revived the PostgreSQL RPM packages for SUSE and has given them a permanent place at the openSUSE Build Service. This builds on and supercedes my earlier offering, so please go to the new URL in the future. And please send your suggestions and contributions; it is "open" after all. By the way, the packages developed there are integrated into the official SUSE distributions, so it is the "real thing".

April 14, 2008 06:06 PM

Heard of geekspeakr.com? Brenda Wallace is created a place to store the names and interesting details of women speakers for conferences.

I took a snapshot of the tag cloud so far:

Yay for PostgreSQL being the only database represented! So far!

April 14, 2008 08:20 AM