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: Wed, 20 Feb 2013 18:16:04 +0000
Message-ID: <20130220181603.GB29651@anubis.morrow.me.uk> (raw)
In-Reply-To: <CAL_0b1v0rn48ecG1wcSG1ML+W=pW5-A5yFo7nzXi1NCV8S35XQ@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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
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
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: <20130220181603.GB29651@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