Обсуждение: Implicit coercions need to be reined in
Currently, if PG has a single-argument function named after its result
type, the function is assumed to represent a valid implicit type
coercion. For example, I can do
regression=# select now() || now(); ?column?
------------------------------------------------------------2001-11-21 15:12:13.226482-052001-11-21 15:12:13.226482-05
(1 row)
because there is a function text(timestamp) returning text, and
this function gets invoked implicitly.
It strikes me that this is a bad idea, and will get a lot worse as we
add more conversion functions. With enough implicit coercions one will
never be entirely sure how the system will interpret a mixed-datatype
expression. Nonetheless, having more conversion functions seems like a
good idea --- I think there should be a numeric-to-text conversion
function, for example, and someone was just complaining in pggeneral
about the lack of a boolean-to-text coercion. The problem is that
there's no way to create a conversion function without inducing an
implicit coercion path (unless you give the function a nonobvious name).
What I'd like to suggest (for 7.3 or later) is adding a boolean column
to pg_proc that indicates "can be implicit coercion". A function for
which this is not set can be used as an explicitly requested coercion,
but not an implicit one. Thus it'd be possible to define text(boolean)
and allow it to be called explicitly, without creating an implicit
coercion path and thereby losing a lot of type safety.
I have not gone over the existing implicit coercions to see which ones
I like and don't like, but I think a good first cut at maintaining
sanity would be to disable any cross-type-category implicit coercions.
Thus, for example, int4 to float8 seems like an okay implicit coercion,
but not int4 to text.
Note that this would cause the system to reject some things it accepts
now, for example:
regression=# select 45::int4 || 66::int4;?column?
----------4566
(1 row)
This sort of thing would need explicit coercions to text under my proposal.
Comments?
regards, tom lane
On Wed, 21 Nov 2001, Tom Lane wrote: > It strikes me that this is a bad idea, and will get a lot worse as we > add more conversion functions. With enough implicit coercions one will > never be entirely sure how the system will interpret a mixed-datatype > expression. Nonetheless, having more conversion functions seems like a > good idea --- I think there should be a numeric-to-text conversion > function, for example, and someone was just complaining in pggeneral > about the lack of a boolean-to-text coercion. The problem is that > there's no way to create a conversion function without inducing an > implicit coercion path (unless you give the function a nonobvious name). > > What I'd like to suggest (for 7.3 or later) is adding a boolean column > to pg_proc that indicates "can be implicit coercion". A function for > which this is not set can be used as an explicitly requested coercion, > but not an implicit one. Thus it'd be possible to define text(boolean) > and allow it to be called explicitly, without creating an implicit > coercion path and thereby losing a lot of type safety. I think something of this sort is a good thing. :) It's a bit of a pain in one way, but it makes understanding what's going on simpler, especially given things like the person who was getting text cut off at 31 or whatever characters due to the fact that there was an implicit coercion through name.
> Thus, for example, int4 to float8 seems like an okay implicit coercion, > but not int4 to text. int4 to text is imho one of the most important implicit coercions of all. Still, a field to mark an implicit coercion function is probably a good thing. Imho a numeric to text implicit coercion would also be great :-) I come from a db where all coercions are possible implicitly, this has not been a problem as long as there is a way to overrule. Andreas
Tom Lane writes: > What I'd like to suggest (for 7.3 or later) is adding a boolean column > to pg_proc that indicates "can be implicit coercion". Great! I've always wished for this sort of thing. I think from this there are only a few more steps to the full SQL99 specification of user-defined casts. In particular it'd involve two boolean flags: one that means "this is a cast function" (the function is used internally for a CAST() invocation), and one for allowing it to be used for automatic conversion. We currently don't have the first one on the radar because we use the function name and argument types as the indicator. One more thing is that instead of looking up a cast function from type A to type B as "returns A, is named A, takes arg B" it could be looked up as "returns A, takes arg B, is a cast function", which would generalize this whole thing a bit more. References: SQL99 Part 2 11.49, 11.52 (I haven't read the whole thing, but I think it's the idea.) -- Peter Eisentraut peter_e@gmx.net
"Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at> writes:
> I come from a db where all coercions are possible implicitly,
> this has not been a problem as long as there is a way to overrule.
Yeah, but how rich was its type structure compared to Postgres'?
It might indeed be safe/reasonable to allow implicit coercions to
text from all other types. I'm not sure. I am sure that if any
datatype coercion one could possibly want is available implicitly,
it's going to be very difficult to predict the system's behavior.
In fact, this would probably make the default behavior appear to
have *fewer* automatic coercions not more: anytime there wasn't
an exact type match, the parser would have too many alternatives
and would be unable to select a unique function or operator candidate
from among those it could reach by means of implicit coercions.
We've seen some reports of such problems already, and it'll get worse
as we add implicit coercions.
Of course, you could always turn on the "can be implicit coercion"
flag for whichever pg_proc entries you really wanted ...
regards, tom lane
Peter Eisentraut <peter_e@gmx.net> writes:
> One more thing is that instead of looking up a cast function from type A
> to type B as "returns A, is named A, takes arg B" it could be looked up as
> "returns A, takes arg B, is a cast function", which would generalize this
> whole thing a bit more.
I thought about that, but it would require keeping an extra index on
pg_proc (there's no efficient way to search by result type now). Not
clear that it's worth it, especially considering that there's really
only one reasonable name for a cast function anyway. What else would
you want to call it than the name of the destination type?
Also, this convention assures that there's at most *one* cast function
for any type coercion A to B. If the name could be anything then we'd
need some auxiliary mechanism to prevent conflicting cast functions
from being declared. Seems like a lot of mechanism for a dubious goal.
regards, tom lane
Awhile back I suggested adding a boolean column to pg_proc to control which type coercion functions could be invoked implicitly, and which would need an explicit cast: http://archives.postgresql.org/pgsql-hackers/2001-11/msg00803.php There is a relevant bug report #484 showing the dangers of too many implicit coercion paths: http://archives.postgresql.org/pgsql-bugs/2001-10/msg00108.php I have added such a column as part of the pg_proc changes I'm currently doing to migrate aggregates into pg_proc. So it's now time to debate the nitty-gritty: exactly which coercion functions should not be implicitly invokable anymore? My first-cut attempt at this is shown by the two printouts below. The first cut does not allow any implicit coercions to text from types that are not in the text category, which seems a necessary rule to me --- the above-cited bug report shows why free coercions to text are dangerous. However, it turns out that several of the regression tests fail with this rule; see the regression diffs below. Should I consider these regression tests wrong, and correct them? If not, how can we limit implicit coercions to text enough to avoid the problems illustrated by bug #484? Another interesting point is that I allowed implicit coercions from float8 to numeric; this is necessary to avoid breaking cases likeinsert into foo(numeric_col) values(12.34); since the constant will be initially typed as float8. However, because I didn't allow the reverse coercion implicitly, this makes numeric "more preferred" than float8. Thus, for example,select '12.34'::numeric + 12.34; which draws a can't-resolve-operator error in 7.2, is resolved as numeric addition with these changes. Is this a good thing, or not? We could preserve the can't-resolve behavior by marking numeric->float8 as an allowed implicit coercion, but that seems ugly. I'm not sure we can do a whole lot better without some more wide-ranging revisions of the way we handle untyped numeric literals (as in past proposals to invent an UNKNOWNNUMERIC pseudo-type). Also, does anyone have any other nits to pick with this classification of which coercions are implicitly okay? I've started with a fairly tough approach of disallowing most implicit coercions, but perhaps this goes too far. regards, tom lane Coercions allowed implicitly: oid | result | input | prosrc ------+-------------+-------------+----------------------- 860 | bpchar | char | char_bpchar 408 | bpchar | name | name_bpchar 861 | char | bpchar | bpchar_char 944 | char | text | text_char 312| float4 | float8 | dtof 236 | float4 | int2 | i2tof 318 | float4 | int4 | i4tof 311| float8 | float4 | ftod 235 | float8 | int2 | i2tod 316 | float8 | int4 | i4tod 482| float8 | int8 | i8tod 314 | int2 | int4 | i4toi2 714 | int2 | int8 | int82 313| int4 | int2 | i2toi4 480 | int4 | int8 | int84 754 | int8 | int2 | int28 481| int8 | int4 | int481177 | interval | reltime | reltime_interval1370 | interval | time | time_interval 409 | name | bpchar | bpchar_name 407 | name | text | text_name1400 | name | varchar | text_name1742 | numeric | float4 | float4_numeric1743 | numeric | float8 | float8_numeric1782| numeric | int2 | int2_numeric1740 | numeric | int4 | int4_numeric1781 | numeric | int8 | int8_numeric 946 | text | char | char_text 406 | text | name | name_text2046| time | timetz | timetz_time2023 | timestamp | abstime | abstime_timestamp2024 | timestamp | date | date_timestamp2027 | timestamp | timestamptz | timestamptz_timestamp1173 | timestamptz | abstime | abstime_timestamptz1174 | timestamptz | date | date_timestamptz2028 | timestamptz | timestamp | timestamp_timestamptz2047| timetz | time | time_timetz1401 | varchar | name | name_text (38 rows) Coercions that will require explicit CAST, ::type, or typename(x) syntax (NB: in 7.2 all of these would have been allowed implicitly): oid | result | input | prosrc ------+-------------+-------------+------------------------------------------2030 | abstime | timestamp | timestamp_abstime1180| abstime | timestamptz | timestamptz_abstime1480 | box | circle | circle_box1446 |box | polygon | poly_box1714 | cidr | text | text_cidr1479 | circle | box | box_circle1474| circle | polygon | poly_circle1179 | date | abstime | abstime_date 748 | date | text | text_date2029 | date | timestamp | timestamp_date1178 | date | timestamptz | timestamptz_date1745| float4 | numeric | numeric_float4 839 | float4 | text | text_float41746 | float8 | numeric | numeric_float8 838 | float8 | text | text_float81713 | inet | text |text_inet 238 | int2 | float4 | ftoi2 237 | int2 | float8 | dtoi21783 | int2 | numeric | numeric_int2 818 | int2 | text | text_int2 319 | int4 | float4 | ftoi4 317 | int4 |float8 | dtoi41744 | int4 | numeric | numeric_int4 819 | int4 | text | text_int4 483 | int8 | float8 | dtoi81779 | int8 | numeric | numeric_int81289 | int8 | text | text_int81263| interval | text | text_interval1541 | lseg | box | box_diagonal 767 | macaddr | text | text_macaddr 817 | oid | text | text_oid1447 | path | polygon | poly_path1534| point | box | box_center1416 | point | circle | circle_center1532 | point |lseg | lseg_center1533 | point | path | path_center1540 | point | polygon | poly_center1448| polygon | box | box_poly1544 | polygon | circle | select polygon(12, $1)1449 | polygon | path | path_poly1200 | reltime | int4 | int4reltime1194 | reltime | interval | interval_reltime749 | text | date | date_text 841 | text | float4 | float4_text 840 | text | float8 | float8_text 730 | text | inet | network_show 113 | text | int2 | int2_text112 | text | int4 | int4_text1288 | text | int8 | int8_text1193 | text | interval | interval_text 752 | text | macaddr | macaddr_text 114 | text | oid | oid_text 948| text | time | time_text2034 | text | timestamp | timestamp_text1192 | text | timestamptz| timestamptz_text 939 | text | timetz | timetz_text1364 | time | abstime | select time(cast($1as timestamp without time zone))1419 | time | interval | interval_time 837 | time | text | text_time1316 | time | timestamp | timestamp_time2022 | timestamp | text | text_timestamp1191 | timestamptz| text | text_timestamptz 938 | timetz | text | text_timetz1388 | timetz | timestamptz| timestamptz_timetz1619 | varchar | int4 | int4_text1623 | varchar | int8 | int8_text (66 rows) Regression failures with this set of choices (I've edited the output to remove diffs that are merely consequences of the actual failures): *** ./expected/char.out Mon May 21 12:54:46 2001 --- ./results/char.out Wed Apr 10 11:48:16 2002 *************** *** 18,23 **** --- 18,25 ---- -- any of the following three input formats are acceptable INSERT INTO CHAR_TBL (f1) VALUES ('1'); INSERTINTO CHAR_TBL (f1) VALUES (2); + ERROR: column "f1" is of type 'character' but expression is of type 'integer' + You will need to rewrite or cast the expression INSERT INTO CHAR_TBL (f1) VALUES ('3'); -- zero-length char INSERTINTO CHAR_TBL (f1) VALUES (''); *** ./expected/varchar.out Mon May 21 12:54:46 2001 --- ./results/varchar.out Wed Apr 10 11:48:17 2002 *************** *** 7,12 **** --- 7,14 ---- -- any of the following three input formats are acceptable INSERT INTO VARCHAR_TBL (f1) VALUES ('1'); INSERTINTO VARCHAR_TBL (f1) VALUES (2); + ERROR: column "f1" is of type 'character varying' but expression is of type 'integer' + You will need to rewrite or cast the expression INSERT INTO VARCHAR_TBL (f1) VALUES ('3'); -- zero-length char INSERTINTO VARCHAR_TBL (f1) VALUES (''); *** ./expected/strings.out Fri Jun 1 13:49:17 2001 --- ./results/strings.out Wed Apr 10 11:49:29 2002 *************** *** 137,147 **** (1 row) SELECT POSITION(5 IN '1234567890') = '5' AS "5"; ! 5 ! --- ! t ! (1 row) ! -- -- test LIKE -- Be sure to form every test as a LIKE/NOT LIKE pair. --- 137,145 ---- (1 row) SELECT POSITION(5 IN '1234567890') = '5' AS "5"; ! ERROR: Function 'pg_catalog.position(unknown, int4)' does not exist ! Unable to identify a function that satisfies the given argument types ! You may need to add explicit typecasts -- -- test LIKE -- Be sure to form every test as a LIKE/NOT LIKE pair. *** ./expected/alter_table.out Fri Apr 5 12:03:45 2002 --- ./results/alter_table.out Wed Apr 10 11:51:06 2002 *************** *** 363,374 **** CREATE TEMP TABLE FKTABLE (ftest1 varchar); ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable;NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s) -- As should this ALTER TABLE FKTABLEADD FOREIGN KEY(ftest1) references pktable(ptest1); NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGNKEY check(s) DROP TABLE pktable; - NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "fktable" - NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "fktable" DROP TABLE fktable; CREATE TEMPTABLE PKTABLE (ptest1 int, ptest2 text, PRIMARY KEY(ptest1, ptest2)); --- 363,376 ---- CREATE TEMP TABLE FKTABLE (ftest1 varchar); ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable;NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s) + ERROR: Unable to identify an operator '=' for types 'character varying' and 'integer' + You will have to retype this query using an explicit cast -- As should this ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1)references pktable(ptest1); NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s) + ERROR: Unable to identify an operator '=' for types 'character varying' and 'integer' + You will have to retype this query using an explicit cast DROP TABLE pktable; DROP TABLE fktable; CREATE TEMP TABLEPKTABLE (ptest1 int, ptest2 text, PRIMARY KEY(ptest1, ptest2)); *** ./expected/rules.out Thu Mar 21 10:24:35 2002 --- ./results/rules.out Wed Apr 10 11:51:11 2002 *************** *** 1026,1037 **** 'Al Bundy', 'epoch'::text ); UPDATE shoelace_data SET sl_avail = 6 WHERE sl_name = 'sl7'; SELECT* FROM shoelace_log; sl_name | sl_avail | log_who | log_when ! ------------+----------+----------+-------------------------- ! sl7 | 6 | Al Bundy | Thu Jan 01 00:00:00 1970 ! (1 row) CREATE RULE shoelace_ins AS ON INSERT TO shoelace DO INSTEAD --- 1026,1038 ---- 'Al Bundy', 'epoch'::text ); + ERROR: column "log_when" is of type 'timestamp without time zone' but expression is of type 'text' + You will need to rewrite or cast the expression UPDATE shoelace_data SET sl_avail = 6 WHERE sl_name = 'sl7'; SELECT* FROM shoelace_log; sl_name | sl_avail | log_who | log_when ! ---------+----------+---------+---------- ! (0 rows) CREATE RULE shoelace_ins AS ON INSERT TO shoelace DO INSTEAD *** ./expected/foreign_key.out Wed Mar 6 01:10:56 2002 --- ./results/foreign_key.out Wed Apr 10 11:51:17 2002 *************** *** 733,747 **** -- because varchar=int does exist CREATE TABLE FKTABLE (ftest1 varchar REFERENCES pktable); NOTICE: CREATETABLE will create implicit trigger(s) for FOREIGN KEY check(s) DROP TABLE FKTABLE; ! NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "pktable" ! NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "pktable" -- As should this CREATE TABLEFKTABLE (ftest1 varchar REFERENCES pktable(ptest1)); NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGNKEY check(s) DROP TABLE FKTABLE; ! NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "pktable" ! NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "pktable" DROP TABLE PKTABLE; -- Two columns,two tables CREATE TABLE PKTABLE (ptest1 int, ptest2 text, PRIMARY KEY(ptest1, ptest2)); --- 733,749 ---- -- because varchar=int does exist CREATE TABLE FKTABLE (ftest1 varchar REFERENCES pktable); NOTICE: CREATETABLE will create implicit trigger(s) for FOREIGN KEY check(s) + ERROR: Unable to identify an operator '=' for types 'character varying' and 'integer' + You will have to retype this query using an explicit cast DROP TABLE FKTABLE; ! ERROR: table "fktable" does not exist -- As should this CREATE TABLE FKTABLE (ftest1 varchar REFERENCES pktable(ptest1));NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) + ERROR: Unable to identify an operator '=' for types 'character varying' and 'integer' + You will have to retype this query using an explicit cast DROP TABLE FKTABLE; ! ERROR: table "fktable" does not exist DROP TABLE PKTABLE; -- Two columns, two tables CREATE TABLE PKTABLE (ptest1 int,ptest2 text, PRIMARY KEY(ptest1, ptest2)); *** ./expected/domain.out Wed Mar 20 13:34:37 2002 --- ./results/domain.out Wed Apr 10 11:51:23 2002 *************** *** 111,116 **** --- 111,118 ---- create domain ddef2 oid DEFAULT '12'; -- Type mixing, function returns int8 create domain ddef3 text DEFAULT5; + ERROR: Column "ddef3" is of type text but default expression is of type integer + You will need to rewrite or cast the expression create sequence ddef4_seq; create domain ddef4 int4 DEFAULT nextval(cast('ddef4_seq'as text)); create domain ddef5 numeric(8,2) NOT NULL DEFAULT '12.12';
Tom, My feeling is that this change as currently scoped will break a lot of existing apps. Especially the case where people are using where clauses of the form: bigintcolumn = '999' to get a query to use the index on a column of type bigint. thanks, --Barry Tom Lane wrote: > Awhile back I suggested adding a boolean column to pg_proc to control > which type coercion functions could be invoked implicitly, and which > would need an explicit cast: > http://archives.postgresql.org/pgsql-hackers/2001-11/msg00803.php > There is a relevant bug report #484 showing the dangers of too many > implicit coercion paths: > http://archives.postgresql.org/pgsql-bugs/2001-10/msg00108.php > > I have added such a column as part of the pg_proc changes I'm currently > doing to migrate aggregates into pg_proc. So it's now time to debate > the nitty-gritty: exactly which coercion functions should not be > implicitly invokable anymore? > > My first-cut attempt at this is shown by the two printouts below. > The first cut does not allow any implicit coercions to text from types > that are not in the text category, which seems a necessary rule to me > --- the above-cited bug report shows why free coercions to text are > dangerous. However, it turns out that several of the regression > tests fail with this rule; see the regression diffs below. > > Should I consider these regression tests wrong, and correct them? > If not, how can we limit implicit coercions to text enough to avoid > the problems illustrated by bug #484? > > Another interesting point is that I allowed implicit coercions from > float8 to numeric; this is necessary to avoid breaking cases like > insert into foo(numeric_col) values(12.34); > since the constant will be initially typed as float8. However, because > I didn't allow the reverse coercion implicitly, this makes numeric > "more preferred" than float8. Thus, for example, > select '12.34'::numeric + 12.34; > which draws a can't-resolve-operator error in 7.2, is resolved as > numeric addition with these changes. Is this a good thing, or not? > We could preserve the can't-resolve behavior by marking numeric->float8 > as an allowed implicit coercion, but that seems ugly. I'm not sure we > can do a whole lot better without some more wide-ranging revisions of > the way we handle untyped numeric literals (as in past proposals to > invent an UNKNOWNNUMERIC pseudo-type). > > Also, does anyone have any other nits to pick with this classification > of which coercions are implicitly okay? I've started with a fairly > tough approach of disallowing most implicit coercions, but perhaps this > goes too far. > > regards, tom lane > > Coercions allowed implicitly: > > oid | result | input | prosrc > ------+-------------+-------------+----------------------- > 860 | bpchar | char | char_bpchar > 408 | bpchar | name | name_bpchar > 861 | char | bpchar | bpchar_char > 944 | char | text | text_char > 312 | float4 | float8 | dtof > 236 | float4 | int2 | i2tof > 318 | float4 | int4 | i4tof > 311 | float8 | float4 | ftod > 235 | float8 | int2 | i2tod > 316 | float8 | int4 | i4tod > 482 | float8 | int8 | i8tod > 314 | int2 | int4 | i4toi2 > 714 | int2 | int8 | int82 > 313 | int4 | int2 | i2toi4 > 480 | int4 | int8 | int84 > 754 | int8 | int2 | int28 > 481 | int8 | int4 | int48 > 1177 | interval | reltime | reltime_interval > 1370 | interval | time | time_interval > 409 | name | bpchar | bpchar_name > 407 | name | text | text_name > 1400 | name | varchar | text_name > 1742 | numeric | float4 | float4_numeric > 1743 | numeric | float8 | float8_numeric > 1782 | numeric | int2 | int2_numeric > 1740 | numeric | int4 | int4_numeric > 1781 | numeric | int8 | int8_numeric > 946 | text | char | char_text > 406 | text | name | name_text > 2046 | time | timetz | timetz_time > 2023 | timestamp | abstime | abstime_timestamp > 2024 | timestamp | date | date_timestamp > 2027 | timestamp | timestamptz | timestamptz_timestamp > 1173 | timestamptz | abstime | abstime_timestamptz > 1174 | timestamptz | date | date_timestamptz > 2028 | timestamptz | timestamp | timestamp_timestamptz > 2047 | timetz | time | time_timetz > 1401 | varchar | name | name_text > (38 rows) > > Coercions that will require explicit CAST, ::type, or typename(x) syntax > (NB: in 7.2 all of these would have been allowed implicitly): > > oid | result | input | prosrc > ------+-------------+-------------+------------------------------------------ > 2030 | abstime | timestamp | timestamp_abstime > 1180 | abstime | timestamptz | timestamptz_abstime > 1480 | box | circle | circle_box > 1446 | box | polygon | poly_box > 1714 | cidr | text | text_cidr > 1479 | circle | box | box_circle > 1474 | circle | polygon | poly_circle > 1179 | date | abstime | abstime_date > 748 | date | text | text_date > 2029 | date | timestamp | timestamp_date > 1178 | date | timestamptz | timestamptz_date > 1745 | float4 | numeric | numeric_float4 > 839 | float4 | text | text_float4 > 1746 | float8 | numeric | numeric_float8 > 838 | float8 | text | text_float8 > 1713 | inet | text | text_inet > 238 | int2 | float4 | ftoi2 > 237 | int2 | float8 | dtoi2 > 1783 | int2 | numeric | numeric_int2 > 818 | int2 | text | text_int2 > 319 | int4 | float4 | ftoi4 > 317 | int4 | float8 | dtoi4 > 1744 | int4 | numeric | numeric_int4 > 819 | int4 | text | text_int4 > 483 | int8 | float8 | dtoi8 > 1779 | int8 | numeric | numeric_int8 > 1289 | int8 | text | text_int8 > 1263 | interval | text | text_interval > 1541 | lseg | box | box_diagonal > 767 | macaddr | text | text_macaddr > 817 | oid | text | text_oid > 1447 | path | polygon | poly_path > 1534 | point | box | box_center > 1416 | point | circle | circle_center > 1532 | point | lseg | lseg_center > 1533 | point | path | path_center > 1540 | point | polygon | poly_center > 1448 | polygon | box | box_poly > 1544 | polygon | circle | select polygon(12, $1) > 1449 | polygon | path | path_poly > 1200 | reltime | int4 | int4reltime > 1194 | reltime | interval | interval_reltime > 749 | text | date | date_text > 841 | text | float4 | float4_text > 840 | text | float8 | float8_text > 730 | text | inet | network_show > 113 | text | int2 | int2_text > 112 | text | int4 | int4_text > 1288 | text | int8 | int8_text > 1193 | text | interval | interval_text > 752 | text | macaddr | macaddr_text > 114 | text | oid | oid_text > 948 | text | time | time_text > 2034 | text | timestamp | timestamp_text > 1192 | text | timestamptz | timestamptz_text > 939 | text | timetz | timetz_text > 1364 | time | abstime | select time(cast($1 as timestamp without time zone)) > 1419 | time | interval | interval_time > 837 | time | text | text_time > 1316 | time | timestamp | timestamp_time > 2022 | timestamp | text | text_timestamp > 1191 | timestamptz | text | text_timestamptz > 938 | timetz | text | text_timetz > 1388 | timetz | timestamptz | timestamptz_timetz > 1619 | varchar | int4 | int4_text > 1623 | varchar | int8 | int8_text > (66 rows) > > > Regression failures with this set of choices (I've edited the output to > remove diffs that are merely consequences of the actual failures): > > *** ./expected/char.out Mon May 21 12:54:46 2001 > --- ./results/char.out Wed Apr 10 11:48:16 2002 > *************** > *** 18,23 **** > --- 18,25 ---- > -- any of the following three input formats are acceptable > INSERT INTO CHAR_TBL (f1) VALUES ('1'); > INSERT INTO CHAR_TBL (f1) VALUES (2); > + ERROR: column "f1" is of type 'character' but expression is of type 'integer' > + You will need to rewrite or cast the expression > INSERT INTO CHAR_TBL (f1) VALUES ('3'); > -- zero-length char > INSERT INTO CHAR_TBL (f1) VALUES (''); > > *** ./expected/varchar.out Mon May 21 12:54:46 2001 > --- ./results/varchar.out Wed Apr 10 11:48:17 2002 > *************** > *** 7,12 **** > --- 7,14 ---- > -- any of the following three input formats are acceptable > INSERT INTO VARCHAR_TBL (f1) VALUES ('1'); > INSERT INTO VARCHAR_TBL (f1) VALUES (2); > + ERROR: column "f1" is of type 'character varying' but expression is of type 'integer' > + You will need to rewrite or cast the expression > INSERT INTO VARCHAR_TBL (f1) VALUES ('3'); > -- zero-length char > INSERT INTO VARCHAR_TBL (f1) VALUES (''); > > *** ./expected/strings.out Fri Jun 1 13:49:17 2001 > --- ./results/strings.out Wed Apr 10 11:49:29 2002 > *************** > *** 137,147 **** > (1 row) > > SELECT POSITION(5 IN '1234567890') = '5' AS "5"; > ! 5 > ! --- > ! t > ! (1 row) > ! > -- > -- test LIKE > -- Be sure to form every test as a LIKE/NOT LIKE pair. > --- 137,145 ---- > (1 row) > > SELECT POSITION(5 IN '1234567890') = '5' AS "5"; > ! ERROR: Function 'pg_catalog.position(unknown, int4)' does not exist > ! Unable to identify a function that satisfies the given argument types > ! You may need to add explicit typecasts > -- > -- test LIKE > -- Be sure to form every test as a LIKE/NOT LIKE pair. > > *** ./expected/alter_table.out Fri Apr 5 12:03:45 2002 > --- ./results/alter_table.out Wed Apr 10 11:51:06 2002 > *************** > *** 363,374 **** > CREATE TEMP TABLE FKTABLE (ftest1 varchar); > ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable; > NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s) > -- As should this > ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable(ptest1); > NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s) > DROP TABLE pktable; > - NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "fktable" > - NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "fktable" > DROP TABLE fktable; > CREATE TEMP TABLE PKTABLE (ptest1 int, ptest2 text, > PRIMARY KEY(ptest1, ptest2)); > --- 363,376 ---- > CREATE TEMP TABLE FKTABLE (ftest1 varchar); > ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable; > NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s) > + ERROR: Unable to identify an operator '=' for types 'character varying' and 'integer' > + You will have to retype this query using an explicit cast > -- As should this > ALTER TABLE FKTABLE ADD FOREIGN KEY(ftest1) references pktable(ptest1); > NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s) > + ERROR: Unable to identify an operator '=' for types 'character varying' and 'integer' > + You will have to retype this query using an explicit cast > DROP TABLE pktable; > DROP TABLE fktable; > CREATE TEMP TABLE PKTABLE (ptest1 int, ptest2 text, > PRIMARY KEY(ptest1, ptest2)); > > *** ./expected/rules.out Thu Mar 21 10:24:35 2002 > --- ./results/rules.out Wed Apr 10 11:51:11 2002 > *************** > *** 1026,1037 **** > 'Al Bundy', > 'epoch'::text > ); > UPDATE shoelace_data SET sl_avail = 6 WHERE sl_name = 'sl7'; > SELECT * FROM shoelace_log; > sl_name | sl_avail | log_who | log_when > ! ------------+----------+----------+-------------------------- > ! sl7 | 6 | Al Bundy | Thu Jan 01 00:00:00 1970 > ! (1 row) > > CREATE RULE shoelace_ins AS ON INSERT TO shoelace > DO INSTEAD > --- 1026,1038 ---- > 'Al Bundy', > 'epoch'::text > ); > + ERROR: column "log_when" is of type 'timestamp without time zone' but expression is of type 'text' > + You will need to rewrite or cast the expression > UPDATE shoelace_data SET sl_avail = 6 WHERE sl_name = 'sl7'; > SELECT * FROM shoelace_log; > sl_name | sl_avail | log_who | log_when > ! ---------+----------+---------+---------- > ! (0 rows) > > CREATE RULE shoelace_ins AS ON INSERT TO shoelace > DO INSTEAD > > *** ./expected/foreign_key.out Wed Mar 6 01:10:56 2002 > --- ./results/foreign_key.out Wed Apr 10 11:51:17 2002 > *************** > *** 733,747 **** > -- because varchar=int does exist > CREATE TABLE FKTABLE (ftest1 varchar REFERENCES pktable); > NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) > DROP TABLE FKTABLE; > ! NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "pktable" > ! NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "pktable" > -- As should this > CREATE TABLE FKTABLE (ftest1 varchar REFERENCES pktable(ptest1)); > NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) > DROP TABLE FKTABLE; > ! NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "pktable" > ! NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "pktable" > DROP TABLE PKTABLE; > -- Two columns, two tables > CREATE TABLE PKTABLE (ptest1 int, ptest2 text, PRIMARY KEY(ptest1, ptest2)); > --- 733,749 ---- > -- because varchar=int does exist > CREATE TABLE FKTABLE (ftest1 varchar REFERENCES pktable); > NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) > + ERROR: Unable to identify an operator '=' for types 'character varying' and 'integer' > + You will have to retype this query using an explicit cast > DROP TABLE FKTABLE; > ! ERROR: table "fktable" does not exist > -- As should this > CREATE TABLE FKTABLE (ftest1 varchar REFERENCES pktable(ptest1)); > NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) > + ERROR: Unable to identify an operator '=' for types 'character varying' and 'integer' > + You will have to retype this query using an explicit cast > DROP TABLE FKTABLE; > ! ERROR: table "fktable" does not exist > DROP TABLE PKTABLE; > -- Two columns, two tables > CREATE TABLE PKTABLE (ptest1 int, ptest2 text, PRIMARY KEY(ptest1, ptest2)); > > *** ./expected/domain.out Wed Mar 20 13:34:37 2002 > --- ./results/domain.out Wed Apr 10 11:51:23 2002 > *************** > *** 111,116 **** > --- 111,118 ---- > create domain ddef2 oid DEFAULT '12'; > -- Type mixing, function returns int8 > create domain ddef3 text DEFAULT 5; > + ERROR: Column "ddef3" is of type text but default expression is of type integer > + You will need to rewrite or cast the expression > create sequence ddef4_seq; > create domain ddef4 int4 DEFAULT nextval(cast('ddef4_seq' as text)); > create domain ddef5 numeric(8,2) NOT NULL DEFAULT '12.12'; > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >
Barry Lind <barry@xythos.com> writes:
> My feeling is that this change as currently scoped will break a lot of
> existing apps. Especially the case where people are using where clauses
> of the form: bigintcolumn = '999' to get a query to use the index on
> a column of type bigint.
Eh? That case will not change behavior in the slightest, because
there's no type conversion --- the literal is interpreted as the target
type to start with.
regards, tom lane
OK. My mistake. In looking at the regression failures in your post, I thought I saw errors being reported of this type. My bad. --Barry Tom Lane wrote: > Barry Lind <barry@xythos.com> writes: > >>My feeling is that this change as currently scoped will break a lot of >>existing apps. Especially the case where people are using where clauses >>of the form: bigintcolumn = '999' to get a query to use the index on >>a column of type bigint. > > > Eh? That case will not change behavior in the slightest, because > there's no type conversion --- the literal is interpreted as the target > type to start with. > > regards, tom lane >
Barry Lind <barry@xythos.com> writes:
> OK. My mistake. In looking at the regression failures in your post, I
> thought I saw errors being reported of this type. My bad.
Well, although that particular case isn't a problem, I am sure that this
change will break some existing applications --- the question is how
many, and do we feel that they're all poorly coded?
I suspect that the main thing that will cause issues is removal of
implicit coercions to text. For example, in 7.2 and before you can do
test72=# select 'At the tone, the time will be ' || now(); ?column?
-------------------------------------------------------------At the tone, the time will be 2002-04-11
11:49:27.309181-04
(1 row)
since there is an implicit timestamp->text coercion; or in a less
plausible example,
test72=# select 123 || (33.0/7.0); ?column?
---------------------1234.71428571428571
(1 row)
With my proposed changes, both of these examples will require explicit
casts. The latter case might not bother people but I'm sure that
someone out there is using code much like the former case.
Since I didn't see an immediate batch of squawks, I think I will go
ahead and commit what I have; we can always revisit the implicit-allowed
flag settings later.
regards, tom lane
...
> Since I didn't see an immediate batch of squawks, I think I will go
> ahead and commit what I have; we can always revisit the implicit-allowed
> flag settings later.
Squawk. But I haven't had time to look at the full ramifications of your
proposed change, so it is inappropriate to comment, right?
We have never been in complete agreement on the optimal behavior for
type coersion, but it seems that most users are blissfully ignorant of
the potential downsides of the current behavior. Another way to phrase
that would be to say that it actually does the right thing in the vast
majority of cases out in the field.
We'll probably both agree that it would be nice to avoid *hard coded*
rules of any kind for this, but do you share my concern that moving this
to a database table-driven set of rules will affect performance too
much?
- Thomas
Thomas Lockhart <lockhart@fourpalms.org> writes:
> We have never been in complete agreement on the optimal behavior for
> type coersion, but it seems that most users are blissfully ignorant of
> the potential downsides of the current behavior. Another way to phrase
> that would be to say that it actually does the right thing in the vast
> majority of cases out in the field.
Could be; we probably see more complaints about the lack of any coercion
path for particular cases than about inappropriate implicit coercions.
But we do see a fair number of the latter. (And in the cases where I've
resisted adding more coercions, it was precisely because I thought it'd
be dangerous to allow them implicitly --- that concern goes away once
we can mark a coercion function as not implicitly invokable.)
> We'll probably both agree that it would be nice to avoid *hard coded*
> rules of any kind for this, but do you share my concern that moving this
> to a database table-driven set of rules will affect performance too
> much?
AFAICT the performance cost is negligible: find_coercion_function has to
look at the pg_proc row anyway. The relevant change looks like
PointerGetDatum(oid_array), ObjectIdGetDatum(typnamespace));
! if (!HeapTupleIsValid(ftup))
! {
! ReleaseSysCache(targetType);
! return InvalidOid;
! }
! /* Make sure the function's result type is as expected, too */
! pform = (Form_pg_proc) GETSTRUCT(ftup);
! if (pform->prorettype != targetTypeId) { ReleaseSysCache(ftup);
- ReleaseSysCache(targetType);
- return InvalidOid; }
! funcid = ftup->t_data->t_oid;
! ReleaseSysCache(ftup); ReleaseSysCache(targetType); return funcid; }
--- 711,734 ---- Int16GetDatum(nargs), PointerGetDatum(oid_array),
ObjectIdGetDatum(typnamespace));
! if (HeapTupleIsValid(ftup)) {
+ Form_pg_proc pform = (Form_pg_proc) GETSTRUCT(ftup);
+
+ /* Make sure the function's result type is as expected */
+ if (pform->prorettype == targetTypeId && !pform->proretset &&
+ !pform->proisagg)
+ {
+ /* If needed, make sure it can be invoked implicitly */
+ if (isExplicit || pform->proimplicit)
+ {
+ /* Okay to use it */
+ funcid = ftup->t_data->t_oid;
+ }
+ } ReleaseSysCache(ftup); }
! ReleaseSysCache(targetType); return funcid; }
I do not see any reason not to install the mechanism; we can fine-tune
the actual pg_class.proimplicit settings as we get experience with them.
regards, tom lane
Since it seems we still want to debate this a little, I've modified the
initial set of implicit-coercion-allowed flags to allow silent coercions
from the standard datatypes to text. This un-breaks most of the
regression tests that were failing before. I still want to debate the
wisdom of allowing this, but there's no point in changing the regress
tests until we're agreed.
An interesting breakage that remained was that the foreign_key tests
were assuming a "text = integer" comparison would fail, while a
"varchar = integer" comparison would succeed ... which is not only
pretty bogus in itself, but becomes even more so when you notice that
there isn't a varchar = integer operator. Apparently, because we had
implicit coercions in *both* directions between text and integer,
the system couldn't figure out how to resolve text = integer; but
since there was an int->varchar and no varchar->int coercion, it
would resolve varchar = integer as varchar = integer::varchar.
With the attached settings, both cases are accepted as doing text =
int::text. I'm not convinced that this is a step forward; I'd prefer
to see explicit coercion needed to cross type categories. But that's
the matter for debate.
The lines marked XXX are the ones that I enabled since yesterday, and
would like to disable again:
implicit | result | input | prosrc
----------+-------------+-------------+--------------------------------------no | abstime | timestamp |
timestamp_abstimeno | abstime | timestamptz | timestamptz_abstimeno | box | circle |
circle_boxno | box | polygon | poly_boxyes | bpchar | char | char_bpcharyes |
bpchar | name | name_bpcharyes | char | text | text_charno | cidr | text
| text_cidrno | circle | box | box_circleno | circle | polygon | poly_circleno
| date | abstime | abstime_dateno | date | text | text_dateno | date |
timestamp | timestamp_dateno | date | timestamptz | timestamptz_dateyes | float4 | float8
|dtofyes | float4 | int2 | i2tofyes | float4 | int4 | i4tofno | float4 |
numeric | numeric_float4no | float4 | text | text_float4yes | float8 | float4 |
ftodyes | float8 | int2 | i2todyes | float8 | int4 | i4todyes | float8 |
int8 | i8todno | float8 | numeric | numeric_float8no | float8 | text |
text_float8no | inet | text | text_inetno | int2 | float4 | ftoi2no | int2
| float8 | dtoi2yes | int2 | int4 | i4toi2yes | int2 | int8 | int82no
| int2 | numeric | numeric_int2no | int2 | text | text_int2no | int4 |
float4 | ftoi4no | int4 | float8 | dtoi4yes | int4 | int2 | i2toi4yes |
int4 | int8 | int84no | int4 | numeric | numeric_int4no | int4 | text
|text_int4no | int8 | float8 | dtoi8yes | int8 | int2 | int28yes | int8
| int4 | int48no | int8 | numeric | numeric_int8no | int8 | text |
text_int8yes | interval | reltime | reltime_intervalno | interval | text | text_intervalyes
| interval | time | time_intervalno | lseg | box | box_diagonalno | macaddr
|text | text_macaddryes | name | bpchar | bpchar_nameyes | name | text |
text_nameyes | name | varchar | text_nameyes | numeric | float4 | float4_numericyes |
numeric | float8 | float8_numericyes | numeric | int2 | int2_numericyes | numeric |
int4 | int4_numericyes | numeric | int8 | int8_numericno | oid | text |
text_oidno | path | polygon | poly_pathno | point | box | box_centerno |
point | circle | circle_centerno | point | lseg | lseg_centerno | point | path
| path_centerno | point | polygon | poly_centerno | polygon | box | box_polyno
| polygon | circle | select polygon(12, $1)no | polygon | path | path_polyno |
reltime | int4 | int4reltimeno | reltime | interval | interval_reltimeyes | text |
char | char_textXXX | text | date | date_textXXX | text | float4 |
float4_textXXX | text | float8 | float8_textno | text | inet | network_showXXX
|text | int2 | int2_textXXX | text | int4 | int4_textXXX | text | int8
| int8_textXXX | text | interval | interval_textno | text | macaddr | macaddr_textyes
| text | name | name_textno | text | oid | oid_textXXX | text | time
| time_textXXX | text | timestamp | timestamp_textXXX | text | timestamptz |
timestamptz_textXXX | text | timetz | timetz_textno | time | abstime | select
time(cast($1as timestamp without time zone))no | time | interval | interval_timeno | time
|text | text_timeno | time | timestamp | timestamp_timeyes | time | timetz |
timetz_timeyes | timestamp | abstime | abstime_timestampyes | timestamp | date |
date_timestampno | timestamp | text | text_timestampyes | timestamp | timestamptz |
timestamptz_timestampyes | timestamptz | abstime | abstime_timestamptzyes | timestamptz | date |
date_timestamptzno | timestamptz | text | text_timestamptzyes | timestamptz | timestamp |
timestamp_timestamptzno | timetz | text | text_timetzyes | timetz | time |
time_timetzno | timetz | timestamptz | timestamptz_timetzno | varchar | int4 | int4_textno
| varchar | int8 | int8_textyes | varchar | name | name_text
(103 rows)
regards, tom lane
> I suspect that the main thing that will cause issues is removal of > implicit coercions to text. For example, in 7.2 and before you can do > > test72=# select 'At the tone, the time will be ' || now(); > ?column? > ------------------------------------------------------------- > At the tone, the time will be 2002-04-11 11:49:27.309181-04 > (1 row) I have seen this coding practice extremely often and would be very unhappy if it were not allowed any more. Imho automatic coercions are a good thing and should be done where possible. Other extensible databases also allow this without a cast. Imho the main culprit is finding a "number" format that will not loose precision when implicitly propagated to. Andreas
> The lines marked XXX are the ones that I enabled since yesterday, and > would like to disable again: > > implicit | result | input | prosrc > ----------+-------------+-------------+-------------------------------------- > no | varchar | int8 | int8_text Wow, I am completely at a loss why you would not allow implicit coercions that do not loose any data in the process. Coercing an int to float would in some cases loose precision. It is thus imho debateable what to do here, but not for the rest. I would think it a major step in the wrong direction. Andreas
"Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at> writes: > Wow, I am completely at a loss why you would not allow implicit coercions > that do not loose any data in the process. Haven't you read the previous threads? Implicit coercions are dangerous, because they cause the system to resolve operators in unexpected ways. See, eg, bug #484: http://archives.postgresql.org/pgsql-bugs/2001-10/msg00103.php http://archives.postgresql.org/pgsql-bugs/2001-10/msg00108.php I'm not by any means opposed to *all* implicit coercions, but cross-type-category ones strike me as bad news. In particular, if all datatypes have implicit coercions to text then type checking is pretty much a thing of the past :-( ... the system will be able to resolve nearly anything by interpreting it as a text operation. See above bug. I suspect you are going to argue that you are prepared to live with such misbehavior because it's too darn convenient not to have to write ::text. Well, maybe that is indeed the community consensus, but I want to see a discussion about it first. And in any case I want a fairly well-defined, circumscribed policy about which implicit coercions we will have. regards, tom lane
...
> Haven't you read the previous threads? Implicit coercions are
> dangerous, because they cause the system to resolve operators in
> unexpected ways.
Sure he's read the threads. The conclusion is *not* obvious, and any
blanket statement to that effect trivializes the issues in a non-helpful
way imho.
I'd like to see a continuing discussion of this before leaping to a
conclusion; now that we have (somewhat) more control over coersions some
additional tuning is certainly warranted but hopefully it will not
require removing reasonable and convenient behaviors.
- Thomas
Thomas Lockhart <thomas@fourpalms.org> writes:
> I'd like to see a continuing discussion of this before leaping to a
> conclusion; now that we have (somewhat) more control over coersions some
> additional tuning is certainly warranted but hopefully it will not
> require removing reasonable and convenient behaviors.
Absolutely --- that's why I started this latest round of discussion.
What I'm really looking for is a way that we can allow (some?) implicit
text coercions while getting rid of the sort of misbehavior exemplified
by that bug report I keep referring to. Has anyone got any ideas about
how to do that? It's one thing to say that "apples || oranges" should
be interpreted as "apples::text || oranges::text", but it is quite
another to say that "apples <= oranges" should be handled that way.
Also: now that we can create non-implicit coercion functions, I would
like to add functions for bool<->int, numeric<->text, and other
coercions that would be handy to have, but previously we resisted on
the grounds that they'd turn the type checking system into a joke.
But perhaps some of these *should* be allowed as implicit coercions.
I'd like to develop a well-thought-out policy for which coercions should
be implicit, rather than making ad-hoc decisions.
So far the only policy-like idea that I've had is to forbid cross-type-
category implicit coercions. That would solve the comparing-apples-to-
oranges problem; but if people think it'd cause too much collateral
damage, how about proposing some other rule?
regards, tom lane
...
> What I'm really looking for is a way that we can allow (some?) implicit
> text coercions while getting rid of the sort of misbehavior exemplified
> by that bug report I keep referring to. Has anyone got any ideas about
> how to do that? It's one thing to say that "apples || oranges" should
> be interpreted as "apples::text || oranges::text", but it is quite
> another to say that "apples <= oranges" should be handled that way.
Hmm. istm that we might need some information to travel with the
operators, not just the coersion functions themselves. We have a fairly
type-rich system, but need to preserve the ability to add types and a
*partial* set of functions and operators and get reasonable behaviors.
- Thomas
Thomas Lockhart <thomas@fourpalms.org> writes:
>> It's one thing to say that "apples || oranges" should
>> be interpreted as "apples::text || oranges::text", but it is quite
>> another to say that "apples <= oranges" should be handled that way.
> Hmm. istm that we might need some information to travel with the
> operators, not just the coersion functions themselves. We have a fairly
> type-rich system, but need to preserve the ability to add types and a
> *partial* set of functions and operators and get reasonable behaviors.
Could we do anything based on looking at the whole set of candidate
operators? For example, I think that the reason "apples || oranges"
is so appealing is that there really is only one way to interpret
the || operator; whereas of course there are lots of different <=
operators. Perhaps we could be more forgiving of implicit coercions
when there are fewer candidate operators, in some way? Again, something
based on type categories would make sense to me. Perhaps allow
cross-category implicit coercions only if there are no candidate
operators accepting the input's native category?
regards, tom lane
...
> Could we do anything based on looking at the whole set of candidate
> operators? For example, I think that the reason "apples || oranges"
> is so appealing is that there really is only one way to interpret
> the || operator; whereas of course there are lots of different <=
> operators. Perhaps we could be more forgiving of implicit coercions
> when there are fewer candidate operators, in some way? Again, something
> based on type categories would make sense to me. Perhaps allow
> cross-category implicit coercions only if there are no candidate
> operators accepting the input's native category?
Hmm. That might be a winner. Since everything can be turned into text,
there will always be a fallback to text available. Allow it for
operators which are text-only operators (are there any built in besides
"||"?) and disallow that fallback for the others?
One edge case which we are probably concerned about is the "typeless
literal". I'd guess that we make the right choice in *most* cases
already.
I guess I'm worried that we may be cutting back on the allowed implicit
coersions, when we might really be missing only one or two explicit
coersion functions to fill in the set. I haven't looked at that in a
while...
- Thomas
Tom Lane wrote: > Thomas Lockhart <thomas@fourpalms.org> writes: > >> It's one thing to say that "apples || oranges" should > >> be interpreted as "apples::text || oranges::text", but it is quite > >> another to say that "apples <= oranges" should be handled that way. > > > Hmm. istm that we might need some information to travel with the > > operators, not just the coersion functions themselves. We have a fairly > > type-rich system, but need to preserve the ability to add types and a > > *partial* set of functions and operators and get reasonable behaviors. > > Could we do anything based on looking at the whole set of candidate > operators? For example, I think that the reason "apples || oranges" > is so appealing is that there really is only one way to interpret > the || operator; whereas of course there are lots of different <= > operators. Perhaps we could be more forgiving of implicit coercions > when there are fewer candidate operators, in some way? Again, something > based on type categories would make sense to me. Perhaps allow > cross-category implicit coercions only if there are no candidate > operators accepting the input's native category? Yes, I think any solution will have to consider the number of possible conversions for a given mix of function/args. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026