

PostgreSQL 9.2 released - lest
http://www.postgresql.org/about/news/1415/

======
craigkerstiens
We've been pretty excited about this release to come for some time at Heroku
as its loaded with great features. In addition to the JSON datatype here's a
bit of a longer list of features that are pretty noteworthy in the release:

\- Allow libpq connection strings to have the format of a URI

\- Add a JSON data type

\- Allow the planner to generate custom plans for specific parameter values
even when using prepared statements

\- Add the SP-GiST (Space-Partitioned GiST) index access method

\- Add support for range data types

\- Cancel queries if clients get disconnected

\- Add CONCURRENTLY option to DROP INDEX

\- Add a tcn (triggered change notification) module to generate NOTIFY events
on table changes

\- Allow pg_stat_statements to aggregate similar queries via SQL

\- text normalization. Users with applications that use non-parameterized SQL
will now be able to monitor query performance without detailed log analysis.

------
metabrew
Since postgres has basic json type support now, and PL/Javascript exists, it's
only a matter of time until an extension appears that lets you deploy
javascript applications directly to the database.

Who needs CouchDB or Node.js when you can just say CREATE EXTENSION
'couchnodegres.js'

~~~
joevandyk
Once the database can start receiving and returning json, then you can treat
it as a webservice and not have the client involved in SQL. I think this is
_huge_.

~~~
recuter
Can you elaborate a little bit on how an architecture like that would look
like? I had great hopes for CouchDB once.

~~~
y0ghur7_xxx
HTML/JS frontend calls simple node server that does nothing but call a stored
procedure like

    
    
       spc_handle_request(req_headers, req_body, http_method, ...)
    

stored procedure does simple routing and does something like

    
    
      insert into table (a, b, c) values (to_json(req_body).a, to_json(req_body).b, to_json(req_body).c);

or

    
    
      to_json(select * from emp);
    

Time to implement simple crud app: 10 minutes.

------
mattdeboard
I am actually pretty excited about the native JSON support, and overall I am a
huge fan of Postgres, but this is the most press-release-y press release ever*
. By that I mean that the quotes are way too "perfect", the kind you only see
in press releases. Some PR or marketing guy wrote them then showed them to the
person to whom they'd be attributed to get their ok. Nothing inherently wrong
with it, just struck me as funny.

* having written more than my share of press releases in my time

~~~
masklinn
> I am actually pretty excited about the native JSON support

There really isn't much to it yet, it's basically a varchar field with JSON
validation, it's not like you can query or index it.

I actually find the `row_to_json` and `array_to_json` functions more
interesting for now (though strangely enough there's no `hstore_to_json`)

~~~
StavrosK
Eh, if you want to index it you have the hstore. I do wonder why they don't
deserialize to an hstore type, though.

~~~
pilif
I'm sure something like that will be added. One of the issues there facing is
that hstore is a string => string map, so a conversion from json to that will
be lossy (i.e. 'that "true" in hstore, is that the string "true" or a
converted boolean true from json?').

What you can do right now though is use PL/V8 to query the JSON fields. Then
you can use functional indexes to still being able to speed up queries. Yes.
You could that before but now there's a guarantee that a field of type json
contains just that, meaning that your application logic will get simpler.

~~~
masklinn
> One of the issues there facing is that hstore is a string => string map, so
> a conversion from json to that will be lossy

Or the insertion/conversion routine can assert that the JSON object is a
string:string mapping only.

~~~
joevandyk
Why would that be helpful? I'd want arrays, hashes, integers, etc.

~~~
masklinn
You won't get them _in an hstore[0] column_ , which was the context of my
reply. Of course in a JSON column you want full JSON support, but my and my
parent's post were about converting back and forth between hstore and json.

Since hstore only handles string:string mapping, the choice is either to
silently corrupt data by stringifying all values in the json->hstore encoding,
or erroring out if the input data is anything other than string:string.

The latter would be what I'd prefer, and more in line with usual Postgres
behavior.

[0] <http://www.postgresql.org/docs/9.2/static/hstore.html>

------
einhverfr
My two favorite features are not so high on the PR docs though.

The first is SECURITY BARRIER and LEAKPROOF which gives us an ability to
rethink how to multi-tenant applications. This is a game changer and will get
even better in future versions I am sure.

The second is NO INHERIT constraints, which I will certainly be making good
use of. It is also a complete game changer when it comes to table inheritance
and partitioning, and my main use will be things like CHECK (false) NOINHERIT
to ensure that a table in fact never has rows of its own.

There is an amazing amount of good stuff going on around Postgres right now.
Postgres-XC was recently released, and more. It is an amazing data modelling
platform and ORDBMS.

~~~
masklinn
Could you talk about them further and explain why they're your favorite new
features?

~~~
jeffdavis
Regarding the inheritance features, he's been writing an extensive series on
the subject recently:

[http://ledgersmbdev.blogspot.com/2012/08/intro-to-
postgresql...](http://ledgersmbdev.blogspot.com/2012/08/intro-to-postgresql-
as-object.html)

(though he should provide forward/backward links in the posts -- as it stands
you need to find the other parts in the archive links on the right)

~~~
einhverfr
Managing the forward/backward links in a series this long while it is still
coming out is a pain. They will be added as the series draws to a close next
week though.

------
EvanAnderson
I'm always impressed by the PostgreSQL team.

Personally, I'm excited about the range types and I can see immediate
usefulness for them. My own applications aside, anything that helps developers
create schemas that are better able to handle temporal data is a good thing.

~~~
jeltz
Range types combined with exclusion constraints solve the problem of not
allowing overlapping bookings using a database constraint. The solution is
simple, clean, and flexible unlike the workarounds.

Example of adding such a constraint:

    
    
      ALTER TABLE reservation ADD EXCLUDE USING gist (room WITH =, during WITH &&);
    

In this example a room cannot be double booked.

EDIT: This is a feature entirely unique to PostgreSQL.

~~~
jeffdavis
I'll be speaking on this use case next week at Postgres Open (
<http://postgresopen.org> ) as part of a larger demo of temporal databases in
postgresql.

Jonathan S. Katz will also be presenting on Range Types.

------
rabidsnail
I was expecting the json support, but SP-GiST is a very welcome surprise.
<http://www.postgresql.org/docs/9.2/static/spgist-intro.html>

User-extensible spacial index types. This makes Postgres perfect for online
machine learning.

------
r4vik
direct link to what's new:
[http://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9...](http://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.2)

------
spitfire
Now someone please make usable tools for it on OSX. Postgres badly needs
front-end tools of the quality of sequel pro.

I am a huge fan of Postgres, it's never let me down. But data exploration, ad
hoc querying and such is a pain in psql. These tools are badly needed.

~~~
mhurron
pgAdmin not work on OS X? I'm pretty sure it does.

Just found this:
[http://wiki.postgresql.org/wiki/Community_Guide_to_PostgreSQ...](http://wiki.postgresql.org/wiki/Community_Guide_to_PostgreSQL_GUI_Tools)

~~~
einhverfr
The complaint usually is "it doesn't look like a native app." Mac users are
famously picky about only using apps that look native ;-)

------
forgotmyhnlogin
The absolute best feature of 9.2 is that you can now add \x auto to your
psqlrc file and never have to suffer unreadable results again

~~~
joevandyk
what does '\x auto' do?

~~~
jeffdavis
It's a display feature in the "psql" client program.

Normal result:

    
    
         column1 | column2 | column3 
        ---------+---------+---------
               1 | a       |     9.9
               2 | b       |    19.9
        (2 rows)
    

Using \x:

    
    
        -[ RECORD 1 ]-
        column1 | 1
        column2 | a
        column3 | 9.9
        -[ RECORD 2 ]-
        column1 | 2
        column2 | b
        column3 | 19.9
    

The first form is tabular and works well for a few columns; but doesn't work
well when there are many columns, because the lines start to wrap. So you use
\x for wide tables to make the result readable (but, obviously, fewer rows are
shown at a time).

Using "\x auto" automatically chooses which format to use based on your
terminal width.

~~~
joevandyk
Nice!

I tried using it for a result that contains really wide columns. I'm seeing a
screen full of hyphens separating the rows.

You'd think that the hyphens would stretch across just one line of the screen,
instead of across the whole result set. See
[https://img.skitch.com/20120910-fn1abpp3w94yhg63hc8yemt4a4.p...](https://img.skitch.com/20120910-fn1abpp3w94yhg63hc8yemt4a4.png)

~~~
jeffdavis
Oh, interesting.

That's a problem for very wide fields, which aren't going to be handled very
well even using \x.

\x was meant to handle large numbers of fields, or slightly wider fields.

But you're right, maybe that could be cleaned up a little more.

------
lest
"PostgreSQL 9.2 will ship with native JSON support, covering indexes,
replication and performance improvements, and many more features. We are
eagerly awaiting this release and will make it available in Early Access as
soon as it’s released by the PostgreSQL community," said Ines Sombra, Lead
Data Engineer, Engine Yard.

------
jeltz
One thing I love about PostgreSQL development is all the small nice fixes
added in every version.

Of the small fixes in 9.1 my personal favorite is probably the cleanup of
pg_stat_activity. There are also many other nice small fixes like improved tab
completion for some commands and the ability to set environment variables in
psql.

------
tosivakumar
We are excited about Cascading Replication because it reduces network data
transfer over WAN when we have multiple Read Replicas within and across
datacenters.

------
gtirloni
mysql only gets mentioned 2 (now 3) times in this thread? oracle seems to be
doing a job!

~~~
flyinRyan
Why would anyone bring up mysql?

