Re: insert rule not firing on insert with exists subselect - Mailing list pgsql-general

From Chris Kratz
Subject Re: insert rule not firing on insert with exists subselect
Date
Msg-id 200404131720.03528.chris.kratz@vistashare.com
Whole thread Raw
In response to Re: insert rule not firing on insert with exists subselect  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-general
OK, that makes sense.  I guess we will be implementing some triggers.

Thanks,

-Chris

On Tuesday 13 April 2004 5:13 pm, Tom Lane wrote:
> Chris Kratz <chris.kratz@vistashare.com> writes:
> > What am I missing?
>
> You're thinking that the rule is a trigger.  It's not.  It's a query
> transformation mechanism that adds a new query to be executed after your
> INSERT.  What actually gets executed is effectively
>
> Original query:
>
> insert into table2 (col1) select col1 from table1 where not exists
> (select 1 from table2 where table2.col1 = table1.col1);
>
> Query added by rule:
>
> insert into table3 (col1)
>  select col1 from table1 where not exists
>  (select 1 from table2 where table2.col1 = table1.col1);
>
> By the time the second query begins, there *is* a table2 row matching
> every row in table1, because you just got done inserting ones to match
> any that didn't have a match.  So in the second query, the EXISTS test
> succeeds at every row of table1 and no rows are produced to insert into
> table3.
>
> This could be made to work if the order of the queries were reversed,
> but that isn't going to happen because it would break other uses of
> ON INSERT rules that need to be able to see the inserted row(s).
> So AFAICS you're gonna have to use a trigger.
>
>             regards, tom lane

--
Chris Kratz
Systems Analyst/Programmer
VistaShare LLC
www.vistashare.com

pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: insert rule not firing on insert with exists subselect
Next
From: "Development - multi.art.studio"
Date:
Subject: Re: performance problem aftrer update from 7.1 to 7.4.2