Re: Volatile functions in WITH

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