On Wed, 6 Jan 2010 13:01:02 -0800,
Richard Broersma <richard.broersma@gmail.com> wrote:
> On Wed, Jan 6, 2010 at 12:40 PM, Seb <spluque@gmail.com> wrote:
>> I'm trying to create a rule to be applied on update to a view that
>> consists of two joined tables. Table 'shoes' below is left-joined
>> with table 'shoelaces' in the view 'footwear'. I'd like to create a
>> simple update rule on the view, only if the value of a common column
>> corresponds to an inexistent record in 'shoelaces', so the result is
>> an INSERT into 'shoelaces' with the new record:
> A couple of year's ago, I was seriously looking into update-able
> views. But from my experience, I'm sorry to say you not going to find
> a robust solution to this problem. There are at least three problems
> with joined table update-able views: 1) You can only issue
> insert-update-delete statements that will only affect one row. 2) You
> cannot serialize the update of a view's virtual row like you can with
> a table's row. This allow leave the possibility of concurrent update
> anomalies. 3) Application frameworks that use optimistic locking or
> use the updated row count for validation will complain (and
> automatically roll-back your work) when you attempt to perform an
> update.
> The official use for update-able views is for limiting the results
> from a *single* base table.
> Having said all of this, it is possible to do what your describing.
> I've seen Keith Larson make update-able views from a composite of
> selected UNION and FULL OUT JOIN queries. But his solution was
> extremely hackish.
Thank you, Richard. So IIUC, this may not be problematic in my
particular case of a single user database, where I have some control
over concurrent operations, i.e. the possibility of those anomalies is
minimal (or at least is under my control to a large extent). WRT item
(1), in the example I showed (with the last rule), the following update
appears to work correctly:
UPDATE footwear SET sl_name='sl3' WHERE sh_name='sh2' OR sh_name='sh4';
where 2 tuples are inserted into shoelaces, as expected. Maybe you're
referring to views with other types of joined tables? Do you think the
NOT EXISTS statement in my last rule makes sense in the context of what
I described? I'm not sure I'm following the docs on the rule system
properly on how the NEW and OLD relations should be used, especially the
apparent contradiction in the "condition" parameter.
At any rate, I'm thankful for the warning about the limitations of
updteable views.
--
Seb
