Rule appears not to fire on insert w/ "except" - Mailing list pgsql-general

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;