Forgot your password?
typodupeerror
Databases

PostgreSQL 9.3 Will Feature UPDATEable Views 68

Posted by Unknown Lamer
from the long-overdue-features dept.
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.
This discussion has been archived. No new comments can be posted.

PostgreSQL 9.3 Will Feature UPDATEable Views

Comments Filter:
  • Good use-case? (Score:5, Interesting)

    by nobodyman (90587) * on Wednesday December 12, 2012 @01:16PM (#42262315) Homepage

    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)

      by magarity (164372) on Wednesday December 12, 2012 @01:19PM (#42262375)

      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)

      by Anonymous Coward on Wednesday December 12, 2012 @01:25PM (#42262465)

      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.

      • by rsborg (111459)

        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.

        • by ppanon (16583)
          The select won't break unless, as you put it a select * is used. But an insert on the base table would break if your additional fld2 is non-nullable without a default value.
    • by Metiu (14532)

      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...

      • by Qzukk (229616)

        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)

      by Anrego (830717) * on Wednesday December 12, 2012 @01:40PM (#42262699)

      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).

    • Do any slashdotters know of a situation where an update-able view would be handy/ideal?

      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, Informative)

      by ArsenneLupin (766289) on Wednesday December 12, 2012 @01:46PM (#42262755)

      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.

      • by Anonymous Coward

        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

  • 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.

  • An improvement (Score:4, Informative)

    by jellomizer (103300) on Wednesday December 12, 2012 @01:23PM (#42262429)

    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.

  • by mobby_6kl (668092) on Wednesday December 12, 2012 @01:26PM (#42262473)

    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:

    • The view must have exactly one entry in its FROM, which must be a table or another updatable view.
    • The view definition must not contain WITH, DISTINCT, GROUP BY, HAVING, LIMIT, or OFFSET clauses at the top level.
    • The view definition must not contain set operations (UNION, INTERSECT or EXCEPT) at the top level.
    • All columns in the viewâ(TM)s select list must be simple references to columns of the underlying relation. They cannot be expressions, literals or functions. System columns cannot be referenced, either.
    • No column of the underlying relation can appear more than once in the viewâ(TM)s select list.
    • The view must not have the security_barrier property.
    • 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

      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

  • by KavyBoy (35619) on Wednesday December 12, 2012 @01:59PM (#42262921)

    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.

    • by rtaylor (70602)

      * 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]

  • 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)

    by Anonymous Coward on Wednesday December 12, 2012 @02:20PM (#42263157)

    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.

    • 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.

      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.

      • by Abstra_t (2793773)

        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

  • by Anonymous Coward on Wednesday December 12, 2012 @02:34PM (#42263325)

    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!

  • I can't remember an article here on /. that so magnificently qualifies as "News for Nerds" as this one. Bravo.

Never buy from a rich salesman. -- Goldenstern

Working...