operator varchar = integer

2008/5/5 Daniel Schuchardt <daniel_schuchardt@web.de>:

Hey Group,

i know what all will say but i need to recreate the = operator for
datatypes varchar and integer in PostgreSQL 8.3.

Our Software Project has Millions of Lines and so it would be difficult to
check all queries and Datatypes. Also it works really fine and we all know
the risk of wrong auto casting.

Anyone knows the Syntax?

http://people.planetpostgresql.org/peter/index.php?/archives/18-Readding-implicit-casts-in-PostgreSQL-8.3.html

PS. you could search archives or even use google.

--
Filip Rembiałkowski

ups;

i have this script but forgott to execute it in the new createt db....
monday morning....

thnx.

Filip Rembiałkowski schrieb:

Daniel Schuchardt wrote:

i know what all will say but i need to recreate the = operator for
datatypes varchar and integer in PostgreSQL 8.3.

Our Software Project has Millions of Lines and so it would be
difficult
to check all queries and Datatypes. Also it works really fine
and we all
know the risk of wrong auto casting.

Anyone knows the Syntax?

What do you want to achieve?
Maybe there is already a different way to do what you want.

Yours,
Laurenz Albe

On Mon, May 05, 2008 at 11:18:37AM +0200, Daniel Schuchardt wrote:

Hey Group,

i know what all will say but i need to recreate the = operator for
datatypes varchar and integer in PostgreSQL 8.3.

Our Software Project has Millions of Lines and so it would be
difficult to check all queries and Datatypes.

That technical debt is a risk to your whole project, and you need to
dedicate resources to paying it down.

<http://en.wikipedia.org/wiki/Technical_debt&gt;

There are ways to get those automated casts, but they will only make
your situation worse in the long run.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

David Fetter schrieb:

That technical debt is a risk to your whole project, and you need to
dedicate resources to paying it down.

<http://en.wikipedia.org/wiki/Technical_debt&gt;

There are ways to get those automated casts, but they will only make
your situation worse in the long run.

Cheers,
David.

*g* interesting standpoint and your right but:

it is impossible for us to find all the points where the new 8.3
behavoir would crash at the first time. so our next versions would be
very buggy and our customers wouldn't be happy ;-)
the next problem is that our service personal has to be traineed too;
they dont know much about casting, 81 does it automatically; problems
problems problems.

if it is not possible (i know it is) ;-) to recreate automatic casts in
83 we would not be able to upgrade to 83 the next years. the next
possible date would be in about 3-4 years with the next major release.

PS:
our db has about 500 functions, 300 tables, 1000 indexes, 1200 Views
that all use implicit casting.
and: everything is working fine ;-) :-P

so we have to choose another way.

cheers.

Hi,

Daniel Schuchardt wrote:

David Fetter schrieb:

That technical debt is a risk to your whole project, and you need to
dedicate resources to paying it down.

<http://en.wikipedia.org/wiki/Technical_debt&gt;

There are ways to get those automated casts, but they will only make
your situation worse in the long run.

Cheers,
David.

*g* interesting standpoint and your right but:

it is impossible for us to find all the points where the new 8.3
behavoir would crash at the first time. so our next versions would be
very buggy and our customers wouldn't be happy ;-)
the next problem is that our service personal has to be traineed too;
they dont know much about casting, 81 does it automatically; problems
problems problems.

if it is not possible (i know it is) ;-) to recreate automatic casts in
83 we would not be able to upgrade to 83 the next years. the next
possible date would be in about 3-4 years with the next major release.

PS:
our db has about 500 functions, 300 tables, 1000 indexes, 1200 Views
that all use implicit casting.
and: everything is working fine ;-) :-P

so we have to choose another way.

Well err... implicit table joining is also off per default I believe.
So if you had used it a lot you would have a similar problem.

Comparing int with text in general does not sound like a very good
idea to me. It should be quite easy to write a script to identify
such places so you can either change the datatypes (preferred) or
add the cast. Then rerun your automated regression tests...

Cheers
T.

On Mon, May 05, 2008 at 05:26:40PM +0200, Daniel Schuchardt wrote:

David Fetter schrieb:

That technical debt is a risk to your whole project, and you need
to dedicate resources to paying it down.

<http://en.wikipedia.org/wiki/Technical_debt&gt;

There are ways to get those automated casts, but they will only
make your situation worse in the long run.

*g* interesting standpoint and your right but:

No buts. If you create those automated casts, you are just putting
off the inevitable.

it is impossible for us to find all the points where the new 8.3
behavoir would crash at the first time. so our next versions would
be very buggy and our customers wouldn't be happy ;-) the next
problem is that our service personal has to be traineed too; they
dont know much about casting, 81 does it automatically; problems
problems problems.

if it is not possible (i know it is) ;-) to recreate automatic casts
in 83 we would not be able to upgrade to 83 the next years. the next
possible date would be in about 3-4 years with the next major
release.

You're just confirming the fact that your project is in existential
trouble.

PS:
our db has about 500 functions, 300 tables, 1000 indexes, 1200 Views
that all use implicit casting. and: everything is working fine ;-)
:-P

How do you know? 8.3 removed the implicit casts precisely because
they were producing results that could most generously be describe as
"surprising."

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

Tino Wildenhain schrieb:

Hi,

Daniel Schuchardt wrote:

David Fetter schrieb:

That technical debt is a risk to your whole project, and you need to
dedicate resources to paying it down.

<http://en.wikipedia.org/wiki/Technical_debt&gt;

There are ways to get those automated casts, but they will only make
your situation worse in the long run.

Cheers,
David.

*g* interesting standpoint and your right but:

it is impossible for us to find all the points where the new 8.3
behavoir would crash at the first time. so our next versions would be
very buggy and our customers wouldn't be happy ;-)
the next problem is that our service personal has to be traineed too;
they dont know much about casting, 81 does it automatically; problems
problems problems.

if it is not possible (i know it is) ;-) to recreate automatic casts
in 83 we would not be able to upgrade to 83 the next years. the next
possible date would be in about 3-4 years with the next major release.

PS:
our db has about 500 functions, 300 tables, 1000 indexes, 1200 Views
that all use implicit casting.
and: everything is working fine ;-) :-P

so we have to choose another way.

Well err... implicit table joining is also off per default I believe.
So if you had used it a lot you would have a similar problem.

Comparing int with text in general does not sound like a very good
idea to me. It should be quite easy to write a script to identify
such places so you can either change the datatypes (preferred) or
add the cast. Then rerun your automated regression tests...

Cheers
T.

it is not possible to do this. we also used automatic casting as a feature:
if a user starts a search, we never check the data type he puts in. in
81 postgresql automatically casts the user input in dependece of the
column. so all our search masks has to be checked to, edit fields need
to get a data type and so on.

example:

user types 1 -> our application automatically starts a search "LIKE '1%'"
user types 12 -> our application automatically starts a search "LIKE
'12%'"

and so on. and it doenst matter if the searchfield is char or integer,
81 does it automatically.
if it is a good or bad thing to do it is another question. it works in
81 and so we used this feature.

cheers.

yeah, its clear that an upgrade from 8.1 to 8.3 is impossible for us
without a major relase.
there are to many changes so the whole project has to be rechecked.

another example:

in 83:

postgres=# SELECT 1::INTEGER||1::INTEGER;
ERROR: operator does not exist: integer || integer at character 18
HINT: No operator matches the given name and argument type(s). You
might need to add explicit type casts.

83 with autocast from peter e:

ERROR: operator is not unique: integer || integer

in 81:

postgres=# SELECT 1::INTEGER||1::INTEGER;
?column?
----------
11
(1 row)

No buts. If you create those automated casts, you are just putting
off the inevitable.

yes but than it will become a future project. (i think with postgres 8,5
or 8,6 :-P )

You're just confirming the fact that your project is in existential
trouble.

no, we would get trouble if we upgrade. now everybody is happy :-).
btw: our first software release was with postgres 7.3 and since this
time we use this feature (about 6 years?).

How do you know? 8.3 removed the implicit casts precisely because
they were producing results that could most generously be describe as
"surprising."

yeah thats an old discussion. we know about this and so we look for it.
it is a powerfull feature in 81 (and earlier) and yes if you use it in
the wrong way it will produce "surprising" results but if you use it in
the right way it is very powerfull. and if we get a suprising behavoir
we do in an explicit cast. done. ;-)

Cheers,
David.

daniel.

David Fetter <david@fetter.org> writes:

On Mon, May 05, 2008 at 05:26:40PM +0200, Daniel Schuchardt wrote:

our db has about 500 functions, 300 tables, 1000 indexes, 1200 Views
that all use implicit casting. and: everything is working fine ;-)
:-P

How do you know? 8.3 removed the implicit casts precisely because
they were producing results that could most generously be describe as
"surprising."

This should not be underestimated. From the reports we've seen so far,
a very sizable fraction of people who find this kind of failure with 8.3
find out that their application was doing something unexpected in the
cases where it happened. If you've got as many failures as you suggest,
I'd be willing to bet that some of them are bugs in your code, not just
reliance on an implicit feature.

regards, tom lane

Hi,

Daniel Schuchardt wrote:

Tino Wildenhain schrieb:

Hi,

...

Comparing int with text in general does not sound like a very good
idea to me. It should be quite easy to write a script to identify
such places so you can either change the datatypes (preferred) or
add the cast. Then rerun your automated regression tests...

Cheers
T.

it is not possible to do this. we also used automatic casting as a feature:
if a user starts a search, we never check the data type he puts in. in
81 postgresql automatically casts the user input in dependece of the
column. so all our search masks has to be checked to, edit fields need
to get a data type and so on.

example:

user types 1 -> our application automatically starts a search "LIKE '1%'"
user types 12 -> our application automatically starts a search "LIKE
'12%'"

and so on. and it doenst matter if the searchfield is char or integer,
81 does it automatically.
if it is a good or bad thing to do it is another question. it works in
81 and so we used this feature.

hum. (yes still think there is a lot of improvement possible) but
you could either change the schema to use text instead of int
or easily identify the places where LIKE is used and cast the left hand
expression explicitely to text. When I think about it you should
actually know for sure which places they are since you probably created
functional indexes?

Also it sounds like you are assembling SQL for queries? In this case
its even more easy to put the casts in place (or change the column
datatype). You mentioned support people - do they write raw SQL?

T.

Hi,

Daniel Schuchardt wrote:
...

in 81:

postgres=# SELECT 1::INTEGER||1::INTEGER;
?column?
----------
11
(1 row)

*shudder* is this actually a port of an application originally
targeted at M*Sql? ;)

Are you using those columns somewhere with their real type - as
integer? I mean if you use them as text everywhere why not change
the type once?

T.

Tino Wildenhain schrieb:

Hi,

Daniel Schuchardt wrote:
...

in 81:

postgres=# SELECT 1::INTEGER||1::INTEGER;
?column?
----------
11
(1 row)

*shudder* is this actually a port of an application originally
targeted at M*Sql? ;)

Are you using those columns somewhere with their real type - as
integer? I mean if you use them as text everywhere why not change
the type once?

T.

*g*

yes, sure we have to CAST it now. thats no problem. but the problem is
to find all the places where to cast. and you see that there are many
possiblilitys.

another example?:

RAISE NOTICE "error during validation % :",
'ks:"'||ks||'"@"'||loopdate||'"'; (here LoopDate is a DateTime)

another one:

here we need to add 4 CASTS. you see.....

CREATE OR REPLACE FUNCTION date_to_yearmonth_dec(TIMESTAMP) RETURNS
INTEGER AS $$
DECLARE R INTEGER;
BEGIN
IF extract(month FROM $1)<11 THEN
R:=extract(year FROM $1)||0||extract(month FROM $1)-1;
ELSE
R:=extract(year FROM $1)||extract(month FROM $1)-1;
END IF;
RETURN R;
END$$LANGUAGE plpgsql IMMUTABLE;

Tom Lane schrieb:

David Fetter <david@fetter.org> writes:

On Mon, May 05, 2008 at 05:26:40PM +0200, Daniel Schuchardt wrote:

our db has about 500 functions, 300 tables, 1000 indexes, 1200 Views
that all use implicit casting. and: everything is working fine ;-)
:-P

How do you know? 8.3 removed the implicit casts precisely because
they were producing results that could most generously be describe as
"surprising."

This should not be underestimated. From the reports we've seen so far,
a very sizable fraction of people who find this kind of failure with 8.3
find out that their application was doing something unexpected in the
cases where it happened. If you've got as many failures as you suggest,
I'd be willing to bet that some of them are bugs in your code, not just
reliance on an implicit feature.

regards, tom lane

yes true your right but lets make a calculation:

our application runs about 6 Years now so lets say there are 5% queries
that run still in a mistake caus of auto cast.
We have to check about 1200 views, 500 functions, every runtime created
query and so on. Lets say 95% of them run fine now. Now lets think we
check and rewrite all of them. If you let out the time (our customers
wont pay for such changes, they will have more errors the first time
than the last years), we should expect about X % errors because of the
changes (wrong parantheses and so on). So it would be a horror for us.
thats the problem.

examples:

RAISE EXCEPTION "error during validation % :",
'ks:"'||ks||'"@"'||loopdate||'"'; (KS is DECLARED VARCHAR, LoopDate is
a TIMESTAMP);

stempz:=Round(SUM(COALESCE(ba_efftime, timediff(ba_anf, CAST(now() AS
TIMESTAMP(0) WITHOUT TIME ZONE))))) FROM bdea WHERE
timestamp_to_date(ba_anf)=current_date AND ba_ks=oks AND
*ba_ix||'~'||ba_op* IN (SELECT *a2_ab_ix||'~'||a2_n* FROM ab2_wkstplan
JOIN ab2 ON a2_id=a2w_a2_id WHERE a2w_oks=oks AND a2w_ks=ks AND
a2w_planweek=week);

this are integer fields. but they are unique with "*a2_ab_ix||'~'||a2_n*".

another one:

here we need to add 4 CASTS.
CREATE OR REPLACE FUNCTION date_to_yearmonth_dec(TIMESTAMP) RETURNS
INTEGER AS $$
DECLARE R INTEGER;
BEGIN
IF extract(month FROM $1)<11 THEN
R:=extract(year FROM $1)||0||extract(month FROM $1)-1;
ELSE
R:=extract(year FROM $1)||extract(month FROM $1)-1;
END IF;
RETURN R;
END$$LANGUAGE plpgsql IMMUTABLE;

Daniel Schuchardt wrote:

Tino Wildenhain schrieb:

Hi,

Daniel Schuchardt wrote:
...

in 81:

postgres=# SELECT 1::INTEGER||1::INTEGER;
?column?
----------
11
(1 row)

*shudder* is this actually a port of an application originally
targeted at M*Sql? ;)

Are you using those columns somewhere with their real type - as
integer? I mean if you use them as text everywhere why not change
the type once?

T.

*g*

yes, sure we have to CAST it now. thats no problem. but the problem is
to find all the places where to cast. and you see that there are many
possiblilitys.

another example?:

RAISE NOTICE "error during validation % :",
'ks:"'||ks||'"@"'||loopdate||'"'; (here LoopDate is a DateTime)

another one:

here we need to add 4 CASTS. you see.....

CREATE OR REPLACE FUNCTION date_to_yearmonth_dec(TIMESTAMP) RETURNS
INTEGER AS $$
DECLARE R INTEGER;
BEGIN
IF extract(month FROM $1)<11 THEN
R:=extract(year FROM $1)||0||extract(month FROM $1)-1;
ELSE
R:=extract(year FROM $1)||extract(month FROM $1)-1;
END IF;
RETURN R;
END$$LANGUAGE plpgsql IMMUTABLE;

RETURN extract(year FROM $1)*100+extract(month FROM $1)-1;

was too clean and easy? ;))

Looks like a good oportunity to clean up your code before anything
unexpected happens :-)

Cheers
T.

Tino Wildenhain schrieb:

RETURN extract(year FROM $1)*100+extract(month FROM $1)-1;

was too clean and easy? ;))

Looks like a good oportunity to clean up your code before anything
unexpected happens :-)

Cheers
T.

LOL. Yes I don't like such easy things :-P

But you see i have a function so i simply can change it ;-)

thnx for the hint, i picked up the best example ever :-P

Daniel.

Daniel Schuchardt wrote:

Tino Wildenhain schrieb:

RETURN extract(year FROM $1)*100+extract(month FROM $1)-1;

was too clean and easy? ;))

Looks like a good oportunity to clean up your code before anything
unexpected happens :-)

Cheers
T.

LOL. Yes I don't like such easy things :-P
RAISE NOTICE "error during validation % :",

'ks:"'||ks||'"@"'||loopdate||'"'; (here LoopDate is a DateTime)

You know you can use more than one % in a raise and it will take care of
the data types?

create function atest() returns integer as $$
declare
ks integer;
loopdate timestamp;
begin
ks := 3;
loopdate := now();
raise notice 'blah ks:%@%', ks, loopdate;
return 1;
end;
$$ language plpgsql;

postgres=# select atest();
NOTICE: blah ks:3@2008-05-06 09:58:55.812
atest
-------
1
(1 row)

klint.

--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350

Ph: 02 6773 3789
Fax: 02 6773 3266
EMail: kgore4@une.edu.au

another example?:

RAISE NOTICE "error during validation % :", 'ks:"'||ks||'"@"'||
loopdate||'"'; (here LoopDate is a DateTime)

Ehm... What's wrong with RAISE NOTICE "error during validation
ks:"%"@"%" :', ks, loopdate; ? (I don't quite understand the purpose
of that colon at the end, btw).
Allows you to format the date to your liking too, just add a to_char
(loopdate, <format string>).

I know these were just a few examples of your troubles, but so far it
appears it's desirable to get rid of them for better code.

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,481ffc80927661001715755!