I think today might have been the first time I had to use update from on the fly on a production system. I’ve previously always used the “missing-from-clause” style syntax, like this: UPDATE sarbel_ejemplo SET clientele_call = ejemplo.clientele_call WHERE ejemplo_id = ejemplo.ejemplo_id AND ejemplo.valid AND NOT ejemplo.complete; Of course the new(ish) and fancy way to do such a thing would be: UPDATE sarbel_ejemplo SET clientele_call = e.clientele_call FROM ejemplo e WHERE e.valid AND NOT e.complete AND sarbel_ejemplo.ejemplo_id= e.ejemplo_id ; Nothing earth shattering, but a tool every DBA should be familiar with.

I’ve read a number of articles and blog entires over the weekend talking about different [http://asay.blogspot.com/2006/02/full-circle-with-open-source.html open source business models]. Most of them seem to focus on how a single businesses can make money from open source, for example [http://startupfutures.com/article/66 Greenplum] and it’s Bizgress MPP. The other angle I’ve seen, most likely spurred on by [http://people.planetpostgresql.org/xzilla/index.php?/archives/150-OSDB-market-soap-opera-update-mysql-and-firebird.html Oracles recent acquisitions], is the idea of [http://weblog.infoworld.com/openresource/archives/2006/03/maintain_freedo.html open source projects looking for a corporate entity to sell themselves to], and with that getting the monetary backing they need to accelerate growth within their project. Now I imagine that all of this talk is probably a good thing, since it will probably lead to fostering more open source development, but the thing that I don’t like is that it severely overlooks what I think should be the preferred model for getting business support in your open source project; which is the conglomerate of business sponsors. PostgreSQL is of course a shining, if not the prime example, of this model. Of our six core members, none are employed by the same company. Of the six, three don’t even work for companies where PostgreSQL is the core business. Sure, a company like Afilias might use PostgreSQL in it’s core business, and it’s future might be tied to the success of PostgreSQL, but at it’s core they are a [http://www.afilias.info/gateway/index_html registry services company], not a database vendor or database support company. But it doesn’t stop there, of the 26 developers listed on our “Major Developers” list, only two work for the same company ([http://www.commandprompt.com Command Prompt]). The reason this is such a good thing for an open source project is that it helps to develop an extremely multi-faceted community for development. If you cut off one or more heads, there a dozens more left standing. As much as the business’s who use PostgreSQL benefit from having multiple support avenues, the project benefits from having multiple “revenue streams”, where the revenue is developer time and infrastructure support. I think we need to see more encouragement of this model within open source projects and the open source community. There are a number of examples of this type of model, FreeBSD is the classic example, but [http://www.rubyonrails.org/core Ruby On Rails] also thriving under this model (11 core team members employed by 7 different companies). The [http://www.joomla.org/content/blogcategory/13/29/ Joomla team] is another one (24 members, 19 companies), which was [http://www.mamboshack.com/index.php?option=com_content&task=view&id=29&Itemid=40 born] out of a problem with the aforementioned “business owns the project” model. I know there are others, what’s your favorite example?

I wouldn’t normally post about this, but since there seems to be zero information on this error message, I figured I might as well put in a little information on the topic for future generations. Yesterday I was working on a function when I hit the above error; it looked something like this: My initial thought was some type of relation caching issue, but after a quick rebuild of the tables/functions involved, and actually reading the error message a few more times I dismissed this idea, and started leaning toward either a syntax error or a bug. Since you could run that statement from the command line: without getting any errors, I then started leaning toward it being a bug, something around it not understanding how to properly handle the results of the get_family query. Oh, for reference, here is the defintion of get_family, and the defintion of _global_tolerance_exception: You might recognize get_family as a classic nested set function, we’ve been using it without issue since at least 7.3, and I think 7.2 or maybe even 7.1. So I went to #irc, where I think the most telling response was “I have never seen that error before”. Yeah, (google|pgsql.ru) really hadn’t either. The only thing was a pointer to the source code; look in src/backend/commands/trigger.c and you will find the following comments: It can’t be a good sign to trigger an error that has a code comment saying “should not happen”! Well, after kicking it around a bit, I never really did figure out what the problem was, but I did figure out the fix; I changed the _global_tolerance_exception function like so: Apparently it makes a difference whether you call the function using select get_family() or select * from get_family(). I don’t know why, it doesn’t matter on the command line, only in the function. More perplexing is that, while I can easily reproduce this error, I couldn’t make a reproducible test case… I have two theories on this… the first being that maybe there is some gunk in the works of the get_family function within the system tables having come from a 7.3 system originally… I dont think so, since it’s been dumped and restored several times since then, but maybe. Theory number two is that I originally got the error by calling the _global_tolerance_exception from another function that was called via a trigger. Perhaps something in the function got chaced in the server when called via trigger which caused the function being standalone to generate the error? Someone would need to go through and setup all the tables and triggers to proove that theory… in the meantime I’m moving on, but maybe if someone else ever gets this error, maybe this will help.

The new [http://www.amazon.com/exec/obidos/redirect?link_code=as2&path=ASIN/1590595475&tag=zillablog-20&camp=1789&creative=9325 PHP & PostgreSQL book] from Apress was officially published today. It provides a comprehensive guide to PHP functionality along with coverage of the PostgreSQL features that matter most to PHP developers. I think some of the key PostgreSQL material would have to include the information on advanced views and rules and a quick example of doing full text seaches with tsearch2. Hopefully it will do very well; Apress has really been a strong booster for PostgreSQL, with [http://apress.com/book/bookDisplay.html?bID=424 one book released last year], [http://apress.com/book/bookDisplay.html?bID=10105 one more to be released soon], and possibly more on the horizon, so it would be great to see some of that effort rewarded. Also PHP is still the dominate web scripting language, so the more PHP people we can turn on to PostgreSQL, the better it is for all of us.

This past week I uploaded the [http://archives.postgresql.org/pgsql-advocacy/2006-02/msg00143.php first batch of “Web Ad” graphics] for the postgresql project. They were donated by Mike Ellsworth and should be suitable for use in systems like adsense and other web advertising systems. Mike hopes to donate some more in a couple weeks, but these are excellent starters. Also on the heels of that, Julian Blanc of the [http://www.iucn.org The World Conservation Union] has donated some [http://pgfoundry.org/docman/view.php/1000089/169/iucn_elephant_photos.zip beautiful photographs he has of elephants in the wild] for anyone who wants to put some photo shots into thier postgresql related graphics. They are also on the [http://pgfoundry.org/projects/graphics/ graphics project site], please go check them out. Speaking of Julian Blanc, he has also uploaded a [http://pgfoundry.org/pm/?group_id=1000156 task list for the wilddb project]. You’ll recall the wilddb project is the IUCN’s attempt to port the African Elephant Database to an open source system. If you’ve got some spare time, I’d urge you to check out the task list and see if there is an item or two you might be able to work on.

If you’re new to PostgreSQL you may not have heard of [http://sean.chittenden.org/ Sean Chittenden], but long time members will certainly recognize the name. Sean has contributed to a whole number of projects including Apache, FreeBSD, PostgreSQL, and others. Last month he sat down with the folks at the [http://paranode.com/~topfunky/audio/2006/Sean-Chittenden.mp3 rubyonrails podcast to talk] about the rewrite from php to ruby he headed up for [http://www.penny-arcade.com/ Penny-Arcade.com], the use of lighthttpd vs. apache for web serving, the gpl vs the bsd license, and how postgresql eats mysql for lunch. Yeah, Sean’s not one for avoiding controversy [[image /xzilla/templates/default/img/emoticons/smile.png alt=”:-)” style=”display: inline; vertical-align: bottom;” class=”emoticon” /]] This one had almost slipped off my radar, but having listended to it now it’s a doozy, go check it out.

A quick pointer to the news announcement that [http://www.firebirdnews.org/?p=128 mysql has purchased a company called netfrastructure inc], which happens to employ Jim Starkey (one of the founders of InterBase, an ancestor to Firebird) and perhaps more importantly Ann Harrison, one of the primary Firebird developers. The firbird foundation team is trying to do full damage control, [http://www.firebirdnews.org/?p=129 spinning this purchase as a validation of firebirds backend as the best OSS technology on the market]. ISTM it’s more likely there were pieces of technology inside netfrastructure that mysql thought it could polish up and then control for its own commercial offerings without any strings attached. Actually Ann Harrison herself sort of validates this in a (ironic at this point) post that gives an overview of how [http://www.firebirdnews.org/?p=130 you can’t buy up the firebird codebase]. That said, I’m almost certain that you wont see any integration of firebird into mysql (and I don’t think the firebird community would really want that anyway), but it’s probably worth keeping an eye on as this sure looks like mysql’s play to get out from under the shadow of the [http://people.planetpostgresql.org/xzilla/index.php?/archives/148-Oracle-really-did-buy-Sleepycat.html recent oracle maneuvers], and so will have an effect on how the mainstream tech market looks at all open source db’s.

I ran into several people on irc this morning who were having trouble with PostgreSQL functions, especially wrt out parameters (an aweful many who were coming from sql server oddly enough). We were all tripping on the syntax, but I eventually got it worked out and said I would post the code here. I’m a little pressed for time so this will be quick any dirty, but I imagine it will still be pretty helpful for those trying to take advantage of the new tech. -- make some sample data to play with create table users (user_id integer, username text, created timestamptz); insert into users values (1,'one',now()); insert into users values (2,'two',now()); insert into users values (3,'three',now()); -- here is the basic sql based setof record function create or replace function myuser(in myuser integer, out my_user_id integer, out myusername text) returns setof record as $$ select user_id, username from users where user_id <= $1; $$ language 'sql'; dev20100=# select myuser(2); myuser --------- (1,one) (2,two) (2 rows) dev20100=# select * from myuser(2); my_user_id | myusername ------------+------------ 1 | one 2 | two (2 rows) -- Now we do the same thing with plpgsql, this is trickier than it looks when reading the docs! create or replace function myuser2(myuser integer, out my_user_id integer, out myusername text) returns setof record as $$ declare foo record; begin for foo in select user_id, username from users where user_id <= $1 loop my_user_id=foo.user_id; myusername=foo.username; return next; end loop; return; end $$ language 'plpgsql'; dev20100=# select * from myuser(2); my_user_id | myusername ------------+------------ 1 | one 2 | two (2 rows) -- and just for kicks someone wanted to see the non-setof just record way in plpsql create or replace function myuser3(myuser integer, out my_user_id integer, out myusername text) returns record as $$ begin select user_id, username from users where user_id <= $1 into my_user_id, myusername; return; end $$ language 'plpgsql'; dev20100=# select * from myuser3(2); my_user_id | myusername ------------+------------ 1 | one (1 row) lmk if this helps you out! [[image /xzilla/templates/default/img/emoticons/smile.png alt=”:-)” style=”display: inline; vertical-align: bottom;” class=”emoticon” /]]

The [http://www.businessweek.com/technology/content/feb2006/tc20060209_810527.htm roumers] are true, Oracle is buying sleepycat. You can check out the [http://www.oracle.com/corporate/press/2006_feb/sleepycat.html press release from Oracle], or the [http://blog.sleepycat.com/2006/02/next-ten-years.html blog posting from the (former) President of Sleepycat] confirming it. Over the next few days you’ll see a lot of talk about Oracle just trying to diverisfy and Oracle just trying to kill off mysql, but the important thing to remember is that both of these things are probably true. [[image /xzilla/templates/default/img/emoticons/smile.png alt=”:-)” style=”display: inline; vertical-align: bottom;” class=”emoticon” /]] Buying sleepycat does actually give Oracle an entry into the embeded space which they don’t exactly have, and falls in line with some other things they have been doing. I won’t ignore the bonus of getting another shot in at mysql; All oracle really needs to do here is simply put the focus of sleepycat soley into the embeded space and it does shut off a possible innodb exit plan for mysql. I am sure they have thought of this. I’m sure it was a factor. But taking shots at mysql is not taking shots at open source, it’s taking shots at a commercial database vendor. If you’re using BerkelyDB in your open source projects I seriously doubt you have anything to fear. If your using BDB tables in mysql ([http://ebergen.net/wordpress/?p=102 and afaict no one really does]) then yes you might have something to fear. But on the whole Oracle isn’t anti-open-source (look at [http://www.oracle.com/technology/tech/linux/htdocs/oracleonlinux_faq.html what they are doing with linux]), they are just anti RDBMS competition, and especially anti commercial RDBMS competition (and especially anti anti against [http://www.mysql.com/news-and-events/press-release/release_2003_16.html RDBMS and SAP competition]).