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

On Mon, May 30, 2016 at 1:10 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> kes-kes@yandex.ru writes:
> > Because of type of column of source and destination tables are equal
> > postgres does not check actual data so very long data at text field of
> mysql
> > database is inserted into limited field at postgres DB.
>
> 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.
>

K>>=E2=80=8B
4)
=E2=80=8BK>>=E2=80=8B
INSERT INTO files2_102009 SELECT * FROM files2_mysql;

=E2=80=8BI'm surprised that the definition of the object in the FROM clause=
 affects
behavior in this manner...

For the uninitiated while we do allow text type definitions to include
length restrictions the underlying storage model is the same for any and
all text variations you can legally describe (char, varchar, text,
varchar(n)) though some special behaviors occur during input/output
depending upon the type name and optional attribute.

I'd be more inclined to play with this if it didn't require such a specific
combination of technologies to readily reproduce.

I'd at least like to understand the implications this would have on our
system if it is allowed to stand.  The specific situation I'm thinking of
is a table that didn't require a toast but is now faced with a text value
too large to fit onto a page.

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.

David J.