Re: MySQL search query is not executing in Postgres DB - Mailing list pgsql-hackers

On Tue, Nov 27, 2012 at 1:45 PM, Jeff Davis <pgsql@j-davis.com> wrote:
> On Tue, 2012-11-27 at 12:19 -0500, Robert Haas wrote:
>> I admit that there are cases where this could happen, and that it will
>> happen a little more than it does now.  But, as you say, this can
>> happen now, and yet we get very few if any complaints about it,
>> whereas we get regular complaints about the need to insert casts that
>> other database systems do not require.  The fact is that most
>> functions are not overloaded, so the esoterica of overloading affect
>> only a tiny number of relatively sophisticated users.  The need for
>> extra casts cuts a much broader swath through our user base.
>
> Well, I did offer a suggestion that would make your idea safer, which is
> to explicitly opt out of the overloading feature at the time the
> function is created, rather than making it implicit based on how many
> functions happen to have the same name.
>
> The fact that it can only hurt sophisticated users is not convincing to
> me. For one thing, our users are programmers, so they should all feel
> comfortable defining their own functions, and I don't want to make them
> any less so. Next, sophisticated users also make mistakes.
>
> I could also make a security argument. Even today, any user who can
> create a function in your search path can make your queries start
> failing. If we locked down most of the system-defined functions as
> non-overloadable, and allowed users to do the same for their functions
> (maybe even the default one day?), then that would greatly reduce the
> exposure.
>
> The current strictness of the overloaded functions tends to make users
> more explicit about argument types, which reduces the chance of problems
> at the expense of usability and compatibility. Not ideal, but if we make
> it more permissive then we are permanently stuck with less information
> about what types the user intended and which function they intended to
> call. In such an extensible system, that worries me on several fronts.
>
> That being said, I'm not outright in opposition to the idea of making
> improvements like this, I just think we should do so cautiously.

Fair enough.  I certainly admit that I wouldn't like to release with
this code in place and then find out that it's got some critical flaw,
security or otherwise.  A couple of embarrassing bugs have been found
recently in patches I wrote and committed, and I'm not looking to up
that number.  That having been said, I remain unconvinced that any of
the things proposed so far are compelling reasons not to do this.
That doesn't mean there aren't any such reasons, but I am personally
unconvinced that we've found them yet.  Most of the arguments so far
advanced seem to involve overloading (where this proposal doesn't
change anything vs. today); I think you're the only one who has
proposed a situation where it causes a problem (namely, a function
that is overloaded later) but in my personal opinion that's not going
to happen often enough to justify the amount of user pain the current
system imposes.  Of course that's a judgement call.

I do think that applying some kind of explicit flag to the function
indicating whether it should allow implicit assignment
casting/implicit casting to text/overloading/whatever is a possibly
interesting alternative.  It seems clear from our system catalogs that
implicit casting to text is sometimes a desirable behavior and
sometimes not, so it's reasonable to think that perhaps we should put
that under user control.  What I like about "my" proposal (really
Tom's idea) is that it seems like it solves a pretty high percentage
of the problem cases without requiring any explicit user action.  I
actually suspect we could get the "right" behavior even more often by
attaching flags to the function or argument position, but that would
also put more of the onus on the user to get the flags right -- and we
might not even agree amongst ourselves on how the flags should be set.The fact that quote_literal() allows (by the
expedientof
 
overloading) implicit casts to text and that lpad() does not seems
fairly random to me in hindsight; is there a general principle there
that we'd all sign on to?  The nice thing about this proposal is that
it doesn't require any explicit user action.  Of course that's no help
if it does the wrong thing, but since it only fixes cases that are
unambiguous and which currently fail, it's hard for me to see how
that's a real danger.  That doesn't mean there ISN'T a real danger,
but I want to make sure that if we don't do this we have a clear and
understandable reason, and not just bad memories of the last time we
made a change in this area.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company