From: Ben Morrow <ben@morrow.me.uk>
To: gray.ru@gmail.com
To: pgsql-sql@postgresql.org
Subject: Re: Volatile functions in WITH
Date: Wed, 20 Feb 2013 08:19:12 +0000
Message-ID: <20130220081905.GA95525@anubis.morrow.me.uk> (raw)
In-Reply-To: <CAL_0b1uWOn=UukdngytfgzP2ySHgz45qHtpxA5LwUXjjKkor0w@mail.gmail.com>
References: <20130217075859.GE8029@anubis.morrow.me.uk>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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
--
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, gray.ru@gmail.com
Subject: Re: Volatile functions in WITH
In-Reply-To: <20130220081905.GA95525@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