Thread: PG 9.0 and standard_conforming_strings

PG 9.0 and standard_conforming_strings

From
Bruce Momjian
Date:
With the release of Postgres 9.0, should we consider changing the
default for '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. +


Re: PG 9.0 and standard_conforming_strings

From
"Kevin Grittner"
Date:
Bruce Momjian <bruce@momjian.us> wrote:
> With the release of Postgres 9.0, should we consider changing the
> default for 'standard_conforming_strings'?
If not now, when?
-Kevin


Re: PG 9.0 and standard_conforming_strings

From
"David E. Wheeler"
Date:
On Jan 29, 2010, at 11:51 AM, Bruce Momjian wrote:

> With the release of Postgres 9.0, should we consider changing the
> default for 'standard_conforming_strings'?

+1

David



Re: PG 9.0 and standard_conforming_strings

From
Tom Lane
Date:
Bruce Momjian <bruce@momjian.us> writes:
> With the release of Postgres 9.0, should we consider changing the
> default for 'standard_conforming_strings'?

I'm inclined to think we're going to have enough problems without that.
Changing that default will break, approximately speaking, every single
Postgres client app.  Do you really think more than epsilon of them
are clean and ready for such a change?
        regards, tom lane


Re: PG 9.0 and standard_conforming_strings

From
Tom Lane
Date:
I wrote:
> Bruce Momjian <bruce@momjian.us> writes:
>> With the release of Postgres 9.0, should we consider changing the
>> default for 'standard_conforming_strings'?

> I'm inclined to think we're going to have enough problems without that.

BTW, core already had that discussion, but maybe I should repeat it
to try to forestall any other "since this is going to be 9.0, let's
break backwards compatibility in a big way!" proposals.  Now is not
the time to be making big changes; we are much too late in the devel
cycle to work through all the possible consequences.  Because we
switched from it's-8.5 to it's-9.0 at such a late stage, we really
need to consider that that's only a marketing version number and
technical compatibility decisions should be made the same way as
for any other major release.

Perhaps at some point we will choose to do a major version bump where
we really do clean up a lot of bad backwards-compatibility things.  That
needs to be done in a deliberate fashion with a lot of advance planning;
and things should get broken near the beginning of the devel cycle, not
the end.

[ still bearing scars from the 8.3 implicit-cast business, which we
didn't think would generate nearly the backlash it did... ]
        regards, tom lane


Re: PG 9.0 and standard_conforming_strings

From
Bruce Momjian
Date:
Tom Lane wrote:
> Bruce Momjian <bruce@momjian.us> writes:
> > With the release of Postgres 9.0, should we consider changing the
> > default for 'standard_conforming_strings'?
> 
> I'm inclined to think we're going to have enough problems without that.
> Changing that default will break, approximately speaking, every single
> Postgres client app.  Do you really think more than epsilon of them
> are clean and ready for such a change?

Well, if they aren't ready now, then we might as well say we are never
going to change it and update the documentation and TODO list to reflect
that --- we have had standard_conforming_strings since 2005.  We can't
keep pretending this will happen if we have no intention of doing it.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +


Re: PG 9.0 and standard_conforming_strings

From
Alex Hunsaker
Date:
On Fri, Jan 29, 2010 at 13:42, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I wrote:
>> Bruce Momjian <bruce@momjian.us> writes:
>>> With the release of Postgres 9.0, should we consider changing the
>>> default for 'standard_conforming_strings'?
>
>> I'm inclined to think we're going to have enough problems without that.

> [ still bearing scars from the 8.3 implicit-cast business, which we
> didn't think would generate nearly the backlash it did... ]

Yeah that was my first reaction.  But then again we also have a guc
they can change back.  Sure you could create your own typecasts to
restore the old behavior in 8.3 (after trolling the mailing lists, or
finding some blog entry that got created X months after the
release...).   Thats no where near as nice as a simple setting.


Re: PG 9.0 and standard_conforming_strings

From
Robert Haas
Date:
On Fri, Jan 29, 2010 at 3:28 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Bruce Momjian <bruce@momjian.us> writes:
>> With the release of Postgres 9.0, should we consider changing the
>> default for 'standard_conforming_strings'?
>
> I'm inclined to think we're going to have enough problems without that.
> Changing that default will break, approximately speaking, every single
> Postgres client app.  Do you really think more than epsilon of them
> are clean and ready for such a change?

Well, I already had to fix a great many things in my apps to prevent
them from spewing warnings all over creation.  If other people have
done likewise it might not be too bad; OTOH, there's probably not a
huge amount of downside in waiting.

...Robert


Re: PG 9.0 and standard_conforming_strings

From
Bill Moran
Date:
In response to Bruce Momjian <bruce@momjian.us>:

> Tom Lane wrote:
> > Bruce Momjian <bruce@momjian.us> writes:
> > > With the release of Postgres 9.0, should we consider changing the
> > > default for 'standard_conforming_strings'?
> > 
> > I'm inclined to think we're going to have enough problems without that.
> > Changing that default will break, approximately speaking, every single
> > Postgres client app.  Do you really think more than epsilon of them
> > are clean and ready for such a change?
> 
> Well, if they aren't ready now, then we might as well say we are never
> going to change it and update the documentation and TODO list to reflect
> that --- we have had standard_conforming_strings since 2005.  We can't
> keep pretending this will happen if we have no intention of doing it.

Announce it as a change for 9.1 NOW, and then it will be whoever's
fault if they aren't paying attention.  Plenty of time to fix it
if it's announced now.

Also, as long as the config option is there, they can always flip it
back, which makes it MUCH lower overhead than the casting change was.
Overall, I don't think this change is nearly as severe as the cast
change in 8.3, and I don't feel it warrants the same eggshell walking.
When the decision is made to remove the standard_conforming_string
config option altogether ... that'll be a different story!

-- 
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/


Re: PG 9.0 and standard_conforming_strings

From
"Joshua D. Drake"
Date:
On Fri, 2010-01-29 at 15:45 -0500, Bruce Momjian wrote:
> Tom Lane wrote:
> > Bruce Momjian <bruce@momjian.us> writes:
> > > With the release of Postgres 9.0, should we consider changing the
> > > default for 'standard_conforming_strings'?
> >
> > I'm inclined to think we're going to have enough problems without that.
> > Changing that default will break, approximately speaking, every single
> > Postgres client app.  Do you really think more than epsilon of them
> > are clean and ready for such a change?
>
> Well, if they aren't ready now, then we might as well say we are never
> going to change it and update the documentation and TODO list to reflect
> that --- we have had standard_conforming_strings since 2005.  We can't
> keep pretending this will happen if we have no intention of doing it.

I would argue that now is the perfect time for a number of reasons:

(1) 9.x regardless of the fact that it is just a number, reflects a
massive change as a whole.

(2) HS/SR are going to be scary things to use for at least 6 months to a
year. That is not to disparage the hard work, just that they are big
enough and invasive enough to make sure we get through a couple of dot
revs before we start seriously recommending them.

(3) As Bruce suggests, we are on year 6 now. I think we can take the
heat.

(4) The 8.3 issue wasn't nearly as bad as Tom is making it out to be.
Yes, there was a lot of WTF going on, but only by people that aren't
paying attention anyway and the work to fix it was pretty nominal.

(5) The time to change it is NOW, so that when we go into beta it
becomes a serious in your face, we have to fix this if we want to be
compatible with v9 of PostgreSQL.

And get this... because of HS and SR, everybody is going to want to be
compatible with v9.

Sincerely,

Joshua D. Drake


--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering
Respect is earned, not gained through arbitrary and repetitive use or Mr. or Sir.

Re: PG 9.0 and standard_conforming_strings

From
Bruce Momjian
Date:
Tom Lane wrote:
> I wrote:
> > Bruce Momjian <bruce@momjian.us> writes:
> >> With the release of Postgres 9.0, should we consider changing the
> >> default for 'standard_conforming_strings'?
> 
> > I'm inclined to think we're going to have enough problems without that.
> 
> BTW, core already had that discussion, but maybe I should repeat it
> to try to forestall any other "since this is going to be 9.0, let's
> break backwards compatibility in a big way!" proposals.  Now is not
> the time to be making big changes; we are much too late in the devel
> cycle to work through all the possible consequences.  Because we
> switched from it's-8.5 to it's-9.0 at such a late stage, we really
> need to consider that that's only a marketing version number and
> technical compatibility decisions should be made the same way as
> for any other major release.
> 
> Perhaps at some point we will choose to do a major version bump where
> we really do clean up a lot of bad backwards-compatibility things.  That
> needs to be done in a deliberate fashion with a lot of advance planning;
> and things should get broken near the beginning of the devel cycle, not
> the end.
> 
> [ still bearing scars from the 8.3 implicit-cast business, which we
> didn't think would generate nearly the backlash it did... ]

I did ask this same question for the 8.5/9.0 release in April of 2009 so
don't say I am only asking about this at the end of development cycles:
http://archives.postgresql.org/pgsql-hackers/2009-04/msg00512.php

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +


Re: PG 9.0 and standard_conforming_strings

From
Bruce Momjian
Date:
Joshua D. Drake wrote:
> (4) The 8.3 issue wasn't nearly as bad as Tom is making it out to be.
> Yes, there was a lot of WTF going on, but only by people that aren't
> paying attention anyway and the work to fix it was pretty nominal.

The big mistake we made in 8.3 is not having those compatibility
functions that Peter created ready _at_ _release_ _time_.  I believe
that was pure sloppiness on our part.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +


Re: PG 9.0 and standard_conforming_strings

From
Tom Lane
Date:
Alex Hunsaker <badalex@gmail.com> writes:
> On Fri, Jan 29, 2010 at 13:42, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> [ still bearing scars from the 8.3 implicit-cast business, which we
>> didn't think would generate nearly the backlash it did... ]

> Yeah that was my first reaction.  But then again we also have a guc
> they can change back.

"There's a GUC for it" is NOT a helpful answer; if there's one thing
that we've learned the hard way over the past years, it's that GUCs
don't solve compatibility problems.  Applications don't know to set
them, and having the wrong setting can easily become a security hole
(particularly for this one).

I stand by the position that it's way too late in the cycle for
insufficiently-thought-out proposals for major behavioral changes.
        regards, tom lane


Re: PG 9.0 and standard_conforming_strings

From
Bruce Momjian
Date:
Tom Lane wrote:
> Alex Hunsaker <badalex@gmail.com> writes:
> > On Fri, Jan 29, 2010 at 13:42, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >> [ still bearing scars from the 8.3 implicit-cast business, which we
> >> didn't think would generate nearly the backlash it did... ]
> 
> > Yeah that was my first reaction.  But then again we also have a guc
> > they can change back.
> 
> "There's a GUC for it" is NOT a helpful answer; if there's one thing
> that we've learned the hard way over the past years, it's that GUCs
> don't solve compatibility problems.  Applications don't know to set
> them, and having the wrong setting can easily become a security hole
> (particularly for this one).
> 
> I stand by the position that it's way too late in the cycle for
> insufficiently-thought-out proposals for major behavioral changes.

Well, since I asked in April of 2009, at the beginning of the cycle, 6
years after the introduction of the variable, and we still are not doing
it, then let's stop pretending we will ever do it.

The way the docs stand now we hold it over people's heads and issue
warnings that are meaningless if we are never going to change it.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +


Re: PG 9.0 and standard_conforming_strings

From
Alex Hunsaker
Date:
On Fri, Jan 29, 2010 at 14:03, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Alex Hunsaker <badalex@gmail.com> writes:
>> On Fri, Jan 29, 2010 at 13:42, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I stand by the position that it's way too late in the cycle for
> insufficiently-thought-out proposals for major behavioral changes.

After skimming the thread Bruce linked:http://archives.postgresql.org/pgsql-hackers/2009-04/msg00512.php

It certainly seems "insufficiently-thought-out".  :(


Re: PG 9.0 and standard_conforming_strings

From
Josh Berkus
Date:
> I stand by the position that it's way too late in the cycle for
> insufficiently-thought-out proposals for major behavioral changes.

I don't see how announcing this earlier in the dev cycle would help, at
all.  The people who read -hackers have been using
standards-conforming-strings for years.  Further, if we announce it now,
people have 4-5 months to get ready for it, assuming they were updating
to 9.0.0 anyway, which I doubt anyone is.

For this release, I'm already planning to have  big "backwards
compatibility" section and web page with *lots* of warnings and
explanations, and because of the media around the release for once it
will be read.

I'd argue that Bruce is right; if we're not going to do it now, we might
as well stop pretending we ever are.

--Josh Berkus


Re: PG 9.0 and standard_conforming_strings

From
Bruce Momjian
Date:
Alex Hunsaker wrote:
> On Fri, Jan 29, 2010 at 14:03, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > Alex Hunsaker <badalex@gmail.com> writes:
> >> On Fri, Jan 29, 2010 at 13:42, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > I stand by the position that it's way too late in the cycle for
> > insufficiently-thought-out proposals for major behavioral changes.
> 
> After skimming the thread Bruce linked:
>  http://archives.postgresql.org/pgsql-hackers/2009-04/msg00512.php
> 
> It certainly seems "insufficiently-thought-out".  :(

Is this still true?  When we changed plpgsql so it shared the scanner
with the backend scanner, does this issue no longer apply, i.e. 
consider honoring standard_conforming_strings in PL/pgSQL function
bodies?
--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +


Re: PG 9.0 and standard_conforming_strings

From
"Kevin Grittner"
Date:
Alex Hunsaker <badalex@gmail.com> wrote:
> After skimming the thread Bruce linked:
>  http://archives.postgresql.org/pgsql-hackers/2009-04/msg00512.php
> 
> It certainly seems "insufficiently-thought-out".  :(
Just as a clarification, while the GUC was *added* in 8.1, it was
read-only with a value of 'off'.  I submitted a patch and started
using it under 8.1 in February of 2006 (because we had an urgent
need), and it officially became *settable* in 8.2.
I don't have strong feelings about changing the default.  Obviously,
this bites people primarily when converting to PostgreSQL -- that's
when it bit me and that's where people normally are when they post
to the lists about related issues.
It's not clear to me that the issues related to functions have been
thought out sufficiently; my personal feeling is that a function
should run with the setting under which it was created (as the
semantics of the literal seem as though they should be "frozen" at
that point), but that was shot down.  And then there's the issue
about EXECUTE.  If we don't have consensus on a solution to those
issues, maybe we should wait.  Those who need it who are already
using PostgreSQL already have it figured out -- it's just a bump on
the road to converting for those used to standard literals.
-Kevin


Re: PG 9.0 and standard_conforming_strings

From
Tom Lane
Date:
Josh Berkus <josh@agliodbs.com> writes:
>> I stand by the position that it's way too late in the cycle for
>> insufficiently-thought-out proposals for major behavioral changes.

> I don't see how announcing this earlier in the dev cycle would help, at
> all.

We would have more than no-time-at-all to test it and fix any breakage.
Just to start close to home, do you really trust either psql or pg_dump
to be completely free of standard_conforming_strings issues?  How about
JDBC or ODBC?  Python drivers?  PLs?

The really short and sweet answer is that if you have any ambition at
all to ship 9.0 this year, it is too late to add new work items.  This
is a work item, and not a small one.
        regards, tom lane


Re: PG 9.0 and standard_conforming_strings

From
Tom Lane
Date:
Bruce Momjian <bruce@momjian.us> writes:
> Well, since I asked in April of 2009, at the beginning of the cycle, 6
> years after the introduction of the variable, and we still are not doing
> it, then let's stop pretending we will ever do it.

We have made forward progress since that thread (we fixed the plpgsql
parsing issues, partially in 8.4 and completely for 9.0).  We can
continue to make forward progress in the future.  But *right now is not
the time*.  We have more than enough on our plates for 9.0 already, and
just about nobody believes we are going to meet the schedule now.
        regards, tom lane


Re: PG 9.0 and standard_conforming_strings

From
Andres Freund
Date:
On Friday 29 January 2010 23:34:09 Tom Lane wrote:
> Josh Berkus <josh@agliodbs.com> writes:
> >> I stand by the position that it's way too late in the cycle for
> >> insufficiently-thought-out proposals for major behavioral changes.
> > 
> > I don't see how announcing this earlier in the dev cycle would help, at
> > all.
> The really short and sweet answer is that if you have any ambition at
> all to ship 9.0 this year, it is too late to add new work items.  This
> is a work item, and not a small one.
What about anouncing in the 9.0 releasenotes that it will be removed in 9.1?

Andres


Re: PG 9.0 and standard_conforming_strings

From
Bruce Momjian
Date:
Andres Freund wrote:
> On Friday 29 January 2010 23:34:09 Tom Lane wrote:
> > Josh Berkus <josh@agliodbs.com> writes:
> > >> I stand by the position that it's way too late in the cycle for
> > >> insufficiently-thought-out proposals for major behavioral changes.
> > > 
> > > I don't see how announcing this earlier in the dev cycle would help, at
> > > all.
> > The really short and sweet answer is that if you have any ambition at
> > all to ship 9.0 this year, it is too late to add new work items.  This
> > is a work item, and not a small one.
> What about anouncing in the 9.0 releasenotes that it will be removed in 9.1?

You mean turned on by default, right?

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +


Re: PG 9.0 and standard_conforming_strings

From
Tom Lane
Date:
Andres Freund <andres@anarazel.de> writes:
> What about anouncing in the 9.0 releasenotes that it will be removed in 9.1?

That seems quite useless.

I note that we've made such statements before and not followed through
on them; one that just came up again is that contrib/xml2 is a couple
releases past when it was said it'd be removed, and there is still no
prospect of it really dying in the near future.

The bottom line is that these sorts of changes take actual *work*,
and not a trivial amount of it.  No amount of blather in the
documentation will substitute for somebody doing the work.
        regards, tom lane


Re: PG 9.0 and standard_conforming_strings

From
Andres Freund
Date:
On Friday 29 January 2010 23:54:15 Tom Lane wrote:
> Andres Freund <andres@anarazel.de> writes:
> > What about anouncing in the 9.0 releasenotes that it will be removed in
> > 9.1?
> 
> That seems quite useless.
> 
> I note that we've made such statements before and not followed through
> on them; one that just came up again is that contrib/xml2 is a couple
> releases past when it was said it'd be removed, and there is still no
> prospect of it really dying in the near future.
> The bottom line is that these sorts of changes take actual *work*,
> and not a trivial amount of it.  No amount of blather in the
> documentation will substitute for somebody doing the work.
It is not about somebody doing the work, it is about lowering the impact a 
bit.

Andres


Re: PG 9.0 and standard_conforming_strings

From
Josh Berkus
Date:
> We would have more than no-time-at-all to test it and fix any breakage.
> Just to start close to home, do you really trust either psql or pg_dump
> to be completely free of standard_conforming_strings issues?  How about
> JDBC or ODBC?  Python drivers?  PLs?

Oh, yeah.  I was just thinking about the direct user and DBA issues; of
course if there's additional potential breakage, we'll have to defer it.Too bad, it would have been a good time to
breakit from a user
 
perspective.

--Josh Berkus



Re: PG 9.0 and standard_conforming_strings

From
Robert Haas
Date:
On Fri, Jan 29, 2010 at 5:44 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Bruce Momjian <bruce@momjian.us> writes:
>> Well, since I asked in April of 2009, at the beginning of the cycle, 6
>> years after the introduction of the variable, and we still are not doing
>> it, then let's stop pretending we will ever do it.
>
> We have made forward progress since that thread (we fixed the plpgsql
> parsing issues, partially in 8.4 and completely for 9.0).

This is a really good argument.  If there are changes in 9.0 that are
designed to mitigate the impact of this change, then we should wait at
least one or two more releases before doing anything.

I say, let's plan it for 10.0.

...Robert


Re: PG 9.0 and standard_conforming_strings

From
Andres Freund
Date:
On Friday 29 January 2010 23:47:22 Bruce Momjian wrote:
> Andres Freund wrote:
> > On Friday 29 January 2010 23:34:09 Tom Lane wrote:
> > > Josh Berkus <josh@agliodbs.com> writes:
> > > >> I stand by the position that it's way too late in the cycle for
> > > >> insufficiently-thought-out proposals for major behavioral changes.
> > > > 
> > > > I don't see how announcing this earlier in the dev cycle would help,
> > > > at all.
> > > 
> > > The really short and sweet answer is that if you have any ambition at
> > > all to ship 9.0 this year, it is too late to add new work items.  This
> > > is a work item, and not a small one.
> > 
> > What about anouncing in the 9.0 releasenotes that it will be removed in
> > 9.1?
> 
> You mean turned on by default, right?
Obviously, yes ;-)

Andres


Re: PG 9.0 and standard_conforming_strings

From
Cédric Villemain
Date:
2010/1/29 Tom Lane <tgl@sss.pgh.pa.us>:
> Josh Berkus <josh@agliodbs.com> writes:
>>> I stand by the position that it's way too late in the cycle for
>>> insufficiently-thought-out proposals for major behavioral changes.
>
>> I don't see how announcing this earlier in the dev cycle would help, at
>> all.
>
> We would have more than no-time-at-all to test it and fix any breakage.
> Just to start close to home, do you really trust either psql or pg_dump
> to be completely free of standard_conforming_strings issues?  How about
> JDBC or ODBC?  Python drivers?  PLs?

Do you mean that turning standard_conforming_string ON may lead to
error with pg_dump, psql or something else ? (I don't care of projects
outside the official postgresql tarball in this question)

Whether the param is ON or OFF by default, what does that change in this area ?

>
> The really short and sweet answer is that if you have any ambition at
> all to ship 9.0 this year, it is too late to add new work items.  This
> is a work item, and not a small 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
>



--
Cédric Villemain


Re: PG 9.0 and standard_conforming_strings

From
Tom Lane
Date:
Cédric Villemain <cedric.villemain.debian@gmail.com> writes:
> 2010/1/29 Tom Lane <tgl@sss.pgh.pa.us>:
>> We would have more than no-time-at-all to test it and fix any breakage.
>> Just to start close to home, do you really trust either psql or pg_dump
>> to be completely free of standard_conforming_strings issues? �How about
>> JDBC or ODBC? �Python drivers? �PLs?

> Do you mean that turning standard_conforming_string ON may lead to
> error with pg_dump, psql or something else ? (I don't care of projects
> outside the official postgresql tarball in this question)

Maybe.  We concluded in the April 2009 thread that
standard_conforming_strings = ON had gotten little or no field testing,
and I don't see any strong reason to hope that it's gotten much more
since then.  It would be rather surprising if there *aren't* any lurking
bugs in one piece or another of client-side code.  And I don't think
that we should be so myopic as to consider that problems in drivers and
so forth are not of concern.

I would be all for making this change in an orderly fashion pursuant to
some agreed-on plan.  But cramming it in at the last minute because of
an essentially marketing-driven change of version name isn't good
project management, and I'm seriously afraid that doing so would bite
us in the rear.

An actual plan here might look like "let's flip it before 9.1alpha1
so we can get some alpha testing cycles on it" ...
        regards, tom lane


Re: PG 9.0 and standard_conforming_strings

From
Josh Berkus
Date:
> An actual plan here might look like "let's flip it before 9.1alpha1
> so we can get some alpha testing cycles on it" ...

"Hey, let's flip it in 9.1 CF 1, so that we can have some alpha testing
cycles on it."

;-)

--Josh Berkus


Re: PG 9.0 and standard_conforming_strings

From
"Joshua D. Drake"
Date:
On Fri, 2010-01-29 at 15:45 -0500, Bruce Momjian wrote:
> Tom Lane wrote:
> > Bruce Momjian <bruce@momjian.us> writes:
> > > With the release of Postgres 9.0, should we consider changing the
> > > default for 'standard_conforming_strings'?
> > 
> > I'm inclined to think we're going to have enough problems without that.
> > Changing that default will break, approximately speaking, every single
> > Postgres client app.  Do you really think more than epsilon of them
> > are clean and ready for such a change?
> 
> Well, if they aren't ready now, then we might as well say we are never
> going to change it and update the documentation and TODO list to reflect
> that --- we have had standard_conforming_strings since 2005.  We can't
> keep pretending this will happen if we have no intention of doing it.

I would argue that now is the perfect time for a number of reasons:

(1) 9.x regardless of the fact that it is just a number, reflects a
massive change as a whole.

(2) HS/SR are going to be scary things to use for at least 6 months to a
year. That is not to disparage the hard work, just that they are big
enough and invasive enough to make sure we get through a couple of dot
revs before we start seriously recommending them.

(3) As Bruce suggests, we are on year 6 now. I think we can take the
heat.

(4) The 8.3 issue wasn't nearly as bad as Tom is making it out to be.
Yes, there was a lot of WTF going on, but only by people that aren't
paying attention anyway and the work to fix it was pretty nominal.

(5) The time to change it is NOW, so that when we go into beta it
becomes a serious in your face, we have to fix this if we want to be
compatible with v9 of PostgreSQL.

And get this... because of HS and SR, everybody is going to want to be
compatible with v9.

Sincerely,

Joshua D. Drake


-- 
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering
Respect is earned, not gained through arbitrary and repetitive use or Mr. or Sir.



Re: PG 9.0 and standard_conforming_strings

From
Mark Mielke
Date:
On 01/29/2010 09:01 PM, Tom Lane wrote:
> Maybe.  We concluded in the April 2009 thread that
> standard_conforming_strings = ON had gotten little or no field testing,
> and I don't see any strong reason to hope that it's gotten much more
> since then.  It would be rather surprising if there *aren't* any lurking
> bugs in one piece or another of client-side code.  And I don't think
> that we should be so myopic as to consider that problems in drivers and
> so forth are not of concern.
>    

Not to contradict any justifiable investigation, but just as a data point:

All of my installations use:

backslash_quote = off   # on, off, or safe_encoding
escape_string_warning = off
standard_conforming_strings = on

I have not encountered any problems so far. I use PostgreSQL in about 10 
production applications (too tired to count them out :-) ), from psql to 
PHP to Perl to Java. I had also assumed this feature was tested and 
supported when I enabled it, as it seemed to me to be the only sensible 
implementation, and it was consistent with my interpretation of SQL. I 
had done some testing before enabling it the first time and was 
satisfied with the results.

> I would be all for making this change in an orderly fashion pursuant to
> some agreed-on plan.  But cramming it in at the last minute because of
> an essentially marketing-driven change of version name isn't good
> project management, and I'm seriously afraid that doing so would bite
> us in the rear.
>
> An actual plan here might look like "let's flip it before 9.1alpha1
> so we can get some alpha testing cycles on it" ...

Yep.

Cheers,
mark


-- 
Mark Mielke<mark@mielke.cc>



Re: PG 9.0 and standard_conforming_strings

From
Peter Eisentraut
Date:
On fre, 2010-01-29 at 16:06 -0500, Bruce Momjian wrote:
> The way the docs stand now we hold it over people's heads and issue
> warnings that are meaningless if we are never going to change it. 

Maybe the next step should be to leave standard_conforming_strings off
but make the warning an error.



Re: PG 9.0 and standard_conforming_strings

From
Cédric Villemain
Date:
2010/1/30 Tom Lane <tgl@sss.pgh.pa.us>:
> Cédric Villemain <cedric.villemain.debian@gmail.com> writes:
>> 2010/1/29 Tom Lane <tgl@sss.pgh.pa.us>:
>>> We would have more than no-time-at-all to test it and fix any breakage.
>>> Just to start close to home, do you really trust either psql or pg_dump
>>> to be completely free of standard_conforming_strings issues?  How about
>>> JDBC or ODBC?  Python drivers?  PLs?
>
>> Do you mean that turning standard_conforming_string ON may lead to
>> error with pg_dump, psql or something else ? (I don't care of projects
>> outside the official postgresql tarball in this question)
>
> Maybe.  We concluded in the April 2009 thread that
> standard_conforming_strings = ON had gotten little or no field testing,
> and I don't see any strong reason to hope that it's gotten much more
> since then.  It would be rather surprising if there *aren't* any lurking
> bugs in one piece or another of client-side code.  And I don't think
> that we should be so myopic as to consider that problems in drivers and
> so forth are not of concern.

Sure, I was just a bit scared because of production servers with
standard_conforming_string ON.
One interesting thing in this area is that I found very usefull to
turn this param ON for windows path. (so perhaps we will have more
testing coming from windows users than others ...)

>
> I would be all for making this change in an orderly fashion pursuant to
> some agreed-on plan.  But cramming it in at the last minute because of
> an essentially marketing-driven change of version name isn't good
> project management, and I'm seriously afraid that doing so would bite
> us in the rear.

I agree and I don't care this parameter is really on or off by
default. I just wanted to be sure it is sane enough to use it.

>
> An actual plan here might look like "let's flip it before 9.1alpha1
> so we can get some alpha testing cycles on it" ...

Sounds good.

--
Cédric Villemain


Re: PG 9.0 and standard_conforming_strings

From
"Kevin Grittner"
Date:
Tom Lane  wrote:
Cédric Villemain wrote:

>> Do you mean that turning standard_conforming_string ON may lead to
>> error with pg_dump, psql or something else ?

> Maybe. We concluded in the April 2009 thread that
> standard_conforming_strings = ON had gotten little or no field
> testing,

Well, we've been using it in hundreds of databases as our standard
setting in postgresql.conf since February, 2006.  We've used pg_dump
with it many hundreds of times, and tens of millions of JDBC
transactions per day since then.  We use psql heavily, too.  Not a
single sign of problems with it.  Surely that bumps the needle above
"little or no field testing".  Certainly there are PLs and PostgreSQL
features we don't use which should be tested first, but let's not
overstate the case.
> An actual plan here might look like "let's flip it before 9.1alpha1
> so we can get some alpha testing cycles on it" ...
That sounds sane.
-Kevin




Re: PG 9.0 and standard_conforming_strings

From
Euler Taveira de Oliveira
Date:
Peter Eisentraut escreveu:
> Maybe the next step should be to leave standard_conforming_strings off
> but make the warning an error.
> 
It will break application in the same way as enabling the parameter. Besides
that the parameter should be renamed to escape_string_*error* to reflect the
fact that it doesn't emit an error anymore. I don't think it is a good idea.

The main problem of enabling standard_conforming_strings is that applications
and/or programming language DB APIs are not prepared to support this. I don't
see a change in DB APIs (that I know of -- Python, Perl, and PHP) to add
support for producing a string according to standard_conforming_strings parameter.

IMHO we need to encourage such languages to modify their functions so we can
produce strings according to this parameter. These change will minimize the
number of problems in applications. Of course, there will be some problems in
those applications that doesn't use the escape function of the DB API but they
could always disable this parameter. ;)

As for enabling it by default, I'm afraid we will have to wait a few cycles of
development because of those changes in DB APIs. A reasonable target is 10.0. ;)


--  Euler Taveira de Oliveira http://www.timbira.com/


Re: PG 9.0 and standard_conforming_strings

From
Tom Lane
Date:
Euler Taveira de Oliveira <euler@timbira.com> writes:
> Peter Eisentraut escreveu:
>> Maybe the next step should be to leave standard_conforming_strings off
>> but make the warning an error.
>> 
> It will break application in the same way as enabling the parameter. Besides
> that the parameter should be renamed to escape_string_*error* to reflect the
> fact that it doesn't emit an error anymore. I don't think it is a good idea.

Yeah, I agree.  Such a change wouldn't do anything to help with testing
of the standard_conforming_strings = on case.  What it would do is
render the s_c_s = off case entirely useless, unless one also disabled
the error, which pretty much every single user would immediately do.
We might as well just flip the s_c_s setting.  People who don't want to
be bothered will still undo the setting change, but at least then our
default behavior is more standard rather than even less so.
        regards, tom lane


Re: PG 9.0 and standard_conforming_strings

From
"Albe Laurenz"
Date:
Mark Mielke wrote:
> On 01/29/2010 09:01 PM, Tom Lane wrote:
> > Maybe.  We concluded in the April 2009 thread that
> > standard_conforming_strings = ON had gotten little or no field testing,
> > and I don't see any strong reason to hope that it's gotten much more
> > since then.
>
> Not to contradict any justifiable investigation, but just as
> a data point:
>
> All of my installations use:
>
> backslash_quote = off   # on, off, or safe_encoding
> escape_string_warning = off
> standard_conforming_strings = on
>
> I have not encountered any problems so far. I use PostgreSQL in about 10
> production applications (too tired to count them out :-) ), from psql to
> PHP to Perl to Java. I had also assumed this feature was tested and
> supported when I enabled it, as it seemed to me to be the only sensible
> implementation, and it was consistent with my interpretation of SQL. I
> had done some testing before enabling it the first time and was
> satisfied with the results.

FWIW, I also turn it on by default in my company's installations and
revert it if there are problems.

These problems are usually carelessly written third party applications.
We discovered one omission in Npgsql which was fixed quickly.

To the best of my knowledge, JDBC and Npgsql are ready for
standard_conforming_strings=on.

I am all for changing it as soon as reasonably possible.

Yours,
Laurenz Albe


Re: PG 9.0 and standard_conforming_strings

From
"Greg Sabino Mullane"
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160


> The main problem of enabling standard_conforming_strings is
> that applications and/or programming language DB APIs are
> not prepared to support this. I don't see a change in DB
> APIs (that I know of -- Python, Perl, and PHP) to add
> support for producing a string according to
> standard_conforming_strings parameter.

Perl (DBD::Pg anyway) has been compatible since May 2008.

As one of the more vocal critics of the 8.3 implicit casting
incident, I say +1 on making the change. Unlike casting, it's
a simple GUC change to "fix", and now (9.0) is the time to
do it.

- --
Greg Sabino Mullane greg@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201002031233
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8


-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAktps4oACgkQvJuQZxSWSshAIgCg6wxvgVasOksQ8JQaFOeaSQEu
zZwAn0UqIG7Oti6BJVeJYTEx6b7VsZjf
=HJcB
-----END PGP SIGNATURE-----




Re: PG 9.0 and standard_conforming_strings

From
Tom Lane
Date:
"Greg Sabino Mullane" <greg@turnstep.com> writes:
> As one of the more vocal critics of the 8.3 implicit casting
> incident, I say +1 on making the change. Unlike casting, it's
> a simple GUC change to "fix", and now (9.0) is the time to
> do it.

Unfortunately, no: six months ago was the time to do it.

The argument for doing this now hinges solely on a marketing-driven
choice of version name, and not on any actual evidence that applications
are ready for it.  We really need to do this at the start of a devel
and alpha test cycle, not at the end.
        regards, tom lane


Re: PG 9.0 and standard_conforming_strings

From
Aidan Van Dyk
Date:
* Tom Lane <tgl@sss.pgh.pa.us> [100203 12:39]:
> "Greg Sabino Mullane" <greg@turnstep.com> writes:
> > As one of the more vocal critics of the 8.3 implicit casting
> > incident, I say +1 on making the change. Unlike casting, it's
> > a simple GUC change to "fix", and now (9.0) is the time to
> > do it.
> 
> Unfortunately, no: six months ago was the time to do it.
> 
> The argument for doing this now hinges solely on a marketing-driven
> choice of version name, and not on any actual evidence that applications
> are ready for it.  We really need to do this at the start of a devel
> and alpha test cycle, not at the end.

I'm not really worried about users using/testing PG from CVS or alphas -
they are users following PG closely enough that the switch is easy for
them to handle.  

*I* think beta1 is the when this *needs* to be done by.


Sure, it would have been nicer if it was earlier, but beta1 is when
"users" start actually using/testing (by "users" here, I mean ones who
aren't closely following PG development, and changes).  After beta1
comes out, it's absolutely a no-go, but if changing
standard_conforming_strings is something the "community" wants to go
towards, then I say do it now, before we're locked into another release
and another year of it.

a.

-- 
Aidan Van Dyk                                             Create like a god,
aidan@highrise.ca                                       command like a king,
http://www.highrise.ca/                                   work like a slave.

Re: PG 9.0 and standard_conforming_strings

From
Robert Haas
Date:
On Wed, Feb 3, 2010 at 12:34 PM, Greg Sabino Mullane <greg@turnstep.com> wrote:
> Perl (DBD::Pg anyway) has been compatible since May 2008.

I would interpret that to mean that there is a significant possibility
that a too-old DBD::Pg could get used with a new PostgreSQL, and
therefore we shouldn't change anything for 9.0.  May 2008 is not that
long ago, especially for people running systems like RHEL with
five-year major release cycles.

I am not sure I really understand why anyone is a rush to make this
change.  What harm is being done by the status quo?  What benefit do
we get out of changing the default?  The major argument that has been
offered so far is that "if we don't change it now, we never will", but
I don't believe that the tenor of this discussion supports the
contention that Tom or anyone else never wants to make this change.

It also seems to overlook the fact that we are STILL dealing with the
fallout from this change in the core code; Tom gave examples upthread
of changes that are being released for the first time *in 9.0* to
address problems created by this transition.  And that is just the
core code; we have to expect that third-party code will lag behind.

...Robert


Re: PG 9.0 and standard_conforming_strings

From
Alvaro Herrera
Date:
Tom Lane wrote:

> The argument for doing this now hinges solely on a marketing-driven
> choice of version name, and not on any actual evidence that applications
> are ready for it.  We really need to do this at the start of a devel
> and alpha test cycle, not at the end.

Application writers probably didn't bother all that much with alphas
though.  The bulk of them is going to start with the betas, which have
not been delivered yet, so it seems a good time to try.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.


Re: PG 9.0 and standard_conforming_strings

From
"Kevin Grittner"
Date:
Robert Haas <robertmhaas@gmail.com> wrote:
> What harm is being done by the status quo?  What benefit do
> we get out of changing the default?
I really think that the biggest harm is that people trying to
convert to PostgreSQL, or testing PostgreSQL with their
applications, can get bad behavior from use of standard string
literals.  If they post to a list and we point out the setting,
that'll probably be the end of the trouble -- and I have seen a few
such posts.  Interestingly, the frequency of such posts dropped off
after 8.2 was released with the GUC to configure it, which suggests
that people are often reading documentation before making the
attempt or at least doing web searches about the problem and fixing
it without a post to the community.
I do think we might be well-served to have such issues as this and
the "it's not a character string literal, it's a literal of UNKNOWN
type" covered in a page which is prominent enough to be likely to be
read by those considering migration or compatibility testing.  I'm
not sure exactly where that would be, unless it's a couple more FAQ
entries -- but a "compatibility and migration" page might be worth
creating, with a reasonably prominent link from the home page.
-Kevin


Re: PG 9.0 and standard_conforming_strings

From
"Greg Sabino Mullane"
Date:
-----BEGIN PGP SIGNED MESSAGE-----                              
Hash: RIPEMD160                                                 


>> Perl (DBD::Pg anyway) has been compatible since May 2008.

> I would interpret that to mean that there is a significant possibility
> that a too-old DBD::Pg could get used with a new PostgreSQL, and      
> therefore we shouldn't change anything for 9.0.  May 2008 is not that 
> long ago, especially for people running systems like RHEL with        
> five-year major release cycles.                                       

That's a silly conclusion. Applications and drivers are always going to 
lag behind. If someone is having a problem, they either upgrade their   
DBD::Pg or flip the GUC. Are you really saying we should wait           
until 2008 +5 years (2013!) before making this change? Wouldn't we have 
to wait five years past the point when *all* drivers are compatible     
by your definition?                                                     

> I am not sure I really understand why anyone is a rush to make this
> change.  What harm is being done by the status quo?  What benefit do
> we get out of changing the default?  The major argument that has been
> offered so far is that "if we don't change it now, we never will", but
> I don't believe that the tenor of this discussion supports the        
> contention that Tom or anyone else never wants to make this change.   

It's hardly a rush (the GUC has been around and is being used in production), 
and the benefit is standards compatibility, something we strive for           
around here. I personally don't agree with the "now or never" argument,       
but I do agree with the "dot zero release is a good time for changes like this"
argument.

> It also seems to overlook the fact that we are STILL dealing with the
> fallout from this change in the core code; Tom gave examples upthread
> of changes that are being released for the first time *in 9.0* to
> address problems created by this transition.  And that is just the
> core code; we have to expect that third-party code will lag behind.

Which fallout are we still dealing with? Are you saying that the
developers are not up to the challenge of handling this before 9.0
is released? (If this were anything more than a simple boolean GUC
fix, I would be in your corner).

Yes, third-party code will lag behind, but, again, that's the nature of
the game. We didn't wait for every driver, app, and script to support
schemas before we added them in 7.4, for example. We certainly didn't
wait for applications to be implicit casting ready before 8.3, to (over?)use
another example.

- --
Greg Sabino Mullane greg@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201002031342
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAktpxEUACgkQvJuQZxSWSsibFwCeJzeQzUTBFwqHQ451Y23cbLfT
4UUAoK/2Sg/pxq5ipdB2B2ekfzQgW0cT
=5/gh
-----END PGP SIGNATURE-----




Re: PG 9.0 and standard_conforming_strings

From
Tom Lane
Date:
Alvaro Herrera <alvherre@commandprompt.com> writes:
> Tom Lane wrote:
>> The argument for doing this now hinges solely on a marketing-driven
>> choice of version name, and not on any actual evidence that applications
>> are ready for it.  We really need to do this at the start of a devel
>> and alpha test cycle, not at the end.

> Application writers probably didn't bother all that much with alphas
> though.  The bulk of them is going to start with the betas, which have
> not been delivered yet, so it seems a good time to try.

I still think that changing it now is going to open a can of worms that
we shouldn't be opening at this stage.  We have got more than enough to
worry about for 9.0 already.  I think it is absolute folly to believe
that this is only going to be a matter of "flip the default and nothing
else is going to pop up".
        regards, tom lane


Re: PG 9.0 and standard_conforming_strings

From
Rod Taylor
Date:
On Wed, Feb 3, 2010 at 13:20, Robert Haas <robertmhaas@gmail.com> wrote:
> On Wed, Feb 3, 2010 at 12:34 PM, Greg Sabino Mullane <greg@turnstep.com> wrote:
>> Perl (DBD::Pg anyway) has been compatible since May 2008.
>
> I would interpret that to mean that there is a significant possibility
> that a too-old DBD::Pg could get used with a new PostgreSQL, and
> therefore we shouldn't change anything for 9.0.  May 2008 is not that
> long ago, especially for people running systems like RHEL with
> five-year major release cycles.

I fall into this camp with a few machines still running standard RHEL
4 which I believe has DBD::Pg 1.32 installed. We do keep up to date
with PostgreSQL but the machines connecting to it include everything
from brand new web servers through to ancient machines in accounting
running reports.

As much as I would like GUCs to disappear I think this one should
proceed cautiously and probably be a 9.1 or even 9.2 item.


Re: PG 9.0 and standard_conforming_strings

From
Josh Berkus
Date:
> I still think that changing it now is going to open a can of worms that
> we shouldn't be opening at this stage.  We have got more than enough to
> worry about for 9.0 already.  I think it is absolute folly to believe
> that this is only going to be a matter of "flip the default and nothing
> else is going to pop up".

I'll support Tom on this.  I'm already worried about the timeline.

--Josh Berkus


Re: PG 9.0 and standard_conforming_strings

From
Robert Haas
Date:
On Wed, Feb 3, 2010 at 1:46 PM, Greg Sabino Mullane <greg@turnstep.com> wrote:
>>> Perl (DBD::Pg anyway) has been compatible since May 2008.
>
>> I would interpret that to mean that there is a significant possibility
>> that a too-old DBD::Pg could get used with a new PostgreSQL, and
>> therefore we shouldn't change anything for 9.0.  May 2008 is not that
>> long ago, especially for people running systems like RHEL with
>> five-year major release cycles.
>
> That's a silly conclusion. Applications and drivers are always going to
> lag behind. If someone is having a problem, they either upgrade their
> DBD::Pg or flip the GUC. Are you really saying we should wait
> until 2008 +5 years (2013!) before making this change? Wouldn't we have
> to wait five years past the point when *all* drivers are compatible
> by your definition?

I don't think it's a silly conclusion at all, though it's possible
that I am a silly person.  The longer we wait before making an
incompatible change, the more people will have adjusted their code to
the new reality (or upgraded their drivers, etc.) and the fewer things
will break.  Taking this argument to its illogical extreme, we should
never change anything at all, but I'm not proposing that.  What I am
saying is that I got all of the standard_conforming_strings problems
in my own code (that I know about) fixed about a year ago, and it does
not seem implausible to think that there could be people in the world
who take longer to upgrade than I do.  In fact, it seems
overwhelmingly likely.

Kevin Grittner made a good point upthread: the harm in NOT changing
standard_conforming_strings is that we will endure for a longer period
of time with strings that, well, don't conform to the standard, which
may cause problems for people trying to migrate to PostgreSQL from
other database systems.  Conversely, the harm in changing it is that
it may break existing PostgreSQL applications that run just fine on
older releases.  The second problem is something that we can expect to
gradually decrease over time because of (1) the incredibly annoying
escape_string_warning behavior and (2) software version upgrades.
Exactly when the risk is low enough to make the change is a judgement
call.

>> It also seems to overlook the fact that we are STILL dealing with the
>> fallout from this change in the core code; Tom gave examples upthread
>> of changes that are being released for the first time *in 9.0* to
>> address problems created by this transition.  And that is just the
>> core code; we have to expect that third-party code will lag behind.
>
> Which fallout are we still dealing with? Are you saying that the
> developers are not up to the challenge of handling this before 9.0
> is released? (If this were anything more than a simple boolean GUC
> fix, I would be in your corner).

http://archives.postgresql.org/pgsql-hackers/2010-01/msg02992.php

> Yes, third-party code will lag behind, but, again, that's the nature of
> the game. We didn't wait for every driver, app, and script to support
> schemas before we added them in 7.4, for example. We certainly didn't
> wait for applications to be implicit casting ready before 8.3, to (over?)use
> another example.

Implicit casting was in some ways less of a big deal than this change,
at least for me.  It broke some things, but they all BROKE, and then I
fixed them.  When this standard_conforming_strings thing hit, all of
my scripts that run out of cron started pouring out warning messages
which I initially could not figure out how to get rid of.  IIRC,
whatever version of Fedora I was running at the time had a version of
PostgreSQL that generated these stupid warnings, and a version of
DBD::Pg that hadn't yet been updated.  It was thoroughly miserable.
Yeah, I probably could have gotten around it by writing my own custom
escaping function or downloading DBD::Pg off of CPAN and compiling it,
but at the time I didn't even understand whether that would actually
fix the problem.

Plus, I'm not sure anyone here would be willing to advocate the way
that the implicit casting stuff went down as a model for future
changes of similar type.

...Robert


Re: PG 9.0 and standard_conforming_strings

From
Tom Lane
Date:
"Greg Sabino Mullane" <greg@turnstep.com> writes:
> Which fallout are we still dealing with? Are you saying that the
> developers are not up to the challenge of handling this before 9.0
> is released? (If this were anything more than a simple boolean GUC
> fix, I would be in your corner).

I'm not certain that Robert is saying that, but *I* am.  We have enough
to do for 9.0; adding another work item of uncertain magnitude is not
the thing to be doing right now.  The notion that it's "a simple boolean
GUC fix" and won't cause any followup work is unjustifiably optimistic.

And that's just for the core code.  I don't want to blindside driver
writers and other third-party authors with a change like this made at
the end of the cycle.  If we do it at the beginning of the 9.1 devel
cycle, no one will have room to argue that they didn't have adequate
notice ... but they sure will be able to make that complaint if we
do it now.
        regards, tom lane


Re: PG 9.0 and standard_conforming_strings

From
Mark Mielke
Date:
On 02/03/2010 01:20 PM, Robert Haas wrote:
> I am not sure I really understand why anyone is a rush to make this
> change.  What harm is being done by the status quo?  What benefit do
> we get out of changing the default?  The major argument that has been
> offered so far is that "if we don't change it now, we never will", but
> I don't believe that the tenor of this discussion supports the
> contention that Tom or anyone else never wants to make this change.
>    

For myself, it isn't so much a rush as a sense that the code out there 
that will break, will never change unless forced, and any time seems 
better than never.

Correct me if I am wrong - but I think this issue represents an 
exploitable SQL injection security hole. I switched because I convinced 
myself that the ambiguity of \' represented actual danger. I'm concerned 
that if the web front end doing parameter checking and passing in code 
using either '' quoting or \' quoting can be exploited if the server 
happens to be configured the opposite way. To me, this ambiguity can 
only be addressed by everybody agreeing on the right way to do it, and 
'' quoting seems like the right way to do it to me.

Cheers,
mark

-- 
Mark Mielke<mark@mielke.cc>



Re: PG 9.0 and standard_conforming_strings

From
Mark Mielke
Date:
On 02/03/2010 02:15 PM, Robert Haas wrote:
> The longer we wait before making an
> incompatible change, the more people will have adjusted their code to
> the new reality (or upgraded their drivers, etc.) and the fewer things
> will break.
>    

In my experience, the opposite is true, although in this case, the 
damage may already be done.

That is, the longer bad habits are allowed to form, the harder they are 
to break, and the more code is written that may be broken. People won't 
"upgrade" unless forced. At some point, the switch does have to be tripped.

Is now the time? I have no comment. I just don't want to see "never" be 
the time, and if "never" is not the time, than "now" does not seem 
impratical. That said, if you say we'll tell people to prepare for a 
change in 9.0, and enforce the change in a later release, that is fine too.

Cheers,
mark

-- 
Mark Mielke<mark@mielke.cc>



Re: PG 9.0 and standard_conforming_strings

From
Alvaro Herrera
Date:
Rod Taylor escribió:

> As much as I would like GUCs to disappear I think this one should
> proceed cautiously and probably be a 9.1 or even 9.2 item.

Note that this is *not* about removing the configuration setting, only
about flipping its default value.  There has been *no* talk of removing
the setting.

If you have old clients around, simply change the value from the default
to off.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.


Re: PG 9.0 and standard_conforming_strings

From
Robert Haas
Date:
On Wed, Feb 3, 2010 at 2:25 PM, Mark Mielke <mark@mark.mielke.cc> wrote:
> On 02/03/2010 01:20 PM, Robert Haas wrote:
>> I am not sure I really understand why anyone is a rush to make this
>> change.  What harm is being done by the status quo?  What benefit do
>> we get out of changing the default?  The major argument that has been
>> offered so far is that "if we don't change it now, we never will", but
>> I don't believe that the tenor of this discussion supports the
>> contention that Tom or anyone else never wants to make this change.
>
> For myself, it isn't so much a rush as a sense that the code out there that
> will break, will never change unless forced, and any time seems better than
> never.
>
> Correct me if I am wrong - but I think this issue represents an exploitable
> SQL injection security hole. I switched because I convinced myself that the
> ambiguity of \' represented actual danger. I'm concerned that if the web
> front end doing parameter checking and passing in code using either ''
> quoting or \' quoting can be exploited if the server happens to be
> configured the opposite way. To me, this ambiguity can only be addressed by
> everybody agreeing on the right way to do it, and '' quoting seems like the
> right way to do it to me.

OK, you're wrong.  :-)

Yeah, there's a problem if the client and server are configured in
opposite ways, but flipping the default setting of
standard_conforming_strings is not going to make that problem go away.If anything it's going to make it worse.

...Robert


Re: PG 9.0 and standard_conforming_strings

From
Tom Lane
Date:
Mark Mielke <mark@mark.mielke.cc> writes:
> On 02/03/2010 01:20 PM, Robert Haas wrote:
>> I am not sure I really understand why anyone is a rush to make this
>> change.

> For myself, it isn't so much a rush as a sense that the code out there 
> that will break, will never change unless forced, and any time seems 
> better than never.

I have not heard anyone arguing for the position that we should never do
it.  The argument is about whether it's a good idea to do it *right
now*, without any advance notice or planning.

> Correct me if I am wrong - but I think this issue represents an 
> exploitable SQL injection security hole.

Indeed it is, which is one of the reasons to be cautious with changing
it.  We've been telling people to move away from \' for a long time,
but actually flipping the switch that will make their apps insecure
is not something to do on the spur of the moment.
        regards, tom lane


Re: PG 9.0 and standard_conforming_strings

From
Nathan Wagner
Date:
On Wed, 03 Feb 2010 14:41:13 -0500, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Indeed it is, which is one of the reasons to be cautious with changing
> it.  We've been telling people to move away from \' for a long time,
> but actually flipping the switch that will make their apps insecure
> is not something to do on the spur of the moment.

AFAICT the switch was added in 8.2, and mentioned in the release notes
dated 2006-12-05.  The documentation for 8.2 says "The default is
currently
off, causing PostgreSQL to have its historical behavior of treating
backslashes
as escape characters. The default will change to on in a future release
to improve compatibility with the standard."

So people have had three years of warning, which I would hardly
characterize
as "spur of the moment".  If you want the old behavior, change the setting
to off.

I think that a major release point is exactly the right time to do this,
doing it at a minor release number is much less reasonable.

A question for those opposed to doing it now: how exactly do you propose
to
warn people that is different than the notice that it will be changed in
a future release that has been around for the last three years?

--
nw


Re: PG 9.0 and standard_conforming_strings

From
Dimitri Fontaine
Date:
Tom Lane <tgl@sss.pgh.pa.us> writes:
> And that's just for the core code.  I don't want to blindside driver
> writers and other third-party authors with a change like this made at
> the end of the cycle.  If we do it at the beginning of the 9.1 devel
> cycle, no one will have room to argue that they didn't have adequate
> notice ... but they sure will be able to make that complaint if we
> do it now.

Well, in fact my impression is that the time third-party authors are
going to begin to look at things is not alpha1 but beta1. Because until
beta comes out you don't know what's in there. Some commits could get
reverted before entering beta is what is being said, the goal being to
be able to reach code stability in non-infinite time...

In my mind the fact that beta is meant to be about 2 to 3 months old is
for those problems to get solved before release. In short, I am the
blind driver who's not seeing what problem you're talking about.

Regards,
-- 
dim


Re: PG 9.0 and standard_conforming_strings

From
Dimitri Fontaine
Date:
Josh Berkus <josh@agliodbs.com> writes:
>> I still think that changing it now is going to open a can of worms that
>> we shouldn't be opening at this stage.  We have got more than enough to
>> worry about for 9.0 already.  I think it is absolute folly to believe
>> that this is only going to be a matter of "flip the default and nothing
>> else is going to pop up".
>
> I'll support Tom on this.  I'm already worried about the timeline.

Vote time? +1 for Tom's idea that plates are already full.

At the same time, escape_string_warning = on is the default and I guess
everybody find it annoying enough to use the E'foo\tbar' syntax, which
will work all the same once standard_conforming_strings is on. By
design.

And the fact than switching the GUC to off again is so easy makes me
wonder about how high the risk is for third party code. It has been said
earlier in this thread that one of the risky clients is pg_dump. That's
what balances it for me.

Do anyone know how much downloads or testing the alphas have seen?
-- 
dim


Re: PG 9.0 and standard_conforming_strings

From
marcin mank
Date:
A certain prominent web framework has a nasty SQL injection bug when
PG is configured with SCS. This bug is not present without SCS
(details per email for interested PG hackers). I say, hold it off.

Greetings
Marcin Mańk


Re: PG 9.0 and standard_conforming_strings

From
Andrew Dunstan
Date:

marcin mank wrote:
> A certain prominent web framework has a nasty SQL injection bug when
> PG is configured with SCS. This bug is not present without SCS
> (details per email for interested PG hackers). I say, hold it off.
>
>
>   

Any web framework that interpolates user supplied values into SQL rather 
than using placeholders is broken from the get go, IMNSHO. I'm not 
saying that there aren't reasons to hold up moving to SCS, but this 
isn't one of them.

cheers

andrew


Re: PG 9.0 and standard_conforming_strings

From
Robert Haas
Date:
On Wed, Feb 3, 2010 at 5:57 PM, Andrew Dunstan <andrew@dunslane.net> wrote:
> marcin mank wrote:
>> A certain prominent web framework has a nasty SQL injection bug when
>> PG is configured with SCS. This bug is not present without SCS
>> (details per email for interested PG hackers). I say, hold it off.
>
> Any web framework that interpolates user supplied values into SQL rather
> than using placeholders is broken from the get go, IMNSHO. I'm not saying
> that there aren't reasons to hold up moving to SCS, but this isn't one of
> them.

That seems more than slightly harsh.  I've certainly come across
situations where interpolating values (with proper quoting of course)
made more sense than using placeholders.  YMMV, of course.

...Robert


Re: PG 9.0 and standard_conforming_strings

From
"David E. Wheeler"
Date:
On Feb 3, 2010, at 6:16 PM, Robert Haas wrote:

>> Any web framework that interpolates user supplied values into SQL rather
>> than using placeholders is broken from the get go, IMNSHO. I'm not saying
>> that there aren't reasons to hold up moving to SCS, but this isn't one of
>> them.
>
> That seems more than slightly harsh.  I've certainly come across
> situations where interpolating values (with proper quoting of course)
> made more sense than using placeholders.  YMMV, of course.

Not if it leads to Little Bobby Tables's door when, you know, you use SQL conformant strings! Sounds like an app that
needsits quoting function fixed. 

Best,

David



Re: PG 9.0 and standard_conforming_strings

From
Andrew Dunstan
Date:

Robert Haas wrote:
> On Wed, Feb 3, 2010 at 5:57 PM, Andrew Dunstan <andrew@dunslane.net> wrote:
>   
>> marcin mank wrote:
>>     
>>> A certain prominent web framework has a nasty SQL injection bug when
>>> PG is configured with SCS. This bug is not present without SCS
>>> (details per email for interested PG hackers). I say, hold it off.
>>>       
>> Any web framework that interpolates user supplied values into SQL rather
>> than using placeholders is broken from the get go, IMNSHO. I'm not saying
>> that there aren't reasons to hold up moving to SCS, but this isn't one of
>> them.
>>     
>
> That seems more than slightly harsh.  I've certainly come across
> situations where interpolating values (with proper quoting of course)
> made more sense than using placeholders.  YMMV, of course.
>
>
>   

How many injection attacks should we witness before deciding that the 
best defence is to get out of the quoting/escaping game? Personally I 
have reached that threshold.

Remember that this is a web *framework*, something that would ideally be 
using best practice and heightened security awareness. There could be 
cases where some applications with well known structures and queries 
interpolate carefully sanitised values into SQL, but I very much doubt 
that web app frameworks should be indulging in such practices. They 
should go the extra mile, IMNSHO.

Anyway, I think this conversation is going slightly astray.

cheers

andrew