From: Seb <spluque@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: Fwd: conditional rule not applied
Date: Wed, 06 Jan 2010 15:31:10 -0600
Message-ID: <877hrunco1.fsf@kolob.sebmags.homelinux.org> (raw)
References: <87fx6jm0fl.fsf@kolob.sebmags.homelinux.org>
<396486431001061301m1fa305fav211a98bb3b9f62f1@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: spluque@gmail.com
Subject: Re: Fwd: conditional rule not applied
In-Reply-To: <877hrunco1.fsf@kolob.sebmags.homelinux.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox