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

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.