Recently, I was able to attend the local PostgreSQL community meeting here in Portland, and the topic du jour was covering the nifty and interesting features that are found in PG 9.0. Confessing that I haven't really been paying close attention to what's new in 9.0, the talk was incredibly interesting - covering a range of new features in 9.0.The ones I'm really excited about are: /contrib/passwordcheck This newly-added contrib module adds that feature that sysadmins are going to love - being able to add stronger password-based restrictions to the default PG password requirements. You know the sort I mean - must be a mixture of capitalized and non, must be at least one number, must be at least n digits long.
This is exceptionally useful if you're in an environment with strong password requirements. samehost and samenet These pg_hba.conf administration conveniences allow you to match any IP address that the server owns (same host) or any subnet that the server is a part of (same net). This'll be very useful for any multi-homed DB server that needs to listen on all its interfaces; as well as providing an easy way to just say "all my peers" without explicitly needing to remember and declare the IP subnet. Parameter change logging This is a fairly useful change for anyone who's tried to figure out what changed during the last reload of PG. Instead of wondering, you can now configure a logging setting to report what parameters from the config were changed, what they were changed to, and what changes require a full restart before they can be used. EXPLAIN now machine-readable This is an amazingly useful feature, especially when combined with auto_explain - you'll now be able to get explain output in XML, JSON or YAML, allowing for easy parsing, manipulation and comparison of the EXPLAIN output.
This also allows for transformation of the output - easily converting it to HTML, for instance. Easy comparison before and after an index is created, simplifying programmatic discovery of indexes. WHEN clause in triggers WHEN clauses in triggers add a new level of power to the trigger system, by allowing PG to handle the large block of IF statements we always have in our triggers. This should allow us to see a major performance boost for any conditional triggers, since PG doesn't have to descend to the trigger stored procedure for every modification. NOTIFY now takes a string argument Instead of just getting the notification from your database, you'll now be able to pass an optional string. With a clever sproc and some clever string formatting, you can now pass useful information to the NOTIFY receiver, saving yourself from excess work in figuring out what's changed. Simpler event-driven programming? Never a bad thing. Named parameters in stored procedures This is huge for Simpycity. On 9.0, we can now handle argument naming in a whole new way:
This is exceptionally useful if you're in an environment with strong password requirements. samehost and samenet These pg_hba.conf administration conveniences allow you to match any IP address that the server owns (same host) or any subnet that the server is a part of (same net). This'll be very useful for any multi-homed DB server that needs to listen on all its interfaces; as well as providing an easy way to just say "all my peers" without explicitly needing to remember and declare the IP subnet. Parameter change logging This is a fairly useful change for anyone who's tried to figure out what changed during the last reload of PG. Instead of wondering, you can now configure a logging setting to report what parameters from the config were changed, what they were changed to, and what changes require a full restart before they can be used. EXPLAIN now machine-readable This is an amazingly useful feature, especially when combined with auto_explain - you'll now be able to get explain output in XML, JSON or YAML, allowing for easy parsing, manipulation and comparison of the EXPLAIN output.
This also allows for transformation of the output - easily converting it to HTML, for instance. Easy comparison before and after an index is created, simplifying programmatic discovery of indexes. WHEN clause in triggers WHEN clauses in triggers add a new level of power to the trigger system, by allowing PG to handle the large block of IF statements we always have in our triggers. This should allow us to see a major performance boost for any conditional triggers, since PG doesn't have to descend to the trigger stored procedure for every modification. NOTIFY now takes a string argument Instead of just getting the notification from your database, you'll now be able to pass an optional string. With a clever sproc and some clever string formatting, you can now pass useful information to the NOTIFY receiver, saving yourself from excess work in figuring out what's changed. Simpler event-driven programming? Never a bad thing. Named parameters in stored procedures This is huge for Simpycity. On 9.0, we can now handle argument naming in a whole new way:
SELECT * FROM my_func(param='string', another_param=3);Being able to run queries like this will easily simplify Simpycity - just name your arguments the same in both Python and PostgreSQL, and everything will Just Work, clean and simple. Keep an eye out for this update - I can't wait to add support in Simpcyity. Keep an eye out for the updates on the Simpycity project page So Much More! Of course, this is just a subset of the interesting things - the bits I'm really looking forward to. You should go have a look at the whole list, easily found on the Illustrated Postgres 9.0 Wiki.