Re: FDW for PostgreSQL

Tom Lane wrote:
> Applied with a lot of revisions.

I am thrilled.

> As I mentioned earlier, I think it would be better to force the remote
> session's search_path setting to just "pg_catalog" and then reduce the
> amount of explicit schema naming in the queries --- any opinions about
> that?

I think that that would make the remore query much more readable.
That would improve EXPLAIN VERBOSE output, which is a user visible
improvement.

> I took out the checks on collations of operators because I thought they
> were thoroughly broken. In the first place, looking at operator names
> to deduce semantics is unsafe (if we were to try to distinguish equality,
> looking up btree opclass membership would be the way to do that). In the
> second place, restricting only collation-sensitive operators and not
> collation-sensitive functions seems just about useless for guaranteeing
> safety. But we don't have any very good handle on which functions might
> be safe to send despite having collatable input types, so taking that
> approach would greatly restrict our ability to send function calls at all.
>
> The bigger picture here though is that we're already relying on the user
> to make sure that remote tables have column data types matching the local
> definition, so why can't we say that they've got to make sure collations
> match too? So I think this is largely a documentation issue and we don't
> need any automated enforcement mechanism, or at least it's silly to try
> to enforce this when we're not enforcing column type matching (yet?).

I think that the question what to push down is a different question
from checking column data types, because there we can rely on the
type input functions to reject bad values.

Being permissive on collation issues would lead to user problems
along the lines of "my query results are different when I
select from a remote table on a different operating system".
In my experience many users are blissfully ignorant of issues like
collation and encoding.

What about the following design principle:
Only push down conditions which are sure to return the correct
result, provided that the PostgreSQL system objects have not
been tampered with.

Would it be reasonable to push down operators and functions
only if
a) they are in the pg_catalog schema
b) they have been around for a couple of releases
c) they are not collation sensitive?

It makes me uncomfortable to think of a FDW that would happily
push down conditions that may lead to wrong query results.

> Another thing I was wondering about, but did not change, is that if we're
> having the remote transaction inherit the local transaction's isolation
> level, shouldn't it inherit the READ ONLY property as well?

That seems to me like it would be the right thing to do.

Yours,
Laurenz Albe