Обсуждение: Fwd: conditional rule not applied
Hi, Apologies for posting this from postgresql.general, but this failed to get any follow-ups in that NG. Hopefully someone here can shed some light on this.
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. -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug
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
Seb wrote:
> Hi,
>
> Apologies for posting this from postgresql.general, but this failed to
> get any follow-ups in that NG. Hopefully someone here can shed some
> light on this.
[snip]
I can give a 'first cut' solution.
But I strongly discourage from doing this in a real world application as
chances are big, that you'll forget something to implement correctly
(nullable fields come to mind immediately as an example).
Your example is also simplified as it makes no sense for instance, that
sh_name is nullable...
here we go:
------------------------------- cut --------------------------------
create or replace function footwear_upd(in p_sh_id_old integer,in p_sh_name_old varchar,in p_sh_avail_old
integer,inp_sl_name_old varchar,in p_sh_id_new integer,in p_sh_name_new varchar,in p_sh_avail_new integer,in
p_sl_name_new varchar
) returns void as $$
declare l_anz integer := 0;
begin if p_sh_id_old <> p_sh_id_new then select count(*) into l_anz from shoelaces sl where sl.sh_id =
p_sh_id_old; if l_anz > 0 then raise exception 'Cannot update shoes.sh_id referenced by
shoelace.sh_id'; else raise notice 'updating sh_id in shoes (but this doesn''t make sense'; update shoes
shset sh.sh_id = p_sh_id_new where sh.sh_id = p_sh_id_old; end if;end if;if p_sh_name_old <> p_sh_name_new then
updateshoes sh set sh.sh_name = p_sh_name_new where sh.sh_id =
p_sh_id_old;end if;if p_sh_avail_old <> p_sh_avail_new then update shoes sh set sh.sh_avail = p_sh_avail_new where
sh.sh_id=
p_sh_id_old;end if;if p_sl_name_old <> p_sl_name_new then update shoelaces sl set sl_name = p_sl_name_new where
sl.sh_id=
p_sh_id_new;end if;if p_sl_name_old is null and p_sl_name_new is not null then insert into shoelaces(sh_id, sl_name)
values(p_sh_id_new,p_sl_name_new);end if;if p_sl_name_old is not null and p_sl_name_new is null then delete from
shoelaceswhere sl_name = p_sl_name_old;end if;
end;
$$ language plpgsql;
CREATE RULE footwear_newshoelaces_upd AS ON UPDATE TO footwear do instead select footwear_upd(old.sh_id, old.sh_name,
old.sh_avail,old.sl_name, new.sh_id, new.sh_name, new.sh_avail, new.sl_name);
------------------------------- cut --------------------------------
this works nicely although the feedback is not really nice:
lem=# SELECT * FROM footwear;sh_id | sh_name | sh_avail | sl_name
-------+---------+----------+--------- 1 | sh1 | 2 | sl1 2 | sh2 | 0 | 3 | sh3 |
4 | sl2 4 | sh4 | 3 |
(4 rows)
lem=# UPDATE footwear SET sl_name = 'sl3' WHERE sh_name = 'sh2';footwear_upd
--------------
(1 row)
UPDATE 0
lem=# SELECT * FROM footwear;sh_id | sh_name | sh_avail | sl_name
-------+---------+----------+--------- 1 | sh1 | 2 | sl1 2 | sh2 | 0 | sl3 3 | sh3 |
4 | sl2 4 | sh4 | 3 |
(4 rows)
lem=# update footwear set sl_name=null where sh_name='sh2';footwear_upd
--------------
(1 row)
UPDATE 0
lem=# SELECT * FROM footwear;sh_id | sh_name | sh_avail | sl_name
-------+---------+----------+--------- 1 | sh1 | 2 | sl1 2 | sh2 | 0 | 3 | sh3 |
4 | sl2 4 | sh4 | 3 |
(4 rows)
lem=# UPDATE footwear SET sl_name='sl3' WHERE sh_name='sh2' OR
sh_name='sh4';footwear_upd
--------------
(2 rows)
UPDATE 0
lem=# SELECT * FROM footwear;sh_id | sh_name | sh_avail | sl_name
-------+---------+----------+--------- 1 | sh1 | 2 | sl1 2 | sh2 | 0 | sl3 3 | sh3 |
4 | sl2 4 | sh4 | 3 | sl3
(4 rows)
lem=#
Cheers, Leo
On Thu, 07 Jan 2010 16:31:29 +0100, Leo Mannhart <leo.mannhart@beecom.ch> wrote: [...] > I can give a 'first cut' solution. But I strongly discourage from > doing this in a real world application as chances are big, that you'll > forget something to implement correctly (nullable fields come to mind > immediately as an example). Your example is also simplified as it > makes no sense for instance, that sh_name is nullable... > here we go: [...] Thank you very much for your time on this. I can see that this is indeed a difficult thing to implement. I'll study your code carefully to understand the issues a little better. Cheers, -- Seb