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
Date: Thu, 21 Feb 2013 01:25:24 +0000
Message-ID: <20130221012523.GD29651@anubis.morrow.me.uk> (raw)
In-Reply-To: <CAL_0b1u6cRu==2x2U7taTLJBQxDsjs39YqyWGjOg_C-uZarfnQ@mail.gmail.com>
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>
<20130220181603.GB29651@anubis.morrow.me.uk>
<CAL_0b1u6cRu==2x2U7taTLJBQxDsjs39YqyWGjOg_C-uZarfnQ@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
At 12PM -0800 on 20/02/13 you (Sergey Konoplev) wrote:
> On Wed, Feb 20, 2013 at 10:16 AM, Ben Morrow <ben@morrow.me.uk> wrote:
> >> 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
>
> Sorry, I meant "item" of course, "exp" was a typo.
OK.
> > 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.
>
> Okay, I see.
>
> If the case is specific you can make a simple plpgsql function that
> will process it like FOR _row IN DELETE ... RETORNING * LOOP ...
> RETURN NEXT _row; END LOOP;
Yes, I *know* I can write a function if I have to. I can also send the
whole lot down to the client and do the inserts from there, or use a
temporary table. I was hoping to avoid that, since the plain INSERT case
works perfectly well.
> > 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.
>
> Yes, functions are executed depending on the resulting plan "A query
> using a volatile function will re-evaluate the function at every row
> where its value is needed".
>
> > I don't know if this happens, or may sometimes happen, or might happen
> > in the future, for rows eliminated because of DISTINCT.
>
> It is a good point. Nothing guarantees it in a perspective. Optimizer
> guarantees a stable result but not the way it is reached.
Well, it makes functions which perform DML a lot less useful, so I
wonder whether this is intentional behaviour.
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: <20130221012523.GD29651@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