On Mon, May 30, 2016 at 3:01 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "David G. Johnston" <david.g.johnston@gmail.com> writes:
> > On Mon, May 30, 2016 at 1:10 PM, Tom Lane <tgl@sss.pgh.pa.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.
Ok, but do you really not want to handle (or at least explore) this
particular situation that will result in a dump-restore hazard - while
appearing as fully functioning in all other aspects.
I suspect that these records are not checked on COPY TO reading either but
that they are checked when going through COPY FROM - at which point they
will invoke an error.
Is there maybe some way to mark the datum coming from FDW as being dirty
which would preempt optimizations (or force some kind of cleaning) when
writing to a permanent table? We could supply "cleaning" functions to
remove the dirty bit for the included types.
David J.