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

From: Chris Kratz <chris(dot)kratz(at)vistashare(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Rule appears not to fire on insert w/ "except"
Date: 2005-11-21 15:50:45
Message-ID: 200511211050.45903.chris.kratz@vistashare.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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;

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Berend Tober 2005-11-21 16:03:22 Multi-parameter aggregates.
Previous Message Scott Marlowe 2005-11-21 14:58:36 Re: Postgres Disconnection problems