
PostgreSQL 9.3 Beta 1 Released - sickpig
http://www.postgresql.org/about/news/1463/
======
masklinn
The short log is missing one of the most important and awesome new feature to
_application_ writers: a bunch of new PQresultErrorFields[0] on constraint
failure, providing access to (context-dependent) the raw schema name, table
name, column name, constraint name and datatype involved.

Previously these remained locked into the database and to get
programmatically-useable info (for logging, better error messages or to
translate in terms of e.g. your ORM) you had to parse error messages which
were potentially localized and usually lacked half the necessary information.

And even better, for python developers, psycopg2 already supports these
fields[1][2]

[0] [http://www.postgresql.org/docs/devel/static/libpq-
exec.html#...](http://www.postgresql.org/docs/devel/static/libpq-
exec.html#LIBPQ-PQRESULTERRORFIELD)

[1] <http://psycopg.lighthouseapp.com/projects/62710/tickets/149>

[2]
[http://initd.org/psycopg/docs/extensions.html#psycopg2.exten...](http://initd.org/psycopg/docs/extensions.html#psycopg2.extensions.Diagnostics)

~~~
btown
Where would one find out if anyone is working on a patch for ActiveRecord?

~~~
masklinn
The Rails/ActiveRecord bug tracker?

edit: the underlying connector might be a better idea to start with, as it'll
need to expose the fields somehow before an ORM can take advantage of them.

~~~
joevandyk
Looks like work has started on the underlying connector:
[https://bitbucket.org/ged/ruby-pg/issue/161/add-support-
for-...](https://bitbucket.org/ged/ruby-pg/issue/161/add-support-for-pg_diag_-
_name-error)

~~~
masklinn
I wouldn't say "work has started" when the bug has just been reported.

------
jeffdavis
So, what's still missing in postgres?

To make the responses as constructive as possible, please also specify:

* Whether you currently use postgres, and what other systems you use (presumably ones that do have the feature that is missing in postgres)

* Whether you currently need the feature, or whether you anticipate the need in the future, or whether you expect that other people will need it

* Whether the missing feature is preventing you from using postgres, or just a significant pain point (which could include ease-of-use issues)

~~~
joevandyk
I could really use UPSERT.

I'm using postgresql right now.

My use case: I could have a table like:

    
    
        create table items (id uuid primary key, attributes json);
    

I'd have a webapp using javascript for adding/editing new items. If the user
wants to add a new item, the client code would generate the uuid.

Editing and adding new rows would both use the same UPSERT/MERGE code -- no
need for separate insert or update statements -- if the uuid already exists,
it's assumed to be an update, if it doesn't, it's inserted as a new row.

~~~
psadauskas
Here's an UPSERT in postgresql:
<https://gist.github.com/paul/855efdecaaa2ec4deec7>

While we're at it, here's a "find or create" in a single atomic statement:
<https://gist.github.com/paul/75ec84d131e36492b17b>

~~~
ocharles
I believe both of these still have race conditions, and are not the same as
truly atomic upserts. Granted the window is considerably smaller, but their is
still a race condition.

------
zapov
Postgres type system is so much better than any other RDBMS that it's a shame
NoSQL guys didn't spend more time building on top of it. While their optimizer
could use some work here and there it's improving all the time.

We are doing some pretty obscure stuff to it and it's serving us well. Oracle
on the other hand requires so much attention and special handling with it's
type system, that if there were an alternative, I wouldn't even bother with
it.

~~~
gbog
It seems you could easily get a nosql postgres if you limit yourself to one
table with two columns (id, json). Snark apart, it would work quite ok, no?

~~~
masklinn
the json support is fairly recent and doesn't support operating directly on
the data yet (e.g. you have to create functional indexes through plv8js and
you can't directly query on the document's content as you can with array, xml
or hstore columns)

~~~
jeltz
PostgreSQL 9.3 adds functions for accessing JSON data without having to use
plv8js.

~~~
masklinn
Yeah but since it's just being added, it precludes "NoSQL guys" having built
upon it, which is the original complaint.

~~~
jeffdavis
Another intepretation of zapov's comment is that it's a shame the NoSQL
projects didn't try achieving their goals by building postgres extensions (or
even adding new features).

A reasonable question. MongoDB could have focused their energies on really
good JSON support in postgres and making postgres sharding easier.

~~~
masklinn
An interesting interpretation, which I had not considered. Thank you.

------
DrJokepu
The PostgreSQL team just keeps on giving. There is nothing that can stop these
guys.

~~~
mintyfresh
In that spirit, what are the best resources for getting started and diving in
to working with PostgreSQL?

~~~
sickpig
Postgresql documentation is terrific, really [1]. Then you can also use pgsql
wiki [2]. Other useful resources are the searchable mailing lists archive [3].
Have a look also to pgcon.org web site, it's fully packed of presentation
material (mainly pdf slides) gathered since 2007, just to give you an example
look at Tom Lane's "<http://www.pgcon.org/2011/schedule/events/350.en.html>
[4]

Speaking of books a few of the best I'm aware of are:

\- PostgreSQL 9 Admin Cookbook (Simon Riggs, Hannu Krosing)

\- PostgreSQL 9.0 High Performance (Gregory Smith)

\- Instant PostgreSQL Backup and Restore How-to (Shaun M. Thomas)

all them from Packt Publishing

[1] <http://www.postgresql.org/docs/devel/static/>

[2] <http://wiki.postgresql.org/wiki/Main_Page>

[3] <http://www.postgresql.org/list/>

[4] <http://www.pgcon.org/2011/schedule/events/350.en.html>

~~~
jl6
The docs really are great. I once downloaded the whole lot as a PDF and read
it from the beginning on my phone during downtime (like 15 mins on the train).
Read all the way through to the parts that discuss internals then stopped. I
now appreciate it as a relational database, not just as a storage backend.

~~~
nieve
Great idea, you've inspired me to do the same just now. I've been using
PostgreSQL since ~2000 and love it, but I'm still finding new little corners
of the docs worth reading.

If anyone else wants to do this and has Calibre installed, the commandline
ebook-convert utility does a decent job. Change to the postgresql docs
directory and then run

    
    
      ebook-convert html/index.html postgresql_docs.mobi --authors="Postgresql" --title="Postgresql 9.2 Documentation"
    

(or substitute postgresql_docs.epub). There are a lot of tweaks that'd make it
better, but it's readable and properly handles the code font, etc. If you want
much more than that using the Calibre gui might be a good idea. Biggest issue
I'm seeing is that "Up" links & such are broken, so I'm working on that now.

------
simonw
"Indexed regular expression search"

How does that work from a computer science point of view?

UPDATE: Found this talk about it from PGCon last year:
<http://lanyrd.com/2012/pgcon/schdzf/>

~~~
jeltz
It is an extension of how 9.2 can use trigram indexes for LIKE '%foo%'
searches.

------
Refefer
Materialized Views! Well, sort of; it appears they don't automatically refresh
when data has been updated in the underlying tables ala Oracle's
implementation, but this gets you rather close.

~~~
masklinn
FWIW, since Posgtres has hard version cycles (6 months I believe?) the point
was to have a working base out and about, future versions will likely add
auto-update:

> This is a minimal implementation, but should still be useful in many cases.
> Currently data is only populated "on demand" by the CREATE MATERIALIZED VIEW
> and REFRESH MATERIALIZED VIEW statements.

> It is expected that future releases will add incremental updates with
> various timings, and that a more refined concept of defining what is "fresh"
> data will be developed. At some point it may even be possible to have
> queries use a materialized in place of references to underlying tables, but
> that requires the other above-mentioned features to be working first.

[http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;...](http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=3bf3ab8c563699138be02f9dc305b7b77a724307)

~~~
jeltz
The cycles are 12 months, otherwise you are correct.

------
salimmadjd
Eversince I heard Mike Krieger of Instagram Tech Talk at airbnb about how
scaleable Postgres was for them, I've become very interested in Postgres.
Probably will use it for my next startup.

------
sickpig
for a more in depth list of changes / new features look at release note[1] and
the wiki 9.3Beta1 page[2]

[1] <http://www.postgresql.org/docs/devel/static/release-9-3.html>

[2]
[http://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9...](http://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.3)

~~~
venus
Materialised views! Damn, I really could have used that a year and a bit ago..

~~~
netghost
They're a little less than what you might be expecting since they require you
to manually signal when they should be updated, but otherwise they sound
pretty great.

~~~
joevandyk
And you can't read from the matview while it's being updated, iirc.

------
zeroDivisible
I was working professionally with Oracle and MSSQL databases (with way bigger
focus on the latter). I did some freelance projects with MySQL databases,
heck, I managed to hack some small things on Pervasive once or twice.

PostgreSQL was always an outsider which I had ignored, but in my recent
position PostgreSQL is the main database engine for our whole stack. It took
my about a month to get used to the differences in the syntax and PostgreSQL
related stuff and I'm amazed about the features and quality of it, especially
when you remember that this is a free and open source database engine.

On the other hand, if PostgreSQL devs would start a Kickstarter project for
PGSQL specific version of Microsoft SQL Server Management Studio, my both
hands (and feet) would pay. I had tried Database Visualiser (paid version),
PGAdmin and few others, but none of those as is even close to the capabilities
of Management Studio.

Does anybody maybe know any alternative or have any tips to share?

~~~
olavgg
There are plenty if you are willing to pay. I've heard good things about EMS
SQL Management Studio for PostgreSQL for Windows administrators.

------
orf
The "User-defined background workers for automating database tasks" feature
sounds very interesting:
<http://www.postgresql.org/docs/9.3/static/bgworker.html>

I wonder if it would be possible to plug a python/node interpreter into that.

~~~
jeffdavis
"I wonder if it would be possible to plug a python/node interpreter into
that."

Sure, why not?

This is a great feature because it is a significant extensibility improvement.
And that's what postgres is really about: the idea that any user/developer
with an idea has many powerful APIs that can help them achieve that without
waiting for anyone else to agree that the idea is good.

------
willlll
Clearly the best addition in 9.3 is \watch to psql

~~~
iso8859-1
Here's a blog post about it:
[http://michael.otacoo.com/postgresql-2/postgres-9-3-feature-...](http://michael.otacoo.com/postgresql-2/postgres-9-3-feature-
highlight-watch-in-psql/)

------
zeckalpha
"pg_isready database connection checker"

Will pg have less time for YC and HN since he'll be checking so many database
connections now?

