What is new in PostgreSQL 9.0   Checkout our PostGIS in Action book 2nd Edition. First chapter is a free download

Postgres OnLine Journal

Blog View    Periodical View   PostGIS in Action   About the Authors   Consulting



Tuesday, June 08. 2010


What is new in PostgreSQL 9.0

Printer Friendly

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

  1. 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
  2. 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.
  3. 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.
  4. 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

Runner ups

  1. pg_upgrade is now included in contrib and much improved we hear. Can't wait to try this out. This will allow for in-place migration from PostgreSQL 8.3+ -> 9.0
  2. Streaming replication, Hot standby more details Built-in replication in PostgreSQL 9.0
  3. STRING_AGG -- this is a nice to have so you don't need to do array_to_string(ARRAY_AGG(somefield), '\') and with the ORDER BY feature its even better.
  4. Conditional triggers - triggers that only run based on some boolean expression. Improves trigger speed by a bit for some kinds of triggers
  5. ANSI SQL column level triggers
  6. DROP IF EXISTS for columns and constraints
  7. Python 3 support - now you can write database stored functions in PL/Python 3 as well as the 2.5/2.6 series.
  8. Explain plan improvements -- now you can see shared buffers using EXPLAIN (ANALYZE, buffers) SELECT * FROM sometable;
  9. DO it -- run anonymous PL functions subroutines. Useful for one off kind of stuff or mixing various languages in a work process.
  10. Windows 64-bit support
  11. exclusion constraints - this is useful for schedules and so forth to prevent overlapping periods. Jeff Davis has some slides up on how this works. Not Just Unique.
  12. other stuff

Another new feature is the GUC for outputting bytea data as HEX or ESCAPED. The default is now HEX. This has broken our ability to display geometries using OpenJump ad-hoc query tool unless we do a work-around of ALTER DATABASE gisdb SET bytea_output='escape';. We are still not quite sure how much of an issue this is. PostgreSQL 9.0 ST_AsBinary rendering

Posted by Leo Hsu and Regina Obe in 9.0, new in postgresql, postgis, postgresql versions at 22:11 | Comments (13) | Trackback (1)

Trackbacks
Trackback specific URI for this entry

What is new in PgAdmin III 1.12.0
Gathering from the number of hits we got from our What's new in PostgreSQL 9.0, and the large number of slashdot responses we got as well as the fair number of reddit responses, I guess a lot of people are really excited about the upcoming PostgreSQL 9
Weblog: Postgres OnLine Journal
Tracked: Jul 05, 03:21
PingBack
Weblog: www.linuxteam.info
Tracked: Sep 24, 05:30


Comments
Display comments as (Linear | Threaded)

Always cool to see better window function support! Maybe next we'll see a keep() clause?
#1 Noah Yetter on 2010-06-09 09:32 (Reply)
>12. other stuff

Clearly the best new feature(s).
#2 alex rockwell on 2010-06-09 13:54 (Reply)
I'm still waiting on an efficient general-purpose equivalent to MySQL's "REPLACE INTO" SQL extension, or at least "INSERT IGNORE".
#3 Mysid on 2010-06-09 14:14 (Reply)
How about the "ignore nulls" feature for things like last_value? I find it very useful for sequencing things in Oracle (ie: union a buncha things together, order by time or something, then do last_value ignore nulls to get previous value from another table, etc.).
#4 AlexS (Homepage) on 2010-06-09 14:38 (Reply)
so how about alter view?

pfff. view controls aren't great atm.
#5 bob on 2010-06-09 15:15 (Reply)
Bob,

I feel your pain. Well there is

CREATE OR REPLACE VIEW which in PostgreSQL 8.4 allows you to add columns at the end without dropping the view. But yes I am looking for something like what MySQL and SQL server allow to do -- with being able to insert columns in the middle or change datatypes of columns without dropping. Especially when you have views dependent on other views it does get painful.
#5.1 Regina on 2010-06-09 18:30 (Reply)
This is all great, but when will PostgreSQL finally gain synchronous multimaster replication?
#6 UX-admin on 2010-06-09 15:39 (Reply)
Yeah, it's something that seems to keep managers from replacing Oracle with Pg. :-) "It's not cluster."

BTW; If you will implement that one day, stick it on top of RedHat cluster, at least optionally. It's the only "true" cluster out there.
#6.1 Mordae on 2010-06-09 16:48 (Reply)
Until tables aren't stored in separate files I just look on. Something like ASM would be cool as well.

Cool that Postgres is getting into OLAP though. MSSQL Server's lack of features is made up in price, Excel integration, and tools. For some enterprise OLAP in the cloud will be important.

"create or alter table" and other schema modification enhancements would be cooler for the industry in general. Fast and easier schema management would have more of a positive effect on the RDBMS world than "nice" little things like REPLACE INTO which are fairly easy to work around.
#7 twiggy on 2010-06-09 17:29 (Reply)
Twiggy -- you mean like a

CREATE TABLE IF NOT EXISTS like what MySQL has. That would be pretty nice I think.

Yes these kind of usability features sound like they aren't that hard to implement to me but would vastly improve user-friendliness.
#7.1 Leo on 2010-06-09 22:51 (Reply)
What's wrong with PG's on disk storage that you don't like, Twiggy?
#7.2 Ryan on 2010-06-10 09:50 (Reply)
I am not sure, but RANGE window functions are not supported still :( it was removed from core and moved to 9.1
#8 Pavel Stehule (Homepage) on 2010-06-10 08:00 (Reply)
corrected
#8.1 Regina on 2010-06-10 09:18 (Reply)

Add Comment

E-Mail addresses will not be displayed and will only be used for E-Mail notifications.

To prevent automated Bots from commentspamming, please enter the string you see in the image below in the appropriate input box. Your comment will only be submitted if the strings match. Please ensure that your browser supports and accepts cookies, or your comment cannot be verified correctly.
CAPTCHA
 
 

Entry's Links

Quicksearch

Calendar

Back November '24
Mon Tue Wed Thu Fri Sat Sun
        1 2 3
4 5 6 7 8 9 10
11 12 13 14 15 16 17
18 19 20 21 22 23 24
25 26 27 28 29 30  

Categories

All categories

Archives

Subscribe

  • XML

Blog Administration

Open login screen