Postgresql 9.3 Released
postgresql.org
postgresql.org
It's already available in psycopg2 (since 2.5)[2] and ruby-pg (in 0.16)[3], it may be available in other adapters (outside of libpq obviously).
[0] http://www.postgresql.org/docs/9.3/static/libpq-exec.html#LI...
[1] without having to perform string munging on localized error messages, which probably doesn't include half of them
[2] http://psycopg.lighthouseapp.com/projects/62710/tickets/149
[3] https://bitbucket.org/ged/ruby-pg/issue/161/add-support-for-...
Giving this sort of detail direct to users is generally a bad idea for two reasons:
1. It can scare the living bejesus out of some of them.
2. You are handing out knowledge of your applications inner workings. If you have an unfound injection route a malicious entity might be able to use of your error messages to either find the injection flaw and/or find things to do with it once found.
This sort of detail should be logged of course, but give the users a simple code to report to you when reporting the problem ("please quote issue XY0009 when contacting support about this issue") and record the detail against that for your reference.
More information should allow you to produce better reporting. It's also not necessary to present all the information to your users, you can also make better error logs that users can send to the developers.
By all means have a developers mode which does hand out the information more readily (though I tend to be wary of that in case it gets left on in production, and because it is an extra code path that needs to be properly tested), or report the detail directly if it is an internal tool. But the post I was referring to infored (to my mind) handing the detail out to end users (and by "end users" I mean the untrustworthy mob that is the general public).
> It's also not necessary to present all the information to your users, you can also make better error logs that users can send to the developers.
Hence my last para, where I suggest giving the user reference to the detail without giving them the actual details (the code to give to you for looking up the stored exception report). Giving all the info to the user as an encrypted package would work too (in that case "please quote this code in any issue reports" becomes "please copy+paste this block of text into any problem report").
I can't fathom how you'd come to such a conclusion from:
> translate error information back into your application domain and generate logging and error messages which make sense to developers & end users.
which describes more or less the opposite of "giving the error details out fairly directly".
By having error details in a programmatic format, you can now generates messages which are actually useful and sensible to developers and/or end users if you wish to. Something which you could not do before.
Using the information to give a user more indication of whether they should try again or report the issue and wait would be useful and safe, but if you are trying to report to the user "that code already exists, they must be unique" or "name missing, it must be provided" then you have failed input validation as that sort of thing should be picked up on much earlier in a request lifecycle IMO.
I'm surprised how often I still see a full exception report echoed all the way from the data layer of an application up to my browser.
1. Not necessarily, most applications could use a well-designed schema to let the database do these validations and send improved results back to the user, it avoids duplicating (or triplicating) validation information. Especially for things like UNIQUE constraints, you're going to hit the database in-application when the DB will do it anyway? Unless it skips a significant amount of application work it's a complete waste of developer time (and likely application time as well).
2. A good schema can do significantly more complex, interesting and expensive (especially in-application if they need database data) validations than a mere NOT NULL check.
3. In-transaction issues can't be caught by the application server, they'll only blow up in the database.
> I'm surprised how often I still see a full exception report echoed all the way from the data layer of an application up to my browser.
Thus confirming that you're completely missing the point. Again, if one wanted to send database error messages to the end user one could already easily do so.
Due to concurrency, it's almost impossible to enforce unique constraints properly without actually executing the insert/update. Sometimes you can catch it ahead of time, but it's not guaranteed.
Knowing precisely which constraint failed helps improve the error message you give to the user, which may allow them to correct the problem. For instance, if you have two unique constraints involved in a transaction, your application can figure out which one was violated, and you can use that to give the user directions to correct it (e.g. "choose a different username" versus "that email address already has an account here, click here to send you the username").
Why? What is wrong with letting it get picked up where you already have the validation anyways instead of repeating the validation in two different languages?
"Name missing, it must be provided" is an internal validation of the entity which could, in principle, be done reliably in the application; "that code already exists, they must be unique" is a validation against other entities in the DB and therefore could not reliably be done anywhere except in the DB server itself if you have any concurrency.
Now, PostgreSQL can do any data validation you want to do in the application, it can do so at least partially declaratively. You can then pass back messages that the application can use in error handling.
I am actually working on frameworks which essentially delegate most of this to PostgreSQL. It's a very powerful approach but it means the db just doesn't trust the application.
Which has its advantages. The DB essentially becomes an API/service used by the application rather than an integral part thereof, and thus multiple applications can be cleanly plugged into the database instead of an "owner" application providing a service backed by the owned DB.
Exactly, which is what Martin Fowler is pushing with NoSQL... The idea that many people have been using RDBMS's as encapsulated databases for decades is usually lost on the NoSQL marketing though.
But I'd suggest asking on the mailing list about the possibility of getting multiple errors in the future. Not sure that makes sense from the database's point of view though, they're not "validation" issues as far as it's concerned they're integrity errors, a query is putting the db in an invalid state and thus rejected. Because the state is invalid, I'm not sure how the db would progress to get more errors.
> they're integrity errors, a query is putting the db in an invalid state and thus rejected. Because the state is invalid, I'm not sure how the db would progress to get more errors.
However it's a tremendously useful development tool.
"Prevent non-key-field row updates from blocking foreign key checks (Álvaro Herrera, Noah Misch, Andres Freund, Alexander Shulgin, Marti Raudsepp, Alexander Shulgin)
This change improves concurrency and reduces the probability of deadlocks when updating tables involved in a foreign-key constraint. UPDATEs that do not change any columns referenced in a foreign key now take the new NO KEY UPDATE lock mode on the row, while foreign key checks use the new KEY SHARE lock mode, which does not conflict with NO KEY UPDATE. So there is no blocking unless a foreign-key column is changed."
EDIT: And even more thanks to Álvaro and the other developers working on it,
Thank you so much, PostgreSQL team!
As always, there's one feature that makes me want to upgrade immediately. This time it's the NO KEY UPDATE lock mode which will greatly improve the performance of our main application during its lengthy importer runs.
Using pg_upgrade, switching major releases has become something comparably simple to do over the last years, though after last years issues in 9.2.0 (some IN() queries didn't return the correct results), I'm inclined to wait for 9.3.1 this time around.
* The improvements to foreign data wrappers to allow them to be writeable along with the Postgres FDW will improve visibility for the functionality
* Lateral joins
* Materialized views
* The checksums for checking against corruption
This feature will notably increase usability for people who are just getting started on Postgres. Now, I just wish they up the default config value from 32MB to something that's more in line with today's systems.
https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_...
Is there any context in which it would make sense to set up circular replication? This would necessitate that all of your nodes be read-only, so I'm not sure what there would be to replicate.
In any case, a bunch of these new features are hot. Particularly excited about fast failover and custom background workers.
It depends on if each node in the loop knows that it's already seen the replication data before and thus logs and ignores it. That's basically how MySQL (multi-master) replication loops work.
"Automatically updatable VIEWs"
But then read this:
https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_...
"Simple views can now be updated in the same way as regular tables. The view can only reference one table (or another updatable view) and must not contain more complex operators, join types etc."
As primarity a database developer, to me this is useless. Not sure why I'd want a view for only one table.
For permissions there has been column level perms for some time, which is more efficient than multiple views.
Not exactly. The updated columns may only unambiguously reference one table, but you can execute an update on a view which references multiple tables.
This kind of updateable view is pretty much the standard. Oracle doesn't destructure VIEWs to route data to tables. If you think about it, this is not a simple problem to solve.
As a contrast, I really like Riak's ability to just work with one node down...
They do support automatic failover too. The docs are in the Github repo somewhere.
But yeah - if you need automatic failover and master election, you still need third-party tools. Some have had success with pgPool as a out-of-the-box solution (I haven't. I had severe reliability issues with pgPool. You might be more lucky), others produce their own scripts.
The process isn't complicated, it just requires you reading a lot of manpages and thinking ahead, but once you got the process down, postgres itself is reliable enough that its (admittedly limited) tools just work (which is a very good thing).
As long as Postgres doesn't do master-master replication, failover will always be a complicated topic to deal with.
One feature which would be really nice to have is the ability to do a manual switchover, ie making the existing master into a replica and an existing replica into the new master.
Another poster mentioned repmgr which looks good but hasn't had a release in sometime ( https://github.com/2ndQuadrant/repmgr/blob/master/HISTORY ) with 2 new Postgres releases since, although there does seem some sporadic work on a new beta.
Now you change R1 to master, and take M1 "offline", afterwards turn M1 back on, and have it become a slave to R1.
See http://wiki.postgresql.org/wiki/What's_new_in_PostgreSQL_9.3...
nice.
\o/ for PostgreSQL, anyway.
0: http://oldblog.antirez.com/post/redis-persistence-demystifie...
Very cool to build a full text search index based on things inside of JSON fields with about 10 minutes of time.
Yes, using the ->> operator you should be able to create an index on a JSON field, e.g.
CREATE INDEX ON table ((field->>'thing'))
Redis - No clue what you're saying here. If you're using Redis then you're keeping your data in memory and dealing with low level structures. It's for completely different use cases. A classic example is rate limiting for an API. Doing it in Postgres with a disk I/O per request would cripple any semi-popular API. The data doesn't need to be exactly persistent (if we miss a few updates due to the server crashing we don't really care). In exchange for that it's blazing fast for individual writes that we can batch together to backup to something like Postgres (or Redis's built in persistence like AOF).
Redis is a good complement to Postgres. It provides caching and fast, non-persistent data store, while Postgres is the durable database.
See for example Mongres, https://github.com/umitanuki/mongres
I hope this allows frameworks like Meteor to trade up to Postgres.
Sure, the UI is ugly and it blocks on DB ops, but it works. Crashes I haven't had much of tbh
It looks like the author is entertaining the idea of postgres support (if you read further down the thread). A timely donation might make it happen.
Everyone has their own preferences for administration tools, so it's hard to find a one-to-one replacement. You might find something at one of these places though:
http://wiki.postgresql.org/wiki/Community_Guide_to_PostgreSQ... http://www.postgresonline.com/journal/index.php?/archives/13...
It has a lot of user friendly features particularly for data viewing and navigation, such as clicking through foreign key references to referenced rows, and the other way, looking up rows that reference this one, plus a lot of other stuff that I missed from other admin packages.
There's a demo here: http://teampostgresql.herokuapp.com/
Happy to answer any questions.
"Encountered more 0 entries in index columns list than there were expressions in 'indexprs' expressions list for index 'activity_emd5_00_expr_activity_type_expr1_idx'. Current count = '1', parsed expressions = 1, 'indexprs' value='({OPEXPR :opno 3963 :opfuncid 3948 :opresulttype 25 :opretset false :opcollid 100 :inputcollid 100 :args ({VAR :varno 1 :varattno 6 :vartype 114 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnoold 1 :varoattno 6 :location 43} {CONST :consttype 25 :consttypmod -1 :constcollid 100 :constlen -1 :constbyval false :constisnull false :location 60 :constvalue 12 [ 48 0 0 0 118 101 114 116 105 99 97 108 ]}) :location 57} {OPEXPR :opno 3963 :opfuncid 3948 :opresulttype 25 :opretset false :opcollid 100 :inputcollid 100 :args ({VAR :varno 1 :varattno 5 :vartype 114 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnoold 1 :varoattno 5 :location 89} {CONST :consttype 25 :consttypmod -1 :constcollid 100 :constlen -1 :constbyval false :constisnull false :location 108 :constvalue 6 [ 24 0 0 0 105 100 ]}) :location 105})'"
For your reference, here's that index:
"activity_emd5_00_expr_activity_type_expr1_idx" UNIQUE, btree ((source_details ->> 'vertical'::text), activity_type, (activity_details ->> 'id'::text))
Haveing table A, B, C with FK in A and B to C_column1, running function which updates A and other function which updates B, both refering to same C row via FK, but not modyfing it in any way - causes deadlock.
in pre 9.3, now its fixed, so I can use FKs again after so many years and its just creazy:)
That means native multi-master PG replication is on its way. Woot.
It (probably) won't be ACID compliant, but it'll be extremely useful for most use-cases.
Not planning to have database distributed all over the globe and even if I had to distribute the data I would do this on application level with tools like RabbitMQ.
That's why I am sceptical about this new cool and ambitious built-in MM replication.
I think most people struggle with setting up postgres auth. Personally, I find postgres' auth well designed.
Also, any libraries similar to ruby gem "apartment" that would make multitenancy with postgres easier in node.js world?
Bookshelf (http://bookshelfjs.org/) seemed quite nice though, but by the time I got around to it I decided my project wasn't very interesting anyways ;)