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(at)sss(dot)pgh(dot)pa(dot)us>:
> > kes-kes(at)yandex(dot)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.
>
>
​
​
On Mon, May 30, 2016 at 2:28 PM, KES <kes-kes(at)yandex(dot)ru> wrote:

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

​​It isn't quite this cut-and-dry. The user takes some responsibility for
the stuff they load into their database.

https://www.postgresql.org/docs/9.6/static/sql-createforeigntable.html
"""
​Although PostgreSQL does not attempt to enforce constraints on foreign
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.
>

​Just 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 ​trying to point 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.

​David J.​