Re: Volatile functions in WITH

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
 

> 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.03rows=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);

>
> Alternatively, are either of these safe (that is, are they guaranteed to
> call the function once for every row returned by "exp", even if the
> DISTINCT ends up eliminating some of those rows)?
>
>     WITH "exp" AS ( -- as before
>     ), "subst" AS ( -- SELECT add_item(...) as before
>     )
>     SELECT DISTINCT e.msg
>     FROM "exp" e
>         LEFT JOIN "subst" s ON FALSE
>
>     WITH "exp" AS ( -- as before
>     )
>     SELECT DISTINCT s.msg
>     FROM (
>         SELECT e.msg, CASE
>             WHEN e.nref IS NULL THEN NULL
>             ELSE add_item(e.basket, e.nref, e.count)
>         END "subst"
>     ) s
>
> I don't like the second alternative much, but I could live with it if I
> had to.
>
> Ben
>
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql



--
Sergey Konoplev
Database and Software Architect
http://www.linkedin.com/in/grayhemp

Phones:
USA +1 415 867 9984
Russia, Moscow +7 901 903 0499
Russia, Krasnodar +7 988 888 1979

Skype: gray-hemp
Jabber: gray.ru@gmail.com