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

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