trouble caused by change in 7.3 handling of '' in integer context

That explains why my RT started acting wierd (it's in test).

This also breaks PHPGroupware as well. (I've reported it to them).

Is there any way to get this to be a postgresql.conf option for transition?

LER

--On Wednesday, December 18, 2002 16:06:25 -0500 Vivek Khera
<khera@kcilink.com> wrote:

I see in the 7.3 HISTORY file this:

* An empty string ('') is no longer allowed as the input into an
integer field. Formerly, it was silently interpreted as 0.

This is causing major issues with the Request Tracker program
<http://www.bestpractical.com/rt&gt;.

The author has gone so far as to notify all current users that
Postgres 7.3 is not usable with RT, and to stick to 7.2, or to use
MySQL.

Apparently, there is a lot of work that will be necessary to retrofit
RT to this change. Is there any way possible to make this a logged
warning rather than a fatal so that there is time to transition the RT
code? I didn't see any notice in any prior release of the upcoming
change.

This unfortunately locks me into the 7.2 line until (if) RT can be
fixed up, and I could really use the 7.3 improvements for my other
applications.

Thanks.

--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D. Khera Communications, Inc.
Internet: khera@kciLink.com Rockville, MD +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

On 18 Dec 2002, Vivek Khera wrote:

I see in the 7.3 HISTORY file this:

* An empty string ('') is no longer allowed as the input into an
integer field. Formerly, it was silently interpreted as 0.

This is causing major issues with the Request Tracker program
<http://www.bestpractical.com/rt&gt;.

The author has gone so far as to notify all current users that
Postgres 7.3 is not usable with RT, and to stick to 7.2, or to use
MySQL.

This is backwards. RT isn't usable with Postgresql 7.3. There's a SQL
standard (several actually, but 92/99 are mostly it) . If RT had been
programmed to them, it wouldn't even notice this change. I know that
sounds cold hearted, but it isn't Postgresql's job to work with
noncompliant SQL.

Apparently, there is a lot of work that will be necessary to retrofit
RT to this change. Is there any way possible to make this a logged
warning rather than a fatal so that there is time to transition the RT
code? I didn't see any notice in any prior release of the upcoming
change.

the problem is that it is not allowed by spec, and allowing it is against
spec, so it is likely that if it was implemented, it would have to be
similar to the transform_null_equals switch which is off by default.

This unfortunately locks me into the 7.2 line until (if) RT can be
fixed up, and I could really use the 7.3 improvements for my other
applications.

Please be clear here. The problem is with RT, not Postgresql. I know
that doesn't comfort you personally, but it's an important point. If RT
wants to be portable, it's the kind of thing they need to fix, as well as
probably many more things in their SQL code.

Maybe now would be a good time for a code review of their SQL to see what
other possible non-sql spec problems are lurking there.

I'm downloading it now, and I'll take a look at the SQL code in it to see
what can be done.

Oh, that's bad. We tightened up the handling of '' as 0 because we
considered it too error-prone to assume they mean zero when they pass
''.

While we do like the new tightness, we don't want to make things harder
for people porting the code.

How about if I give you a patch against 7.3 that allows '' as 0, and you
ask if the author can distribute it and get his code changed for 7.4?

---------------------------------------------------------------------------

Vivek Khera wrote:

I see in the 7.3 HISTORY file this:

* An empty string ('') is no longer allowed as the input into an
integer field. Formerly, it was silently interpreted as 0.

This is causing major issues with the Request Tracker program
<http://www.bestpractical.com/rt&gt;.

The author has gone so far as to notify all current users that
Postgres 7.3 is not usable with RT, and to stick to 7.2, or to use
MySQL.

Apparently, there is a lot of work that will be necessary to retrofit
RT to this change. Is there any way possible to make this a logged
warning rather than a fatal so that there is time to transition the RT
code? I didn't see any notice in any prior release of the upcoming
change.

This unfortunately locks me into the 7.2 line until (if) RT can be
fixed up, and I could really use the 7.3 improvements for my other
applications.

Thanks.

--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D. Khera Communications, Inc.
Internet: khera@kciLink.com Rockville, MD +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Larry Rosenman wrote:

That explains why my RT started acting wierd (it's in test).

This also breaks PHPGroupware as well. (I've reported it to them).

Is there any way to get this to be a postgresql.conf option for transition?

Oh, wow, we have two now. We did discuss the problems of backward
compatibility, but didn't think it would effect many people.

Folks, what do you want to do?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

On Wed, 18 Dec 2002, Bruce Momjian wrote:

Larry Rosenman wrote:

That explains why my RT started acting wierd (it's in test).

This also breaks PHPGroupware as well. (I've reported it to them).

Is there any way to get this to be a postgresql.conf option for transition?

Oh, wow, we have two now. We did discuss the problems of backward
compatibility, but didn't think it would effect many people.

Folks, what do you want to do?

Make it a GUC setting like transform_null_equals that is defaulted to
false and the dba has to turn on to enable it. Best of both worlds.

That would be nice, and per user/db if possible?

--On Wednesday, December 18, 2002 15:51:39 -0700 "scott.marlowe"
<scott.marlowe@ihs.com> wrote:

On Wed, 18 Dec 2002, Bruce Momjian wrote:

Larry Rosenman wrote:

That explains why my RT started acting wierd (it's in test).

This also breaks PHPGroupware as well. (I've reported it to them).

Is there any way to get this to be a postgresql.conf option for
transition?

Oh, wow, we have two now. We did discuss the problems of backward
compatibility, but didn't think it would effect many people.

Folks, what do you want to do?

Make it a GUC setting like transform_null_equals that is defaulted to
false and the dba has to turn on to enable it. Best of both worlds.

--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

These incompatibilities have been caused by better compliance with standards, right? If
this is the case then I don't think you want to go backwards. Indeed - as a software developer, I
always test my code on pending betas just to see what's going to change before a major release
gets to the general public so my app will already be compliant. To be competitive with the major
RDBMS' this kind of progress needs to be made.

If the postgres developers want to put app developers on notice perhaps they can adopt
a deprecation policy where whenever a feature is going to be removed or changed in a manner
that breaks existing code the following steps should be taken:

1. next release announce it as deprecated and produce a warning message whenever
it's used. if it's major, possible provide a compile option --with<newfun/behaviour> so users can test
it.
2. the following release remove or make the change. if it's major, possibly provide a
compile option --with-<oldfunc/behaviour> so users can temporarily keep the old behaviour.
3. the following release - its done/gone/never to return again

Any app developer/user who can't follow this stable plan isn't interested in a better
postgres anyway.

best regards,

Ben Scherrey

PS: FWIW - it was a bit annoying for "where x = null" to break silently for my client's code. A
deprecation warning/error would have made their app debugging very quick rather than wondering
why their database broke (when it didn't).

12/18/2002 5:28:37 PM, Bruce Momjian <pgman@candle.pha.pa.us> wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Folks, what do you want to do?

Nothing. We are certainly not going to add such a switch to 7.3 at this
point (it's been feature-frozen for months, remember?) and by the time
7.4 is out it'll be moot, because the non-spec-compliant applications
will have been fixed.

I agree with Scott Marlowe's take on this: the correct solution is to
fix the applications.

regards, tom lane

Bruce Momjian writes:

Folks, what do you want to do?

Make them fix their code.

It's one thing to have warts for allowing the market-leading GUI frontend
for databases (MS Access) to work, but if every, with all due respect,
random client wants to have its own break-the-spec-for-me switch we'll go
mad.

Explain to me why it's so hard to say 0 when you mean 0.

--
Peter Eisentraut peter_e@gmx.net

Quoting Peter Eisentraut <peter_e@gmx.net>:

Bruce Momjian writes:

Folks, what do you want to do?

Make them fix their code.

It's one thing to have warts for allowing the market-leading GUI frontend
for databases (MS Access) to work, but if every, with all due respect,
random client wants to have its own break-the-spec-for-me switch we'll go
mad.

Explain to me why it's so hard to say 0 when you mean 0.

because there is legacy code out there, that was ported from MySQL, and
now it used to work with PostgreSQL and it doesn't now.

RT is a MAJOR app.....

the PHPGroupware stuff could also be major, depending on what
people are using.

I guess Vivek and I are just going to have to do without these 2 apps until/
unless they can be rev'd.

I really wish this had been better thought out. I know I crabbed it during
BETA, but was the first one.

now it's too late.

This stinks from a Marketing perspective.

LER

--
Peter Eisentraut peter_e@gmx.net

--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

Vivek,

I'll not echo what others have said so far, and indeed what i've said
myself in a previous thread about this.

An important fact to take into account is that PostgreSQL 7.3 was
available as an open beta for quite a period - This period is both for
maturing changes made during the development and to allow client
authors to test against the new version.

Shouldn't at least ONE person who uses 'Request Tracker' have
perticipated in this beta program? PostgreSQL is community project
after all, you need to be proactive.

Indeed if someone involved with 'Request Tracker' read/skimmed the
hackers list then they could have discussed the implications before
the change was made. The PostgreSQL developers are great (thanks
guys!) but they are not all-seeing and all-knowing, they cannot
foresee all possible implications of a change.

Thanks, Lee.

Vivek Khera writes:

Indeed if someone involved with 'Request Tracker' read/skimmed the
hackers list then they could have discussed the implications before
the change was made. The PostgreSQL developers are great (thanks
guys!) but they are not all-seeing and all-knowing, they cannot
foresee all possible implications of a change.

I agree. Because I have been monitoring the list for some time, I knew of
the change and I wasn't surprised (though I was unhappy) when I received the
dreaded error message and my app exited. Self-inflicted bugs never make me
happy. :)

Shane

"sm" == scott marlowe <scott.marlowe> writes:

sm> Please be clear here. The problem is with RT, not Postgresql. I know
sm> that doesn't comfort you personally, but it's an important point. If RT
sm> wants to be portable, it's the kind of thing they need to fix, as well as
sm> probably many more things in their SQL code.

Yes, RT is at fault, but just making the change without a transition
period is harmful. All I ask is to make it easier to transition
broken apps.

"BM" == Bruce Momjian <pgman@candle.pha.pa.us> writes:

BM> How about if I give you a patch against 7.3 that allows '' as 0, and you
BM> ask if the author can distribute it and get his code changed for 7.4?

That would be great. Actually what would be best is if the code could
log a warning (with the full query) every time it happened, then it
would be easy to run the app for a while and find all the places it
happens. RT dynamically creates its queries so this would be the
easiest way to fix it up. Then RT could be fixed up and not need any
patches to PG.

On Thu, 2002-12-19 at 09:54, Vivek Khera wrote:

"sm" == scott marlowe <scott.marlowe> writes:

sm> Please be clear here. The problem is with RT, not Postgresql. I know
sm> that doesn't comfort you personally, but it's an important point. If RT
sm> wants to be portable, it's the kind of thing they need to fix, as well as
sm> probably many more things in their SQL code.

Yes, RT is at fault, but just making the change without a transition
period is harmful. All I ask is to make it easier to transition
broken apps.

The problem is that adding all of these special flags for different apps
is unmanageable. Even if the flag was there, don't you suspect when the
time came for 7.4 to be released we'd see another volley of emails
asking for the exception to stick around for "just one more release"?
The bottom line is that at some point your code is going to have to be
modified, a transition period really just gives an excuse for
procrastination. Bruce has offered a patch against 7.3 for those who
cant wait to upgrade; I think that should be a sufficient remedy.

Robert Treat

On Thu, 2002-12-19 at 10:03, Vivek Khera wrote:

"BM" == Bruce Momjian <pgman@candle.pha.pa.us> writes:

BM> How about if I give you a patch against 7.3 that allows '' as 0, and you
BM> ask if the author can distribute it and get his code changed for 7.4?

That would be great. Actually what would be best is if the code could
log a warning (with the full query) every time it happened, then it
would be easy to run the app for a while and find all the places it
happens. RT dynamically creates its queries so this would be the
easiest way to fix it up. Then RT could be fixed up and not need any
patches to PG.

IIRC 7.3 has this functionality, i think the parameter is
log_min_error_statement in the postgresql.conf

Robert Treat

"PE" == Peter Eisentraut <peter_e@gmx.net> writes:

PE> It's one thing to have warts for allowing the market-leading GUI frontend
PE> for databases (MS Access) to work, but if every, with all due respect,
PE> random client wants to have its own break-the-spec-for-me switch we'll go
PE> mad.

PE> Explain to me why it's so hard to say 0 when you mean 0.

It is not hard. What is hard is when you change behavior abruptly
between releases without a transition period in which you warn about
an upcoming change when that deprecated feature is used. Ideally, it
would have logged this error for the 7.3 release, and disallowed it
for 7.4, something akin to the LIMIT transition.

I guess nobody realized how some popular applications used this
non-compliant feature before the change was implemented. Perhaps we
learn from this experience and don't do this type of change again.
Perhaps not.

--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D. Khera Communications, Inc.
Internet: khera@kciLink.com Rockville, MD +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/

Vivek Khera <khera@kcilink.com> writes:

It is not hard. What is hard is when you change behavior abruptly
between releases without a transition period in which you warn about
an upcoming change when that deprecated feature is used. Ideally, it
would have logged this error for the 7.3 release, and disallowed it
for 7.4, something akin to the LIMIT transition.

I guess nobody realized how some popular applications used this
non-compliant feature before the change was implemented. Perhaps we
learn from this experience and don't do this type of change again.
Perhaps not.

Yeah, I can personally see a case for deprecating nonstandard features
and spitting out a warning, rather than dropping them abruptly. The
deprecation would last for one major release cycle.

On the other hand, if the nonstandard wart/feature is standing in the
way of implementing a high-quality and desired new feature (not
saying this did or didn't happen in this case; I don't know), is it
worth putting off adding the new feature rather than just excising the
wart and putting up with the pain?

-Doug

Vivek Khera wrote:

"PE" == Peter Eisentraut <peter_e@gmx.net> writes:

PE> It's one thing to have warts for allowing the market-leading GUI frontend
PE> for databases (MS Access) to work, but if every, with all due respect,
PE> random client wants to have its own break-the-spec-for-me switch we'll go
PE> mad.

PE> Explain to me why it's so hard to say 0 when you mean 0.

It is not hard. What is hard is when you change behavior abruptly
between releases without a transition period in which you warn about
an upcoming change when that deprecated feature is used. Ideally, it
would have logged this error for the 7.3 release, and disallowed it
for 7.4, something akin to the LIMIT transition.

I guess nobody realized how some popular applications used this
non-compliant feature before the change was implemented. Perhaps we
learn from this experience and don't do this type of change again.
Perhaps not.

Yes, this was really it --- we didn't realize how many apps used this.
Also, we expected to hit them before final release, when we could have
reverted the change.

The prompting of the change was that '' -> 0 didn't make sense, and
it was masking some COPY file format errors.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073