Re: CREATE FOREGIN TABLE LACUNA - Mailing list pgsql-hackers

On Wed, Mar 14, 2012 at 11:29:14AM -0400, Tom Lane wrote:
> David Fetter <david@fetter.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.

> The posted patch for file_fdw takes the approach of silently
> filtering out rows for which they're not true, which is not
> obviously the right thing either --- quite aside from whether that's
> a sane semantics,

It clearly is for the author's use case.  Other use cases will differ.

> it's not going to scale to foreign key constraints, and even for
> simple NOT NULL and CHECK constraints it results in a runtime
> penalty on selects, which is not what people would expect from a
> constraint.

If people expect FK constraints on, say, a Twitter feed, they're
riding for a very hard fall.  If they expect them on a system with
several PostgreSQL nodes in it, that could very well be reasonable.

Cheers,
David.
-- 
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate