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

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(dot)kratz(at)vistashare(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Cc: "A.j. Langereis" <a(dot)j(dot)langereis(at)inter(dot)nl(dot)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
>
>