Home Home > Tag > PostgreSQL
Sign up | Login

Deprecation notice: openSUSE Lizards user blog platform is deprecated, and will remain read only for the time being. Learn more...

Posts Tagged ‘PostgreSQL’

new package postgresql-plr. Get the power of R inside your postgreSQL database

March 30th, 2012 by

I’m pleased to introduce you to a new available package for postgreSQL database.
The R procedural language extension developed by Joseph E Conway.

R Procedural Language for PostgreSQL


PL/R is a library which allow you to add the power of R statistical engine in your database.
This article will show you how to add it and basic usage on a 12.1 installation.
(The package is also available for 11.4)

Become familiar with the project and how it can help you.
The homepage project : www.joeconway.com/plr/

We admit here, that you are able to manage a posgreSQL server instance, and have already one running.

Guest Blog: Rares Aioanei – Weekly Review of PostgreSQL with openSUSE Flavor

July 3rd, 2010 by

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!

Guest Blog: Rares Aioanei – PostgreSQL Review (openSUSE Flavor)

June 26th, 2010 by

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:


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.


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.

Guest Blog: Rares Aaioanei – Weekly Review of PostgreSQL Project with openSUSE Flavor

June 18th, 2010 by

Hello everyone, and welcome to this week’s edition of OpenSUSE PostgreSQL news!

-The first news for this week is Simon Riggs’ announcement of “CHAR(10)” a short conference on PostgreSQL high availability techniques, including :
“* Clustering
* High Availability
* Replication
as well as
* caching
* scalability
* synchronous replication
* cloud deployment
* parallel databases

Conference covers all the latest tech in PostgreSQL 9.0 and related projects, with 14 speakers from US, Europe and Japan.

Visit http://www.char10.org/ to book and/or pay online

Or contact char10@2ndQuadrant.com”

-As it seems this week’s news are scarce with tech news, and more announcements,  here goes Jason Dixon’s announcement of Surge, the Scalability and Performance Conference, “to be held in Baltimore on Sept 30 and Oct 1, 2010.  The event focuses on case studies that demonstrate successes (and failures) in Web applications and Internet architectures.

Robert Treat will be presenting one of his PostgreSQL talks at Surge, and our Keynote speakers include John Allspaw and Theo Schlossnagle.  We are currently accepting submissions for the Call For Papers through July 9th.  You can find more information, including our current list of speakers, online:


If you’ve been to Velocity, or wanted to but couldn’t afford it, then Surge is just what you’ve been waiting for.  For more information, including CFP, sponsorship of the event, or participating as an exhibitor, please contact us at surge@omniti.com.”

-Kevin Grittner announced on hackers@ the call for a reviewfest, announced as follows : “Folks, The PostgreSQL 9.1 Development Plan: http://wiki.postgresql.org/wiki/PostgreSQL_9.1_Development_Plan calls for a ReviewFest to run from the 15th of June (tomorrow) until the start of the first CommitFest for the 9.1 release.  The idea is that those with time available to contribute beyond what they can usefully contribute to getting 9.0 released can help provide feedback on patches submitted so far, to lighten the load of the CF proper when it starts.  I have agreed to manage this RF.

Of course, we also need reviewers.  I do want to emphasize that we *don’t* want this process to impact the release of 9.0; it is in the best interest of everyone that 9.0 is tested, stable, and released as soon as practicable.  Please think hard about whether there is some testing or review you could do to facilitate the 9.0 release effort, and only participate in this RF to the extent that it doesn’t detract from the other effort.

Also, in testing these patches, be alert to any problems in the *before* version — you may find 9.0 issues in the process of attempting to test these patches, and such issues, if found, should take priority.  If you find a possible 9.0 issue, please set aside efforts to review the patch until you have pursued the preexisting issue.

This is essentially being treated as an early start on the 2010-07 CF, so that is where the process will be managed: https://commitfest.postgresql.org/action/commitfest_view?id=6

Note that we don’t expect any commits for these patches to happen until after the 9.0 stable branch is created and committers are done with their 9.0 release efforts, most likely some time after the 2010-07 CF is officially in progress.  Also, we probably won’t be bumping many patches to “returned with feedback” status during the RF; the apparent work required would need to be more than could reasonably be expected to be completed for the CF.

Before signing up, please review these pages, to get an idea what’s involved:


On the lighter side:


Please send me an email (without copying the list) if you are available to review; feel free to include any information that might be helpful in assigning you an appropriate patch.”

-Speaking of announcements, David E. Wheeler announced the launch of the PGXN development project : “PGXN, the PostgreSQL Extension Network, is modelled on CPAN, the Perl community’s archive of “all things Perl.” PGXN will provide four major pieces of infrastructure to the PostgreSQL community:

* An upload and distribution infrastructure for extension developers
* A centralized index and API of distribution metadata
* A website for searching extensions and perusing their documentation
* A command-line client for downloading, testing, and installing extensions

We have started the fundraising phase of the project now. Thanks to founding sponsors myYearbook.com  and PostgreSQL Experts, Inc., we’re already 2/5 of the way to our goal. Complete details of the project —  including the specification, implementation plan, and  fundraising FAQ — are on the site.”

-In the non-mailing-lists news, this week we have Simon Riggs’ article on planet.potgresql.org titled “Smoothing replication”, Bruce Momijan talks about “The magic of hot steaming replication”, which you may wanna read here – http://momjian.us/main/presentations/technical.html#hot_streaming.

-The main title of this week’s PostgreSQL Weekly News is the release of 9.0 beta2. In other news, pgnotifyd v. 0.1, PostgreSQL local and the usual list of patches.

-This is your latest PostgreSQL Weekly News … see ya next week!

Guest Blog: Rares Aioanei – Weekly Review from PostgreSQL (openSUSE Flavor)

June 11th, 2010 by

Guest Blog from Rares Aioanei

Hello everyone and welcome to this week’s Weekly PostgreSQL News, served openSUSE-style!

-As usual, we start with some insight of this week’s mailing list events, then we will focus on the Planet PostgreSQL posts and news.

-Jon Schewe posted on perform@ an interesting comparison of various filesystems under Linux (OpenSUSE64) , how do they perform and under what circumstances. I ain’t gonna spoil
it for you, so go grab a read.

-Peter Eisentraut, on hackers@, posted a quite interesting idea titled Functional dependencies and GROUP BY : “I have developed a patch that partially implements the “functional dependency” feature that allows some columns to be omitted from the GROUP BY clause if it can be shown that the columns are functionally dependent on the columns in the group by clause and therefore guaranteed to be unique per group.  The full functional dependency deduction rules are pretty big and arcane, so I concentrated on getting a useful subset working.  In particular:

* When grouping by primary key, the other columns can be omitted, e.g.,

* CREATE TABLE tab1 (a int PRIMARY KEY, b int);

* SELECT a, b FROM tab1 GROUP BY a;

This is frequently requested by MySQL converts (and possibly others).

Also, when a column is compared with a constant, it can appear ungrouped:

SELECT x, y FROM tab2 WHERE y = 5 GROUP BY x;

For lack of a better idea, I have made it so that merge-joinable operators qualify as equality operators.  Better ideas welcome.

Other rules could be added over time (but I’m current not planning to work on that myself).

At this point, this patch could use some review and testing with unusual queries that break my implementation. 😉 ”

-This week, The San Francisco Bay Area PostgreSQL Meetup Group will have a meeting on June the 15th; here are the details : http://postgresql.meetup.com/1/calendar/13675701/

-In Planet-related news, Leo Hsu and Regina Obe write about what’s new in PostgreSQL 9.0, since the 2nd beta was released this week :  “PostgreSQL 9.0 beta 2 just got released this week. We may see another beta before 9.0 is finally released, but it looks like PostgreSQL 9.0 will be here probably sometime this month. Robert Treat has a great slide presentation showcasing all the new features. The slide share for those on Robert Treat’s slide share page.  We’ll list the key ones with our favorites at the top:
Our favorites
The window function functionality has been enhanced to support ROWS PRECEDING and FOLLOWING. Recall we discussed this in Running totals and sums using PostgreSQL 8.4 a hack for getting around the lack of ROWS x PRECEDING and FOLLOWING.
No more need for that. This changes our comparison we did Window Functions Comparison Between PostgreSQL 8.4, SQL Server 2008, Oracle, IBM DB2. Now the syntax is inching even closer to Oracle’s window functionality, far superior to SQL Server 2005/2008, and about on par with IBM DB2. We’ll do updated compare late this month or early next month. Depesz has an example of this in Waiting for 9.0 – extended frames for window functions Ordered Aggregates.
This is extremely useful for spatial aggregates and ARRAY_AGG, STRING_AGG, and medians where you care about the order of the aggregation. Will have to give it a try. For example if you are building a linestring using ST_MakeLine, a hack you normally do would be to order your dataset a certain way and then run ST_MakeLine. This will allow you to do ST_MakeLine(pt_geom ORDER BY track_time) or ARRAY_AGG(student ORDER BY score) This is very very cool. Depesz has some examples of ordered aggregates. Join removal — this is a feature that will remove joins from the execution plans where they are not needed. For example
* where you have a left join that doesn’t appear in a where or as a column in select. This is important for people like us that rely on views to allow less skilled users to be able to write meaningful queries without knowing too much about
* joins or creating ad-hoc query tools that allow users to pick from multiple tables. Check out Robert Haas why join removal is cool for more use cases. GRANT/REVOKE ON ALL object IN SCHEMA and ALTER DEFAULT PRIVILEGES. This is just a much simpler user-friendly way of applying permissions. I can’t tell you how many times we get beat up by MySQL users who find the PostgreSQL security management tricky and tedious to get right. Of course you can count on Depesz to have an example of this too Waiting  for 9.0 – GRANT ALL”

-From the PostgreSQL Weekly News from the 6th of June we find out some local news, such as the Italian Conference for Free Software (Cagliari, Sardinia) and the talks, sessions and workshops related to our fav database, we also find news about the release of ChronicDB v2.2.2, plus the usual patchlist.

There you go, that’s all for this week. Enjoy. 🙂

Guest Blog: Rares Aioanei – Weekly Review of the PostgreSQL Project with openSUSE flavor

June 4th, 2010 by

Hello all, and welcome to this week’s edition of the PostgreSQL Weekly News, openSUSE-style!

As accustomed, we’ll start with news picked from the mailing lists, then we’ll move on with news from the PostgreSQL Planet. Here we go…

-We start off this week with Mark Hills’ announcement of the release of pgnotifyd, which,  if we listen to Mark, ” connects to a PostgreSQL database, listening for the named  asynchronous notification. When notification is received it executes the given command.

It is typically a cleaner and more efficent alternative to polling a PostgreSQL database for changes (using a crontab(5) or similar.)

I’ve been using it successfully to synchronise passwords and mailing lists. This is an initial release in the hope others can benefit from it.”
You can find out more here : http://www.pogo.org.uk/~mark/pgnotifyd/ .

-Oleg Bartunov announced he posted pictures from PgCon 2010; you can watch here : http://www.flickr.com/photos/obartunov/sets/72157624042768831/ .

-Giuseppe Maxia announced OpenSQL camp 2010; I will try to copy the most relevant stuff from his announcement, in order not to make this article too big :
“OpenSQL Camp is a free conference of, by, and for the open-source database community of users and developers. The OpenSQLCamp 2010, European Edition (http://opensqlcamp.org/) will take part in parallel to the Free and Open Source Conference 2010 (http://froscon.org/) on Saturday 21st and Sunday 22nd August in St. Augustin, Germany, which is located close to Bonn and Cologne.[…] We would like to invite your project to participate in this event.

We’ve set up a call for participation (http://opensqlcamp.org/Events/2010/Call_for_Participation) – the deadline for submitting your proposal is July 11th.

We are seeking talks related to Open Source Databases of all kind, not just relational databases! Submission about tools and technologies related to OSS databases (e.g. connectors/APIs) are also welcome.

Some ideas and for submissions:

* A how to presentation, showing how to solve a common problem in the database field.
* An introduction/overview about a certain database project/product or related tool
* Providing “best practices” information for administrators
* A deeply technical and developer-centric session about some project’s internals or an API used to connect to a database.

We look forward to your contribution! Please don’t hesitate to contact us via IRC (#opensqlcamp on FreeNode) or our Discussion Group (http://groups.google.com/group/opensqlcamp) ”

-Bruce Momijan announced the timetable for the release of 9.0, which would be as  follows : “Assuming we want a release Postgres 9.0 by mid-August, here is how the timetable would look:

Need RC release to be stable for 1-2 weeks before final
RC must be released by August 1
Beta must be stable for 2-3 weeks before RC
Stable beta must be released by early July

So, we have 5-6 weeks to get a stable beta.  Looking at the open issues: http://wiki.postgresql.org/wiki/PostgreSQL_9.0_Open_Items#Resolved_Issues

it looks like we are doing OK, but we must continue progressing.”

-The ChronicDB release team announced version 2.2.2 ; ChronicDB is a live DB schema update system, allowing DBAs to minimize the downtime of their servers. More on their website, http://chronicdb.com/

-On the Planet , we have Christophe Pettus posting his slides from the Open Bridge 2010 conference, called Introduction to PostgreSQL, here : http://thebuild.com/presentations/intro-to-postgresql-osb2010.pdf.

-The PostgreSQL Weekly News this week offers some local events info, as well as, as usual, the submitted patches list. Check out the news here : http://www.postgresql.org/community/weeklynews/pwn20100531.

There you go, this was the news this week. Enjoy your weekend.

Guest Blog: Rares Aioanei – Weekly Review of PostgreSQL

May 29th, 2010 by

Hello everyone and welcome to the first edition of the PostgreSQL news, served OpenSUSE-style! Let’s start off with news and bits from the mailing lists, then, as we move on, we take a look at news from the Planet and around the Web.

-Pavel (he didn’t write down his last name), a student for Google Summer of Code 2010, proposed in hackers@ the idea of snapshot materialized views in PostgreSQL with questions as to how this should be properly implemented. A quick look at the mailing-list archives will give you a better insight.

-Jeff Davis posted a patch related to small exclusion constraints patch affecting /src/backend/executor/execUtils.c, explaining the patch thusly : “Currently, the check for exclusion constraints performs a sanity check that’s slightly too strict — it assumes that a tuple will conflict with itself. That is not always the case: the operator might be “<>”, in which case it’s perfectly valid for the search for conflicts to not find
itself. This patch simply removes that sanity check, and leaves a comment in place.”

-Marcus Gsteiger on testers@ reported a succesful migration from 8.4 to 9.0beta1 on CentOS on a HP Proliant server. While this is nothing particularily interesting, this report, like others of the kind, shows that 9.0 is becoming more stable and will become a succesful release.

-Robert Haas, in a mail to hackers@ titled “mapping object names to role IDs”had a proposal which I think it will be best if I quote here entirely, since it’s not that easy (for me at least) to summarise : “Suppose you have an object name as a CString and you want to convert it to an OID.  The method of doing this varies widely depending on the object type:

oid = get_database_oid(name);
oid = get_tablespace_oid(name);
oid = GetForeignDataWrapperOidByName(name, true);
oid = GetForeignServerOidByName(name, true);
oid = LookupFuncNameTypeNames(name, args, true);
oid = LookupAggNameTypeNames(name, args, true);
oid = LookupFuncNameTypeNames(name, args, true);
oid = LookupOperNameTypeNames(name, args, true);
oid = GetSysCacheOid1(LANGNAME, CStringGetDatum(name));
oid = GetSysCacheOid1(NAMESPACENAME, CStringGetDatum(name));
oid = GetSysCacheOid1(AMNAME, CStringGetDatum(name));
oid = get_roleid(name);
oid = GetConstraintByName(reloid, name);
oid = FindConversionByName(list_make1(name));
oid = TSDictionaryGetDictid(list_make1(name), true);
oid = TSTemplateGetTmplid(list_make1(name), true);
oid = TSConfigGetCfgid(list_make1(name), true);

If you’d like to throw an error if the object doesn’t exist, then you can change the “true” parameter in the above examples to false, where it exists, or you can use get_roleid_checked for roles.  For the types where a direct syscache lookup is used, you get to write the check yourself.  For constraints, GetConstraintByName throws an error anyway; there’s no equivalent that doesn’t.  Some other object types – like rules and casts – have even more complex logic that is sometimes duplicated in multiple places; and others (like tablespaces) that have convenience functions still manage to duplicate the logic anyway. Long story short, this is kind of a mess.

Looking at the different cases, there seem to be three main cases:

1. Objects that just have one name, like databases and procedural languages.
2. Objects that have possibly-qualified names, like conversions and
text search dictionaries.
3. Objects that have both names and argument, like functions and operators.

There’s enough complexity about the stuff in category #3 that I’m inclined to leave it alone, but try to make the other stuff more standardized.  What I would propose is that we create a new source file somewhere (maybe utils/cache), move all of the other functions of this type there, give them standardized names, and provide them all with an argument that specifies whether an error is to be thrown if the object doesn’t exist.  Begin bikeshedding here.  I suggest that the names be get_<object-type>_oid and that they take two parameters, either a List * for possibly-qualified names (a little wonky, but it’s what we do now) or a char * for unqualified names, and a boolean indicating whether to throw an error if the object isn’t found.  Thus:

Oid get_<object-type>_oid(List *qualname, bool missingok);
Oid get_<object-type>_oid(char *name, bool missingok);

Thus get_database_oid and get_tablespace_oid would remain unchanged except for taking a second argument, get_roleid and get_roleid_checked would merge, and all of the others would change to match that style.


-May 23rd brought another release of the PostgreSQL Weekly News, from which I will try to extract the most important/interesting bits :
-Cybercluster 2.0 has been released : http://www.cybertec.at/en/cybercluster-2-0-synchronous-postgresql-replication       -Security updates for various releases of PostgreSQL have been issued[1], as weel as RPM’s[2] :
[1] – http://www.postgresql.org/docs/current/static/release.html
[2] – http://yum.pgrpms.org/

-Postgres-XC 0.9.1 has been released : http://postgres-xc.sourceforge.net/

-Of course, PostgreSQL will be present at lots of this year’s events, like OSCON or South East Linux Fest; the complete list is also available in this Weekly News issue.
-As every week, there is a quite comprehensive list of source commits to the source tree this week; if you think you wan them listed here, say so in the comments section.

-Pavel Stehule proposed a small-time patch fixing a double free of allocated memory, Jeff Davis also wrote a 6-liner regarding btree_gist support for searching on “not equals”, Josh Berkus had an “Idea for getting rid of VACUUM FREEZE on cold pages”, which although very interesting, is too long, plus the replies, to summarize here. The archives will help you yet again 🙂 .

-Fujii Masao announced he is designing the “synchronous” replication feature based on SR for 9.1, explaining the general idea and asking for thoughts/comments, proposing several sync levels;
apparently, the majority agreed on one (#4).In order not to explain without example, here is a quote from the OP outlining the idea :
“The log-shipping replication has some synch levels as follows. The transaction commit on the master
#1 doesn’t wait for replication (already suppored in 9.0)
#2 waits for WAL to be received by the standby
#3 waits for WAL to be received and flushed by the standby
#4 waits for WAL to be received, flushed and replayed by the standby..etc?

Which should we include in 9.1?”

-Mark Wong announced in general@ the PDXPUG day at OSCON 2010 : “Thanks to the generosity of O’Reilly, we will be having a full day of free PostgreSQL sessions on Sunday, July 18 at the Oregon Convention Center.  Location details and schedule information can be found on the wiki at: http://wiki.postgresql.org/wiki/PDXPUGDay2010 ” .

-Stephen Frost posted a small patch to psql, adding ‘S’ as an optional parameter to \da, while Tom Lane announced his fellow hackers the intention to wrap up 9.0 beta2 on June the 3rd in order to release to the public on the 7th.

-In the Planet PostgreSQL news, we have Dave Page reporting back from PGCon 2010, held in Ottawa, Canada, and of course other Planet members wrote about it : Greg Sabino Mullane, Andrew Dunstan or Selena Deckelmann.

-Selena also posted on the Planet an announcement asking for participation from reviewers (interested, anyone?) since it’s preparation time for the first commitfest for 9.1 .

-Greg Sabino Mullane announced that PostgreSQL finally switched to git as a canonical VCS; this announcement was kind of unnoticed because of posts from PGCon, so if you’re curious and/or want to see pictures, Planet PostgreSQL is the way to look.

-Peter Eisentraut has posted very interesting ideas meant to make RDBMS developers in general and of course Postgres developers in particular; to get a overall idea, here it is : “My problem is that getting database code from the editor to the database server in a safe manner is pretty difficult. […] My answer to that problem is an old friend: package management. Package managers such as dpkg and rpm are pretty well-established solutions and have shown over the years that managing software deployments can be easy and occasionally even fun.” Of course, there;s much more to this article and it’s a recommended read, in my oppinion, so point your browser or RSS reader to the Planet! 🙂

-Peter also has a feed called ‘Visual Explain Reloaded’, referring to the EXPLAIN command (you can check it out here : http://www.postgresql.org/docs/9.0/static/sql-explain.html ) and it’s possibilities to output in different markup languages – JSON, XML, YAML. http://2.bp.blogspot.com/_dgdplFJMdoQ/S_In33KeqYI/AAAAAAAAADU/N_nYFZ0Uack/s1600/veung-dotty.png shows you the resulting JSON output of ‘ regression=> \a\t
regression=> EXPLAIN (FORMAT JSON) SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2 \g |veung ‘ . Pretty nifty, if you ask me.

-Andrew Dunstan announces the release of the PostgreSQL Buildfarm client, version 4.0, which according to the author, has two new features : “The SCM code is substantially rearranged into a separate OO module, with subclasses supporting CVS and Git. New config options support these changes, while old style config parameters for CVS are still supported. Support for running the buildfarm from Git is a requirement before we can move the core community repo, and this meets that requirement (a little later than planned, but as promised almost exactly one year ago). The requirement to specify a port for each branch to build with is gone. If the config parameter ‘base_port’ is specified the code will pick a unique port for the branch, a short number above that setting. The means that the config file does
not need to be changed when a new stable postgres release is made. Again, old-style configs continue to be supported.”

-The same Dave Page posted on the Planet a very interesting comparison between VoltDB and, you guessed it, Postgres – and at the same time it’s a comparison between “traditional” DBMS’s and new-style, lockless DBM systems. Selena Deckelmann tells some stories of new and interesting ideas coming from PgCon 2010 – a worth read by far.