function side effects
in [PgSql]
|
From: Tatsuo Ishii on 23 Feb 2010 20:00 > IMMUTABLE/STABLE/VOLATILE is not really about side effects, it is about > how long the function value can be expected to hold still for. > > There are quite a lot of cases of functions that are marked > conservatively as stable (or even volatile) but could be considered > immutable in particular queries, because the application developer is > prepared to assume that values such as GUCs won't change in his usage. > The traditional way to deal with that is to wrap them in an immutable > wrapper function. There's actually code in the planner to make that > work --- we have to suppress inlining to avoid exposing the not-immutable > guts, else the planner will not do what's wanted. "IMMUTABLE indicates that the function cannot modify the database..." "STABLE indicates that the function cannot modify the database..." Apparently IMMUTABLE/STABLE should not write to database according to docs. Are you saying that in the real world these are ignored? If so, this is an important database intergrity issue as Simon pointed out. > It's *not an error* for a nonvolatile function to call a volatile one. > At least it's never been in the past, and I'm sure you'd break some > applications if you made it so in the future. If this is true, at least the docs should be corrected IMO. > There may be some value in inventing a "has no side effects" marker, but > that should not be confused with IMMUTABLE/STABLE. It seems it is neccessary to invent new marker for not only pgpool but HOT/SR (and may be Slony). They need to know if a query including functions do writes or not *before* sending to backend. Otherwise they got error because they sent a write query to standby. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Tom Lane on 23 Feb 2010 22:18 Tatsuo Ishii <ishii(a)postgresql.org> writes: > Apparently IMMUTABLE/STABLE should not write to database according to > docs. Are you saying that in the real world these are ignored? If so, > this is an important database intergrity issue as Simon pointed out. One more time: these markings are not about whether the function writes to the database. They are about whether its result value can be presumed to be unchanging in various circumstances. Trying to redefine them for another purpose is going to lead to nothing but trouble. And no, there is not an "integrity issue" here. If the planner thinks something is stable or immutable, it might evaluate it fewer times than the user would wish, but that doesn't render the database inconsistent. It just means the user doesn't get the behavior he wanted. That's no different from any other erroneously-written query. > It seems it is neccessary to invent new marker for not only pgpool but > HOT/SR (and may be Slony). They need to know if a query including > functions do writes or not *before* sending to backend. Otherwise they > got error because they sent a write query to standby. Well, that's something we can consider adding in 9.1, but it's far too late for 9.0. Personally I find that goal rather suspect anyway. I think the chances of determining this reliably in pgpool are negligible, even if functions were marked like that. You would need to duplicate *all* of the backend's parsing and all of its state (eg schema search path) in order to discover anything. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Jaime Casanova on 23 Feb 2010 22:46 On Tue, Feb 23, 2010 at 10:18 PM, Tom Lane <tgl(a)sss.pgh.pa.us> wrote: > > Personally I find that goal rather suspect anyway. > I think the chances of determining this reliably in pgpool are > negligible, even if functions were marked like that.  You would need to > duplicate *all* of the backend's parsing and all of its state (eg schema > search path) in order to discover anything. > i agree with that, as Alvaro suggested maybe a way to ask the server about the whole query is the way to go -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL AsesorÃa y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Peter Eisentraut on 1 Mar 2010 14:56 On tis, 2010-02-23 at 16:54 -0500, Jaime Casanova wrote: > On Tue, Feb 23, 2010 at 2:02 PM, Tom Lane <tgl(a)sss.pgh.pa.us> wrote: > > > > There may be some value in inventing a "has no side effects" marker, but > > that should not be confused with IMMUTABLE/STABLE. > > > > a READONLY function? SQL standard: <SQL-data access indication> ::= NO SQL | CONTAINS SQL | READS SQL DATA | MODIFIES SQL DATA Notice also that this is separate from <deterministic characteristic> ::= DETERMINISTIC | NOT DETERMINISTIC which is the SQL standard's variant of volatility. So someone has already had the idea that these two should exist separately. -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Jaime Casanova on 1 Mar 2010 15:30
On Mon, Mar 1, 2010 at 2:56 PM, Peter Eisentraut <peter_e(a)gmx.net> wrote: > On tis, 2010-02-23 at 16:54 -0500, Jaime Casanova wrote: >> On Tue, Feb 23, 2010 at 2:02 PM, Tom Lane <tgl(a)sss.pgh.pa.us> wrote: >> > >> > There may be some value in inventing a "has no side effects" marker, but >> > that should not be confused with IMMUTABLE/STABLE. >> > >> >> a READONLY function? > > SQL standard: > > <SQL-data access indication> ::= > NO SQL > | CONTAINS SQL > | READS SQL DATA > | MODIFIES SQL DATA > good! > Notice also that this is separate from > > <deterministic characteristic> ::= > DETERMINISTIC > | NOT DETERMINISTIC > so IMMUTABLE = DETERMINISTIC NO SQL, STABLE = DETERMINISTIC READS SQL DATA VOLATILE = NOT DETERMINISTIC MODIFIES SQL DATA > which is the SQL standard's variant of volatility. > > So someone has already had the idea that these two should exist > separately. > seems something we should implement -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL AsesorÃa y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers |