PostgreSQL 8.4 has ANSI SQL:2003 window functions support. These are often classified under the umbrella terms of basic Analytical or Online Application Processing (OLAP) functions. They are used most commonly for producing cumulative sums, moving averages and generally rolling calculations that need to look at a subset of the overall dataset (a window frame of data) often relative to a particular row. For users who use SQL window constructs extensively, this may have been one reason in the past to not to give PostgreSQL a second look. While you may not consider PostgreSQL as a replacement for existing projects because of the cost of migration, recoding and testing, this added new feature is definitely a selling point for new project consideration.

If you rely heavily on windowing functions, the things you probably want to know most about the new PostgreSQL 8.4 offering are:

• What SQL window functionality is supported?
• How does PostgreSQL 8.4 offering compare to that of the database you are currently using?
• Is the subset of functionality you use supported?

To make this an easier exercise we have curled thru the documents of the other database vendors to distill what the SQL Windowing functionality they provide in their core product. If you find any mistakes or ambiguities in the below please don't hesitate to let us know and we will gladly amend.

For those who are not sure what this is and what all the big fuss is about, please read our rich commentary on the topic of window functions.

Andreas 'ads' Scherbaum

Up to PostgreSQL 8.3 it was only possible to grant (and revoke) permissions on the entire table. If column level permissions were needed, a workaround like a view solved (more or less) the problem: create the view with the required (allowed) columns, revoke all permissions from the underlaying table, grant permissions to the view.

This - of course - is uneloquent, error prone and does not scale well. For different users requiring access to different columns, a big number of views is needed.

PostgreSQL 8.4 solves the problem with a shiny new feature: column level permissions.

JD wrote:

For those sleeping in PostgreSQL.org just released PostgreSQL 8.4. This is an exciting release with many new features including:

• Parallel Database Restore, speeding up recovery from backup up to 8 times
• Per-Column Permissions, allowing more granular control of sensitive data
• Per-database Collation Support, making PostgreSQL more useful in multi-lingual environments
• In-place Upgrades through pg_migrator (beta), enabling upgrades from 8.3 to 8.4 without extensive downtime
  

  read more

We have ccovered this briefly before, but its an important enough concept to cover again in more detail.

Problem: You are running out of disk space on the drive you keep PostgreSQL data on

Create a new tablespace on a separate drive and move existing tables to it, or create a new tablespace and use for future tables.

What is a tablespace and how to create a tablespace

A tablespace in PostgreSQL is similar to a tablespace in Oracle and a filegroup in SQL Server. It segments a piece of physical disk space for use by the PostgreSQL process for holding data. Below are steps to creating a new tablespace. Tablespaces have existed since PostgreSQL 8.0.

More about tablespaces in PostgreSQL is outlined in the manual PostgreSQL 8.3 tablespaces

While it is possible to create a table index on a different tablespace from the table, we won't be covering that.

Andreas 'ads' Scherbaum

The PostgreSQL Project will have a dev-room at FrOSCon on sunday, august 23, 2009. Talks wanted!

The theme should be PostgreSQL-related, please submit the talk(s) by using the FrOSCon Pentabarf:

https://pentabarf.froscon.org/submission/froscon2009/

Procedure:

1. 
   
2. Create account (if not yet done) and follow the confirmation link in the email
   
   
3. Login in Pentabarf
   
   
4. Create a new event
   
   
5. Choose track "PostgreSQL"
   
   

All submitters will receive a confirmation timely, if the talk is accepted. Who wants to submit a talk about databases in general - or a talk about another database - may choose the "OpenSQL Camp" track.

In perhaps a new trend, I’m blogging from 39011 feet (or so says the seatback in front of me). I’m traveling back home to the east coast from San Jose, CA where I attended (and spoke) at this year’s O’Reilly Velocity Conference.

I participated (and blogged) about the Velocity Summit in which I’ve participated for the past two years. The summit is the unconference preceding the real conference that help the organizers digest current hot topics and better define the conference track for the actual conference. The summit itself is filled with enough brain power to warp space-time, so I drop everything to go to that.

Ironically, despite being a well respected authority in web site (and general internet) scalability and performance, my talk proposals for Velocity 2008 were not accepted — I clearly need to write better proposals. This year, I managed to work my way into the workshop track on Monday. Despite having a bad headache and feeling "off" the day before, I managed to get my act together and put on an A-game for my workshop. For those of you interested, here is my scalable09 slide stack.

I thought I’d take a moment to talk about what I liked about the conference and what I think could use some improvement. I realize this is a down economy and that might be a legitimate justification for some the actions that resulted in some of my disappointment.

First, the negative. I usually start with positive and end with negative because I’m a pessimist. However, all in all the conference was awesome, so I thought I’d get my short list of gripes out of the way early.

O’Reilly is infamous for throwing good conferences for geeks. In my opinion, the field of web operations has been so severely neglected and applies so broadly to the world today that this conference needs to be for everyone.

1. In the next conference, I’d love to see a technical business track. Several of the talks I went to spoke to the dollars and cents lost or earned by paying the right amount of attention to web site performance and better operational paradigms. I thought a lot of the topics would be very useful to business managers.
2. The first day was not video taped and the second and third day were only half video taped. Come on guys, ante up. The attendance fee was substantial, you can afford to give your attendees the value of watching what they had to choose not to attend. I like the option when I go to a conference to choose a session that seems interesting so that I have the opportunity to participate, but often times I find that another session was top notch and I expect to be able to later review a recording of that.
3. Lastly, and this is the most significant. While I thought the conference was extremely well executed (excellent job Jesse, Steve, all your support, and most definitely O’Reilly), it lacked sufficient PR and marketing outreach. I talked with several journalists (as a part of my normal day job) while I was at that conference and not one of them was aware of Velocity — simply embarrassing. Given that the Structure conference was in town that same week, O’Reilly should have invested more in their PR and marketing outreach. It would have resulted in a substantially increased audience and a better venue for teaching the world to run a faster web.

Now that I’ve griped and aired my disappointment. I can focus on the gobs of awesomeness that was Velocity.

1. The conference was put on quite well from an operational perspective. Things started on time, A/V problems were non-existent. Like an idiot, I managed to lose my MacBook Air power adapter and the A/V crew managed to recover it for me. Conferences just plain suck when they have technical difficulties; this one had none.
2. The two tracks at the conference were extremely well articulated and while I wanted to be in both all the time (as OmniTI is a full-stack company, we care about both equally) it was an excellent split.
3. One track was performance which focused intently on user-perceived performance. This was largely front-end (HTML,CSS,JS,etc.) but also had a healthy amount of deep stack performance dis

[continue reading]

At Hi5, we currently use pg_reorg1.0.3 in order to organize data in a clustered fashion. I posted previously about the strategy. Our version is slightly modified, the modifications I made to the C code essentially allow pg_reorg to spin/wait for locks on the objects to be released before proceeding.

The good news is the folks at NTT have incorporated a similar change in pg_reorg 1.0.4. This is a fantastic improvement, and frankly implemented in a cleaner way than my changes.

The crux of the issue is the situation where a database is being auto-vacuumed, you can’t be guaranteed that pg_reorg and the vacuum will not collide. In theory you should not need to vacuum a table which you are pg_reorg’ing because that is the point of a pg_reorg, it’s essentially a vacuum full w/ extra features because the table is being rebuilt from scratch. However in an environment where auto-vacuum is being utilized to keep tables vacuumed, both will need to co-exist.

The change is simple, use the NOWAIT option of lock table to fail if the lock can not be obtained. This is wrapped in a loop until the lock is granted. The effect is pg_reorg patiently sits and waits while your vacuums complete and then it can finish it’s work. The downside is if any of these operations run for too long, then the journal table may grow very large. So there should be some monitoring wrapped around the code if it’s intended to run in the background. For the future we need a backoff algorithm as well as perhaps a limit to the number of spin/sleep cycles, but hey this is excellent progress.

This tool is essential in my humble opinion for everyone running PostgreSQL in a high transaction/high availability environment. By the way, pg_reorg works seamlessly with Slony-I.

The code addition does the following:

for (;;)                        
        {
                command("BEGIN ISOLATION LEVEL READ COMMITTED", 0, NULL);
                res = execute_nothrow(table->lock_table, 0, NULL); 
                if (PQresultStatus(res) == PGRES_COMMAND_OK)
                {
                        PQclear(res);
                        break;
                }
                else if (sqlstate_equals(res, SQLSTATE_LOCK_NOT_AVAILABLE))
                {
                        /* retry if lock conflicted */ 
                        PQclear(res);
                        command("ROLLBACK", 0, NULL);
                        sleep(1);
                        continue;
                }
                else
                {
                        /* exit otherwise */
                        printf("%s", PQerrorMessage(connection));
                        PQclear(res);
                        exit(1);        
                }
        }

The below is a snip of the strace on pg_reorg while it’s waiting for the lock:

rt_sigaction(SIGPIPE, {SIG_IGN}, {SIG_DFL}, 8) = 0
sendto(3, "P\0\0\0008\0SELECT reorg.reorg_apply($"..., 529, 0, NULL, 0) = 529
rt_sigaction(SIGPIPE, {SIG_DFL}, {SIG_IGN}, 8) = 0
poll([{fd=3, events=POLLIN|POLLERR, revents=POLLIN}], 1, -1) = 1
recvfrom(3, "1\0\0\0\0042\0\0\0\4T\0\0\0$\0\1reorg_apply\0\0\0\0"..., 16384, 0, NULL, NULL) = 77
rt_sigaction(SIGPIPE, {SIG_IGN}, {SIG_DFL}, 8) = 0
sendto(3, "P\0\0\0\177\0SELECT 1 FROM pg_locks WHE"..., 178, 0, NULL, 0) = 178
rt_sigaction(SIGPIPE, {SIG_DFL}, {SIG_IGN}, 8) = 0
poll([{fd=3, events=POLLIN|POLLERR, revents=POLLIN}], 1, -1) = 1
recvfrom(3, "1\0\0\0\0042\0\0\0\4T\0\0\0!\0\1?column?\0\0\0\0\0\0\0"..., 16384, 0, NULL, NULL) = 74
rt_sigprocmask(SIG_BLOCK, [CHLD], [], 8) = 0
rt_sigaction(SIGCHLD, NULL, {SIG_DFL}, 8) = 0
rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
nanosleep({1, 0}, {1, 0})

Your Postgresql log file will show the following:

Jun 25 17:09:33 <dbname> postgres[7825]: [37-2] 2009-06-25 17:09:33 PDTSTATEMENT:  LOCK TABLE <tablename> IN ACCESS EXCLUSIVE MODE NOWAIT
Jun 25 17:09:34 <dbname> postgres[7825]: [38-1] 2009-06-25 17:09:34 PDTERROR:  could NOT obtain LOCK ON relation "<tablename>"

• Administration
• Performance
• High Availability
• Migration
• GIS
• Integration
• Solutions and White Papers

• Python/Django/Pylons/TurboGears/Custom
• Perl5/Catalyst/Bricolage
• Potato
• Ruby/Rails
• Java (PLJava would be great)/Groovy/Grails
• Operating System optimization (Linux/FBSD/Solaris/Windows)
• Solutions and White Papers

Michael Brewer wrote:

On Saturday, June 13th, I wound up manning the PostgreSQL booth at SouthEast LinuxFest, in Clemson, South Carolina. This free conference drew a larger crowd than I'd expected; organizers told me there had been some 450 registrants by the day before, and they were expecting a final total of over 500 (with walk-ups).

read more

At long last, after millions and millions of queries just here at work and some more in other places, the prefix project is reaching 1.0 milestone. The release candidate is getting uploaded into debian at the moment of this writing, and available at the following place: prefix-1.0~rc1.tar.gz.

If you have any use for it (as some VoIP companies have already), please consider testing it, in order for me to release a shiny 1.0 next week! :)

Recent changes include getting rid of those square brackets output when it's not neccesary, fixing btree operators, adding support for more operators in the GiST support code (now supported: @>, <@, =, &&). Enjoy!

Last weekend, my brother and I attended SELF 2009. A few thoughts on it:

The mixture of sessions was interesting. There were some really good ones. I think the best session I attended was an OpenSolaris/NetBeans/Glassfish/Virtualbox/ZFS session, given by a Sun employee. He was an excellent presenter, and really showed off the strengths of the technologies in a nice way. He started up enough VMs to make his OpenSolaris laptop chew into swap, and I thought it was fun to see how it dealt with that. I’ve heard Solaris and OpenSolaris do a lot better at avoiding and managing swapping than GNU/Linux, but I couldn’t make any opinion from watching. I did think it was odd to have this session at a “Linux” (yes, they left off the GNU) conference. But I thought the session was a good addition to the conference. In other sessions, and in the hallways and expo, there was a lot more slant towards open-source software and gadgetry in general than there was towards GNU/Linux. The sessions that were about Linux or GNU/Linux were top-heavy towards topics like educational initiatives.

The Free Software Foundation had a booth in the expo hall. It was funny that they didn’t boycott the event, because I know RMS won’t speak at so-called “Linux User Groups” and insists they be called “GNU/Linux User Groups.” I guess the FSF is not unified behind that banner. Regardless, I used the opportunity to renew my membership perpetually. I’m so lazy that I need something like this to stay involved!

The expo hall was dominated by Red Hat, Fedora, and SUSE; PostgreSQL was there, but not MySQL. There was a good variety and number of vendors. It was great to see the healthy support of the event, which was free, by the way.

Clemson, SC is not easy to get to, and while the Clemson campus was attractive and functioned fine, it’s nothing you can’t find elsewhere. I ended up driving over 9 hours to get to it. I’d have preferred the technology triangle, which if nothing else is close to major airports, bus and train stops, and Red Hat.

Richard Hipp talked about the great fsync() bug, a similar talk to the one he gave at the first OpenSQL Camp. Someone asked about Tokyo Cabinet and he responded that he hasn’t found any fsync() calls in its source code. *cough* Something worth thinking about for on-disk usage (I haven’t looked at its source much myself). TC can also be used in-memory-only, and a while back I suggested that usage of it for Drizzle to replace the Memory engine; I don’t know what became of that.

1. Be careful what you put on the SAN. Log files and other local data should not go onto the SAN. Use local files with syslog instead. Think about it: your application is sick and trying to write a log message to tell you about it on a non-responsive file system. In fact, if you have a robust scale-out architecture, don't use a SAN at all. Use database replication and/or DRBD instead to protect your data.
   
2. Test the SAN configuration carefully, especially failover scenarios. What happens when the host fails from access one path to another? What happens when another host picks up the LUN from a "failed" host? Do you have fencing properly enabled?
   
3. Actively look for SAN failures. Write test files to each mounted file system and read them back as part of your regular monitoring. That way you know that the file system is fully "live."