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

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
>
>