Re: Volatile functions in WITH

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

> 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);

>
> 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(at)postgresql(dot)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(dot)ru(at)gmail(dot)com