Re: insert rule not firing on insert with exists subselect

From: Chris Kratz <chris(dot)kratz(at)vistashare(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: insert rule not firing on insert with exists subselect
Date: 2004-04-13 21:20:03
Message-ID: 200404131720.03528.chris.kratz@vistashare.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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(dot)kratz(at)vistashare(dot)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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Holger Hoffstaette 2004-04-13 21:40:24 Re: Filesystem vs. Postgres for images
Previous Message Tom Lane 2004-04-13 21:13:26 Re: insert rule not firing on insert with exists subselect