Beta
×

Welcome to the Slashdot Beta site -- learn more here. Use the link in the footer or click here to return to the Classic version of Slashdot.

Thank you!

Before you choose to head back to the Classic look of the site, we'd appreciate it if you share your thoughts on the Beta; your feedback is what drives our ongoing development.

Beta is different and we value you taking the time to try it out. Please take a look at the changes we've made in Beta and  learn more about it. Thanks for reading, and for making the site better!

PostgreSQL 9.3 Will Feature UPDATEable Views

Unknown Lamer posted about a year and a half ago | from the long-overdue-features dept.

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

cancel ×

68 comments

Sorry! There are no comments related to the filter you selected.

Good use-case? (4, Interesting)

nobodyman (90587) | about a year and a half ago | (#42262315)

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? (3, Informative)

magarity (164372) | about a year and a half ago | (#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? (5, Informative)

Anonymous Coward | about a year and a half ago | (#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.

Re:Good use-case? (0)

Anonymous Coward | about a year and a half ago | (#42263933)

You beat me to. There are some other niche use cases, but you pretty well nailed them.

Please mod up parent.

Re:Good use-case? (1)

rsborg (111459) | about a year and a half ago | (#42269955)

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:Good use-case? (1)

ppanon (16583) | about a year and a half ago | (#42270231)

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.

Re:Good use-case? (1)

Metiu (14532) | about a year and a half ago | (#42262529)

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:Good use-case? (1)

Qzukk (229616) | about a year and a half ago | (#42262697)

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? (4, Informative)

Anrego (830717) | about a year and a half ago | (#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).

Re:Good use-case? (1)

DragonWriter (970822) | about a year and a half ago | (#42262705)

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? (0)

Anonymous Coward | about a year and a half ago | (#42262837)

Why would you want to wrap every table in your database in boilerplate? All application code is going to be going through stored procedures anyway -- it's not like applications are making demands on schema.

Re:Good use-case? (4, Insightful)

DragonWriter (970822) | about a year and a half ago | (#42263117)

Why would you want to wrap every table in your database in boilerplate?

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.

All application code is going to be going through stored procedures anyway

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:Good use-case? (1)

LurkerXXX (667952) | about a year and a half ago | (#42263905)

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:Good use-case? (1)

w_dragon (1802458) | about a year and a half ago | (#42264453)

You can do that with prepared queries directly against tables, you don't need to use stored procedures to get that benefit.

Re:Good use-case? (1)

LurkerXXX (667952) | about a year and a half ago | (#42264481)

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:Good use-case? (2)

DragonWriter (970822) | about a year and a half ago | (#42266123)

Only if you trust every programmer who ever works with your database to do that, and do it correctly.

If you don't, you shouldn't hire them to work on an application that interfaces directly with your database.

Re:Good use-case? (1)

epine (68316) | about a year and a half ago | (#42268705)

If you don't, you shouldn't hire them to work on an application that interfaces directly with your database.

No one ever gets hired for any reason other than competence.

Re:Good use-case? (1)

ppanon (16583) | about a year and a half ago | (#42270247)

Two words: Code reviews. When you hire a hotshot newbie, you explain to him why it's done through prepared statements. If he understands and follows the secure programming guidelines, then he's gained knowledge and you've gained a better programmer. If you run a code review and find he hasn't learned,... hopefully his probation period isn't complete yet.

Re:Good use-case? (1)

xelah (176252) | about a year and a half ago | (#42271047)

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't be bothered to talk the DBA in to adding a new stored procedure for them.

And, of course, SQL injection is not the only security risk....if you're not monitoring and mentoring (or sacking...) developers who don't know how to do things well then this won't be your only problem.

Re:Good use-case? (1)

marcosdumay (620877) | about a year and a half ago | (#42268431)

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:Good use-case? (1)

TooMuchToDo (882796) | about a year and a half ago | (#42270017)

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:Good use-case? (1)

marcosdumay (620877) | about a year and a half ago | (#42271453)

You can build your input sanitization directly into the stored procedure;

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:Good use-case? (1)

DragonWriter (970822) | about a year and a half ago | (#42277045)

You can build your input sanitization directly into the stored procedure

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 parameterized queries avoids SQL injection, using stored procs, in and of itself, does not. (Certainly, most database interface libraries except very-low-level ones hide parameterization behind a function that also encapsulates generating the SQL for a stored proc calls, but that's also the case for most libraries when it comes to SELECT queries. The SQL injection problem comes into play when application developers don't use these functions -- either parameterizing themselves with a low-level library or using a higher-level library correctly -- and insist on generating their own SQL dynamically.)

Re:Good use-case? (1)

Bengie (1121981) | about a year and a half ago | (#42277909)

Parameterized inputs (prepared statements) can work with any binary data, this is not something sanitizing your inputs can handle. Parameterized inputs completely separates the command from the parameters.

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:Good use-case? (0)

Anonymous Coward | about a year and a half ago | (#42281825)

So what happens if the user specifies the password as

' || (SELECT password FROM users WHERE login = 'admin') || '

Re:Good use-case? (1)

amorsen (7485) | about a year and a half ago | (#42265067)

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 them. Don't try to escape, it will break.

Alas, not all other languages are as well protected as SQL. E.g. you cannot do parametrized regular expressions in Perl or Ruby, you are stuck with sanitizing and escaping. HTML is completely hopeless of course.

Re:Good use-case? (1)

LurkerXXX (667952) | about a year and a half ago | (#42265165)

Well, I linked to it just because it was the first google hit that turned up the little bobby tables cartoon.

Re:Good use-case? (1)

BitZtream (692029) | about a year and a half ago | (#42269025)

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 (2)

DragonWriter (970822) | about a year and a half ago | (#42265595)

The "all application code should go through stored procedures" is recommended because you parametrize all user input

"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 practice for most applications (there are some exceptions, such as if you are trying to build something equivalent to a web-based SQL console), but it has nothing to do with using stored procedures. In fact, its quite possible to use stored procs (which can, after all, be called directly from SQL) using dynamically-generated SQL and make all the same mistakes that are possible with doing the same thing with regular DML queries against views or tables. Sure, your database library make provide a method to call a stored proc that automatically parameterizes arguments rather than forcing you to dynamically generated SQL -- but it also almost certainly has the same thing for regular queries.

There may have been a time when this wasn't true of some popular database libraries, which may have also contributed to the popularity of the "use stored procs for everything" approach, but if there was such a time, it wasn't recently.

Re:Good use-case? (1)

Bengie (1121981) | about a year and a half ago | (#42277823)

Say I have a user called "WebClient" for my DB back-end. I can give that user execute permissions on specific stored procedures and allow access only to the schema of "web".

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 as to have the sprocs accept a token as a parameter, which the token maps to a given user account. The sprocs can enforce basic user permissions this way. Unless the the hacker has access to read/write to the token table, it will be very hard for them to get any data out of the system.

Re:Good use-case? (1)

DragonWriter (970822) | about a year and a half ago | (#42281271)

Say I have a user called "WebClient" for my DB back-end. I can give that user execute permissions on specific stored procedures and allow access only to the schema of "web".

I can do exactly the same thing using views in place of stored procs.

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.

I can do exactly the same thing with views in place of stored procs for the exposed access; in fact, I can -- transparently to any interfacing application -- use stored procs to implement any of the the operations on the views where the imperative functionality provided by stored procs is beneficial, while letting the rest be handled by SQL which the DMBS's optimizer can optimize.

Letting a user have direct access to entire views/tables allows for a compromised user account to dump entire tables.

Obviously, letting a user have direct SELECT access to base tables allows them to dump the contents of the entire table. Equally obviously, allowing them to have similar access to a view doesn't allow them to do that unless the view is defined by something like SELECT * FROM base_table. But, if you are worried about preventing that kind of dumping, you won't define a view available to the user that way.

You can go so far as to have the sprocs accept a token as a parameter, which the token maps to a given user account. The sprocs can enforce basic user permissions this way. Unless the the hacker has access to read/write to the token table, it will be very hard for them to get any data out of the system.

This bit of extra work is theoretically useful, I suppose, in that it could protect against the situation where the application's database user account is compromised, but neither the database superuser account nor the application itself (or the OS account it is running under) is compromised (if the application is compromised as well, it can simply collect valid user credentials to use); sane database security practices (e.g., not allowing the applications database user to logon with a simple username/password logon from arbitrary IP addresses) make it impractical to compromise the database user account of the application without, as a prequisite, compromising the application or its OS account or the database superuser account, so its less work to just use the database correctly.

Re:Good use-case? (2, Insightful)

Anonymous Coward | about a year and a half ago | (#42263249)

All application code is going to be going through stored procedures anyway

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:Good use-case? (1)

TooMuchToDo (882796) | about a year and a half ago | (#42270025)

Really? Because at my day gig, our developers write their stored procedures, not a DBA.

Re:Good use-case? (1)

Hognoxious (631665) | about a year and a half ago | (#42264407)

All application code is going to be going through stored procedures anyway

No it isn't.

Re:Good use-case? (4, Informative)

ArsenneLupin (766289) | about a year and a half ago | (#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.

Re:Good use-case? (1)

Anonymous Coward | about a year and a half ago | (#42263625)

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 of functions as one basic operation, for example I have consolidated a "part copy" operation (comprised of several inserts and other logic) into one simple log item (i.e. "INSERT INTO part_copy"). It also keeps the frontend code more streamlined.

To be honest, I never really had a problem with manually constructing my own updateable views in postgresql. In fact, being forced to get your hands dirty with updateable views in postgresql is a very instructive and rewarding process.

What about materialized views? (2)

Metiu (14532) | about a year and a half ago | (#42262397)

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:What about materialized views? (2)

Trepidity (597) | about a year and a half ago | (#42262487)

Postgres has a status page [postgresql.org] on that, fwiw.

Re:What about materialized views? (4, Informative)

schmiddy (599730) | about a year and a half ago | (#42262829)

Materialized views are on the way, hopefully for 9.3 [postgresql.org] . The first pass at this is fairly limited, you have to refresh the matview yourself (i.e. it supports only the "snapshot" type of matview maintenance, per terminology here [jonathangardner.net] ).

Re:What about materialized views? (1)

greg1104 (461138) | about a year and a half ago | (#42262847)

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:What about materialized views? (1)

DragonWriter (970822) | about a year and a half ago | (#42262893)

I'd rather lose UPDATEable views and finally get materialized views

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.

About time, MySQL has had this for years! (0)

Anonymous Coward | about a year and a half ago | (#42262405)

About time, MySQL has had this for years!

http://dev.mysql.com/doc/refman/5.0/en/view-updatability.html

Re:About time, MySQL has had this for years! (1)

Desler (1608317) | about a year and a half ago | (#42263225)

Yes but is MySQL webscale [youtube.com] yet?

Re:About time, MySQL has had this for years! (1)

DragonWriter (970822) | about a year and a half ago | (#42267003)

About time, MySQL has had this for years!

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

jellomizer (103300) | about a year and a half ago | (#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.

That's a weirdly specific topic to post on /. (3, Informative)

mobby_6kl (668092) | about a year and a half ago | (#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.

Re:That's a weirdly specific topic to post on /. (1)

DragonWriter (970822) | about a year and a half ago | (#42262951)

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) or end up with automatic-but-not-obvious behavior.

Re:That's a weirdly specific topic to post on /. (0)

Anonymous Coward | about a year and a half ago | (#42263357)

this is a general failure of the evolved SQL semantics. reversible evaluation is one of the big strengths of declarative programming.

Re:That's a weirdly specific topic to post on /. (1)

Anonymous Coward | about a year and a half ago | (#42263949)

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 many schemas, NULL won't be acceptable).

I could go on - each of the restrictions can be illustrated by cases where it would be entirely impossible, or at least highly ambiguous how to implement view updating. It is not a failure of SQL.

Re:That's a weirdly specific topic to post on /. (1)

DragonWriter (970822) | about a year and a half ago | (#42265957)

this is a general failure of the evolved SQL semantics.

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 insert/update interpretation, and if you wanted to make them "updatable", you'd need to define for your system what that even means.

Following the trend... (5, Funny)

KavyBoy (35619) | about a year and a half ago | (#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.

Re:Following the trend... (0)

Nivag064 (904744) | about a year and a half ago | (#42263479)

You forgot:

WHERE clauses now have a built in check to ensure you are not accessing copyright material that the local (*) media mafia don't want you too.

* here 'local' refers to American Media, which by their definition means: wherever you live, regardless of country

Re:Following the trend... (1)

Nivag064 (904744) | about a year and a half ago | (#42269909)

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:Following the trend... (2)

rtaylor (70602) | about a year and a half ago | (#42264829)

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

Re:Following the trend... (0)

Anonymous Coward | about a year and a half ago | (#42266379)

You would make a good Mark Zuckerberg. In fact, he probably has a job for you.

Nice. (0)

Anonymous Coward | about a year and a half ago | (#42262957)

Now for less-hairy trigger writing. Simply attaching an anonymous function instead of always requiring a function AND a trigger desc would be helpful already.

I'll stick with triggers (1)

Sean (422) | about a year and a half ago | (#42263067)

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 (3, Interesting)

Anonymous Coward | about a year and a half ago | (#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.

Re:The Rules System (1)

DragonWriter (970822) | about a year and a half ago | (#42266051)

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.

Re:The Rules System (1)

Abstra_t (2793773) | about a year and a half ago | (#42267313)

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 underneath to maintain necessary data, and a system using the rules system, handling the logic within the rule/trigger on the view.

My experience/projects are a bit unique though, standardization means little to me in my database. I just want awesome features. Nobody, sane at least, has had the late night inspiration to try and port The Schemaverse [schemaverse.com] to Oracle or SQL server.

-Abstrct (AC from parent)

postgresql? no way (3, Funny)

Anonymous Coward | about a year and a half ago | (#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!

Re:postgresql? no way (1)

Nivag064 (904744) | about a year and a half ago | (#42263513)

Yeah, right on man!

Don't want no bad ACID!!

MySQL is far less ACIDic than postgresql!!!

Re:postgresql? no way (1)

swilly (24960) | about a year and a half ago | (#42267345)

Why didn't you use MongoDB? MongoDB is web scale.

Catching up... (0)

hebcal (25008) | about a year and a half ago | (#42263503)

Mysql and MSSQL (and I assume Oracle and DB2) have had this for a while. Seems like the headline should be "PostgreSQL finally implements a feature that everybody else has had for years."

Re:Catching up... (0)

Anonymous Coward | about a year and a half ago | (#42263877)

"PostgreSQL implements useless functionality just because everybody else has it."

I've been a DBA and programmer for years, never longed for updateable views.

News for Nerds (1)

Un pobre guey (593801) | about a year and a half ago | (#42265323)

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

ZIM had this in the 1980's (0)

Anonymous Coward | about a year and a half ago | (#42268801)

The Zanthe Information Manager (an ER database) could deal with updateable views in the mid-1980's. It was sold to Sterling software around 1990, and Sterling was sold to IBM. Who knows what happened to it? It was ahead of current relational technology even at this late date.

Re:ZIM had this in the 1980's (1)

BitZtream (692029) | about a year and a half ago | (#42269067)

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.

Check for New Comments
Slashdot Login

Need an Account?

Forgot your password?

Submission Text Formatting Tips

We support a small subset of HTML, namely these tags:

  • b
  • i
  • p
  • br
  • a
  • ol
  • ul
  • li
  • dl
  • dt
  • dd
  • em
  • strong
  • tt
  • blockquote
  • div
  • quote
  • ecode

"ecode" can be used for code snippets, for example:

<ecode>    while(1) { do_something(); } </ecode>