From: Ben Morrow <ben@morrow.me.uk>
To: pgsql-sql@postgresql.org
Subject: Volatile functions in WITH
Date: Sun, 17 Feb 2013 07:58:59 +0000
Message-ID: <20130217075859.GE8029@anubis.morrow.me.uk> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
Suppose I run the following query:
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
),
"subst" AS (
INSERT INTO "item" ("basket", "ref", "count")
SELECT e.basket, e.nref, e.count
FROM "exp" e
WHERE e.nref IS NOT NULL
)
SELECT DISTINCT e.msg
FROM "exp" e
This is a very convenient and somewhat more flexible alternative to
INSERT... DELETE RETURNING (which doesn't work). However, the "item"
table has a unique constraint on (basket, ref), so sometimes I need to
update instead of insert; to handle this I have a VOLATILE function,
add_item. Unfortunately, if I call it the obvious way
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
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.)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ben@morrow.me.uk
Subject: Re: Volatile functions in WITH
In-Reply-To: <20130217075859.GE8029@anubis.morrow.me.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox