UPDATE is not allowed in a non-volatile function

Gaetano Mendola <mendola@bigfoot.com> writes:

The fact that a non-volatile function can not perform
update is a good improvement but on the other side will
limit too much if I know what I'm doing.

I've got zero sympathy for this argument. It's been documented right
along that functions with side-effects must be marked volatile. You
don't have a lot of room to complain because 8.0 started to enforce that.

In practice you can circumvent the restriction by splitting the
function in two (ie, there is no check that a nonvolatile function
doesn't call any volatile functions). So if you insist on sticking
with an unsafe application design, you can do it with relatively
localized changes.

regards, tom lane

Tom Lane wrote:

Gaetano Mendola <mendola@bigfoot.com> writes:

The fact that a non-volatile function can not perform
update is a good improvement but on the other side will
limit too much if I know what I'm doing.

I've got zero sympathy for this argument. It's been documented right
along that functions with side-effects must be marked volatile. You
don't have a lot of room to complain because 8.0 started to enforce that.

In practice you can circumvent the restriction by splitting the
function in two (ie, there is no check that a nonvolatile function
doesn't call any volatile functions). So if you insist on sticking
with an unsafe application design, you can do it with relatively
localized changes.

I do not consider my design as "unsafe", this is for example how a
cache works: expose a "read" without side effect but updating internal
statistics. After all the read will not alter the data that it expose
but other data that the user even don't know the existence.

However I think that "that missing check" is "unsafe" and jeopardize the
effort to avoid a wrong user design.

Having say that I'm happy to know that what I did will continue to work
splitting the function in two parts.

Regards
Gaetano Mendola

Tom Lane wrote:

Gaetano Mendola <mendola@bigfoot.com> writes:

The fact that a non-volatile function can not perform
update is a good improvement but on the other side will
limit too much if I know what I'm doing.

I've got zero sympathy for this argument. It's been documented right
along that functions with side-effects must be marked volatile. You
don't have a lot of room to complain because 8.0 started to enforce that.

In practice you can circumvent the restriction by splitting the
function in two (ie, there is no check that a nonvolatile function
doesn't call any volatile functions). So if you insist on sticking
with an unsafe application design, you can do it with relatively
localized changes.

I do not consider my design as "unsafe", this is for example how a
cache works: expose a "read" without side effect but updating internal
statistics. After all the read will not alter the data that it expose
but other data that the user even don't know the existence.

However I think that "that missing check" is "unsafe" and jeopardize the
effort to avoid a wrong user design.

Having say that I'm happy to know that what I did will continue to work
splitting the function in two parts.

Regards
Gaetano Mendola

Gaetano,

I do not consider my design as "unsafe", this is for example how a
cache works: expose a "read" without side effect but updating internal
statistics. After all the read will not alter the data that it expose
but other data that the user even don't know the existence.

However I think that "that missing check" is "unsafe" and jeopardize the
effort to avoid a wrong user design.

Having say that I'm happy to know that what I did will continue to work
splitting the function in two parts.

I think Gaetano has a point but I consider the solution somewhat kludgy.
The Rationale for my opinion is that since there is a need to accomplish
what Gaetano needs, there should be declarative power to express it and
thus, prevent "unsafe" designs. We need a way to declare a function
"stable with no _intrusive_ side effects". It's far better to explicitly
state this then to rely on a flaw of the current function calling mechanism.

The current read-only status, maintained and passed to the SPI execute
functions by function developers themselves, is not good enough. There
are two main reasons for this:
a) Nesting is not accounted for. The correct behavior should be to block
all nested volatile calls as soon as a function declared non-volatile is
called. I can of course enforce this within the PL/Java domain but I
have no control over functions written in other languages.
b) The responsability to maintain the read-only flag should not be
pushed onto the function developers. It's fully possible for the
PostgreSQL calling mechanism to maintain some global structure that is
updated prior to calling functions and then for the SPI functions to
consult that structure.

Regards,
Thomas Hallgren

Gaetano,

I do not consider my design as "unsafe", this is for example how a
cache works: expose a "read" without side effect but updating internal
statistics. After all the read will not alter the data that it expose
but other data that the user even don't know the existence.

At issue is the working definitions of the function states (and yes, I know
these aren't the formal definitions but these are what is useful):
IMMUTABLE: result of function will always be the same given the same inputs;
STABLE: result of function will be the same for the duration of the
transaction.
VOLATILE: you can't count on any particular result based on the inputs

Any function involving an UPDATE -- and I write a LOT of them -- clearly falls
into the last group. You never know what you're going to get as a result of
an UPDATE; you could get an index violation, a lock wait, a deadlock, a
constraint violation, etc.

--
Josh Berkus
Aglio Database Solutions
San Francisco

Josh Berkus wrote:

Gaetano,

I do not consider my design as "unsafe", this is for example how a
cache works: expose a "read" without side effect but updating internal
statistics. After all the read will not alter the data that it expose
but other data that the user even don't know the existence.

At issue is the working definitions of the function states (and yes, I know
these aren't the formal definitions but these are what is useful):
IMMUTABLE: result of function will always be the same given the same inputs;
STABLE: result of function will be the same for the duration of the
transaction.
VOLATILE: you can't count on any particular result based on the inputs

Any function involving an UPDATE -- and I write a LOT of them -- clearly falls
into the last group. You never know what you're going to get as a result of
an UPDATE; you could get an index violation, a lock wait, a deadlock, a
constraint violation, etc.

Do this means that an IMMUTABLE function is free of errors (nothrow) ?
You can fire a deadlock also calling an IMMUTABLE function.

As you wrote:

IMMUTABLE: result of function will always be the same given the same inputs;

and my "read" satisfy this definition: the result is the same given the same inputs,
but also performs update statistics that will not interfere with the data exposed.

I think avoid an IMMUTABLE function perform update is a big limit and fortunatelly
the limit is no so big given the fact that a function marked as IMMUTABLE can call
a function marked as VOLATILE ( that IMHO is not so good... ).

Regards
Gaetano Mendola

Thomas Hallgren <thhal@mailblocks.com> writes:

The Rationale for my opinion is that since there is a need to accomplish
what Gaetano needs, there should be declarative power to express it and
thus, prevent "unsafe" designs. We need a way to declare a function
"stable with no _intrusive_ side effects".

What you think is non-intrusive may not be so at the database's level.

regards, tom lane

Tom,

What you think is non-intrusive may not be so at the database's level.

I know. But thats not my point. Look at this this way:

I'd like to declare a function STABLE. And I'd like to trust that
declaration 100%. So a stable function must *never* call a function that
is VOLATILE. Not directly and not implicit through nesting.

I think we agree that the current way of enforcing that protection can't
be trusted. As a function developer you really need to know what you are
doing and take great care not to call a volatile function from within a
stable or immutable function. The system won't protect you at all.

My suggestion is first and foremost an attempt to enforce the procection
and make the STABLE declaration really mean something so that all users
can benefit from this and be able to rely on the concept. So far, no
mention of non-intrusive. I'd really like your opinion on this part as a
separate issue.

Now, some people, like Gaetano, might want to go further and do things
that are beyond what PostgreSQL can provide 100% protection for. They
*want* to take on the responsability themselves. That's where my new
function characteristic with "non-intrusive" comes in. I admitt that
"non-intrusive" might be a bad term for this. What I mean is a
characteristic that overrides my suggested 100% reliable interpretation
of STABLE. This characteristic is not intended for the everyday function
developer and should be documented as such.

Regards,
Thomas Hallgren

On Wednesday 03 November 2004 18:06, Thomas Hallgren wrote:

Tom,

What you think is non-intrusive may not be so at the database's level.

I know. But thats not my point. Look at this this way:

I'd like to declare a function STABLE. And I'd like to trust that
declaration 100%. So a stable function must *never* call a function that
is VOLATILE. Not directly and not implicit through nesting.

I think we agree that the current way of enforcing that protection can't
be trusted. As a function developer you really need to know what you are
doing and take great care not to call a volatile function from within a
stable or immutable function. The system won't protect you at all.

I think the guidelines are fairly clear on what types of functions should be
declared with which types. But the key is that these are guidelines, not hard
and fast rules, since there may be times when you need to ignore them.

My suggestion is first and foremost an attempt to enforce the procection
and make the STABLE declaration really mean something so that all users
can benefit from this and be able to rely on the concept. So far, no
mention of non-intrusive. I'd really like your opinion on this part as a
separate issue.

"users" shouldn't care. the function developer should determine the details
and "users" shouldn't have to think about it.

Now, some people, like Gaetano, might want to go further and do things
that are beyond what PostgreSQL can provide 100% protection for. They
*want* to take on the responsability themselves. That's where my new
function characteristic with "non-intrusive" comes in. I admitt that
"non-intrusive" might be a bad term for this. What I mean is a
characteristic that overrides my suggested 100% reliable interpretation
of STABLE. This characteristic is not intended for the everyday function
developer and should be documented as such.

Well, personally I prefered the way thing worked in 7.4, but I'm willing to
live with the 8.x method. If you forcibly prevent the work around though,
you better provide a work around, and if that mean a fourth function type I
could live with that; it's certainly better than marking these types of
functions volitile, which is a non-starter in my application.

--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

Robert,

I think the guidelines are fairly clear on what types of functions should be
declared with which types. But the key is that these are guidelines, not hard
and fast rules, since there may be times when you need to ignore them.

In 7.4 they where indeed guidelines. In 8.x the semantics of the
function attribute are meant to be enforced. I quote Tom from this
thread: "You don't have a lot of room to complain because 8.0 started to
enforce that.".

"users" shouldn't care. the function developer should determine the details
and "users" shouldn't have to think about it.

The "user" in this context, is the user of the function attribute, i.e.
the function developer. Sorry if I was unclear.

Well, personally I prefered the way thing worked in 7.4, but I'm willing to
live with the 8.x method. If you forcibly prevent the work around though,
you better provide a work around, and if that mean a fourth function type I
could live with that; it's certainly better than marking these types of
functions volitile, which is a non-starter in my application.

Right, I also preferred 7.4 since I consider it less ambiguous then the
current solution. But a much better solution is just around the corner
and it would be unfortunate if some fairly rare scenarios are used as an
argument to prevent it. Especially since those scenarios can be easily
catered for by introducing a fourth type.

Regards,
Thomas Hallgren

Tom Lane wrote:

Thomas Hallgren <thhal@mailblocks.com> writes:

The Rationale for my opinion is that since there is a need to accomplish
what Gaetano needs, there should be declarative power to express it and
thus, prevent "unsafe" designs. We need a way to declare a function
"stable with no _intrusive_ side effects".

What you think is non-intrusive may not be so at the database's level.

Right, but the actual solution is far from be the good one.
If you claim that an immutable function "must not" do update because
otherwise the database could be in a inconsisten status, then we are in
trouble permitting a non-immutable function to be called by an
"immutable" one. I like see postgres stable as always was till now and
I prefer seen my code completelly broken than see someone call
a non-immutable function inside a "immutable" one and claim on this
list that he lost data.

I think a clean solution is enforce the check between functions call
( I prefer even only this one), and at the same time provide a "mutable"
attribute for tables ( a mutable table can be updated even inside an
immutable contest ).

Regards
Gaetano Mendola