Thread: Rule appears not to fire on insert w/ "except"

First version of this I sent this morning did not appear to go through.
Please disregard if you received the first one.

----------  Original Message  ----------

Hello All,

We have finally tracked down a bug in our application to a rewrite rule on a
table.  In essence, the rewrite rule in question logs any inserts to another
table.  This works correctly in all cases except where an "except" clause is
used in the insert statement.  In this case, the rows are inserted into the
primary table as expected, but the rule either does not fire, or fires in
such a way that nothing is placed in the changes table.

We have deduced that this is either a PG bug, or it is some side effect of
 the sql rewrite which is causing unexpected behavior (for us).  I'm sure
 it's probably the latter, but we are scratching our heads as to why that
 might be. Can one of the gurus help us understand what is going on in this
 case?

 As a side note, is there a way to see the final sql after all "rewrite"
 rules have been processed?  It might help us understand what is going on.

This is in pg 8.0.4 (8.0.3 as well). I would be interested to know if the
 same behavior happens in 8.1.

Thanks,

-Chris

------------ Test Cases --------------------

-- Not working case, insert w/ except clause
begin;

create table test1(id serial, data text);

create table test2(id serial, data text);
insert into test2(data) values('abc');

create table test_que(row_id integer);

CREATE OR REPLACE RULE debug_rule AS
    ON INSERT TO test1
   do INSERT INTO test_que (row_id)
  VALUES (new.id);

insert into test1
  select id,data from test2
  except select id,data from test1;

--   We will have 1 row inserted
select * from test1;

-- But no rows here even though a row was placed in test1
select * from test_que;

rollback;


-- Working Case, insert is identical w/o the except clause
begin;

create table test1(id serial, data text);

create table test2(id serial, data text);
insert into test2(data) values('abc');

create table test_que(row_id integer);

CREATE OR REPLACE RULE debug_rule AS
    ON INSERT TO test1
   do INSERT INTO test_que (row_id)
  VALUES (new.id);

-- Insert w/o except clause
insert into test1
  select id,data from test2;

-- Now we have 1 row in test1
select * from test1;

-- And this time the rewrite rule triggered and
-- we have 1 row in test_que
select * from test_que;

rollback;

Chris Kratz <chris.kratz@vistashare.com> writes:
> CREATE OR REPLACE RULE debug_rule AS
>     ON INSERT TO test1
>    do INSERT INTO test_que (row_id)
>   VALUES (new.id);

You would be a whole lot better off doing this with a trigger.

> insert into test1
>   select id,data from test2
>   except select id,data from test1;

I believe the problem with this is that the rule re-evaluates the
command to generate the "new" results, and by that point the rows
have already been inserted into test1, thus disappear from the
result of the "except" ...

            regards, tom lane

On Monday 21 November 2005 03:35 pm, you wrote:
> Chris Kratz <chris.kratz@vistashare.com> writes:
> > CREATE OR REPLACE RULE debug_rule AS
> >     ON INSERT TO test1
> >    do INSERT INTO test_que (row_id)
> >   VALUES (new.id);
>
> You would be a whole lot better off doing this with a trigger.
>
> > insert into test1
> >   select id,data from test2
> >   except select id,data from test1;
>
> I believe the problem with this is that the rule re-evaluates the
> command to generate the "new" results, and by that point the rows
> have already been inserted into test1, thus disappear from the
> result of the "except" ...
>
>             regards, tom lane

We were afraid of that.  Thanks for the info Tom.  I'm not entirely sure I
grasp exactly why it's going on, but we will switch over to a trigger.

Thanks,

-Chris