[GENERAL] Immutable functions and cache invalidation.
Say I have a function like this. CREATE OR REPLACE FUNCTION some_constant( ) RETURNS text LANGUAGE 'plpgsql' COST 100 IMMUTABLE ROWS 0 AS $BODY$ begin return 'some_string'; end; $BODY$; Then I have another function that calls it but is also immutable CREATE OR REPLACE FUNCTION some_proc( ) RETURNS text LANGUAGE 'plpgsql' COST 100 IMMUTABLE ROWS 0 AS $BODY$ declare x textl; begin x := some_constant(); end; $BODY$; will postgres know to invalidate the cache on some_proc() if I change the returned value in some_constant()? Thanks.
Re: [GENERAL] Immutable functions, Exceptions and the Query Optimizer
Cochise Ruhulessin wrote:
> Regarding your question about what the CHECK constraint should achieve, I had
> abstracted by use case
> into Books/Book Types, which may have caused some vagueness. The actual use
> case are the following
> tables.
[...]
> CREATE TABLE persons(
> person_id int8 NOT NULL PRIMARY KEY,
> place_of_birth_id int8
> REFERENCES features (feature_id)
> ON UPDATE CASCADE
> ON DELETE RESTRICT
> INITIALLY IMMEDIATE,
> CHECK (features_get_feature_code(place_of_birth_id) ~ 'PC.*|ADM.*|PP.*')
> );
>
>
> The CHECK constraint should achieve that "persons.place_of_birth_id" is
> always a country, or a
> (first_order) adminitrative division, or a city (which is defined by
> "features.gtype_id").
>
> Though this could be done by creating a multi-column foreign key on
> ("features.feature_id","features.gtype_id"), this would violate the
> principles of normalization.
True; but if you don't mind that, it would be a nice solution
since you already have a unique index on features(feature_id, feature_code).
> Of course this could also be achieved by a TRIGGER, but that seems a little
> redundant to me.
I think a trigger is the best solution here.
Why is it more redundant than a CHECK constraint?
Both will do about the same thing, with the advantage
that the trigger solution would be correct and won't
give you any trouble at dump/reload time.
Yours,
Laurenz Albe
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Immutable functions, Exceptions and the Query Optimizer
Cochise Ruhulessin wrote: > If an immutable function raises an exception, is that exception cached by the > query optimizer? Or does > it only cache in the case that a function actually returns a value? If an error occurs, query processing is terminated, so nothing needs to be cached. PostgreSQL doesn't cache function results, immutable or not: CREATE OR REPLACE FUNCTION i(integer) RETURNS integer LANGUAGE plpgsql IMMUTABLE STRICT AS $$BEGIN RAISE NOTICE 'Called for %', $1; RETURN $1; END$$; WITH t(t) AS (VALUES (1), (2), (1)) SELECT i(t) FROM t; NOTICE: Called for 1 NOTICE: Called for 2 NOTICE: Called for 1 i --- 1 2 1 (3 rows) The difference is that an immutable function, when applied to a constant, can be evaluated at query planning time: WITH t(t) AS (VALUES (1), (2), (1)) SELECT i(42) FROM t; NOTICE: Called for 42 i 42 42 42 (3 rows) Notice that the function was evaluated only once. > The use case is a table books(book_id NOT NULL PRIMARY KEY, type_id) wherein > type_id is considered > immutable (enforced with a trigger). No database object is immutable (note that "immutable" means something else here than in the case of a function, so don't mix those up). You can, for example, drop the table. Any function that SELECTs from the database cannot be immutable. > The function f() must return type_id given book_id, and raise an exception if > no entity with book_id > exists. I'd like this function to be immutable so it can be used as a check > constraint. The documentation says in http://www.postgresql.org/docs/current/static/sql-createtable.html Currently, CHECK expressions cannot contain subqueries nor refer to variables other than columns of the current row. CHECK constraints are only verified when the value is modified, so nothing can prevent the constraint from getting violated after the row has been added. It might, for example, lead to problems during dump/restore, as seen here: http://www.postgresql.org/message-id/29488.1332857...@sss.pgh.pa.us What should the CHECK constraint achieve? Maybe it can be expressed with a BEFORE trigger or some other construct. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Immutable functions, Exceptions and the Query Optimizer
Hello all, If an immutable function raises an exception, is that exception cached by the query optimizer? Or does it only cache in the case that a function actually returns a value? The use case is a table books(book_id NOT NULL PRIMARY KEY, type_id) wherein type_id is considered immutable (enforced with a trigger). The function f() must return type_id given book_id, and raise an exception if no entity with book_id exists. I'd like this function to be immutable so it can be used as a check constraint. Kind regards, Cochise Ruhulessin
Re: [GENERAL] immutable functions
Andy Chambers writes: > The documentation has this to say about immutable functions... >> or otherwise use information not directly present in its argument list > If the arguments are "row variables", does this allow access to the > data in the row? Sure. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] immutable functions
The documentation has this to say about immutable functions... > or otherwise use information not directly present in its argument list If the arguments are "row variables", does this allow access to the data in the row? For example, is it safe to make the following function definition immutable. CREATE OR REPLACE FUNCTION distance(geocodes, geocodes) RETURNS double precision AS $BODY$ select case $1.zip = $2.zip when true then 0 else ((acos(sin(($1.lat) * (pi()/180)) * sin(($2.lat)*(pi()/180)) + cos(($1.lat)*(pi()/180)) * cos(($2.lat)*(pi()/180)) * cos(($1.lon - $2.lon) * (pi()/180*(180/pi())* 60 * 1.1515) end; $BODY$ LANGUAGE sql immutable COST 100; Cheers, Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] immutable functions and enumerate type casts in indexes
Tom Lane ha scritto:
Edoardo Panfili <[EMAIL PROTECTED]> writes:
labelDatum = DirectFunctionCall1(enum_out,datumIbrido);
label = (char *) DatumGetPointer(labelDatum);
Just FYI, preferred style for the second line would be
label = DatumGetCString(labelDatum);
Nearly all standard data types have DatumGetFoo and FooGetDatum
macros to hide the conversion details (even if it's only a cast).
the clean version:
--
#include "utils/builtins.h"
PG_FUNCTION_INFO_V1(esterna_nome);
Datum esterna_nome(PG_FUNCTION_ARGS){
Datum datumIbrido = PG_GETARG_DATUM(0);
Datum labelDatum;
char *label;
labelDatum = DirectFunctionCall1(enum_out,datumIbrido);
label = DatumGetCString(labelDatum);
if(strcmp(label,("label_constant"))==0){
...
}
...
}
--
thank you again!
Edoardo
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] immutable functions and enumerate type casts in indexes
Edoardo Panfili <[EMAIL PROTECTED]> writes: > labelDatum = DirectFunctionCall1(enum_out,datumIbrido); > label = (char *) DatumGetPointer(labelDatum); Just FYI, preferred style for the second line would be label = DatumGetCString(labelDatum); Nearly all standard data types have DatumGetFoo and FooGetDatum macros to hide the conversion details (even if it's only a cast). regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] immutable functions and enumerate type casts in indexes
Martijn van Oosterhout ha scritto:
On Tue, Sep 02, 2008 at 10:53:03AM +0200, Edoardo Panfili wrote:
But i have a little question about parameters of enum_out.
Datum enum_out(PG_FUNCTION_ARGS);
this is a part of my function
---
Datum esterna_nome2(PG_FUNCTION_ARGS){
int label;
label = enum_out(fcinfo);
sprintf(debug,"false enum_out: \"%s\" ",unrolled);
elog(LOG, debug);
---
but it works only because my enum parameter is the first (and using
fcinfo is a little obscure).
Look in the fmgr.h header for functions like DirectFunctionCall1 and
various other ways of calling functions.
Now it works! thank you to Martin and Tom.
this is a code fragment:
--
#include "utils/builtins.h"
PG_FUNCTION_INFO_V1(esterna_nome);
Datum esterna_nome(PG_FUNCTION_ARGS){
Datum datumIbrido = PG_GETARG_DATUM(0);
Datum labelDatum;
char *label;
labelDatum = DirectFunctionCall1(enum_out,datumIbrido);
label = (char *) DatumGetPointer(labelDatum);
if(strcmp(label,("label_constant"))==0){
...
}
...
}
--
I don't know why but I need <<#include "utils/builtins.h">>
The line "label = (char *) DatumGetPointer(labelDatum);" is essential to
use the information in strcmp() if I use directly labelDatum it does not
works (but it works inside a sprintf(buffer,"%s",labelDatum)).
thank you again
Edoardo
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] immutable functions and enumerate type casts in indexes
On Tue, Sep 02, 2008 at 10:53:03AM +0200, Edoardo Panfili wrote:
> But i have a little question about parameters of enum_out.
> Datum enum_out(PG_FUNCTION_ARGS);
> this is a part of my function
> ---
> Datum esterna_nome2(PG_FUNCTION_ARGS){
> int label;
> label = enum_out(fcinfo);
> sprintf(debug,"false enum_out: \"%s\" ",unrolled);
> elog(LOG, debug);
> ---
> but it works only because my enum parameter is the first (and using
> fcinfo is a little obscure).
Look in the fmgr.h header for functions like DirectFunctionCall1 and
various other ways of calling functions.
Have a nice day,
--
Martijn van Oosterhout <[EMAIL PROTECTED]> http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.
signature.asc
Description: Digital signature
Re: [GENERAL] immutable functions and enumerate type casts in indexes
Tom Lane ha scritto:
Edoardo Panfili <[EMAIL PROTECTED]> writes:
my enumerated type is (this is a subset)
CREATE TYPE hibridation AS ENUM('none','genus','specie');
function declaration
CREATE FUNCTION ename(text,boolean,text,text RETURNS text AS
'funzioniGDB.so' LANGUAGE C IMMUTABLE;
index creation (the type of ibrido is hibridation)
CREATE INDEX i_specie_nome_specie ON specie
(esterna_nome(ibrido::text,proParte,genere,specie));
the result is
ERROR: functions in index expression must be marked IMMUTABLE
Now, maybe for your purposes here it's okay to consider it immutable.
In that case what I'd suggest doing is redefining ename() to take the
enum directly. You could invoke enum_out within the function if you
really need a text equivalent.
thank you! this is the right way for me.
Now it works.
But i have a little question about parameters of enum_out.
Datum enum_out(PG_FUNCTION_ARGS);
this is a part of my function
---
Datum esterna_nome2(PG_FUNCTION_ARGS){
int label;
label = enum_out(fcinfo);
sprintf(debug,"false enum_out: \"%s\" ",unrolled);
elog(LOG, debug);
---
but it works only because my enum parameter is the first (and using
fcinfo is a little obscure).
I must build a FunctionCallInfo structure (I think) but how?
Edoardo
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] immutable functions and enumerate type casts in indexes
Edoardo Panfili <[EMAIL PROTECTED]> writes:
> my enumerated type is (this is a subset)
> CREATE TYPE hibridation AS ENUM('none','genus','specie');
> function declaration
> CREATE FUNCTION ename(text,boolean,text,text RETURNS text AS
> 'funzioniGDB.so' LANGUAGE C IMMUTABLE;
> index creation (the type of ibrido is hibridation)
> CREATE INDEX i_specie_nome_specie ON specie
> (esterna_nome(ibrido::text,proParte,genere,specie));
> the result is
> ERROR: functions in index expression must be marked IMMUTABLE
Yeah, enum_out is considered STABLE not IMMUTABLE. I think this is
correct for the long term, even though right now you could make some
argument for an IMMUTABLE marking. For instance, we might in future
allow renaming of an enum member. (Actually, you can do that today
if you don't mind poking pg_enum by hand ...)
Now, maybe for your purposes here it's okay to consider it immutable.
In that case what I'd suggest doing is redefining ename() to take the
enum directly. You could invoke enum_out within the function if you
really need a text equivalent.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] immutable functions and enumerate type casts in indexes
Hello,
I have a problem with enumerated types in functions parameters.
my enumerated type is (this is a subset)
CREATE TYPE hibridation AS ENUM('none','genus','specie');
function declaration
CREATE FUNCTION ename(text,boolean,text,text RETURNS text AS
'funzioniGDB.so' LANGUAGE C IMMUTABLE;
index creation (the type of ibrido is hibridation)
CREATE INDEX i_specie_nome_specie ON specie
(esterna_nome(ibrido::text,proParte,genere,specie));
the result is
ERROR: functions in index expression must be marked IMMUTABLE
Searching on google I found some explanation: the problem arises with
"non immutable" typea as "data" but I can't figure the problem (or
better, the solution) with enumerate types.
What can I do?
thank you
Edoardo
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
