PostgreSQL 9.3 Will Feature UPDATEable Views 68
Picking up work abandoned around Postgres 8.2, a patch recently hit the PostgreSQL 9.3 branch that adds SQL-92 automatically updatable views. For many common cases, you will no longer have to write hairy triggers to fake UPDATE support (e.g. if you have a view that hides a few internal columns). Limitations currently include only supporting views with at most one table in the FROM clause. This complements the under-advertised INSTEAD OF trigger support added in 9.1.
Good use-case? (Score:5, Interesting)
Back when I was working w/ Oracle (10 i think) I remember trying out update-able views, but I found that the rules and restrictions were such that it seemed like only trivial views could be updated and I decided I was easier to just work w/ the underlying tables and/or write sprocs whenever I need to do more far-reaching updates.
That said, I never dug that deep into the subject. Do any slashdotters know of a situation where an update-able view would be handy/ideal?
Re:Good use-case? (Score:4, Informative)
Do any slashdotters know of a situation where an update-able view would be handy/ideal?
Teradata is usually set up so that every basic DML operation (insert, update, delete) is done via views.
Re:Good use-case? (Score:5, Informative)
it seemed like only trivial views could be updated
Sadly true, the UPDATE has to be merged with the SELECT the view is built from to give an executable SQL. That's not always trivial to do.
Do any slashdotters know of a situation where an update-able view would be handy/ideal?
AFAIK the most common uses are:
a) Providing restricted views of tables to users with lower privileges
(eg a view of a users table where a connected user can read username, email etc but not password hash)
b) Providing a view for backwards compatibility with older applications when a table schema is updated, to avoid breaking queries in the older application until it is updated.
Re: (Score:2)
b) Providing a view for backwards compatibility with older applications when a table schema is updated, to avoid breaking queries in the older application until it is updated.
This doesn't seem interesting to me - why would my "select fld1 from tblA" break when I add fld2 to tblA schema? Or are you talking about poorly written code that does select * syntax?
A properly coded app will be fixing it's select syntax to future-proof it's queries anyway. Updateable views may make this easier, but select * should be avoided for all the other reasons (security, performance, scalability, etc) anyway.
Re: (Score:2)
Re: (Score:1)
I'd think of something like an UPDATEable filtered table. The view would just be a SELECT with some WHERE clause on some huge table. This way, you'd probably manage a much smaller amount of data, but you'd be able to update it. Maybe the data you filter out is not only unwanted, but it could also be obsolete, so you'd prefer not to filter it in the client application.
Just an hypothesis...
Re: (Score:2)
It's a good hypothesis. One of the major use-cases for views is security: give a user SELECT access to a limited VIEW and they can read just the allowed portion of a table that they do not have access to read normally. Previously, this user could not have their UPDATE access limited the same way, it would have to be hand-coded in a SECURITY DEFINER procedure, and hopefully it's defined right.
Re:Good use-case? (Score:5, Informative)
Only two use cases I can see are restricting access to a subset of columns (in which case a server side procedure works fine), or to allow legacy apps to work within new schemas (although this sounds like a really messy solution, and has that whole "temporary fix that gets left in forever" feel to it).
Re: (Score:2)
Every case where you are using a relational database with an application, since ideally every application should have its own set of views that form the application's interface to the database rather than using base tables. Having simple views (which usually will be fairly common if you are doing this) automatically updatable lowers the barriers to doing it.
Re:Good use-case? (Score:5, Insightful)
Other than in the case of a single-application database (and, even then, only in fairly simple cases), using views rather than base tables for application isn't wrapping the tables in boilerplate.
The "all application code should go through stored procedures" rule is, as I understand it, a pragmatic rule that was recommended largely because most actual database systems at the time that rule became popular had view support that fell far enough short of the ideal in the relational model that the better and older "all application code should run against views" approach was generally impractical (helped by the fact that it let you have application programmers that didn't understand anything about relational databases.)
Re: (Score:2)
Um, NO. The "all application code should go through stored procedures" is recommended because you parametrize all user input, which prevents all softs of nasty sql injection attacks from little Bobby Drop Tables.
http://psoug.org/blogs/mike/2010/04/11/little-bobby-tables/ [psoug.org]
Re: (Score:2)
Re: (Score:2)
Only if you trust every programmer who ever works with your database to do that, and do it correctly. Chances are some hotshot newbie is going to come along and not do it for some reason, leaving that database vulnerable.
Re: (Score:3)
If you don't, you shouldn't hire them to work on an application that interfaces directly with your database.
Re: (Score:2)
No one ever gets hired for any reason other than competence.
Re: (Score:2)
Re: (Score:2)
Maybe it mitigates things a little, but if your developers don't have a good understanding of databases then they're never going to produce a database-based application that works well. Yes, you can tell them 'we don't trust you' and set up a nice little DBA/developer power struggle. But it's not going to stop them designing their application badly, having it break because they don't understand how concurrency and locking works or doing stuff like writing joins in their code because they don't think to/can'
Re: (Score:2)
Not only that, what is the difference - security wise - of those two:
execute_query("EXISTS(SELECT * FROM users WHERE login = " + login " AND pass = " + pass_hash + ")")
execute_query("SELECT sp_authenticate(" + login + ", " + pass + ")")
Re: (Score:2)
You can build your input sanitization directly into the stored procedure; someone at Google once said, "If the policy isn't built into the code, it doesn't exist."
Re: (Score:2)
No, you can't. You can't put sanitization in the database. All the database sees is a query, it can't know if the arguments are right.
Re: (Score:2)
The stored proc in the example doesn't get called until the query dynamically built from the input is parsed and evaluated, so the stored proc is exactly as vulnerable to SQL injection as the SELECT query. Which is true in the general case. SQL injection isn't a result of using select queries over using stored procs, its a result of using dynamically generated SQL using untrusted (or improperly-trusted) input. Using prepared paramete
Re: (Score:2)
Ever try to stream a 1TB file to a DB using "sanitized" inputs? I can do that with parmeterized inputs and it's fast as writing to the file system. Not that I recommend doing this.
Re: (Score:2)
It's funny that you talk about parametrizing user input and then link to a page recommending the thoroughly-beaten-to-death idea of sanitizing user input. Yes, you should sanitize user input. You should also program your application so that nothing serious breaks if the sanitization fails. Because sooner or later it will fail.
SQL has had the problem sorted for ages, every decent database has a way to send commands which do not break no matter how many quote marks or semicolons the user tries to inject. Use
Re: (Score:2)
Well, I linked to it just because it was the first google hit that turned up the little bobby tables cartoon.
Re: (Score:2)
Parameterized queries are the way you 'do it in every database'. No escaping needed. It just works. That's the point.
Parameterizing user input doesn't require SPs (Score:3)
"You parameterize all user input" doesn't require use of stored procedures, or even views, it just requires not dynamically creating SQL by string concatenation/interpolation, and instead using the functions available in every database interface library that allow you to parameterize queries.
Obviously, not using user input it to dynamically create SQL is a fundamentally essential security pr
Re: (Score:2)
The web server does not have access to anything except to execute sprocs. This means if that user is compromised, the most they can do is call sprocs that hopefully have some basic business logic.
Letting a user have direct access to entire views/tables allows for a compromised user account to dump entire tables.
You can go so far a
Re: (Score:2)
I can do exactly the same thing using views in place of stored procs.
I can do exactly the same thing with views in place of stored procs fo
Re: (Score:2, Insightful)
Because the DBA is being paid the big bucks to write stored procedures for every possible way someone might try to whack the database with Crystal Reports.
Re: (Score:2)
Really? Because at my day gig, our developers write their stored procedures, not a DBA.
Re: (Score:2)
No it isn't.
Re:Good use-case? (Score:5, Informative)
Do any slashdotters know of a situation where an update-able view would be handy/ideal?
Two pre-existing applications accessing the same data, and each of them expects the data to be laid out in a (slightly different) schema.
So, define tables with a common physical representation of the data, and then define views to map that representation to each one of the application's expectations.
O, and only automatically updatable views have restrictions that make them useless. The INSTEAD OF trigger allows to make almost any view updatable, but you need to tell the DB how.
Re: (Score:1)
Agreed. I have a few cases where legacy code needs to update a newer backend, and the cleanest solution is to construct a wrapper view which allows the legacy code to remain unchanged. Another use would be to hide or isolate logic from the frontend application. For example, I have an application for production scheduling where the user can edit (what appears to be) a simple table, but the "table" is actually constructed on the fly with joins and logic. Yet another use is to force the backend to log a series
What about materialized views? (Score:2)
I'd rather lose UPDATEable views and finally get materialized views [wikipedia.org]. They would be a huge performance and clarity help. If you have a query that takes some time (due to data size) and it's source tables are not updated frequently, you can make a table with the results by hand, but the DB should be able to do it by itself.
Re: (Score:3)
Postgres has a status page [postgresql.org] on that, fwiw.
Re:What about materialized views? (Score:5, Informative)
Re: (Score:2)
An early implementation of Materialized Views was just submitted in November [postgresql.org] to the project. It may not be finished in time for PostgreSQL 9.3, but it will almost certainly be in the next release if not that one.
Re: (Score:2)
There is quite a bit work [nabble.com] going on on an initial implementation of matviews targetted for 9.3; losing updatable views wouldn't be likely to help you get matviews any sooner.
Re: (Score:1)
Yes but is MySQL webscale [youtube.com] yet?
Re: (Score:2)
And MySQL still lacks a lot of features that PostgreSQL has had for years (including SQL standard features like CTEs), many of which (CTEs included) are (at least, IMO) more generally useful than automatically updateable views.
An improvement (Score:4, Informative)
It is a nice feature... However I don't see it too useful for the way I work.
Being that if I have a view it is often because it needs to have many tables involved. And I would need to setup an on Insert or on Update rule on the view anyways.
That's a weirdly specific topic to post on /. (Score:4, Informative)
But nevertheless quite interesting. The idea of updatable views is certainly a good one, but it seems that the current limitations make this feature more or less useless for now:
Re: (Score:2)
Its not really so much a matter of "current limitations". You can go beyond it a little bit, maybe, but there is a limit not far from the current documented limits beyond which you lose logical clarity as to the semantics of what an update to a view means, so updatable views either need explicit definition (which you can already do
Re: (Score:1)
this is a general failure of the evolved SQL semantics. reversible evaluation is one of the big strengths of declarative programming.
If you have a table that contains (for example) customer invoices and payments, and a view that shows you balances outstanding by customer, how is this view supposed to be updated? Should Postgres just invent fictitious invoices or cash receipts to balance up the ledger?
If you have a view that pulls only a few of the columns from multiple tables, and the remaining columns do not have any obvious defaults, how is updating the view supposed to update the missing information for the underlying columns? (In man
Re: (Score:2)
Some of it certainly is; in a perfect relational system, the boundaries on automatically updatable views would be broader and include any view where every possible row in the view had a 1:1 relationship with a possible row in each of the tables the view was based on (which is the same as saying that the view includes a candidate key for every base table.)
But even in an ideal system there are plenty of conceivable views which don't have a natural inser
Following the trend... (Score:5, Funny)
If we follow the trend of other products, I would expect to see this in the 9.4 release notes:
* Removed "DISTINCT" and "GROUP BY". Usability studies show that most queries do not use them and new users find them confusing.
* "SELECT *" queries now return additional entries from Amazon.
* SQL language extensions to integrate Facebook and Twitter.
* Column order, if not specified in "ORDER BY", is heuristically determined from previous queries.
It's just great to see a release of anything that is actually better than the what it is replacing.
Re: (Score:1)
Parent got marked as a troll for some reason, don't now why, obviously: someone either lacking a sense of humour, or not knowing how America bullies other countries (or thinks it is okay).
In New Zealand illegal methods where used to obtain evidence in the Kim dot com case - and totally innocent people can't get at their own data - thanks to the US government & the media mafia.
As for PostgreSQL, that is my favourite database - I've also used MySQL, Oracle, and several other databases .
Re: (Score:3)
* SQL language extensions to integrate Facebook and Twitter.
This already exists as a plugin.
There is a Foreign Data Wrapper which allows you to make a twitter feed look like a local table.
FDW's exist for a large number of 3rd party data stores:
http://wiki.postgresql.org/wiki/Foreign_data_wrappers [postgresql.org]
I'll stick with triggers (Score:2)
Glad to see yet another nice feature for my favorite database. This one doesn't really excite me. I hardly ever want to update views without joins anyway.
The Rules System (Score:3, Interesting)
This is a nice feature for standardization (and thus, compatibility and portability with other SQL systems) but it's also important to know that PostgreSQL also has a "rules" system that allows for much more complicated view/table relationships. Rules allow for you to redirect the new and old values to updates, inserts and delete statements across as many tables/rows as required based on query being run against a view. It is very similar to a trigger really.
Read more about it here: http://www.postgresql.org/docs/9.2/static/rules.html
I used the Rules system to handle most of the security within The Schemaverse (schemaverse.com), an application written completely within a PostgreSQL database.
Re: (Score:2)
This is better than rules (or triggers), where it works not only because it is standard (which triggers are, as well), but also because it doesn't require explict definition of the actions.
Re: (Score:1)
That's a good point too. I hadn't really thought of that part of it but being able to cover more use-cases with less code certainly does sound helpful. You may end up just moving code around here though, if you were doing certain checks and balances during the rule/view trigger, this would get pushed to the underlying table to enforce security there (not that this is a bad thing admittedly).
I would love to see the performance differences between a system using this, with additional constraints or triggers
postgresql? no way (Score:3, Funny)
i dont know what all this talk about postgresql is i dont post anything in my web site i use mysql its a professional platform because the data is mine combined with php i can use mysql_real_escape_string because the php developers are really really good im off to stackoverflow to get help for my recursive mysql menu system i think recursive queries also work not sure ooohhh pokemon is on see you all soon!
Re: (Score:1)
Yeah, right on man!
Don't want no bad ACID!!
MySQL is far less ACIDic than postgresql!!!
Re: (Score:2)
Why didn't you use MongoDB? MongoDB is web scale.
News for Nerds (Score:2)
Re: (Score:2)
There plenty of things it doesn't do, what's your point? Many things it doesn't do because they were bad ideas, some just because they aren't that important.
This one is one that most people don't care about, it's just not that useful to MOST people.