Re: Volatile functions in WITH

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?

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?

> 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.

What about optimizing it out in WITH - I would like to listen to
hackers' opinion, because for me it looks like a bug.

--
Sergey Konoplev
Database and Software Architect
http://www.linkedin.com/in/grayhemp

Phones:
USA +1 415 867 9984
Russia, Moscow +7 901 903 0499
Russia, Krasnodar +7 988 888 1979

Skype: gray-hemp
Jabber: gray.ru@gmail.com