Re: Check constraint on foreign table using SQL function

On 12/25/2014 09:27 AM, Tom Lane wrote:
> Andreas Ulbrich <andreas.ulbrich@matheversum.de> writes:
>> Questions:
>> Wy is the check constraint function in a select called?
>> The search_path seams not to be set for the SQL function, is this
>> behavior correct?
>
> Like Adrian, I'm a bit suspicious whether this test script is creating
> the objects in the schema you think it is.  It might be all right as
> long as you execute it as user "andreas", but certainly not without that.

Forgot about the special case of schema_name = user_name. The first part
of the script does have the username = schema_name(andreas) and the
search_path seems to have "$user" at the start, so the objects should be
in SCHEMA andreas. Still it would be nice to see confirmation of this.
Also to verify, or not, what you mention below, other tab_b objects in
the path.

>
> Anyway, as far as your second question goes, the postgres_fdw wrapper
> intentionally forces the search_path to be just "pg_catalog" in its
> remote session.  We're aware that this breaks carelessly written
> triggers and CHECK functions and so on, but really such functions
> are broken anyhow; they should not be assuming anything about what
> search_path they're called with.
>
> As far as the first question goes, that shouldn't happen and in my
> testing here I couldn't replicate it.  So that fuels some suspicion
> as to whether you're really accessing the table you think you are.
> Maybe there's a view or something also named "tab_b"?
>
>             regards, tom lane
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com