Hello, everyone, and welcome to this week’s PostgreSQL News, issue 130!

-Josh Berkus made an official announcement related to 7.4 and 8.0 becoming EOS (End-Of-Support) : “The next bugfix or security update release for PostgreSQL versions 7.4 and 8.0 will be the last updates for those versions. This is in accord with the PostgreSQL Release Support Policy announced in December. We urge users still using 7.4 or 8.0 in production to begin planning migration to newer versions immediately.

Version 8.1 will stop being updated in November, so users of version 8.1 should be planning an upgrade this year.

While we regret the inconvenience to some PostgreSQL users, the community has limited resources and time spend maintaining old versions of PostgreSQL reduces the time we have to develop new features.  Users who are unable to upgrade their PostgreSQL installations will need to make their own plans for backporting patches, possibly with the help of a commercial PostgreSQL support provider.”

-Steve Singer announced the release of Slony-I 2.0.4 , which introduces  some fixes to issues introduced by 2.0.3 . http://www.slony.info is the address you need 🙂

-Josh Berkus wrote on hackers@, proposing that the release date of beta3 will be July the 8th, in order to give American contributors enough time to recover after their national holiday. After discussions and oppinion exchanges, we should expect the release of the 3rd beta in the first half of July.

-In Planet PostgreSQL we have Bruce Momijan talking about PostgreSQL 9.0 Illustrated, which is a easier way to understand what to expect in the new release, so if you feel confused by the release notes, use this link : http://wiki.postgresql.org/wiki/Illustrated_9_0

-Leo Hsu and Regina Obe talk about importing data into PostgreSQL using OpenOffice Base 3.2; look it up here : http://www.postgresonline.com/journal/index.php?/archives/167-Importing-data-into PostgreSQL-using-Open-Office-Base-3.2.html#extended .  Since the authors’ native tongue isn’t English, you may encounter some language-related flaws, but other than that, it’s an article worth reading. 🙂

-Finally, we try to summarise the PostgreSQL Weekly News (the official news, that is) :

-This week sees the release of Benetl v3.5, an ETL tool for files using PostgreSQL.
-Muldis-D 0.130.0, a specification for an object-relational language intended to run atop, among other systems, PostgreSQL, is released.
-Except these news, and the ones we’ve already talked about, this week is again kinda poor in news. For the usual list of patches, accepted, rejected and pending, check the weekly news.

That’s it for me, see you next week with hopefully more interesting news!

Hi all, and welcome! Let’s see what’s new in the PostgreSQL world this week…

-Benoit Carpentier announced the release of Benetl v. 3.5; Benetl is a free ETL tool for files using PostgreSQL. You can find out more/download from www.benetl.net .

-Darren Duncan announced version 0.130.0 of the specification of the Muldis D language for ORDBs : “The largest change since last month’s spec version 0.129.1 is the elimination of the “$” sigil that was used semi-gratuitously to mark data-entities (variables, parameters, attributes, named expression nodes).  These are now regularly formatted as barewords instead, like most languages and SQL, but unlike Perl.

Another significant change is renaming a syntax shorthand from “>foo” to “=>foo” for clarity; it means “foo => foo” and has nothing to do with “greater-than”.

A few other improvements were made to the concrete grammars as well.”

-Robert Haas started a thread on hackers@ titled “deprecating =>, take two”. Here are some excerpts : “By consensus, we have removed the new-to-9.0 operator text[] => text[] and renamed the hstore => text[] operator.  (The current name is “%”, but there is some discussion of “%>”, some yet other name, or getting rid of it altogether; please comment on that thread if you wish to weigh in.)  This means that the only remaining => operator in CVS is the text => text operator which constructs a single-element hstore, which has been around since 8.2.  In lieu of providing a substitute operator, Tom Lane proposed that we simply encourage people to use the hstore(text, text) function which does the same thing:

http://archives.postgresql.org/pgsql-hackers/2010-06/msg00711.php

Per that email, and subsequent concurrence, here is a series of patches which does the following:

1. In CVS HEAD, document the hstore(text, text) function and adjust CREATE OPERATOR to throw a warning when => is used as an operator name, using the wording previously suggested by Tom.
2. In the back branches, add an hstore(text, text) function.  These branches already have a tconvert(text, text) function which does the same thing, but the consensus seemed to be that we do not want to go  back to the name tconvert() for this functionality, and that back-patching the new name was preferable.
3. In 8.4 and 8.3, also add hstore(text, text) to the documentation. 8.2 appears to have no contrib documentation.

Barring vigorous objections, I will apply these tomorrow so that we can consider deprecating => as an operator name in 9.1, for better compliance with the SQL standard.

http://archives.postgresql.org/pgsql-hackers/2010-05/msg01501.php”

The general consensus was that it’s a good idea, but for all the comments and proposals I recommend you read the whole thread.

-Josh Berkus announced the first draft of the PSQL 9.0 release announcement ( http://wiki.postgresql.org/wiki/90ReleaseDraft ) also asking for feedback from the release team; people stepped in with suggestions and you might wanna check the link above for details.

-Moving on to PostgreSQL Planet, we delve into the Weekly News, which isscarce this week, so except the usual local news and the list of submitted and rejected patches, there really is nothing more. If the aforementioned topics interest you, check out http://www.postgresql.org/community/weeklynews/pwn20100620 .

-Andrew Gierth writes about range aggregation with window functions, describing the problem as ” Assume you have a table of ranges expressed as “start” (s) and “end” (e) columns; we’ll assume that these are half-open intervals, i.e. each row represents the range [s,e). We also assume that the constraint (e > s) is enforced. The problem is to aggregate all overlapping ranges and produce a result with one row for each
disjoint collection of ranges.” His solution and explanations are to be found here : http://blog.rhodiumtoad.org.uk/2010/06/21/range-aggregation-with-window-functions/ .

-Leo Hsu and Regina Obe write about NOT IN NULL and the mathematical and even philosophical implications of NULL : ” I know a lot has been said about this beautiful value we affectionately call  NULL, which is neither here nor there and that manages to catch many of us off guard with its casual neither here nor thereness. Database analysts who are really just back seat mathematicians in disguise like to philosophize about the unknown and pat themselves on the back when they feel they have mastered the unknown better than any one else. Of course database spatial analysts, the worst kind of back seat mathematicians, like to talk not only about NULL but about EMPTY and compare notes with their brethren and write dissertations about what to do about something that is neither here nor there but is more known
than the unknown, but not quite as known as the empty string.” Read more here: http://www.postgresonline.com/journal/index.php?/archives/166-NOT-IN-NULL-Uniqueness-trickery.html#extended .

-Andrew Dunstan wrote a concentrated version of a thread in hackers@ regarding the use of enums : ” Some people don’t seem to get enumeration types. They think of them as C programmers tend to – as  symbolic names for integer values.

But quiche eaters like me have quite a different point of view. Languages like Ada have had first class  enumeration types for a long time (Ada is nearly 30 years old now). PostgreSQL’s enum types are more of this kind, an ordered set of labels. Some people naïvely expect that underneath they will be stored as  their ordinal position in the label set. In fact, for a technical reason, they are not. Rather, they are stored as globally (within the database) unique Oids. This is a bit counter-intuitive to some people, but really, it’s just an implementation detail.

The biggest thing that bugs people about PostgreSQL’s enums is that you can’t extend them, i.e. you can’t add more labels to the list. There is a workaround involving creating a new type, but it involves rewriting the tables that use them, which is unpleasant. Recently I have given some thought to that. I came up with scheme for a new enum type that would have been extensible. But as often happens, Tom Lane came up with a better idea. I’ve been working on fleshing that out, especially testing the possible performance impact, and I hope we can have something in 9.1. Being able to add labels to an enum set without table rewriting would make them much more usable.

Okay getting to the point, one of our clients asked us about a peculiar problem they had with a query, and the strange results they were getting. We admit this still manages to catch us off guard every once in a while.”

Well, that’s about it, folks. Take care and have a nice weekend.