
Oh, it was quite long time for 8.4 version of PostgreSQL to finally get up on it’s feet and stand firmly – 16 months. After a sixteen-month pregnancy, the development team gave birth to a pretty elephant calf. Well done guys!
At the very same day development team of PostgresDAC
– the newborn calf’s friend
– decided to release
PostgresDAC 2.5.2 Beta with support for 8.4 server features.
And that’s not just an advertising words.
We’ve prepared v2.5.2 Beta with a lot of improvements. It was passed our internal tests but this is still beta version.
The main changes directly related to PostgreSQL 8.4 release would be:
From others features without a doubt should be mentioned:
It is worth noting that there were only two bug reports – and they were fixed – in this release and only one was developers’ fault, the other one appeared due to internal changes of Delphi 2009 after Update 3\4.
May the Force be with you, postgresmen!
Regular readers will know that I've been thinking a lot about testing SQL result sets and how to how to name result testing functions, and various implementation issues. I am very happy to say that I've now committed the first three such test functions to the Git repository. They've been tested on 8.4 and 8.3. Here's what I came up with.
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:
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.
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:
We have ccovered this briefly before, but its an important enough concept to cover again in more detail.
Create a new tablespace on a separate drive and move existing tables to it, or create a new tablespace and use for future tables.
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.
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:
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.
Now that I’ve griped and aired my disappointment. I can focus on the gobs of awesomeness that was Velocity.
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>"
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).
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!
I've been thinking more about testing SQL result sets and how to name functions that do such testing, and I've started to come to some conclusions. Some of the constraints I'm looking at:
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.