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

The convention here is to inline or bottom post.

30.05.2016, 20:10, "Tom Lane" <tgl@sss.pgh.pa.us>:
> > 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 o=
f
> 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.
>
>
=E2=80=8B
=E2=80=8B
On Mon, May 30, 2016 at 2:28 PM, KES <kes-kes@yandex.ru> wrote:

> To my mind, postgres should not rely on third party code and write broken
> data to the disk.
>

=E2=80=8B=E2=80=8BIt isn't quite this cut-and-dry.  The user takes some res=
ponsibility for
the stuff they load into their database.

https://www.postgresql.org/docs/9.6/static/sql-createforeigntable.html
"""
=E2=80=8BAlthough PostgreSQL does not attempt to enforce constraints on for=
eign
tables, it does assume that they are correct for purposes of query
optimization. If there are rows visible in the foreign table that do not
satisfy a declared constraint, queries on the table might produce incorrect
answers. It is the user's responsibility to ensure that the constraint
definition matches reality.
"""

While this isn't a table constraint it seems the same provision applies to
data types and their attributes.

I suspect that the only reason this example gets through whatever checks
are present is because of the fact that varchar(n) is just, basically, a
domain over text.

I suppose this may override other records (even those the user have no
> access) which are stored close to this broken one if data in the 'text'
> field of mysql are large enough.
>

=E2=80=8BJust because PostgreSQL doesn't validate that the length is within=
 the
constrained limit doesn't impact how it gets stored.  PostgreSQL will
measure the length as it writes the data and request the necessary amount
of free memory/space to hold it.  That is what I was =E2=80=8Btrying to poi=
nt out
with my brief description of the implementation of the text data type.

I'll add my last comment to Tom's most recent reply.

=E2=80=8BDavid J.=E2=80=8B