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);
-or-
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.

Thoughts?”

-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.