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

On Monday 21 November 2005 08:05 pm, Jerry Sievers wrote:
> Chris Kratz <chris(dot)kratz(at)vistashare(dot)com> writes:
> > 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.
>
> You must be referring to something like;
>
> insert into foo
> select *
> from sometable
> except
> select *
> from someothertable
> ;
>
> If there's an EXCEPT clause on INSERT, I've never seen it.
>
> Perhaps you should post your insert query and your rule declaration.
>
> > 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.
>
> Not SQL but see config setting;
>
> debug_print_rewritten

Hello Jerry,

The insert statement is included in the test case. Here it is again.

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

The goal of the except was to only insert items from test2 that don't already
exist in test1.

Thanks for the hint on debug_print_rewritten. I'll look into that.

-Chris

--
Chris Kratz