Thread: Client-requested cast mode to emulate Pg8.2 on v8.3
Hi all - I've spotted the cast-related "regressions" being discussed here http://archives.postgresql.org/pgsql-general/2007-11/msg00505.php ... as a Moodle developer supporting Pg, the stricter cast rules in pg 8.3 are somewhat worrying. Is there a straightforward way to configure a given DB or a client connection to emulate Pg v8.2.x casting rules? I say they are worrying because Moodle code has many ocurrences of /* bla.id is an INT8 */ SELECT x,y,z FROM bla WHERE id='1'; And we also often quote INT values for inserts/updates, I am not sure if this is supported either. This is quite widespread in the codebase -- for histerical raisins that will be familiar to anyone involved in LAMP projects -- and unlikely to change quickly. For the record, I generally agree that the stricter rules are good... as long as there's fallback to the old lazy-fuzzy-ambiguous mode to help large projects make the transition ;-) cheers, martin PS: I'm not actually in the list - CCs welcome...
Martin Langhoff <martin@catalyst.net.nz> writes:
> I say they are worrying because Moodle code has many ocurrences of
> /* bla.id is an INT8 */
> SELECT x,y,z FROM bla WHERE id='1';
> And we also often quote INT values for inserts/updates, I am not sure if
> this is supported either.
This is not a problem. Read up on unknown-type literals --- that
behavior isn't changing. The cases that we are tightening up on
involve values that are of *known* non-string data types being used
in situations where logically only a string should appear.
regards, tom lane
Tom Lane wrote: > Martin Langhoff <martin@catalyst.net.nz> writes: >> I say they are worrying because Moodle code has many ocurrences of >> /* bla.id is an INT8 */ >> SELECT x,y,z FROM bla WHERE id='1'; >> And we also often quote INT values for inserts/updates, I am not sure if >> this is supported either. > > This is not a problem. Read up on unknown-type literals --- that > behavior isn't changing. Tom, thanks for the clarification - reading up on those now... > The cases that we are tightening up on > involve values that are of *known* non-string data types being used > in situations where logically only a string should appear. Hmmm. We'll have to test and see if we have any in Moodle. - Is there a way to turn it back to the old behaviour with a warning going to the logs? - Is there a way to get v8.2.x to warn on the dubious casts so we can tighten the application side while on v8.2? cheers, martin
On Wed, Nov 14, 2007 at 06:56:06PM +1300, Martin Langhoff wrote: > Hmmm. We'll have to test and see if we have any in Moodle. All that has happened is that the *implicit* casting is gone. They will now simply produce errors, the fix being to explicity cast it to the type you wanted, rather than the system guessing. The example you gave is not a problem, because unknown != text. It's only an issue if you're doing things like performing text operations (substr,like etc) on non-text things (like dates, numbers, etc). > - Is there a way to turn it back to the old behaviour with a > warning going to the logs? No. > - Is there a way to get v8.2.x to warn on the dubious casts > so we can tighten the application side while on v8.2? Seems to me the easiest way would be to try it out on an 8.3 installation and exercise each query once. There may be a better way but I don't know it... Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Those who make peaceful revolution impossible will make violent revolution inevitable. > -- John F Kennedy
Attachment
> > - Is there a way to turn it back to the old behaviour with a > > warning going to the logs? > > > No. > > > > - Is there a way to get v8.2.x to warn on the dubious casts > > so we can tighten the application side while on v8.2? > > > Seems to me the easiest way would be to try it out on an 8.3 > installation and exercise each query once. There may be a better way > but I don't know it... Hi, This seems like it is one of the most frustrating (for me) decisions that has ever been made by the postgres developers... My situation is the following : I inherited an application based on a dead project (byline, and don't even mention aplaws, it's about as alive a zombie from Resident Evil... it moves, but it ain't alive!) and we currently use postgres 8.1. The performance sucks, and there are several things in 8.3 that are very interesting, notably synchronous_commit, plus all the perfermance goodies since 8.1. But it is COMPLETELY out of the question to redo the db abstraction layer, and without these implicit casts that is what will be needed. Is there REALLY no way to reenable it? I fully realise and respect the logic in doing this but not having a fallback (even if it means recompiling from source) is painful! Am I really stuck with pre-8.3? Cheers Anton
"Anton Melser" <melser.anton@gmail.com> writes: > ... But it is COMPLETELY out of the > question to redo the db abstraction layer, and without these implicit > casts that is what will be needed. Is there REALLY no way to reenable > it? http://people.planetpostgresql.org/peter/index.php?/archives/18-Readding-implicit-casts-in-PostgreSQL-8.3.html regards, tom lane
On 21/03/2008, Tom Lane <tgl@sss.pgh.pa.us> wrote: > "Anton Melser" <melser.anton@gmail.com> writes: > > ... But it is COMPLETELY out of the > > > question to redo the db abstraction layer, and without these implicit > > casts that is what will be needed. Is there REALLY no way to reenable > > it? > > > http://people.planetpostgresql.org/peter/index.php?/archives/18-Readding-implicit-casts-in-PostgreSQL-8.3.html Tom the Champion strikes again! Cheers Anton
On Mar 21, 2008, at 5:58 PM, Anton Melser wrote: > Tom the Champion strikes again! > Cheers > Anton I have the suspicion that his mother is named Lois, his father is unknown and he has a sensitivity to Kryptonite. But that's just speculation of course... Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,47e3ecbe9784203213352!
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Fri, 21 Mar 2008 18:13:27 +0100 Alban Hertroys <dalroi@solfertje.student.utwente.nl> wrote: > On Mar 21, 2008, at 5:58 PM, Anton Melser wrote: > > > Tom the Champion strikes again! > > Cheers > > Anton > > I have the suspicion that his mother is named Lois, his father is > unknown and he has a sensitivity to Kryptonite. But that's just > speculation of course... > > Alban Hertroys Superman married Lois, I hope that isn't his Mom's name. Joshua D. Drake - -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ United States PostgreSQL Association: http://www.postgresql.us/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFH4+3kATb/zqfZUUQRAmLqAJwOEpP72iWgZ9ZaW2wKt2ozk9ayegCgky7j ChRNSQDwQHMHks3xHDa+cFs= =mRsX -----END PGP SIGNATURE-----
> > I have the suspicion that his mother is named Lois, his father is > > unknown and he has a sensitivity to Kryptonite. But that's just > > speculation of course... > > > > Alban Hertroys > > > Superman married Lois, I hope that isn't his Mom's name. I got that he was the *son* of Superman... and really, in which episode does he marry Lois (I admit I am not a devotee...)? I thought the whole point was the sexual tension between the two... Anyway, maybe I spoke too soon :-(. ERROR: operator is not unique: integer || unknown I did, of course, not follow the instructions and just blinding applied them all, but from reading them it doesn't look like the issue here. Does this error mean there are too many operators or not enough? Meaning another function + cast would solve it? Or maybe making the function more complex (by taking into account more possible cases)? Cheers Anton
"Anton Melser" <melser.anton@gmail.com> writes:
> Anyway, maybe I spoke too soon :-(.
> ERROR: operator is not unique: integer || unknown
> I did, of course, not follow the instructions and just blinding
> applied them all, but from reading them it doesn't look like the issue
> here. Does this error mean there are too many operators or not enough?
Too many. You might have to remove the anynonarray || text and
text || anynonarray operators if you're going to continue to rely
on implicit casts to text.
regards, tom lane
> > Anyway, maybe I spoke too soon :-(. > > > ERROR: operator is not unique: integer || unknown > > > I did, of course, not follow the instructions and just blinding > > applied them all, but from reading them it doesn't look like the issue > > here. Does this error mean there are too many operators or not enough? > > > Too many. You might have to remove the anynonarray || text and > text || anynonarray operators if you're going to continue to rely > on implicit casts to text. Thanks for that. Any chance someone could give me more newbie instructions? :-) I suppose you are talking about anytextcat(anynonarray, text) and textanycat(text, anynonarray) But I can't see anywhere obvious where I can "deactivate" them... I looked for likely suspects in pg_operator, pg_cast... but I'm not really sure what I'm doing. Anyone? Thanks heaps. Anton
"Anton Melser" <melser.anton@gmail.com> writes:
>> Too many. You might have to remove the anynonarray || text and
>> text || anynonarray operators if you're going to continue to rely
>> on implicit casts to text.
> Thanks for that. Any chance someone could give me more newbie instructions?
You'd have to do something like
DELETE FROM pg_operator WHERE oprcode = 'anytextcat'::regproc;
since there isn't any higher-level command that will let you delete a
built-in operator.
I recommend practicing on a scratch database ;-)
regards, tom lane
> You'd have to do something like
> DELETE FROM pg_operator WHERE oprcode = 'anytextcat'::regproc;
> since there isn't any higher-level command that will let you delete a
> built-in operator.
>
> I recommend practicing on a scratch database ;-)
Thanks for the tip, though alas that didn't seem to fix it...
select 1 || '/'
ERROR: operator is not unique: integer || unknown
LINE 1: select 1 || '/'
^
HINT: Could not choose a best candidate operator. You might need to
add explicit type casts.
and even
select 1 || '/'::text
ERROR: operator is not unique: integer || text
LINE 1: select 1 || '/'::text
^
HINT: Could not choose a best candidate operator. You might need to
add explicit type casts.
Am I in between a rock and a hard place here?
Thanks again,
Anton
"Anton Melser" <melser.anton@gmail.com> writes:
>> You'd have to do something like
>> DELETE FROM pg_operator WHERE oprcode = 'anytextcat'::regproc;
>> since there isn't any higher-level command that will let you delete a
>> built-in operator.
>>
>> I recommend practicing on a scratch database ;-)
> Thanks for the tip, though alas that didn't seem to fix it...
Did you remove the other one too?
regards, tom lane
On 25/03/2008, Tom Lane <tgl@sss.pgh.pa.us> wrote: > "Anton Melser" <melser.anton@gmail.com> writes: > > >> You'd have to do something like > >> DELETE FROM pg_operator WHERE oprcode = 'anytextcat'::regproc; > >> since there isn't any higher-level command that will let you delete a > >> built-in operator. > >> > >> I recommend practicing on a scratch database ;-) > > > Thanks for the tip, though alas that didn't seem to fix it... > > > Did you remove the other one too? Actually, I hadn't even properly deleted the first one (don't know where I did delete it, but it wasn't in the right place!) :-(. This is not my day! The app appears to be working again now. I won't bother you again with this - promised! Thanks a million. Cheers Anton ps for reference... DELETE FROM pg_operator WHERE oprcode = 'anytextcat'::regproc; DELETE FROM pg_operator WHERE oprcode = 'textanycat'::regproc;