pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedAW: Problems with RULE
4+ messages / 2 participants
[nested] [flat]
* AW: Problems with RULE
@ 2001-03-07 07:18 Jens Hartwig <jens.hartwig@t-systems.de>
0 siblings, 1 reply; 4+ messages in thread
From: Jens Hartwig @ 2001-03-07 07:18 UTC (permalink / raw)
To: 'Tom Lane' <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
JH:
> > [...]
> > I tried to implement the following rule: if someone wants to delete a
record
> > from a table t_xyz (id integer, deleted boolean) the record should get a
> > delete-flag (deleted = true). When this "pre-deleted" record is deleted
for
> > the next time it should be physically deleted from the database.
> > [...]
TL:
> In my experience, anything you want to do that can be expressed as
> an operation or condition on an individual target tuple of an
> INSERT/UPDATE/DELETE is best done in a trigger, for reasons of both
> performance and understandability. Rules are good for things that
> involve conditions on multiple tuples.
> [...]
I am afraid, that I do not really understand this: if I insert one record in
a view there also is only ONE tuple involved, isn't it? By the way, I admit
that my example is not really useful :-) It should only demonstrate the use
of rules for the book I am writing on.
Further I do not understand the following:
> You'd probably have better luck doing this with a trigger. With this
> rule, the DELETE query expands into two operations, which can
> be written
> as:
>
> UPDATE t_xyz SET deleted = true
> WHERE id IN
> (SELECT old.id FROM t_xyz old WHERE old.id = 1 AND
> old.deleted = false);
>
> DELETE FROM t_xyz WHERE id = 1 AND NOT (deleted = false);
What would have happened, if I executed an unconditional DELETE?
=> DELETE FROM t_xyz;
Which statement would have been generated by PostgreSQL in this case?
Best Regards, Jens
-----------------------------------------------------
T-Systems
Projektleiter
debis Systemhaus GEI GmbH
Hausanschrift: Eichhornstraße 3, 10785 Berlin
Postanschrift: 10785 Berlin
Telefon: (004930) 25 54-32 82
Telefax: (004930) 25 54-31 87
Mobiltelefon: (0170) 167 26 48
E-Mail: jens.hartwig@t-systems.de
Internet: http://www.t-systems.de
^ permalink raw reply [nested|flat] 4+ messages in thread
* AW: Problems with RULE
@ 2001-03-07 07:23 Jens Hartwig <jens.hartwig@t-systems.de>
0 siblings, 0 replies; 4+ messages in thread
From: Jens Hartwig @ 2001-03-07 07:23 UTC (permalink / raw)
To: dev@archonet.com; +Cc: pgsql-sql
Hello Richard,
this was a very precise analysis - thanks for the effort you made!
Nevertheless the Tom's explanation of the behaviour in case of views was
sufficient for me. But still I don't understand the behaviour in my case ...
Best regards, Jens
PS: I use 7.1b4
-----------------------------------------------------
T-Systems
Projektleiter
debis Systemhaus GEI GmbH
Hausanschrift: Eichhornstraße 3, 10785 Berlin
Postanschrift: 10785 Berlin
Telefon: (004930) 25 54-32 82
Telefax: (004930) 25 54-31 87
Mobiltelefon: (0170) 167 26 48
E-Mail: jens.hartwig@t-systems.de
Internet: http://www.t-systems.de
> -----Ursprüngliche Nachricht-----
> Von: dev@archonet.com [mailto:dev@archonet.com]
> Gesendet: Dienstag, 6. März 2001 19:10
> An: Jens Hartwig
> Cc: PSQL-Sql (E-Mail)
> Betreff: Re: [SQL] Problems with RULE
>
>
> On 3/6/01, 7:11:48 AM, Jens Hartwig <jens.hartwig@t-systems.de> wrote
> regarding [SQL] Problems with RULE:
>
> > Hello all,
>
> > I tried to implement the following rule: if someone wants
> to delete a
> record
> > from a table t_xyz (id integer, deleted boolean) the record
> should get a
> > delete-flag (deleted = true). When this "pre-deleted"
> record is deleted
> for
> > the next time it should be physically deleted from the database.
>
> Jens - more info
>
> Definitely a bug if my testing is correct (see below) - I'll file a
> report on it and include your example too (hope that's OK)
>
> - Richard Huxton
>
> -- OK define a table foo with data and a view voo showing
> -- even-numbered entries
> --
> richardh=> create table foo (a int, b text);
> CREATE
> richardh=> insert into foo values (1,'aaa');
> INSERT 1287580 1
> richardh=> insert into foo values (2,'bbb');
> INSERT 1287581 1
> richardh=> insert into foo values (3,'ccc');
> INSERT 1287582 1
> richardh=> insert into foo values (4,'ddd');
> INSERT 1287583 1
> richardh=> create view voo as select * from foo where (a % 2)=0;
> CREATE
> richardh=> select * from voo;
> a | b
> ---+-----
> 2 | bbb
> 4 | ddd
> (2 rows)
>
> -- Now define an insert rule with a where on voo
> --
> richardh=> CREATE RULE voo_ins_rule AS ON INSERT TO voo WHERE
> (NEW.a % 2)=0
> DO INSTEAD INSERT INTO foo VALUES (NEW.a, NEW.b);
> CREATE
> richardh=> insert into voo values (99,'zzz');
> ERROR: Cannot insert into a view without an appropriate rule
> richardh=> insert into voo values (98,'yyy');
> ERROR: Cannot insert into a view without an appropriate rule
> richardh=> select * from foo;
> a | b
> ---+-----
> 1 | aaa
> 2 | bbb
> 3 | ccc
> 4 | ddd
> (4 rows)
>
> richardh=> select * from voo;
> a | b
> ---+-----
> 2 | bbb
> 4 | ddd
> (2 rows)
>
> -- OK: rule wasn't accepted, so lets add another rule to voo
> without a
> where
> --
> richardh=> CREATE RULE voo_ins_rule2 AS ON INSERT TO voo DO
> INSTEAD INSERT
> INTO
> foo VALUES (NEW.a, NEW.b);
> CREATE
> richardh=> insert into voo values (99,'zzz');
> INSERT 1287602 1
> richardh=> insert into voo values (98,'yyy');
> INSERT 1287604 1
> richardh=> select * from foo;
> a | b
> ----+-----
> 1 | aaa
> 2 | bbb
> 3 | ccc
> 4 | ddd
> 99 | zzz
> 98 | yyy
> 98 | yyy
> (7 rows)
>
> richardh=> select * from voo;
> a | b
> ----+-----
> 2 | bbb
> 4 | ddd
> 98 | yyy
> 98 | yyy
> (4 rows)
>
> -- So: looks like either rule2 executes twice or both fire.
> -- Is it because we have a second rule?
> --
> richardh=> drop rule voo_ins_rule2;
> DROP
> richardh=> CREATE RULE voo_ins_rule3 AS ON INSERT TO voo
> WHERE (NEW.a %
> 2)=1 DO
> INSTEAD INSERT INTO foo VALUES (NEW.a, NEW.b);
> CREATE
> richardh=> insert into voo values (99,'zzz');
> ERROR: Cannot insert into a view without an appropriate rule
> richardh=> insert into voo values (98,'yyy');
> ERROR: Cannot insert into a view without an appropriate rule
> richardh=> select * from foo;
> a | b
> ----+-----
> 1 | aaa
> 2 | bbb
> 3 | ccc
> 4 | ddd
> 99 | zzz
> 98 | yyy
> 98 | yyy
> (7 rows)
>
> richardh=> select * from voo;
> a | b
> ----+-----
> 2 | bbb
> 4 | ddd
> 98 | yyy
> 98 | yyy
> (4 rows)
>
> -- No: it must be the lack of where on rule2
> -- Let's put rule2 back in and see what executes now
> --
> richardh=> CREATE RULE voo_ins_rule2 AS ON INSERT TO voo DO
> INSTEAD INSERT
> INTO
> foo VALUES (NEW.a, NEW.b);
> CREATE
> richardh=> insert into voo values (99,'zzz');
> INSERT 1287608 1
> richardh=> insert into voo values (98,'yyy');
> INSERT 1287610 1
> richardh=> select * from foo;
> a | b
> ----+-----
> 1 | aaa
> 2 | bbb
> 3 | ccc
> 4 | ddd
> 99 | zzz
> 98 | yyy
> 98 | yyy
> 99 | zzz
> 99 | zzz
> 98 | yyy
> 98 | yyy
> (11 rows)
>
> richardh=> select * from voo;
> a | b
> ----+-----
> 2 | bbb
> 4 | ddd
> 98 | yyy
> 98 | yyy
> 98 | yyy
> 98 | yyy
> (6 rows)
>
> -- OK: so it looks like rules with "WHERE" don't execute until
> -- there is a rule that fires unconditionally, when
> -- the "WHERE" is recognised and applies accordingly.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: AW: Problems with RULE
@ 2001-03-07 16:43 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jens Hartwig <jens.hartwig@t-systems.de>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2001-03-07 16:43 UTC (permalink / raw)
To: Jens Hartwig <jens.hartwig@t-systems.de>; +Cc: pgsql-sql
"Jens Hartwig" <jens.hartwig@t-systems.de> writes:
> What would have happened, if I executed an unconditional DELETE?
> => DELETE FROM t_xyz;
> Which statement would have been generated by PostgreSQL in this case?
Unfortunately, I didn't keep the prior discussion, so I don't remember
exactly what the rule was. But the general idea for conditional rules
is that we generate
rule-action
WHERE rule-action's-own-conditions
AND rule-condition
AND conditions-from-original-query
(repeat for each action of each relevant rule) and then if we didn't
find any relevant unconditional INSTEAD rules, we generate
original-query-action
WHERE conditions-from-original-query
AND NOT (conditions-of-conditional-INSTEAD-rules)
There's a more extensive discussion in the Programmer's Guide,
http://www.postgresql.org/devel-corner/docs/postgres/rules.html
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* AW: Problems with RULE
@ 2001-03-07 17:00 Jens Hartwig <jens.hartwig@t-systems.de>
0 siblings, 0 replies; 4+ messages in thread
From: Jens Hartwig @ 2001-03-07 17:00 UTC (permalink / raw)
To: 'Richard Huxton' <dev@archonet.com>; +Cc: pgsql-sql
> [...]
> So - if you issue "DELETE FROM t_xyz" you'd want two things to happen:
>
> 1. where deleted is false set it to true
> 2. where deleted was true delete the record
>
> So - PG rewrites the query into two parts:
>
> DELETE FROM t_xyz WHERE old.deleted=false
> DELETE FROM t_xyz WHERE NOT(old.deleted=false)
> [...]
Oooooh ... that's it! I hit my head against the wall :-)
Thank you very much!
Best regards, Jens Hartwig
-----------------------------------------------------
T-Systems
Projektleiter
debis Systemhaus GEI GmbH
Hausanschrift: Eichhornstraße 3, 10785 Berlin
Postanschrift: 10785 Berlin
Telefon: (004930) 25 54-32 82
Telefax: (004930) 25 54-31 87
Mobiltelefon: (0170) 167 26 48
E-Mail: jens.hartwig@t-systems.de
Internet: http://www.t-systems.de
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2001-03-07 17:00 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-03-07 07:18 AW: Problems with RULE Jens Hartwig <jens.hartwig@t-systems.de>
2001-03-07 16:43 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-03-07 07:23 AW: Problems with RULE Jens Hartwig <jens.hartwig@t-systems.de>
2001-03-07 17:00 AW: Problems with RULE Jens Hartwig <jens.hartwig@t-systems.de>
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