Thread: Re: [BUGS] BUG #4027: backslash escaping not disabled in plpgsql
Peter Eisentraut wrote: > Tom Lane wrote: > > plpgsql does not consider standard_conforming_strings --- it still uses > > backslash escaping in its function bodies regardless. Since the > > language itself is not standardized, I see no particular reason that > > standard_conforming_strings should govern it. > > I think plpgsql should behave either consistently with the rest of PostgreSQL > or with Oracle, which it is copied from. > > > I believe the reason for > > not changing it was that it seemed too likely to break existing > > functions, with potentially nasty consequences if they chanced to be > > security definers. > > Is this actually true or did we just forget it? :-) I have added this TODO item: Consider honoring standard_conforming_strings in PL/pgSQL functionbodies * http://archives.postgresql.org/pgsql-bugs/2008-03/msg00102.php Are we every going to enable standard_conforming_strings by default? If not, I will remove the TODO item mentiong this. standard_conforming_strings was added in Postgres 8.1, and escape_string_warning was enabled in 8.2. I think the big issue is that having standard_conforming_strings affect function behavior introduces the same problems we have had in the past of having a GUC affect function behavior. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
On Thu, Apr 9, 2009 at 11:16 AM, Bruce Momjian <bruce@momjian.us> wrote: > Peter Eisentraut wrote: >> Tom Lane wrote: >> > plpgsql does not consider standard_conforming_strings --- it still uses >> > backslash escaping in its function bodies regardless. Since the >> > language itself is not standardized, I see no particular reason that >> > standard_conforming_strings should govern it. >> >> I think plpgsql should behave either consistently with the rest of PostgreSQL >> or with Oracle, which it is copied from. >> >> > I believe the reason for >> > not changing it was that it seemed too likely to break existing >> > functions, with potentially nasty consequences if they chanced to be >> > security definers. >> >> Is this actually true or did we just forget it? :-) > > I have added this TODO item: > > Consider honoring standard_conforming_strings in PL/pgSQL function > bodies > > * http://archives.postgresql.org/pgsql-bugs/2008-03/msg00102.php > > Are we every going to enable standard_conforming_strings by default? If > not, I will remove the TODO item mentiong this. > standard_conforming_strings was added in Postgres 8.1, and > escape_string_warning was enabled in 8.2. > > I think the big issue is that having standard_conforming_strings affect > function behavior introduces the same problems we have had in the past > of having a GUC affect function behavior. I think this should wait at least one more release. Based on my experience, there are probably a LOT of applications out there that have yet to be updated. It wouldn't bother me if we never enabled it by default, either. I'm just -1 on doing it now. ...Robert
Bruce Momjian <bruce@momjian.us> wrote:
> standard_conforming_strings was added in Postgres 8.1, and
> escape_string_warning was enabled in 8.2.
Other way around -- the warning was available in 8.1; the standard
character string literals were available in 8.2.
> I think the big issue is that having standard_conforming_strings
> affect function behavior introduces the same problems we have had in
> the past of having a GUC affect function behavior.
Can't that be managed with this CREATE FUNCTION option?:
SET configuration_parameter { TO value | = value | FROM CURRENT }
I would like to see standard character string literals at least
available in PL/pgSQL, although I don't personally care whether it is
the default or whether I need to specify it with the above option.
Might it not confuse people to have this GUC behave differently than
others, though?
-Kevin
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> Bruce Momjian <bruce@momjian.us> wrote:
>> I think the big issue is that having standard_conforming_strings
>> affect function behavior introduces the same problems we have had in
>> the past of having a GUC affect function behavior.
> Can't that be managed with this CREATE FUNCTION option?:
> SET configuration_parameter { TO value | = value | FROM CURRENT }
It can be, the question is whether we're prepared to break everything
under the sun until people add that.
regards, tom lane
Tom Lane wrote:
> "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> > Bruce Momjian <bruce@momjian.us> wrote:
> >> I think the big issue is that having standard_conforming_strings
> >> affect function behavior introduces the same problems we have had in
> >> the past of having a GUC affect function behavior.
>
> > Can't that be managed with this CREATE FUNCTION option?:
> > SET configuration_parameter { TO value | = value | FROM CURRENT }
>
> It can be, the question is whether we're prepared to break everything
> under the sun until people add that.
I think we would first have to agree to issue escape_string_warning
warnings for code in PL/pgSQL functions, then think about having
standard_conforming_strings control PL/pgSQL behavior; this is what we
did with SQL and it seems to have worked.
-- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB
http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
>> Can't that be managed with this CREATE FUNCTION option?:
>> SET configuration_parameter { TO value | = value | FROM CURRENT }
>
> It can be, the question is whether we're prepared to break
everything
> under the sun until people add that.
Well, surely the 8.3 behavior is not what we want.
(1) The plpgsql parser identifies the boundaries of the string based
on backslash escapes.
(2) The character string literal is interpreted based on the GUC
setting the first time the function is executed (and presumably the
first time executed after the function's invalidated).
(3) Subsequent changes to the GUC don't affect how it's interpreted.
scca=# show standard_conforming_strings ;standard_conforming_strings
-----------------------------on
(1 row)
scca=# create or replace function kjgtest() returns text language
plpgsql immutable strict as $$ begin return '\x49'; end; $$;
CREATE FUNCTION
scca=# select * from kjgtest();kjgtest
---------\x49
(1 row)
scca=# set standard_conforming_strings = off;
SET
scca=# select * from kjgtest();kjgtest
---------\x49
(1 row)
scca=# create or replace function kjgtest() returns text language
plpgsql immutable strict as $$ begin return '\x49'; end; $$;
CREATE FUNCTION
scca=# select * from kjgtest();kjgtest
---------I
(1 row)
scca=# set standard_conforming_strings = on;
SET
scca=# select * from kjgtest();kjgtest
---------I
(1 row)
scca=# create or replace function kjgtest() returns text language
plpgsql immutable strict as $$ begin return '\x49'; end; $$;
CREATE FUNCTION
scca=# set standard_conforming_strings = off;
SET
scca=# select * from kjgtest();kjgtest
---------I
(1 row)
-Kevin
Bruce Momjian <bruce@momjian.us> writes:
> Tom Lane wrote:
>> It can be, the question is whether we're prepared to break everything
>> under the sun until people add that.
> I think we would first have to agree to issue escape_string_warning
> warnings for code in PL/pgSQL functions, then think about having
> standard_conforming_strings control PL/pgSQL behavior; this is what we
> did with SQL and it seems to have worked.
Well, considering that we are still afraid to pull the trigger on
changing the standard_conforming_strings default, it's a bit premature
to claim that it "worked" for SQL. But I agree that some kind of
stepwise process will be necessary if we want to try to change this.
IIRC there was some discussion of using plpgsql's (undocumented) #option
syntax to control this, rather than having a GUC that would be specific
to plpgsql.
regards, tom lane
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> wrote: > Well, surely the 8.3 behavior is not what we want. Unless I'm missing something, plpgsql *already* effectively recognizes and respects the standard_conforming_strings GUC *except* as the last character of a conforming string literal within the procedure body, and then not always. Am I missing something here? scca=# set standard_conforming_strings = on; SET scca=# create or replace function kjgtest() returns text language plpgsql immutable as 'begin return \'\x49\'; end;'; Expanded display is on. Invalid command \';. Try \? for help. scca=# \x Expanded display is off. scca-# create or replace function kjgtest() returns text language plpgsql immutable as $$ begin return '\x49\'; end; $$; ERROR: syntax error at or near "create" LINE 2: create or replace function kjgtest() returns text language p... ^ scca=# create or replace function kjgtest() returns text language plpgsql immutable as $$ begin return '\x49\\'; end; $$; CREATE FUNCTION scca=# select kjgtest();kjgtest ---------\x49\\ (1 row) scca=# set standard_conforming_strings = off; SET scca=# create or replace function kjgtest() returns text language plpgsql immutable as 'begin return \'\x49\'; end;'; CREATE FUNCTION scca=# select kjgtest();kjgtest ---------I (1 row) scca=# create or replace function kjgtest() returns text language plpgsql immutable as $$ begin return '\x49\'; end; $$; ERROR: unterminated string CONTEXT: compile of PL/pgSQL function "kjgtest" near line 1 scca=# create or replace function kjgtest() returns text language plpgsql immutable as $$ begin return '\x49\\'; end; $$; CREATE FUNCTION scca=# select kjgtest();kjgtest ---------I\ (1 row) Given this behavior, how much could be working for standard_conforming_strings = on which would break with more complete support? Maybe this particular GUC should default to an implied SET standard_conforming_strings FROM CURRENT and the plpgsql parser should use it? Can anyone show a working case that would break with that? -Kevin
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> Unless I'm missing something, plpgsql *already* effectively recognizes
> and respects the standard_conforming_strings GUC *except* as the last
> character of a conforming string literal within the procedure body,
> and then not always. Am I missing something here?
Yes --- I think you are confusing parsing of the string literal that
is the argument of CREATE FUNCTION with the parsing that the plpgsql
interpreter does on the function body once it gets it. In particular,
this example:
create or replace function kjgtest() returns text language
plpgsql immutable as $$ begin return 'foo\'; end; $$;
fails regardless of the standard_conforming_strings setting, because
the plpgsql interpreter considers the backslash to escape the quote
regardless.
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> wrote: > I think you are confusing parsing of the string literal that > is the argument of CREATE FUNCTION with the parsing that the plpgsql > interpreter does on the function body once it gets it. In > particular, this example: > > create or replace function kjgtest() returns text language > plpgsql immutable as $$ begin return 'foo\'; end; $$; > > fails regardless of the standard_conforming_strings setting, because > the plpgsql interpreter considers the backslash to escape the quote > regardless. Oh, I'm not confused about that at all. I'm arguing that it's a bad idea. I agree with the OP that this is a bug. Did you look at my other examples of behavior? In particular: scca=# create or replace function kjgtest() returns text language plpgsql immutable as $$ begin return '\x49\\'; end; $$; CREATE FUNCTION scca=# select kjgtest();kjgtest ---------\x49\\ (1 row) Can you show one case where having plgpsql parse the function body based on the standard_conforming_strings GUC would break *anything* that now works? That's an allegation which I haven't been able to confirm, so I'm wondering about the basis. -Kevin
I wrote: > Tom Lane <tgl@sss.pgh.pa.us> wrote: >> I think you are confusing parsing of the string literal that >> is the argument of CREATE FUNCTION with the parsing that the plpgsql >> interpreter does on the function body once it gets it. > Oh, I'm not confused about that at all. I'm arguing that it's a bad > idea. To be more explicit, I see that there is a third parser phase -- when the function is planned, the original contents of the character string literal are passed to the normal PostgreSQL execution engine, which parses them again, potentially using different rules from those used by the plpgsql interpreter. I maintain that having the execution engine use different rules for looking at the value of the literal than the plpgsql parser used to find the boundaries of the literal is where the weird corner case bugs come in. For someone using string literal '\x49\\' in a plpgsql function, the plpgsql parser sees it as a two character string, but when the function is actually run, depending on whether the first execution is using standard string literals, this can be either a two character or a six character string. Unless the coder of the function uses the SET option in declaring the function, they don't know what value will be used at run time, and it may change from run to run. It seems to me that we already have exactly the kinds of problems you say you want to avoid, and that there is an obvious fix to avoid them. -Kevin
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> I think you are confusing parsing of the string literal that
>> is the argument of CREATE FUNCTION with the parsing that the plpgsql
>> interpreter does on the function body once it gets it.
> Oh, I'm not confused about that at all. I'm arguing that it's a bad
> idea. I agree with the OP that this is a bug. Did you look at my
> other examples of behavior?
I ignored all the ones that used non-dollar-quote syntax for the overall
function body, since they are just confusing the issue.
> Can you show one case where having plgpsql parse the function body
> based on the standard_conforming_strings GUC would break *anything*
> that now works?
regression=# create function foo() returns int as $$
regression$# begin
regression$# raise notice 'foo\'s bar';
regression$# return 1;
regression$# end$$ language plpgsql;
CREATE FUNCTION
regression=# select foo();
NOTICE: foo's barfoo
----- 1
(1 row)
In this case the string literal isn't actually ever passed to the main
SQL engine, so the SQL quoting rules aren't relevant. (I don't remember
offhand if anything besides RAISE works that way.)
It may be that this isn't a very important case, but to claim that
it doesn't exist is simply wrong.
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> wrote: >> Can you show one case where having plgpsql parse the function body >> based on the standard_conforming_strings GUC would break *anything* >> that now works? > > regression=# create function foo() returns int as $$ > regression$# begin > regression$# raise notice 'foo\'s bar'; > regression$# return 1; > regression$# end$$ language plpgsql; > CREATE FUNCTION > regression=# select foo(); > NOTICE: foo's bar > foo > ----- > 1 > (1 row) > > In this case the string literal isn't actually ever passed to the > main SQL engine, so the SQL quoting rules aren't relevant. (I don't > remember offhand if anything besides RAISE works that way.) > > It may be that this isn't a very important case, but to claim that > it doesn't exist is simply wrong. OK, I didn't try that. Point taken. It is a bigger mess than I thought then. The aspect of 8.3 behavior that concerns me most is that neither the author of a function, nor anyone using it, can control or predict which way a string literal with a backslash will be interpreted, unless the author explicitly specifies the SET standard_conforming_strings clause in the function declaration. I'm betting that most people writing and using plpgsql functions don't know that. Any thoughts about what can or should be done about that? -Kevin
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> The aspect of 8.3 behavior that concerns me most is that neither the
> author of a function, nor anyone using it, can control or predict
> which way a string literal with a backslash will be interpreted,
> unless the author explicitly specifies the SET
> standard_conforming_strings clause in the function declaration.
Yeah. This is one reason why I'm still afraid to flip the default
value of standard_conforming_strings --- there seems too much risk
of widespread breakage.
I don't have a good solution for it, but I agree it's a problem.
regards, tom lane
On Sat, Apr 11, 2009 at 4:40 AM, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote: > The aspect of 8.3 behavior that concerns me most is that neither the > author of a function, nor anyone using it, can control or predict > which way a string literal with a backslash will be interpreted, > unless the author explicitly specifies the SET > standard_conforming_strings clause in the function declaration. I'm > betting that most people writing and using plpgsql functions don't > know that. Any thoughts about what can or should be done about that? Isn't this exactly the same problem that application authors have been facing with SQL in their code? Namely, if there's a backslash anywhere in a string literal you *cannot* leave it as a bare single-quoted string literal. You need to decide whether you want the backslash treated as an escape character (and therefore use E quoting), or as a backslash (and therefore use $$ quoting). Until you've done that for every single string literal with a backslash, your application isn't ready for standard_conforming_strings to be switched on. I agree that there are probably a great many app authors out there who don't realise how very boned they might be if the default GUC gets changed and they haven't prepared their SQL to cope. Cheers, BJ
Brendan Jurd wrote: > On Sat, Apr 11, 2009 at 4:40 AM, Kevin Grittner > <Kevin.Grittner@wicourts.gov> wrote: > > The aspect of 8.3 behavior that concerns me most is that neither the > > author of a function, nor anyone using it, can control or predict > > which way a string literal with a backslash will be interpreted, > > unless the author explicitly specifies the SET > > standard_conforming_strings clause in the function declaration. ?I'm > > betting that most people writing and using plpgsql functions don't > > know that. ?Any thoughts about what can or should be done about that? > > Isn't this exactly the same problem that application authors have been > facing with SQL in their code? > > Namely, if there's a backslash anywhere in a string literal you > *cannot* leave it as a bare single-quoted string literal. You need to > decide whether you want the backslash treated as an escape character > (and therefore use E quoting), or as a backslash (and therefore use $$ > quoting). > > Until you've done that for every single string literal with a > backslash, your application isn't ready for > standard_conforming_strings to be switched on. > > I agree that there are probably a great many app authors out there who > don't realise how very boned they might be if the default GUC gets > changed and they haven't prepared their SQL to cope. I assume those authors are getting warnings, which is something we don't for PL/pgSQL now. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Tom Lane <tgl@sss.pgh.pa.us> wrote: > "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes: >> The aspect of 8.3 behavior that concerns me most is that neither >> the author of a function, nor anyone using it, can control or >> predict which way a string literal with a backslash will be >> interpreted, unless the author explicitly specifies the SET >> standard_conforming_strings clause in the function declaration. > > Yeah. This is one reason why I'm still afraid to flip the default > value of standard_conforming_strings --- there seems too much risk > of widespread breakage. > > I don't have a good solution for it, but I agree it's a problem. Now that I see that string literals are currently interpreted inconsistently, I don't think there's any way to get to a sane behavior without risking some breakage somewhere. If, as I've seen some people assert, most people aren't setting the standard_conforming_strings = on, it would seem to be reasonable to put the risk with that 'on' setting. Let me ask this -- If we were to change the plpgsql parser to pay attention to the GUC, it couldn't break anything for any environment which always has the GUC 'off', could it? If not, I am having a hard time seeing a smoother transition than to change the plpgsql parser to use the GUC, and to have the CREATE FUNCTION statement make a special case of defaulting this GUC to FROM CURRENT. Making an exception of this offends a little, but not as badly as unpredictable runtime behavior. An advantage of this approach is that it would be just another place to check your string literals when and if you go to switch over to standard literals. Whether to ever change the default behavior over to the standard is more of a "marketing" decision than a technical one, I think. -Kevin
Bruce Momjian <bruce@momjian.us> writes:
> Brendan Jurd wrote:
>> I agree that there are probably a great many app authors out there who
>> don't realise how very boned they might be if the default GUC gets
>> changed and they haven't prepared their SQL to cope.
> I assume those authors are getting warnings, which is something we don't
> for PL/pgSQL now.
To the extent that the strings are getting passed through to the main
SQL engine, they do get warnings now, and pretty noisy ones:
regression=# create function foo2() returns text as $$
begin return 'foo\'s bar';
end$$ language plpgsql;
WARNING: nonstandard use of \' in a string literal
LINE 1: SELECT 'foo\'s bar' ^
HINT: Use '' to write quotes in strings, or use the escape string syntax (E'...').
QUERY: SELECT 'foo\'s bar'
CONTEXT: SQL statement in PL/PgSQL function "foo2" near line 2
CREATE FUNCTION
regression=# select foo2();
WARNING: nonstandard use of \' in a string literal
LINE 1: SELECT 'foo\'s bar' ^
HINT: Use '' to write quotes in strings, or use the escape string syntax (E'...').
QUERY: SELECT 'foo\'s bar'
CONTEXT: PL/pgSQL function "foo2" line 2 at RETURN foo2
-----------foo's bar
(1 row)
It's the corner cases where plpgsql doesn't pass strings through that
are worrisome. It's possible that RAISE is the only such case ---
anyone want to check?
Actually, what this thread is leading me towards is the idea that almost
nobody really has standard_conforming_strings turned on in production
(except maybe with apps ported from Oracle or someplace else). If they
did, we'd be seeing more complaints about plpgsql not working properly.
So maybe we *could* change plpgsql to honor the GUC without anyone
noticing too much.
regards, tom lane
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> Let me ask this -- If we were to change the plpgsql parser to pay
> attention to the GUC, it couldn't break anything for any environment
> which always has the GUC 'off', could it?
Right, because the behavior wouldn't actually change.
I'm starting to lean in the same direction --- the current plpgsql
behavior with the GUC 'on' is sufficiently broken that it seems unlikely
anyone is doing much with plpgsql and that setting.
It still remains that actually flipping the default would probably
provoke lots of breakage, but plpgsql's current behavior doesn't
help that.
regards, tom lane
Tom Lane wrote: > "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes: > > Let me ask this -- If we were to change the plpgsql parser to pay > > attention to the GUC, it couldn't break anything for any environment > > which always has the GUC 'off', could it? > > Right, because the behavior wouldn't actually change. > > I'm starting to lean in the same direction --- the current plpgsql > behavior with the GUC 'on' is sufficiently broken that it seems unlikely > anyone is doing much with plpgsql and that setting. > > It still remains that actually flipping the default would probably > provoke lots of breakage, but plpgsql's current behavior doesn't > help that. It would be nice to know if we are ever going to set standard_conforming_strings to on. If not, we can remove the TODO item. The bigger question is if we aren't going to turn it on was there any value to setting escape_string_warning to on in 8.2? We required a lot of users to prefix their strings with 'E'. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Bruce Momjian <bruce@momjian.us> wrote: > It would be nice to know if we are ever going to set > standard_conforming_strings to on. My personal bias is to go to the standard behavior as the default at some point. For legacy reasons, I don't know that you would ever want to remove the setting; especially since I don't think it adds much code if you're going to support the E'...' literals. The ugliest thing about this GUC is that it adds some complications to the flex code, but it doesn't seem that bad to me. -Kevin
Kevin Grittner wrote: > Bruce Momjian <bruce@momjian.us> wrote: > > It would be nice to know if we are ever going to set > > standard_conforming_strings to on. > > My personal bias is to go to the standard behavior as the default at > some point. For legacy reasons, I don't know that you would ever want > to remove the setting; especially since I don't think it adds much > code if you're going to support the E'...' literals. The ugliest > thing about this GUC is that it adds some complications to the flex > code, but it doesn't seem that bad to me. Agreed, we would probably never remove standard_conforming_strings. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Bruce Momjian <bruce@momjian.us> writes:
> Kevin Grittner wrote:
>> My personal bias is to go to the standard behavior as the default at
>> some point. For legacy reasons, I don't know that you would ever want
>> to remove the setting; especially since I don't think it adds much
>> code if you're going to support the E'...' literals. The ugliest
>> thing about this GUC is that it adds some complications to the flex
>> code, but it doesn't seem that bad to me.
> Agreed, we would probably never remove standard_conforming_strings.
Yeah, I don't see that happening either. I agree with Kevin that it
would be nice to flip the default at some point, but I'm afraid it's a
long way off yet.
Back to the point at hand: do we want to look at making plpgsql respect
the GUC? I think it's a bit trickier than it looks, because we don't
want duplicate warnings from both plpgsql and the main parser for
strings that get fed through. I'm inclined to deal with the special
case (RAISE and anything else similar) by changing the code so that we
*do* feed the string literal through the main parser, not for any
functional effect but just to have it throw the right warnings/errors.
Otherwise the plpgsql lexer has to somehow know when to warn and when
not, which'd be a mess.
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> wrote: > do we want to look at making plpgsql respect the GUC? +1 > I'm inclined to deal with the special case (RAISE and anything else > similar) by changing the code so that we *do* feed the string > literal through the main parser, not for any functional effect but > just to have it throw the right warnings/errors. +1 -Kevin
Tom, > Actually, what this thread is leading me towards is the idea that almost > nobody really has standard_conforming_strings turned on in production > (except maybe with apps ported from Oracle or someplace else). If they > did, we'd be seeing more complaints about plpgsql not working properly. > So maybe we *could* change plpgsql to honor the GUC without anyone > noticing too much. Actually, a lot of people are using $escapes$ for all nested quotes in plpgsql. So they wouldn't notice the problem. -- Josh Berkus PostgreSQL Experts Inc. www.pgexperts.com
>>>>> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes: Tom> Back to the point at hand: do we want to look at making plpgsqlTom> respect the GUC? Surely what matters is the value of the GUC at the time that you did the CREATE FUNCTION, not the value at the time you happen to be calling it? -- Andrew (irc:RhodiumToad)
Andrew Gierth <andrew@tao11.riddles.org.uk> wrote: > Surely what matters is the value of the GUC at the time that you did > the CREATE FUNCTION, not the value at the time you happen to be > calling it? Well, that's a change I'm arguing for. That would require both the plpgsql parser change Tom is talking about, and a change to CREATE FUNCTION such that there is an implied SET standard_compliant_strings FROM CURRENT -- which is something I've suggested a couple times; there's been no explicit response to that. See back here in the thread for some behavior which surprised me: http://archives.postgresql.org/pgsql-hackers/2009-04/msg00519.php -Kevin
Andrew Gierth <andrew@tao11.riddles.org.uk> writes:
> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:
> Tom> Back to the point at hand: do we want to look at making plpgsql
> Tom> respect the GUC?
> Surely what matters is the value of the GUC at the time that you did
> the CREATE FUNCTION, not the value at the time you happen to be
> calling it?
No, it isn't, and that's not the immediate problem anyway --- the
immediate problem is that plpgsql doesn't respect *any* value of
the GUC.
regards, tom lane
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> Well, that's a change I'm arguing for. That would require both the
> plpgsql parser change Tom is talking about, and a change to CREATE
> FUNCTION such that there is an implied SET standard_compliant_strings
> FROM CURRENT -- which is something I've suggested a couple times;
> there's been no explicit response to that.
If you want one: it seems like a really bad idea. Aside from the sheer
ugliness of special-casing one particular GUC, it would break existing
pg_dump files, since pg_dump has no idea that its setting of
standard_conforming_strings might influence the behavior of functions
it defines.
I don't actually see that standard_conforming_strings is worse than
search_path or half a dozen other settings that will influence the
semantics of SQL queries. If anything it's less bad than those since
it's less likely to break things silently. The whole topic just
illustrates that "invent a GUC" is not a pain-free solution to handling
definitional conflicts.
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> wrote: > "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes: >> a change to CREATE FUNCTION such that there is an implied SET >> standard_compliant_strings FROM CURRENT Hopefully obvious, I meant standard_conforming_strings. > it seems like a really bad idea. Then perhaps a note in the PL/pgSQL docs about the importance of specifying that clause if the function contains any character string literals which include a backslash? Such a note should probably point out that without this clause, the runtime value of any such literal will be dependent on the value of standard_conforming_strings when the plan is generated. I think that many will find that behavior surprising; so if it's not feasible to change it, we should at least document it. -Kevin