Re: [HACKERS] function side effects
On mån, 2010-03-01 at 15:30 -0500, Jaime Casanova wrote: so IMMUTABLE = DETERMINISTIC NO SQL, STABLE = DETERMINISTIC READS SQL DATA VOLATILE = NOT DETERMINISTIC MODIFIES SQL DATA It might be tempting to create such a mapping, but there could be a number of pitfalls, especially if you define it as a commutative equivalence rather than say logical implications. For example, MODIFIES SQL DATA ought to imply VOLATILE, but the reverse is not true. When the volatility attribute was introduced, we briefly looked at the standard deterministic attribute, but concluded that it would be better to create settings that describe how the PostgreSQL planner/executor works instead of some abstract setting that is descriptive but doesn't actually help optimizing the query. We might actually end up with all three groups of settings at some point. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] function side effects
On mån, 2010-03-01 at 16:29 -0500, Tom Lane wrote: Peter Eisentraut pete...@gmx.net writes: SQL standard: SQL-data access indication ::= NO SQL | CONTAINS SQL | READS SQL DATA | MODIFIES SQL DATA Huh. I understand three of those, but what is the use of CONTAINS SQL? My reading is that CONTAINS SQL allows/indicates the use of non-data statements such as CREATE or ALTER, whereas READS SQL DATA and MODIFIES SQL DATA specifically refer to reading or writing table data. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] function side effects
On tis, 2010-02-23 at 16:54 -0500, Jaime Casanova wrote: On Tue, Feb 23, 2010 at 2:02 PM, Tom Lane t...@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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] function side effects
On Mon, Mar 1, 2010 at 2:56 PM, Peter Eisentraut pete...@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 t...@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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] function side effects
Peter Eisentraut pete...@gmx.net writes: SQL standard: SQL-data access indication ::= NO SQL | CONTAINS SQL | READS SQL DATA | MODIFIES SQL DATA Huh. I understand three of those, but what is the use of CONTAINS SQL? Seems like that would have to be the same as the last one, or maybe the next-to-last one if you're prepared to assume it's read-only SQL. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] function side effects
Jaime Casanova írta: On Mon, Mar 1, 2010 at 2:56 PM, Peter Eisentraut pete...@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 t...@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 At least the combinations to recognize the current IMMUTABLE/STABLE/VOLATILE features. By definition, READS SQL DATA and MODIFIES SQL DATA cannot be DETERMINISTIC. But I can imagine some C and PL/Perl functions that are NOT DETERMINISTIC NO SQL. And what does CONTAINS SQL mean? Is it distinct from the other two READS/MODIFIES SQL DATA markers? SELECT CURRENT_TIMESTAMP may be an example but it doesn't seem to be significantly different from $$SELECT $1 || $2;$$ LANGUAGE SQL or the same written in PL/Perl or C. Best regards, Zoltán Böszörményi -- Bible has answers for everything. Proof: But let your communication be, Yea, yea; Nay, nay: for whatsoever is more than these cometh of evil. (Matthew 5:37) - basics of digital technology. May your kingdom come - superficial description of plate tectonics -- Zoltán Böszörményi Cybertec Schönig Schönig GmbH http://www.postgresql.at/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] function side effects
On Mon, Mar 1, 2010 at 4:29 PM, Tom Lane t...@sss.pgh.pa.us wrote: Peter Eisentraut pete...@gmx.net writes: SQL standard: SQL-data access indication ::= NO SQL | CONTAINS SQL | READS SQL DATA | MODIFIES SQL DATA Huh. I understand three of those, but what is the use of CONTAINS SQL? Seems like that would have to be the same as the last one i guess the safer asumption is: treat it as MODIFIES SQL DATA -- 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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] function side effects
Tom Lane t...@sss.pgh.pa.us wrote: Peter Eisentraut pete...@gmx.net writes: SQL standard: SQL-data access indication ::= NO SQL | CONTAINS SQL | READS SQL DATA | MODIFIES SQL DATA Huh. I understand three of those, but what is the use of CONTAINS SQL? Seems like that would have to be the same as the last one, or maybe the next-to-last one if you're prepared to assume it's read-only SQL. On a quick search of the spec, the best I was able to tell was that you are required to use CONTAINS SQL if the language is SQL. Perhaps it figures that the database engine can determine the read/write behavior directly if the language is SQL, and you tell it what it does if you're coding in some other language. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] function side effects
Tatsuo Ishii wrote: Hi, I'm wondering if we could detect a funcion has a side effect, i.e. does a write to database. This is neccessary for pgpool to decide if a qeury should to be sent to all of databases or not. If a query includes functions which do writes to database, it should send the query to all of databases, otherwise the contents of databases go into inconsistent state. I was talking about this to someone in Cuba and one conclusion we reached was that this was a fairly difficult task -- consider that someone may choose to define an innocent-looking operator using a volatile function. If you only examine things that look like functions in the query you will miss those. The only way to figure out whether a query has a write effect is to ask the server about the whole query. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] function side effects
I'm wondering if we could detect a funcion has a side effect, i.e. does a write to database. This is neccessary for pgpool to decide if a qeury should to be sent to all of databases or not. If a query includes functions which do writes to database, it should send the query to all of databases, otherwise the contents of databases go into inconsistent state. I was talking about this to someone in Cuba and one conclusion we reached was that this was a fairly difficult task -- consider that someone may choose to define an innocent-looking operator using a volatile function. If you only examine things that look like functions in the query you will miss those. The only way to figure out whether a query has a write effect is to ask the server about the whole query. In general you are right. However in most database application systems, it is possible that all functions are properly designed and implemented (at least they want so). In this world, more or less PostgreSQL functions are just a part of their applications. If they trust their client side applications, why they cannot trust PostgreSQL custom functions as well? -- 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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] function side effects
Tom Lane t...@sss.pgh.pa.us wrote: Those classifications are meant as planner directives; they are NOT meant to be bulletproof. Hanging database integrity guarantees on whether a non volatile function changes anything is entirely unsafe. To give just one illustration of the problems, a nonvolatile function is allowed to call a volatile one. Could it work to store a flag in each process to indicate when it is executing a non-volatile function, and throw an error on any attempt to call a volatile function or modify the database? -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] function side effects
Kevin Grittner kevin.gritt...@wicourts.gov writes: Tom Lane t...@sss.pgh.pa.us wrote: Those classifications are meant as planner directives; they are NOT meant to be bulletproof. Hanging database integrity guarantees on whether a non volatile function changes anything is entirely unsafe. To give just one illustration of the problems, a nonvolatile function is allowed to call a volatile one. Could it work to store a flag in each process to indicate when it is executing a non-volatile function, and throw an error on any attempt to call a volatile function or modify the database? 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. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] function side effects
I was talking about this to someone in Cuba and one conclusion we reached was that this was a fairly difficult task -- consider that someone may choose to define an innocent-looking operator using a volatile function. If you only examine things that look like functions in the query you will miss those. The only way to figure out whether a query has a write effect is to ask the server about the whole query. In general you are right. However in most database application systems, it is possible that all functions are properly designed and implemented (at least they want so). In this world, more or less PostgreSQL functions are just a part of their applications. If they trust their client side applications, why they cannot trust PostgreSQL custom functions as well? Still there could be none honest functions such as calling volatile functions from non volatile function in the PostgreSQL system(I have not made any investigation. But it's possible). Or vendor provided functions (for example embedded in closed source packages) might fall into this category. Probably it's enough for pgpool to have a black list of such that function. Maintaining such a list is a boring task but I cannot think of any good way at this point. -- 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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] function side effects
On Tue, Feb 23, 2010 at 11:08 AM, Tom Lane t...@sss.pgh.pa.us wrote: It's *not an error* for a nonvolatile function to call a volatile one. it should be considered an error i think, someone think there is a use cas for calling volatile functions inside stable ones but i can see what that reason could be... 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. i'm sure of that too, but in this case seems reasonable to do so -- 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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] function side effects
Tom Lane t...@sss.pgh.pa.us wrote: Kevin Grittner kevin.gritt...@wicourts.gov writes: throw an error on any attempt to call a volatile function or modify the database? It's *not an error* for a nonvolatile function to call a volatile one. Right, we all know it currently doesn't throw an error, but I can't think of anywhere I'd like to have someone do that in a database for which I have any responsibility. Does anyone have a sane use case for a non-volatile function to call a volatile one or to update the database? -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] function side effects
On Tue, Feb 23, 2010 at 4:52 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Right, we all know it currently doesn't throw an error, but I can't think of anywhere I'd like to have someone do that in a database for which I have any responsibility. Does anyone have a sane use case for a non-volatile function to call a volatile one or to update the database? So consider for example a function which explicitly sets the timezone and then uses timestamp without timezone functions (which are volatile only because the GUC variable might change between calls). Or somebody who uses the tsearch functions because they're planning to not change their dictionaries. Or builds a hash function by calling random after setting the seed to a specific value -- this is actually a fairly popular strategy for building good hash functions. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] function side effects
Greg Stark gsst...@mit.edu wrote: Kevin Grittner kevin.gritt...@wicourts.gov wrote: Does anyone have a sane use case for a non-volatile function to call a volatile one or to update the database? So consider for example a function which explicitly sets the timezone and then uses timestamp without timezone functions (which are volatile only because the GUC variable might change between calls). OK, I can see where that would be sane, but it seems more fragile than using timestamp with time zone. But, OK, something sane and functional could break on that. Or somebody who uses the tsearch functions because they're planning to not change their dictionaries. I didn't realize tsearch functions were volatile. Should they really be so? Or builds a hash function by calling random after setting the seed to a specific value -- this is actually a fairly popular strategy for building good hash functions. I'd never seen that. I'm not sure I understand where that comes in useful, but if you've seen it enough to call it fairly popular I guess I have to accept it. Thanks for the examples. They did make me consider a real-life type of process which isn't currently implemented as a PostgreSQL function, but conceivably could be -- randomizing a pool of jurors to facilitate jury selection. My eyes are opened. :-) -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] function side effects
On Tue, Feb 23, 2010 at 6:39 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Or somebody who uses the tsearch functions because they're planning to not change their dictionaries. I didn't realize tsearch functions were volatile. Should they really be so? Uhm, my mistake. They're stable. Ok, for that one I'll substitute a function which uses pg_read_file knowing that the file in question won't be changed. Perhaps it's a per-machine key or something like that. Or builds a hash function by calling random after setting the seed to a specific value -- this is actually a fairly popular strategy for building good hash functions. I'd never seen that. I'm not sure I understand where that comes in useful, but if you've seen it enough to call it fairly popular I guess I have to accept it. http://en.wikipedia.org/wiki/Universal_hashing They have the useful property that it's hard for an attacker to contrive data which has poor collision behaviour. Thanks for the examples. They did make me consider a real-life type of process which isn't currently implemented as a PostgreSQL function, but conceivably could be -- randomizing a pool of jurors to facilitate jury selection. My eyes are opened. :-) I'm not actually sure I follow what you're picturing. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] function side effects
Greg Stark gsst...@mit.edu writes: On Tue, Feb 23, 2010 at 6:39 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: I didn't realize tsearch functions were volatile. Should they really be so? Uhm, my mistake. They're stable. 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. There may be some value in inventing a has no side effects marker, but that should not be confused with IMMUTABLE/STABLE. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] function side effects
On Tue, Feb 23, 2010 at 2:02 PM, Tom Lane t...@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. Yeah, that's what I was thinking, too ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] function side effects
Greg Stark gsst...@mit.edu wrote: Kevin Grittner kevin.gritt...@wicourts.gov wrote: Thanks for the examples. They did make me consider a real-life type of process which isn't currently implemented as a PostgreSQL function, but conceivably could be -- randomizing a pool of jurors to facilitate jury selection. My eyes are opened. :-) I'm not actually sure I follow what you're picturing. Well, to facilitate people's rights to a jury of their peers, we obtain lists of people in each county based on having a drivers license or state ID, being registered to vote, etc., then (after eliminating duplicates and those who have served on juries in recent years) we randomly select a subset, who get questionnaires, from which (at a later date) we randomly pick people to summon for jury a juror panel, from which (on each day they appear) we randomly select people for particular juries. Any flaw in the randomness of selection could constitute grounds for an appeal of the outcome of a case, so we have to be careful about process. (Randomness being defined as the properties that nobody with an interest in the case can control or predict who will be selected from one group into the next, and there is no bias on anything related to demographics, like age or last name [which could correlate with ethnicity]). Sounds like fun, eh? -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] function side effects
On Tue, Feb 23, 2010 at 2:02 PM, Tom Lane t...@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? -- 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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] function side effects
On Mon, 2010-02-22 at 23:49 -0500, Tom Lane wrote: Tatsuo Ishii is...@postgresql.org writes: I'm wondering if we could detect a funcion has a side effect, i.e. does a write to database. Currently we have three properties of functions: IMMUTABLE, STABLE and VOLATILE. According to docs IMMUTABLE or STABLE functions do not write to database. Those classifications are meant as planner directives; they are NOT meant to be bulletproof. You make them sound like hints. (I thought we frowned on those?) That isn't true, they don't just change the optimal plan in the way the enable_* parameters do. Immutable functions are reduced in ways that would give the wrong answer if the function is actually volatile. Referring to function properties as planner directives hides their critical importance to the output of a query that calls such functions. Hanging database integrity guarantees on whether a non volatile function changes anything is entirely unsafe. To give just one illustration of the problems, a nonvolatile function is allowed to call a volatile one. So wrongly marking a function as something other than volatile *is* a data integrity issue. Why is that OK? ISTM that this should work the way Tatsuo wants it to work. Immutability should be passed down through the call stack to ensure we can't get this wrong. If people have been advising clients to set things immutable when they are not that seems fairly questionable. We shouldn't avoid fixing an integrity loophole just simply to preserve a planner backdoor, especially since other backdoors are specifically avoided. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] function side effects
On Tue, 2010-02-23 at 12:51 +0900, Tatsuo Ishii wrote: I'm wondering if we could detect a funcion has a side effect, i.e. does a write to database. This is neccessary for pgpool to decide if a qeury should to be sent to all of databases or not. If a query includes functions which do writes to database, it should send the query to all of databases, otherwise the contents of databases go into inconsistent state. Currently we have three properties of functions: IMMUTABLE, STABLE and VOLATILE. According to docs IMMUTABLE or STABLE functions do not write to database. VOLATILE functions *may* do writes to database. Maybe I could regard VOLATILE functions always do write, but priblem is, VOLATILE qfunctions such as random() and timeofday() apparently do not write and sending those queries that include such functions is overkill. Can we VOLATILE property divide into two categories, say, VOLATILE without write, and VOLATILE with write? pgpool parses the query before deciding how to route it, yes? Why not mark random() and timeofday() as stable in the pgpool catalog, yet leave them as volatile on the database servers? It will just work then. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] function side effects
Simon Riggs si...@2ndquadrant.com writes: So wrongly marking a function as something other than volatile *is* a data integrity issue. Why is that OK? ISTM that this should work the way Tatsuo wants it to work. Please read the rest of the thread. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] function side effects
I'm wondering if we could detect a funcion has a side effect, i.e. does a write to database. This is neccessary for pgpool to decide if a qeury should to be sent to all of databases or not. If a query includes functions which do writes to database, it should send the query to all of databases, otherwise the contents of databases go into inconsistent state. Currently we have three properties of functions: IMMUTABLE, STABLE and VOLATILE. According to docs IMMUTABLE or STABLE functions do not write to database. VOLATILE functions *may* do writes to database. Maybe I could regard VOLATILE functions always do write, but priblem is, VOLATILE qfunctions such as random() and timeofday() apparently do not write and sending those queries that include such functions is overkill. Can we VOLATILE property divide into two categories, say, VOLATILE without write, and VOLATILE with write? pgpool parses the query before deciding how to route it, yes? Right. Why not mark random() and timeofday() as stable in the pgpool catalog, yet leave them as volatile on the database servers? It will just work then. Please note that random() and timeofday() are just examples. What I'm thinking about was, a function which directly or indirectly cause write to database (thus lead writing to log). Consider a function that calls those has-side-effect functions. We need a property which is inherited to child function to parent function. -- 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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] function side effects
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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] function side effects
Tatsuo Ishii is...@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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] function side effects
On Tue, Feb 23, 2010 at 10:18 PM, Tom Lane t...@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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] function side effects
Tatsuo Ishii is...@postgresql.org writes: I'm wondering if we could detect a funcion has a side effect, i.e. does a write to database. Currently we have three properties of functions: IMMUTABLE, STABLE and VOLATILE. According to docs IMMUTABLE or STABLE functions do not write to database. Those classifications are meant as planner directives; they are NOT meant to be bulletproof. Hanging database integrity guarantees on whether a non volatile function changes anything is entirely unsafe. To give just one illustration of the problems, a nonvolatile function is allowed to call a volatile one. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
