Re: CREATE FOREGIN TABLE LACUNA

On 14/03/2012 16:47, David Fetter wrote:
> On Wed, Mar 14, 2012 at 11:29:14AM -0400, Tom Lane wrote:
>> David Fetter <david(at)fetter(dot)org> writes:
>>> On Wed, Mar 14, 2012 at 10:27:28AM -0400, Tom Lane wrote:
>>>> Hm. That opinion seems to me to connect to the recently-posted
>>>> patch to make contrib/file_fdw enforce NOT NULL constraints.
>>>> Should we instead have the position that constraints declared for
>>>> foreign tables are statements that we can take on faith, and it's
>>>> the user's fault if they are wrong?
>>
>>> I think that's something FDWs need to be able to communicate to
>>> PostgreSQL. For example, something talking to another PostgreSQL
>>> would (potentially, anyhow) have access to deep knowledge of the
>>> remote side, but file_fdw would only have best efforts even for
>>> clever things like statistics.
>>
>> I think we're talking at cross-purposes. What you're saying seems
>> to assume that it's the system's responsibility to do something
>> about a constraint declared on a foreign table. What I'm suggesting
>> is that maybe it isn't.
>
> Actually, I'm suggesting that this behavior needs to be controlled,
> not system-wide, but per FDW, and eventually per server, table and
> column.

>> A constraint, ordinarily, would be enforced against table *updates*,
>> and then just assumed valid during reads. In the case of a foreign
>> table, we can't enforce constraints during updates because we don't
>> have control of all updates.
>
> I think that the situation will become a bit more nuanced than that.
> A FDW could delegate constraints to the remote side, and in principle,
> the remote side could inform PostgreSQL of all types of changes (DML,
> DCL, DDL).
>
>> Should we ignore declared constraints because they're not
>> necessarily true? Should we assume on faith that they're true?
>
> Neither. We should instead have ways for FDWs to say which
> constraints are local-only, and which presumed correct on the remote
> side. If they lie when asserting the latter, that's pilot error.
>

I don't understand what value would that bring. Do you propose that, if
a FDW declares a constraint as local-only, the planner should ignore
them but when declared as remote, it could use this information ?

Let me describe a simple use case we have in one of our web
applications, which would benefit from foreign keys on foreign tables.

The application has users, stored in a users table, which can upload
files. The files are stored on the server's filesystem, using one folder
per user, named after the user_id.

Ex:

/
1/
myfile.png
2/
myotherfile.png

This filesystem is accessed using the StructuredFS FDW, which maps a
file system tree to a set of columns corresponding to parts of the file
path: every file which path matches the pattern results in a row. Using
the aforementioned structure, the foreign table would have an user_id
column, and a filename column.

Now, the FDW itself cannot know that the foreign key will be enforced,
but as the application developer, I know that every directory will be
named after an user_id.

Allowing foreign keys on such a foreign table would allow us to describe
the model more precisely in PostgreSQL, and external tools could use
this knowledge too, even if PostgreSQL completely ignore them.
Especially ORMs relying on foreign keys to determine join conditions
between tables.

On the other hand, should foreign keys referencing a foreign table be
allowed too ? From a foreign table to another, from a local table to a
foreign table ?

Regards,

--
Ronan Dunklau