Re: Bug? Function with side effects not evaluated in CTE

>Would help to include the explain(s). Did you ANALYZE after the insert; if
>not the planner probably still thought the table was empty (thus the
>matching explain) but upon execution realized it had records and thus
needed
>to run the CTE.

I did not do an ANALYZE after the insert, I think the plan would still be
the same either way. I did what I should have done to start with and ran
explain analyze on the query which showed that it found (or did not find)
matching rows. After reading the remaining emails I think I understand
Thanks for explaining.

Here is my two cents (take it for what it is worth). I agree with Merlin on
this. I work as a developer at a large corporation, and in my experience
very few of the developers can write "good" SQL/data access, and then only
a fraction of them even try to understand the planner. Although the
behavior makes sense (from what was explained above) I does not do what one
would expect (the same thing every time). Then when you read the manual
on VOLATILE it states the optimizer makes no assumptions on such functions,
resulting in people asking why is this happening.

On Fri, Oct 18, 2013 at 3:39 PM, David Johnston <polobo(at)yahoo(dot)com> wrote:

> ajelinek(at)gmail(dot)com wrote
> > but if I insert one row before I run the sql the CTE is
> > executed and I get a new row in the table. I was hoping that I would see
> > a
> > difference in the explain, but the explain with an empty table where the
> > CTE is *not* executed is identical to the explain where there is one row
> > in
> > the table already and the CTE *is* executed resulting in a new row.
>
> Would help to include the explain(s). Did you ANALYZE after the insert; if
> not the planner probably still thought the table was empty (thus the
> matching explain) but upon execution realized it had records and thus
> needed
> to run the CTE.
>
> Since the executor cannot fully trust the statistics, and a full scan of an
> empty table would be very fast, scanning the table to delete would be a
> necessary first step before running the CTE for the secondary conditions
> (where clause). An implicit first-condition/result is that a DELETE on an
> empty table is effectively a No-Op. The only reason to override that no-op
> would be if a CTE needs to be run by policy as Tom noted.
>
>
> > I thought maybe Postgres was not executing the CTE because it knows that
> > there are no rows in the table for it to delete, however if I change the
> > CTE to be an insert returning instead of a function I get different
> > results. Even when the table is empty I get new row created.
>
> Like Tom said, if you don't hide the INSERT inside a function the CTE will
> always be executed.
>
> David J.
>
>
>
>
> --
> View this message in context:
> http://postgresql.1045698.n5.nabble.com/Bug-Function-with-side-effects-not-evaluated-in-CTE-tp5774792p5775095.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>