Date: Wed, 20 Feb 2013 18:16:04 +0000
From: Ben Morrow <ben@morrow.me.uk>
To: Sergey Konoplev <gray.ru@gmail.com>
Cc: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: Volatile functions in WITH
Message-ID: <20130220181603.GB29651@anubis.morrow.me.uk>
References: <20130217075859.GE8029@anubis.morrow.me.uk>
	<CAL_0b1uWOn=UukdngytfgzP2ySHgz45qHtpxA5LwUXjjKkor0w@mail.gmail.com>
	<20130220081905.GA95525@anubis.morrow.me.uk>
	<CAL_0b1v0rn48ecG1wcSG1ML+W=pW5-A5yFo7nzXi1NCV8S35XQ@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CAL_0b1v0rn48ecG1wcSG1ML+W=pW5-A5yFo7nzXi1NCV8S35XQ@mail.gmail.com>
User-Agent: Mutt/1.5.21 (2010-09-15)
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

At  8AM -0800 on 20/02/13 you (Sergey Konoplev) wrote:
> On Wed, Feb 20, 2013 at 12:19 AM, Ben Morrow <ben@morrow.me.uk> wrote:
> > 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.
> 
> Okay I think I got it. The function catches exception when INSERTing
> and does UPDATE instead, correct?

Well, it tries the update first, but yes. It's pretty-much exactly the
example in the PL/pgSQL docs.

> If you got mixed up with plpgsql anyway what is the reason of making
> this WITH query constructions instead of implementing everything in a
> plpgsql trigger on DELETE on exp then?

I'm not sure what you mean. "exp" isn't a table, it's a WITH CTE. The
statement is deleting some entries from "item", and replacing some of
them with new entries, based on the information in the "item_expired"
view. I can't do anything with a trigger on "item", since there are
other circumstances where items are deleted that shouldn't trigger
replacement.

> > 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.
> 
> From the documentation "VOLATILE indicates that the function value can
> change even within a single table scan, so no optimizations can be
> made". So they are guaranteed to behave as you need in your last
> example.

Well, that's ambiguous. The return value can change even within a single
scan, so if you want 3 return values you have to make 3 calls. But what
if you don't actually need one of those three: is the planner allowed to
optimise the whole thing out? For instance, given

    select * 
    from (select j.type, random() r from item j) i
    where i.type = 1

the planner will transform it into

    select i.type, random() r
    from item i
    where i.type = 1

before planning, so even though random() is volatile it will only get
called for rows of item with type = 1. I don't know if this happens, or
may sometimes happen, or might happen in the future, for rows eliminated
because of DISTINCT.

(I think perhaps what I would ideally want is a PERFORM verb, which is
just like SELECT but says 'actually calculate all the rows implied here,
without pulling in additional filter conditions'. WITH would then have
to treat a top-level PERFORM inside a WITH the same as DML.)

Ben


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql