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

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:

> 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

--
-------------------------------------------------------------------------------
Jerry Sievers   305 854-3001 (home)     WWW ECommerce Consultant
                305 321-1144 (mobile    http://www.JerrySievers.com/

On Mon, Nov 21, 2005 at 08:05:19PM -0500, Jerry Sievers wrote:
> Chris Kratz <chris.kratz@vistashare.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.

I suppose you could wrap the SELECT...EXCEPT in parens.

WARNING Untested Code:

INSERT INTO foo
(SELECT a,b,c FROM bar
EXCEPT
SELECT a,b,c FROM baz);

HTH :)

Cheers,
D
--
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100   mobile: +1 415 235 3778

Remember to vote!

On Monday 21 November 2005 08:05 pm, Jerry Sievers wrote:
> Chris Kratz <chris.kratz@vistashare.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

On Monday 21 November 2005 08:16 pm, David Fetter wrote:
> On Mon, Nov 21, 2005 at 08:05:19PM -0500, Jerry Sievers wrote:
> > Chris Kratz <chris.kratz@vistashare.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.
>
> I suppose you could wrap the SELECT...EXCEPT in parens.
>
> WARNING Untested Code:
>
> INSERT INTO foo
> (SELECT a,b,c FROM bar
> EXCEPT
> SELECT a,b,c FROM baz);
>
> HTH :)
>
> Cheers,
> D

Hello David,

Yes, we actually did try the parenthesis, but we still observed the same
behavior.

Thanks though,

-Chris

--
Chris Kratz

Dear Chris,

What about this:

insert into test1
    select id, data from test2
    where id, data not in (select id, data from test1);

of which one would expect the same results...

Yours,

Aarjan

Ps. notice that you are inserting data into a serial column (in your
examples as well), as far as I know this is not common practice since your
sequence is now not correct anymore.
Therefore, better would be:

insert into test1 (data)
    select data from test2
    where id, data not in (select id, data from test1);

or to make the id column in table test1 of the integer type.

----- Original Message -----
From: "Chris Kratz" <chris.kratz@vistashare.com>
To: <pgsql-general@postgresql.org>
Cc: "Jerry Sievers" <jerry@jerrysievers.com>
Sent: Tuesday, November 22, 2005 2:12 PM
Subject: Re: [GENERAL] Rule appears not to fire on insert w/ "except"


> On Monday 21 November 2005 08:05 pm, Jerry Sievers wrote:
> > Chris Kratz <chris.kratz@vistashare.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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly
>
>

On Tuesday 22 November 2005 08:34 am, A.j. Langereis wrote:
> Dear Chris,
>
> What about this:
>
> insert into test1
>     select id, data from test2
>     where id, data not in (select id, data from test1);
>
> of which one would expect the same results...
>
> Yours,
>
> Aarjan
>
> Ps. notice that you are inserting data into a serial column (in your
> examples as well), as far as I know this is not common practice since your
> sequence is now not correct anymore.
> Therefore, better would be:
>
> insert into test1 (data)
>     select data from test2
>     where id, data not in (select id, data from test1);
>
> or to make the id column in table test1 of the integer type.

Hello Aarjan,

Thanks for the hint, but I get the same behavior with the not in syntax.  And
you are right about the serial issue.  My example was somewhat contrived as I
was trying to get it down to a minimal set of steps to reproduce.  The real
table is actually a denormalized table we use for reporting, so the serial
comes from test2 always.  Anyway, the testcase with the not in clause showing
the same behavior is at the end of this email.

Also, the id,data not in (select id,data... clause didn't work.  I changed it
to use only id in my test case below.  Actually if there is a way to do
multiple column comparisons like this it would be interesting.  We generally
have done it with a where not exists (select 1 from table where col1=id and
col2=data).

As Tom Lane pointed out in an earlier email.  The problem is happening because
when the rule is processed, the inserts have already happened.  So, to get
the new.id value, it reruns the select * from test2 except select * from
test1, there is no data returned because the except removes everything, so
the rule never fires.  I actually had a problem recently where a serial was
incremented 2x because a rule referenced new.id instead of the currval on the
appropriate sequence.  Same problem.  The rule causes a re-evaluation of the
orginal sql statement to get the new.* values.

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 (data)
    select data from test2
   where id not in (select id 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 test case
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 (data)
    select data from test2;

--   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;
--
Chris Kratz

Dear Chris,

Sorry, I forgot the ():

insert into test1
  select id, data from test2
  where (id, data) not in (select id, data from test1);

With the story of Tom Lane, your solution would be a before trigger I guess:

create or replace function trg_test() returns "trigger" as
'
begin
insert into test_que (row_id) values (new.id);
return new;
end;
'
language plpgsql;

create trigger trigger_test
  before insert
  on test1
  for each row
  execute procedure trg_test();

Yours,

Aarjan

----- Original Message -----
From: "Chris Kratz" <chris.kratz@vistashare.com>
To: <pgsql-general@postgresql.org>
Cc: "A.j. Langereis" <a.j.langereis@inter.nl.net>
Sent: Tuesday, November 22, 2005 2:54 PM
Subject: Re: [GENERAL] Rule appears not to fire on insert w/ "except"


> On Tuesday 22 November 2005 08:34 am, A.j. Langereis wrote:
> > Dear Chris,
> >
> > What about this:
> >
> > insert into test1
> > select id, data from test2
> > where id, data not in (select id, data from test1);
> >
> > of which one would expect the same results...
> >
> > Yours,
> >
> > Aarjan
> >
> > Ps. notice that you are inserting data into a serial column (in your
> > examples as well), as far as I know this is not common practice since
your
> > sequence is now not correct anymore.
> > Therefore, better would be:
> >
> > insert into test1 (data)
> > select data from test2
> > where id, data not in (select id, data from test1);
> >
> > or to make the id column in table test1 of the integer type.
>
> Hello Aarjan,
>
> Thanks for the hint, but I get the same behavior with the not in syntax.
And
> you are right about the serial issue.  My example was somewhat contrived
as I
> was trying to get it down to a minimal set of steps to reproduce.  The
real
> table is actually a denormalized table we use for reporting, so the serial
> comes from test2 always.  Anyway, the testcase with the not in clause
showing
> the same behavior is at the end of this email.
>
> Also, the id,data not in (select id,data... clause didn't work.  I changed
it
> to use only id in my test case below.  Actually if there is a way to do
> multiple column comparisons like this it would be interesting.  We
generally
> have done it with a where not exists (select 1 from table where col1=id
and
> col2=data).
>
> As Tom Lane pointed out in an earlier email.  The problem is happening
because
> when the rule is processed, the inserts have already happened.  So, to get
> the new.id value, it reruns the select * from test2 except select * from
> test1, there is no data returned because the except removes everything, so
> the rule never fires.  I actually had a problem recently where a serial
was
> incremented 2x because a rule referenced new.id instead of the currval on
the
> appropriate sequence.  Same problem.  The rule causes a re-evaluation of
the
> orginal sql statement to get the new.* values.
>
> 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 (data)
>     select data from test2
>    where id not in (select id 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 test case
> 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 (data)
>     select data from test2;
>
> --   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;
> --
> Chris Kratz
>
>