Re: Foreign table permissions and cloning

On Thu, Apr 14, 2011 at 8:26 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> 1. Accepting non-functional constraint declarations is something we've
> been heard to ridicule mysql for.  With good reason.
>
> 2. It probably won't be too long before the planner makes optimization
> decisions that assume NOT NULL declarations to be truthful.  When that
> day comes, I don't want to be seeing an exception for foreign tables in
> that logic.
>
> 3. When we do get around to making it actually work, we will have a
> backwards-compatibility problem if prior versions accepted the
> declaration but treated it as a no-op.

The planner *already* makes optimization decisions that assume NOT
NULL declarations are truthful (see: left join reordering). That's
why we have them for foreign tables, and why we eventually will need
constraints as well.

Of course, we have no guarantee that the data on the foreign side
matches those constraints. But we don't have any guarantee that it
matches the data type declarations, either. We could insist that
every column must be of type text and allow NULLs, but that seems like
it would be losing rather a lot of the functionality. The point of a
datatype declaration, or a NOT NULL declaration, or any other such
thing with respect to foreign tables is that the user is making an
assertion about what the data looks like, hopefully with some
cooperation from the FDW. It's ridiculous to suppose that we will
really be able to control anything at all about the data on the
foreign side, even the number of columns. But our job is to shove
whatever information is present into the schema the user has
specified, or throw an error.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company