Re: 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(at)catalyst(dot)net(dot)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(at)catalyst(dot)net(dot)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(at)svana(dot)org> http://svana.org/kleptog/
> Those who make peaceful revolution impossible will make violent revolution inevitable.
> -- John F Kennedy

> > - 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(dot)anton(at)gmail(dot)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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Anton Melser" <melser(dot)anton(at)gmail(dot)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(at)solfertje(dot)student(dot)utwente(dot)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(dot)anton(at)gmail(dot)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(dot)anton(at)gmail(dot)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(dot)anton(at)gmail(dot)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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Anton Melser" <melser(dot)anton(at)gmail(dot)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;