pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedFwd: conditional rule not applied
5+ messages / 3 participants
[nested] [flat]
* Fwd: conditional rule not applied
@ 2010-01-06 20:40 Seb <spluque@gmail.com>
0 siblings, 2 replies; 5+ messages in thread
From: Seb @ 2010-01-06 20:40 UTC (permalink / raw)
To: pgsql-sql
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.
Topics:
conditional rule not applied
Re: conditional rule not applied
Re: conditional rule not applied
Re: conditional rule not applied
Re: conditional rule not applied
Hi,
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:
---<--------------------cut here---------------start------------------->---
CREATE TABLE shoes (
sh_id serial PRIMARY KEY,
sh_name text,
sh_avail integer
);
CREATE TABLE shoelaces (
sl_id serial PRIMARY KEY,
sh_id integer REFERENCES shoes,
sl_name text
);
INSERT INTO shoes (sh_name, sh_avail)
VALUES ('sh1', 2), ('sh2', 0), ('sh3', 4), ('sh4', 3);
INSERT INTO shoelaces (sh_id, sl_name)
VALUES (1, 'sl1'), (3, 'sl2');
SELECT * FROM shoes;
sh_id | sh_name | sh_avail
-------+---------+----------
1 | sh1 | 2
2 | sh2 | 0
3 | sh3 | 4
4 | sh4 | 3
SELECT * FROM shoelaces;
sl_id | sh_id | sl_name
-------+-------+---------
1 | 1 | sl1
2 | 3 | sl2
(2 rows)
CREATE VIEW footwear AS
SELECT sh.sh_id, sh_name, sh_avail, sl_name
FROM shoes sh LEFT JOIN shoelaces sl USING (sh_id);
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)
CREATE RULE footwear_nothing_upd AS
ON UPDATE TO footwear DO INSTEAD NOTHING;
CREATE RULE footwear_newshoelaces_upd AS
ON UPDATE TO footwear
WHERE NEW.sl_name <> OLD.sl_name AND OLD.sl_name IS NULL
DO
INSERT INTO shoelaces (sh_id, sl_name)
VALUES(NEW.sh_id, NEW.sl_name);
-- Testing: result should be a new record in 'shoelaces'
UPDATE footwear SET sl_name = 'sl3' WHERE sh_name = 'sh2';
-- but that doesn't happen:
SELECT * FROM shoelaces;
sl_id | sh_id | sl_name
-------+-------+---------
1 | 1 | sl1
2 | 3 | sl2
(2 rows)
---<--------------------cut here---------------end--------------------->---
Any tips would be much appreciated.
--
Seb
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Wed, 30 Dec 2009 19:39:15 -0600,
Seb <spluque@gmail.com> wrote:
> CREATE RULE footwear_nothing_upd AS
> ON UPDATE TO footwear DO INSTEAD NOTHING;
> CREATE RULE footwear_newshoelaces_upd AS
> ON UPDATE TO footwear
> WHERE NEW.sl_name <> OLD.sl_name AND OLD.sl_name IS NULL
> DO
> INSERT INTO shoelaces (sh_id, sl_name)
> VALUES(NEW.sh_id, NEW.sl_name);
I think my error is in the test expression, which doesn't deal properly
with the null value, so correcting:
CREATE RULE footwear_nothing_upd AS
ON UPDATE TO footwear DO INSTEAD NOTHING;
CREATE RULE footwear_newshoelaces_upd AS
ON UPDATE TO footwear
WHERE NEW.sl_name IS DISTINCT FROM OLD.sl_name AND OLD.sl_name IS NULL
DO
INSERT INTO shoelaces (sh_id, sl_name)
VALUES(NEW.sh_id, NEW.sl_name);
However, could a more direct and robust test for an inexistent record in
'shoelaces' be made?
--
Seb
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Wed, 30 Dec 2009 20:04:51 -0600,
Seb <spluque@gmail.com> wrote:
> On Wed, 30 Dec 2009 19:39:15 -0600,
> Seb <spluque@gmail.com> wrote:
> CREATE RULE footwear_nothing_upd AS
>> ON UPDATE TO footwear DO INSTEAD NOTHING; CREATE RULE
>> footwear_newshoelaces_upd AS ON UPDATE TO footwear WHERE NEW.sl_name
>> <> OLD.sl_name AND OLD.sl_name IS NULL DO INSERT INTO shoelaces
>> (sh_id, sl_name) VALUES(NEW.sh_id, NEW.sl_name);
> I think my error is in the test expression, which doesn't deal
> properly with the null value, so correcting:
> CREATE RULE footwear_nothing_upd AS
> ON UPDATE TO footwear DO INSTEAD NOTHING;
> CREATE RULE footwear_newshoelaces_upd AS
> ON UPDATE TO footwear
> WHERE NEW.sl_name IS DISTINCT FROM OLD.sl_name AND OLD.sl_name IS NULL
> DO
> INSERT INTO shoelaces (sh_id, sl_name)
> VALUES(NEW.sh_id, NEW.sl_name);
> However, could a more direct and robust test for an inexistent record
> in 'shoelaces' be made?
Any ideas? I'm not sure this is the best way to test whether the record
to update corresponds to a inexistent record in 'shoelaces'. Thanks.
--
Seb
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Tue, 05 Jan 2010 20:20:13 -0600,
Seb <spluque@gmail.com> wrote:
> On Wed, 30 Dec 2009 20:04:51 -0600,
> Seb <spluque@gmail.com> wrote:
> On Wed, 30 Dec 2009 19:39:15 -0600,
>> Seb <spluque@gmail.com> wrote:
> CREATE RULE footwear_nothing_upd AS
>>> ON UPDATE TO footwear DO INSTEAD NOTHING; CREATE RULE
>>> footwear_newshoelaces_upd AS ON UPDATE TO footwear WHERE NEW.sl_name
>>> <> OLD.sl_name AND OLD.sl_name IS NULL DO INSERT INTO shoelaces
>>> (sh_id, sl_name) VALUES(NEW.sh_id, NEW.sl_name);
>> I think my error is in the test expression, which doesn't deal
>> properly with the null value, so correcting:
>> CREATE RULE footwear_nothing_upd AS
>> ON UPDATE TO footwear DO INSTEAD NOTHING;
>> CREATE RULE footwear_newshoelaces_upd AS
>> ON UPDATE TO footwear
>> WHERE NEW.sl_name IS DISTINCT FROM OLD.sl_name AND OLD.sl_name IS NULL
>> DO
>> INSERT INTO shoelaces (sh_id, sl_name)
>> VALUES(NEW.sh_id, NEW.sl_name);
>> However, could a more direct and robust test for an inexistent record
>> in 'shoelaces' be made?
> Any ideas? I'm not sure this is the best way to test whether the
> record to update corresponds to a inexistent record in
> 'shoelaces'. Thanks.
Would this express the intention any better?
CREATE RULE footwear_nothing_upd AS
ON UPDATE TO footwear DO INSTEAD NOTHING;
CREATE RULE footwear_newshoelaces_upd AS
ON UPDATE TO footwear
WHERE NOT EXISTS (SELECT sh_id FROM shoelaces WHERE NEW.sh_id=shoelaces.sh_id)
DO
INSERT INTO shoelaces (sh_id, sl_name)
VALUES(NEW.sh_id, NEW.sl_name);
--
Seb
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Wed, 06 Jan 2010 09:39:45 -0600,
Seb <spluque@gmail.com> wrote:
> Would this express the intention any better?
> CREATE RULE footwear_nothing_upd AS
> ON UPDATE TO footwear DO INSTEAD NOTHING;
> CREATE RULE footwear_newshoelaces_upd AS
> ON UPDATE TO footwear
> WHERE NOT EXISTS (SELECT sh_id FROM shoelaces WHERE NEW.sh_id=shoelaces.sh_id)
> DO
> INSERT INTO shoelaces (sh_id, sl_name)
> VALUES(NEW.sh_id, NEW.sl_name);
Adding to my confusion here, is the fact that the rule above seems to
work well, even though the docs say:
---<--------------------cut here---------------start------------------->---
condition
Any SQL conditional expression (returning boolean). The condition
expression cannot refer to any tables except NEW and OLD, and cannot
contain aggregate functions.
---<--------------------cut here---------------end--------------------->---
So the WHERE condition in the rule above should not be allowed since it
does reference a table other than NEW and OLD in the EXISTS statement.
Any enlightening comments appreciated.
--
Seb
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Fwd: conditional rule not applied
@ 2010-01-06 21:01 Richard Broersma <richard.broersma@gmail.com>
parent: Seb <spluque@gmail.com>
1 sibling, 1 reply; 5+ messages in thread
From: Richard Broersma @ 2010-01-06 21:01 UTC (permalink / raw)
To: Seb <spluque@gmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Fwd: conditional rule not applied
@ 2010-01-06 21:31 Seb <spluque@gmail.com>
parent: Richard Broersma <richard.broersma@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Seb @ 2010-01-06 21:31 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Fwd: conditional rule not applied
@ 2010-01-07 15:31 Leo Mannhart <leo.mannhart@beecom.ch>
parent: Seb <spluque@gmail.com>
1 sibling, 1 reply; 5+ messages in thread
From: Leo Mannhart @ 2010-01-07 15:31 UTC (permalink / raw)
To: Seb <spluque@gmail.com>; +Cc: pgsql-sql
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
,in p_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 sh set 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
update shoes 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 shoelaces where 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Fwd: conditional rule not applied
@ 2010-01-08 03:43 Seb <spluque@gmail.com>
parent: Leo Mannhart <leo.mannhart@beecom.ch>
0 siblings, 0 replies; 5+ messages in thread
From: Seb @ 2010-01-08 03:43 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2010-01-08 03:43 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-01-06 20:40 Fwd: conditional rule not applied Seb <spluque@gmail.com>
2010-01-06 21:01 ` Richard Broersma <richard.broersma@gmail.com>
2010-01-06 21:31 ` Seb <spluque@gmail.com>
2010-01-07 15:31 ` Leo Mannhart <leo.mannhart@beecom.ch>
2010-01-08 03:43 ` Seb <spluque@gmail.com>
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