Re: Volatile functions in WITH

Quoth gray(dot)ru(at)gmail(dot)com (Sergey Konoplev):
> On Sat, Feb 16, 2013 at 11:58 PM, Ben Morrow <ben(at)morrow(dot)me(dot)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