Re: Volatile functions in WITH - Mailing list pgsql-sql

Quoth gray.ru@gmail.com (Sergey Konoplev):
> On Sat, Feb 16, 2013 at 11:58 PM, Ben Morrow <ben@morrow.me.uk> wrote:
> >     WITH "exp" AS ( -- as before
> >     ),
> >     "subst" AS (
> >         SELECT add_item(e.basket, e.nref, e.count)
> >         FROM "exp" e
> >         WHERE e.nref IS NOT NULL
> >     )
> >     SELECT DISTINCT e.msg
> >     FROM "exp" e
> 
> Alternatively I suppose you can try this one:
> 
>  WITH "exp" AS (
>         DELETE FROM "item" i
>         USING "item_expired" e
>         WHERE e.oref = i.ref
>         AND i.basket = $1
>         RETURNING i.basket, e.oref, e.nref, i.count, e.msg
>     ),
>     "upd" AS (
>         UPDATE "item" SET "count" = e.count
>         FROM "exp" e
>         WHERE e.nref IS NOT NULL
>         AND ("basket", "nref") IS NOT DISTINCT FROM (e.basket, e.nref)
>         RETURNING "basket", "nref"
>     )
>    "ins" AS (
>         INSERT INTO "item" ("basket", "ref", "count")
>         SELECT e.basket, e.nref, e.count
>         FROM "exp" e LEFT JOIN "upd" u
>         ON ("basket", "nref") IS NOT DISTINCT FROM (e.basket, e.nref)
>         WHERE e.nref IS NOT NULL AND (u.basket, u.nref) IS NULL
>     )
>     SELECT DISTINCT e.msg
>     FROM "exp" e

That's not reliable. A concurrent txn could insert a conflicting row
between the update and the insert, which would cause the insert to fail
with a unique constraint violation.

> > then the planner sees that the results of "subst" are not used, and
> > doesn't include it in the query plan at all.
> >
> > Is there any way I can tell WITH that add_item is actually a data-
> > modifying statement? Adding FOR UPDATE doesn't seem to help (I didn't
> > really expect it would.)
> 
> In this regard I would like to listen to gugrus' opinion too.
> 
> EXPLAIN ANALYZE WITH t AS (SELECT random()) SELECT 1;
>                                      QUERY PLAN
> ------------------------------------------------------------------------------------
>  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.002..0.003
> rows=1 loops=1)
>  Total runtime: 0.063 ms
> (2 rows)
> 
> EXPLAIN ANALYZE WITH t AS (SELECT random()) SELECT 1 from t;
>                                          QUERY PLAN
> --------------------------------------------------------------------------------------------
>  CTE Scan on t  (cost=0.01..0.03 rows=1 width=0) (actual
> time=0.048..0.052 rows=1 loops=1)
>    CTE t
>      ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual
> time=0.038..0.039 rows=1 loops=1)
>  Total runtime: 0.131 ms
> (4 rows)
> 
> I couldn't manage to come to any solution except faking the reference
> in the resulting query:
> 
> WITH t AS (SELECT random()) SELECT 1 UNION ALL (SELECT 1 FROM t LIMIT 0);

Yes, I can do experiments too; the alternatives I gave before both work
on my test database. What I was asking was whether they are guaranteed
to work in all situations, given that the planner can in principle see
that the extra table reference won't affect the result.

Ben