Re: Implicit coercions need to be reined in - Mailing list pgsql-hackers

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