Re: BUG #14164: Postgres allow to insert more data into field than this field allow

"David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> On Mon, May 30, 2016 at 1:10 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> I would say this is a bug in mysql_fdw, not Postgres proper. In general
>> it's the responsibility of an FDW to ensure that data it passes back
>> meets the constraints of the foreign table declaration.

> At some point in this SELECT->INSERT process there has to be a recognition
> that the data we are seeing is external and needs to be validated against
> the rules and assumptions of PostgreSQL. It's not our fault that mysql_fdw
> is broken but it is our fault that we allowed its brokenness to result is
> non-conforming persisted data.

> Either the SELECT should fail because a PostgreSQL varchar(255) cannot hold
> longer data (probably this) or the INSERT should fail so that at least the
> impact of the broken varchar is limited to an active query and doesn't make
> it onto disk.

Indeed, the SELECT should have failed, and it's mysql_fdw that should have
issued the error. We cannot expect to protect users against every sort of
malfeasance or misfeasance that might occur in extension C code. Consider
for example that the Datum mysql_fdw is handing back and claiming to be
varchar(255) might not be textual at all, or it might be in the wrong
encoding, etc etc. It wouldn't even be possible for the core code to
detect some of those cases, and it certainly would impose a lot of
overhead to add checking that should be redundant.

(BTW, I checked this and verified that an equivalent case in postgres_fdw
does throw an error; so it's *possible* for an FDW to get this right.
mysql_fdw simply doesn't.)

regards, tom lane