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. 🙂
Both comments and pings are currently closed.
