Thread: Postgres 8.3 broke everything
Upgrading to Postgres 8.3 broke virtually every site we host, and I finally figured out why. In 8.2 you could do this: create table foo (
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Thu, 21 Feb 2008 17:53:35 -0500 "Alex Turner" <armtuk@gmail.com> wrote: > Upgrading to Postgres 8.3 broke virtually every site we host, and I > finally figured out why. In 8.2 you could do this: > > create table foo ( No that would fail. Did you forget the rest of your message? :) Joshua D. Drake - -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL SPI Liaison | SPI Director | PostgreSQL political pundit -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHvgQUATb/zqfZUUQRAsULAJ9KE29XS7jx2M88b62/wSM252jvWACdERA4 RBy/uiLQHgsd9/t56C0wUz8= =Ad7K -----END PGP SIGNATURE-----
Yeah - I pressed tab to indent my code, and of course it tabbed to the
next element on the page, which was the send button, then I hit a key,
and it sent the message before I was ready. I tested my hypothesis,
but it was wrong. I haven't quite figured it out yet. Something to
do with casting a char to an int I think, but I can't reproduce it
yet. I think it might only be in prepared statements, all I know is
that somewhere the behaviour changed, but I don't know yet that it's
definitely Postgresql that changed, but it all started when I
upgraded. It's my own stupid fault for the way I'm using JSTL, it's
what I get for doing ${param.keyId} in a <sql:param> hoping that it
would just cast it like it does in a bean. Sometimes it worked, and
other times it didn't, except that mostly it did.
Alex
On Thu, Feb 21, 2008 at 6:07 PM, Joshua D. Drake <jd@commandprompt.com> wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
>
>
> On Thu, 21 Feb 2008 17:53:35 -0500
> "Alex Turner" <armtuk@gmail.com> wrote:
>
> > Upgrading to Postgres 8.3 broke virtually every site we host, and I
> > finally figured out why. In 8.2 you could do this:
> >
> > create table foo (
>
> No that would fail.
>
> Did you forget the rest of your message? :)
>
> Joshua D. Drake
>
>
> - --
> The PostgreSQL Company since 1997: http://www.commandprompt.com/
> PostgreSQL Community Conference: http://www.postgresqlconference.org/
> Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
> PostgreSQL SPI Liaison | SPI Director | PostgreSQL political pundit
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.6 (GNU/Linux)
>
> iD8DBQFHvgQUATb/zqfZUUQRAsULAJ9KE29XS7jx2M88b62/wSM252jvWACdERA4
> RBy/uiLQHgsd9/t56C0wUz8=
> =Ad7K
> -----END PGP SIGNATURE-----
>
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Thu, 21 Feb 2008 19:49:29 -0500 "Alex Turner" <armtuk@gmail.com> wrote: > Yeah - I pressed tab to indent my code, and of course it tabbed to the > next element on the page, which was the send button, then I hit a key, > and it sent the message before I was ready. I tested my hypothesis, > but it was wrong. I haven't quite figured it out yet. Something to > do with casting a char to an int I think, but I can't reproduce it Based on this I bet you are being nailed by: # Non-character data types are no longer automatically cast to TEXT (Peter, Tom) Previously, if a non-character value was supplied to an operator or function that requires text input, it was automatically cast to text, for most (though not all) built-in data types. This no longer happens: an explicit cast to text is now required for all non-character-string types. For example, these expressions formerly worked: substr(current_date, 1, 4) 23 LIKE '2%' but will now draw "function does not exist" and "operator does not exist" errors respectively. Use an explicit cast instead: substr(current_date::text, 1, 4) 23::text LIKE '2%' (Of course, you can use the more verbose CAST() syntax too.) The reason for the change is that these automatic casts too often caused surprising behavior. An example is that in previous releases, this expression was accepted but did not do what was expected: current_date < 2017-11-17 This is actually comparing a date to an integer, which should be (and now is) rejected — but in the presence of automatic casts both sides were cast to text and a textual comparison was done, because the text < text operator was able to match the expression when no other < operator could. Types char(n) and varchar(n) still cast to text automatically. Also, automatic casting to text still works for inputs to the concatenation (||) operator, so long as least one input is a character-string type. - -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL SPI Liaison | SPI Director | PostgreSQL political pundit -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHviIeATb/zqfZUUQRAlyKAJ46gSgeP5dKS+CXba/lBhBL5dev1QCdEfGF mZtO5L9dK2nAZZU4Cp6K+sA= =HTW2 -----END PGP SIGNATURE-----
Yeah - I reckon that would do it ;) And it did break just about every website on our systems! Though the change is understandable Alex On Thu, Feb 21, 2008 at 8:15 PM, Joshua D. Drake <jd@commandprompt.com> wrote: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > On Thu, 21 Feb 2008 19:49:29 -0500 > > "Alex Turner" <armtuk@gmail.com> wrote: > > > > Yeah - I pressed tab to indent my code, and of course it tabbed to the > > next element on the page, which was the send button, then I hit a key, > > and it sent the message before I was ready. I tested my hypothesis, > > but it was wrong. I haven't quite figured it out yet. Something to > > do with casting a char to an int I think, but I can't reproduce it > > Based on this I bet you are being nailed by: > > > # > > Non-character data types are no longer automatically cast to TEXT > (Peter, Tom) > > Previously, if a non-character value was supplied to an operator or > function that requires text input, it was automatically cast to text, > for most (though not all) built-in data types. This no longer happens: > an explicit cast to text is now required for all non-character-string > types. For example, these expressions formerly worked: > > substr(current_date, 1, 4) > 23 LIKE '2%' > > but will now draw "function does not exist" and "operator does not > exist" errors respectively. Use an explicit cast instead: > > substr(current_date::text, 1, 4) > 23::text LIKE '2%' > > (Of course, you can use the more verbose CAST() syntax too.) The reason > for the change is that these automatic casts too often caused > surprising behavior. An example is that in previous releases, this > expression was accepted but did not do what was expected: > > current_date < 2017-11-17 > > This is actually comparing a date to an integer, which should be (and > now is) rejected — but in the presence of automatic casts both sides > were cast to text and a textual comparison was done, because the text < > text operator was able to match the expression when no other < operator > could. > > Types char(n) and varchar(n) still cast to text automatically. Also, > automatic casting to text still works for inputs to the concatenation > (||) operator, so long as least one input is a character-string type. > > > > - -- > > The PostgreSQL Company since 1997: http://www.commandprompt.com/ > PostgreSQL Community Conference: http://www.postgresqlconference.org/ > Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate > PostgreSQL SPI Liaison | SPI Director | PostgreSQL political pundit > > -----BEGIN PGP SIGNATURE----- > Version: GnuPG v1.4.6 (GNU/Linux) > > iD8DBQFHviIeATb/zqfZUUQRAlyKAJ46gSgeP5dKS+CXba/lBhBL5dev1QCdEfGF > mZtO5L9dK2nAZZU4Cp6K+sA= > =HTW2 > -----END PGP SIGNATURE----- >