..::Planet PostgreSQL::..

This is a product that has nothing to do with PostgreSQL yet, so it does seem kind of odd that we are listing this in our product showcase section.

Well it is not even really a product per se, but I was just so enamored by the beauty of the layout and the usefulness of these cheat sheets, that I felt it was worthy of being listed in our showcase section since some of these would be useful to the PostgreSQL community of programmers.

What is this product you ask? It is DZone RefCardz. When I discovered this array of cheat sheets, I became a glutton and downloaded about 10 of them. It made me even want to start learning Ruby and also made me realize how little I know about CSS.

I was disappointed to find out that while they have an Essential MySQL, which by the way is extremely useful, they don't also have an Essential PostgreSQL yet. Someone should really write one of these things for PostgreSQL, but I guess it would be best for that someone to be a published author since it seems the main focus of RefCardz is as a publicity card for an author's book.

As a side note, it did get me thinking about the format of Postgres OnLine Journal cheat sheets and as many people have mentioned, perhaps we do try to cram too much information on one page. The layout of RefCardz cheat sheets seems to provide a good balance between amount of content and brevity and made me realize that having a multi-page cheat sheet is not such a bad thing. Their choice of colors, layout and diagrams is just mesmerizing.

Another quick blog update... I think search should now be fixed as well. After the import, I noticed all searching was broken, and also that many of the links when scrolling through the history would not work. This turned out to be two issues: the first was that my recent re-import of data uploaded this post which contained some invalid utf8 data. This meant that anytime the data was searched on, it would generate an invalid utf8 error, breaking the search. I'm unsure if I would have had this problem on 8.3 (we're currently running this on 8.2), but luckily I still have some code from a recent project where I had to ferret out invalid utf8 data, so that was relatively easily found a fixed, which fixed the search as a whole.

The next thing that I needed to fix was the broken permalinks. As it turns out, serendipity has a table to store permalinks based on configuration of your blog. As I hadn't dealt with that on the re-import, it was broken, so I simply needed to re-populate that table for the old (or is that new) entries. Much thanks to the guys on irc (particularly lluad) for helping me work through some regex issues.

So that should make it much easier to find and reference any of the old entries. As always, should you find any broken permalinks, please do drop me an email.

I know it appears that it has been pretty quiet since we open sourced Replicator but that isn't the case. We have been actively working on 1.9 and fixing 1.8 Beta as bug reports have come in, including a bug to sequence replication. However, 1.9 is the true milestone release where we have finally moved away from the original architecture. For those that don't know, the original architecture looked like this:

master-->mcp
          |
          |
  -----------------
  |       |       |
  s0      s1      s2

The mcp would handle all communication and data transfer between the master and the slaves. The idea behind the architecture was to achieve maximum efficiency for the master, meaning that the number of the slaves never affected the performance of the master. However this architecture came with a cost. A single point of failure. If the mcp were to ever crash, replication would stop. When we started down the path of 1.9 it was made very clear to me by Alvaro and Alexey that this was not acceptable. In return I made it very clear that any architectural change we made must not suffer from the Slony problem (performance degradation based on number of subscribers). Together we were all very clear to each other and Alvaro came up with a new architecture. The new architecture calls for a "Forwarder" process and from a topological view doesn't look much different than the MCP. It does however offer us a great deal more flexibility and stability. Here is the new architecture:

master-->forwarder0
          |
          |
  -----------------
  |       |       |
  s0      s1      s2

How is this different? It is different in a couple of ways. First, the forwarder is now integrated into the PostgreSQL backend. This removes the mcp binary entirely. It also greatly decreases the redundancy of the code. Secondly if the primary forwarder goes down a slave can become the forwarder. This removes the single point of failure. You can read more information on the forwarder here. Other items up for idle thoughts on the possibilities of this new architecture is a single monitoring point. With versions of replicator = 1.8, you can have a clear idea of which replication transactions have been received and transfered to each slave but you must access each slave individually to see what transaction has actually been restored. So what else is coming with Replicator 1.9? In continuing our cleanup of the architecture we are completely rewriting the ROLE and GRANT/REVOKE replication. This is actually the first step of the other half of the Major Feature of Replicator 1.9, DDL replication. We are currently investigating the opportunity to have certain DDL operations automatically replicate. The most obvious of these would be:

CREATE TABLE

ALTER TABLE

CREATE DOMAIN

etc...

We decided to pass on replicating CREATE FUNCTION due to complexity in dealing with externally linked libraries as well as various dependency problems. We may look at this again in the future. For more information on this feature please visit the thread. If you are interested in testing you can grab the 1.8 Beta or 1.9 from SVN. You can also get the 1.8 Beta from The pgsqlrpms project. The next developer meeting is on 01.08.09 at 11:00AM PST. We are holding in in the #replicator channel on Freenode. All are welcome.

Yes, that’s me, with Tom Lane. You, too, might be able to get your picture with Tom!

Like Josh Berkus said yesterday:

As of today, you have 2 weeks left to submit talk proposals to PGCon.

You know you want to. PGCon is the international conference for PostgreSQL hackers, sysadmins, application developers, SQL geeks and other Smart People. Submit your talk! Be a Smart Person too!

PGCon will be happening May 21-22 in Ottawa, Canada, with tutorials on May 19 and 20. Some financial help is often available for speakers, but none is available for non-speakers. So submit, submit!

We particularly could use some talks on the new 8.4 features, really creative PostgreSQL applications, massive Postgres scaling, PostGIS, BioPostgres, and a few case studies. This means you.

I attended PgCon last year for the first time. Not only were the presentations top notch, but Dan Langille’s hospitality set the groundwork for yet another fantastic community-building experience PostgreSQL community members experienced during the 2006 Anniversary summit in Toronto, again in 2007 at the first PgCon.

We had plenty of outstanding socializing and hacking opportunities. Last year’s conference started with a gathering of committers that was fodder for great pub and hallway track conversation all week. Great talks I saw included Andrew Sullivan’s Idle thoughts on PostgreSQL Project Management, Greg Sabino Mullane’s Bucardo talk about this multi-master replication tool, and Magnus Hagander’s walk through how search.postgresql.org was implemented.

Ottawa was beautiful last year, and I can’t wait to go back this May!

Both Josh Berkus and Hubert made blog entries about our last excerpt. In general I will start off by saying that we are more or less in agreement on what is good SQL coding format.

Here are the things I think we can all agree on

• SQL Keywords should be upper cased
• Field names should be prefixed with their tables especially when you have a multi-join statement involved
• Use JOIN syntax instead of stuffing everything in the WHERE though we are highly against just saying JOIN. We prefer INNER JOIN

The major points of contention I think are

• Should you use aliases over tables and if you use aliases should you keep them short or more descriptive. Josh thinks table names should be used where possible and when aliases are used they should be longer than a few characters and Hubert agrees with us that short aliases are fine and in fact desirable. I think we all agree aliases should be meaningful when used, but our idea of what it means to be meaningful is a little different.
• In use of JOIN syntax -- we prefer using INNER JOIN instead of using just JOIN and in fact find it quite irritating that PostgreSQL rewrites our INNERs as JOIN. I suspect Hubert and Josh and many other PostgreSQL folk are on the other side of the fence on this. The reason we feel strongly about this is there are so many kinds of JOINS - INNER JOIN, RIGHT JOIN, LEFT JOIN, CROSS JOIN, FULL JOIN, and the horrid NATURAL JOIN (that should be shot and put out of its misery). To just say JOIN to us is just confusing.
• While you can write LEFT OUTER JOIN, the OUTER is kind of pointless because no one goes around writing LEFT INNER JOINS
• Use well supported standards where possible. This means CURRENT_TIMESTAMP instead of now(). now() is not in all databases, but most relational databases support CURRENT_TIMESTAMP

Continue reading "SQL Coding Standards To Each His Own Part II"

SIGMOD 2009 Programming Contest

I just noticed that there's a programming contest at SIGMOD this year. The problem is relatively simple and tractable, although there are some interesting wrinkles:

• The data is inserted "online" by 50 concurrent threads, which means there is no opportunity to do offline bulk build/reorganization of the index.
• Solutions need to provide serializability, including avoiding the "phantom problem" (although that shouldn't be too hard: next-key locking should work).
• Solutions are also penalized when they fail to meet a response-time SLA, which makes good performance about more than merely maximizing throughput.

As of today, you have 2 weeks left to submit talk proposals to pgCon ...

I have gotten a couple more of the West 2007 Videos.

PostgreSQL Partitioning - Robert Treat

Babel of PLs - David Fetter

PostgreSQL Conference, U.S. is having a PgDay at LinuxFest Northwest in Bellingham Washington on April 25th, 2009. The PgDay (and LinuxFest Northwest) is a free event. We are holding the PgDAY on the first day of the event (a Saturday) parallel with LFNW. There was over 700 attendees to LFNW last year. LFNW is hoping for even more this year! In short, we are looking for some PostgreSQL talks (45 minutes each) to fill out the day. Please click here to submit your talk.

Selena Deckelmann has created an detailed map showing Postgres user groups around the world.

In the ongoing effort to try to measure Postgres activity, I found the new Google Archive Search provides a good overview of Postgres news activity.

My employer, EnterpriseDB, has scheduled me to do webcasts of my most popular talks during the next few months; they also have several non-technical webcasts. I already did a webcast about replication in December, and the content is online now (free registration required).

I recently posted that we are targeting Postgres 8.4 beta to start in February. If you are curious about the features that will be in 8.4, there are a few places to look:

• Items marked done "[D]" on the TODO list
• Partial list of completed items on a wiki
• Larger list of completed items

First off, before I get started on some more guidelines for writing maintainable queries, Regina and Leo already wrote about Part I ...

So, Wez tagged me for the 7 things, uh, thing. For some reason this chain feels appropriate given the turn of a new year, so here are 7 things (possibly wierd) you may have not known about me:


Continue reading "select * from things limit 7"

Well, after a long time playing with nightly Webkit because Firefox wasn't so good on my mac, I decided to give it a try again with 3.1 beta2 and.... Wow! It's amazing! Super fast and I can even say that it seems faster than last webkit builds. I also liked that it seems to use a little less cpu than webkit which is very nice!

Kudos to Mozilla Firefox people who is doing an excellent job regarding performance of firefox on OSX and from what I saw on other OS's too.

Problem You are developing a consultant search application where by a user looking for a consultant can check all the items in the list they require a consultant to have. You have 2 tables. consultant and consultant_qual. The consultant_qual has primary key formed by 2 fields consultant_id and qual_id. qual_id contains a constrained list with things like 'OpenLayers', 'PostGIS', 'Python', 'C#' etc.

How do you devise a query that given a list of checked options by the user, only returns consultants with not 1 but all of those qualifications?

Continue reading "How to require all checked conditions are met by a result"

This post is basically just an reply to Josh Berkus blog post. Additionally, it refers to “SQL Coding Standards To Each His Own” by Leo Hsu and Regina Obe. Well, I’ve read what Josh wrote, and I though to myself - oh my, I must be doing something wrong, as I would never use long aliases. [...]

In the first part of this series Fusion Charts and PostgreSQL Part 1: Database Analysis of USDA DB in our November/December 2008 issue, we did some back-seat analysis of a database we had never seen before and formulated some thoughts of things that would be useful to see in a dashboard as well as starting to develop some views to support our Dashboard. In the second part of our Fusion Charts series, we covered creating a Dashboard application in ASP.NET that demonstrated both VB.NET and C# variants using the database we loaded and prepped in Part 1.

In this part three of our series, we shall conclude by demonstrating the same application we did in ASP.NET in PHP.

We are going to create a simple dashboard that has the following features:

1. A drop-down list to allow the user to pick the kind of chart to display the data in (Bar, column, funnel etc)
2. A drop-down list that allows the user to pick the metric to explore -- e.g. Cholestrol, Vitamin K, Caffeine etc.
3. 2 charts -- one chart showing the top 5 food groups for our metric and another showing the top 5 foods for our metric

Our final product will look like this:

You can see the app in action - USDA Food Stats and discover some interesting things about the food you eat or were considering eating.

Continue reading "Fusion Charts and PostgreSQL Part 3: PHP Dashboard"

Hot on the heels of Devrim's Snapshot RPMs, I'm pleased to say we now have 'early experience' one-click installers available to download and play with the latest features committed to the PostgreSQL and pgAdmin source trees. We're aiming to update the installers periodically after interesting features are added - the first of which is Hitoshi Harada's SQL:2008 Window Functions patch for PostgreSQL.

Over the Holidays I spent some time pushing forward with recovering my old blog posts and getting them loaded into the new blog. While the posts are now visible, not everything is 100%, so I thought a quick run-down of what works and what doesn't might be appropriate.

1. 90%+ of blog posts have been re-imported; I believe I have copies of the missing ones, but they didn't play nicely with the importer script. I'll probably get them up some day, but if you find you're looking for a specific one that doesn't show up, drop me a line and I'll re-import it by hand
.

2. Categories have been re-built for imported entries, so those following category specific feeds, you should see the related entries. I'm not sure it's 100%, but this is somewhat non-critical, so probably will be left as is. If you find something really wrong or think something is worth updating, just let me know.


3. Speaking of search, none of the entries show up in search results right now. I believe I need to store the text bodies in multiple places for the search code to work against it, but I haven't looked to closely at that. It is something I plan to fix though (actually I think all of search is broken now... woopsie)


4. Each entries unique identifier should have been preserved, which means that you should be able to map old entries to new ones with a little url hacking. For example, if you are looking for the old entry
   

   http://people.planetpostgresql.org/xzilla/index.php?/archives/149-out-parameter-sql-plpgsql-examples.html

   you can find it on the new blog by going to

   http://www.xzilla.net/index.php?/archives/149-out-parameter-sql-plpgsql-examples.html

   (This could probably be turned into a nice apache re-write rule, but that's up to the planetpostgresql.org maintainer, not me)


5. links and styles don't work in old blog posts; the links are there, but they got converted into some kind of wiki formatting during conversion. I probably won't go back and fix this, but again if you really need a specific post cleaned up, let me know. (Or if someone can come up with some generic sql replacement fu I can run, I'd be inclined to try it


6. Comments are all missing, but I think I have them all, and with the id preservation, this should be something that is recoverable, I just haven't gotten to it yet.

Big thanks go to Magnus for the initial re-import script (even if it was in python). While I hacked it to do recovery in a different way, he worked out most of the hard parts which made things actually possible for me (although I am now much more familiar with python regular expressions than I ever imagined I'd be)

CIDR

This weekend, I'll be at the CIDR 2009, the biennial Conference on Innovative Data Systems Research. It's an interesting conference: not as formal or as high-pedigree as the prestigious database conferences (SIGMOD and VLDB), but the papers are usually interesting and provocative. There is one track of peer reviewed papers and one of track of "Perspectives" that are selected by the program committee to spark a discussion. I'm one of the authors on a Perspectives track paper, "Continuous Analytics: Rethinking Query Processing in a Network-Effect World" — which is essentially a fancy title for the thesis that stream processing techniques are more widely applicable to mainstream business analytics than most people seem to think.

If you'll be there, say hi. In the near future, I hope to post more about the paper, and the rest of the research I've been doing so far at school.

Last year was the third year that PDXPUG has been operating in Portland, and I decided to look back at our year of meetings. Here goes:

January 11 - 10 things you can use in PostgreSQL 8.3
February 26 - Extreme Database Makeover: RT
March 20 - Managing Internet Services: Using the right tool for the job
April 17 - Rails on PostgreSQL
May 15 - PostgreSQL for Pythoneers
June 19 - The relational model
July 20 - PDXPUG DAY!, and the schedule
August 21 - Tsearch2 and Materialized Views (Guest speaker from Seattle!!)
September 18 - The Visual Planner
October 16 - Point In Time Recovery
November 20 - Reviewed 8.4 features with the help of depesz’s blog
December - Coder’s Social

Thanks everyone who gave talks and attended meetings! User groups are only as good as the people who participate in them, and this list shows just how talented, diverse and fun the Postgres community is in Portland. I love you guys!

Looking forward - once again, we’ve already scheduled talks through the next four months! I feel like the group is running on its own momentum, and that is a fabulous feeling. We have a data visualization talk, another Extreme Database Makeover, and hopefully a presentation about teaching database theory with PostgreSQL.

Our next meeting is on January 15, 7pm with Stephen Jazdzewski traveling all the way from Eugene to present SplendidCRM, a formerly Microsoft SQL-only system that is now compatible with PostgreSQL. I am happy to see more of our Microsoft colleagues joining and presenting to the user group communities, as I’ve always felt they are underrepresented in our groups. Also, I’m happy to host another out-of-town presenter here in Portland! Hope to see you on the 15th.

I was reading Josh Berkus last blog post and was intrigued by his last post Writing Maintainable Queries Part I.

He is right that lots has been said about coding standards in other languages and even right out holy wars have been launched on the subject, but as far as SQL goes, not quite enough has been said on the subject for us to have a great war to end all wars.

I was also happy to see that we agreed with all his points except his first one. Yes I felt dissed, and thought hmm if someone as important as Josh thinks our aliases should be very descriptive and we should use the table name rather than the alias where possible, surely there must be something wrong with me for not believing in this fundamental philosophy.

In the rest of this excerpt I shall make fun of Josh and also set forth some of our own SQL Coding guidelines. Hopefully Josh won't take too much offense at this small jibe.

Continue reading "SQL Coding Standards To Each His Own"