Re: Check constraint on foreign table using SQL function

On 12/26/2014 05:18 AM, Andreas Ulbrich wrote:
> On 25.12.2014 23:50, Adrian Klaver wrote:
>> On 12/25/2014 11:21 AM, Andreas Ulbrich wrote:
>>> Hey.
>>>
>>> In the first part I connect to testdb (the remote db). Here I create the
>>> schema 'andreas'. That the search_path correct is, shows the output of
>>> the pgplsql version.
>>
>> Yeah, that was a false alarm on my part. Forgot about $user in the path.
>>>
>>> Then, in the second part, I'm postgres (this db ist empty since
>>> compiling).
>>> The usermapping says, I wont to be andreas@testdb.
>>> The plpgsql-version (and the other work arround: schema qualified, set
>>> search_path) shows, that my assumptions are correct.
>>>
>>> First I'm wondering, why are the constraints are checked in select, but
>>> this is not the main problem, because, if I would do an INSERT, I will
>>> get the same problem.
>>
>> The issue seems to begin here:
>>
>> CREATE TABLE IF NOT EXISTS tab_b (
>>      id INTEGER PRIMARY KEY,
>>      id_a INTEGER NOT NULL REFERENCES tab_a,
>>      name TEXT,
>>      CHECK(test_name_b(id_a, name))
>>    );
>> The CHECK calls test_name_b() which has
>>
>> SELECT $2 = name FROM tab_a WHERE id = $1  in it
>>
>> As Tom said fdw calls have a limited search_path and the tab_b table
>> is not schema qualified in the function, so:
>>
>> 2014-12-24 13:11:27 CET andreas(at)testdb QUERY:   SELECT $2 = name FROM
>> tab_a WHERE id = $1
>> 2014-12-24 13:11:27 CET postgres(at)postgres ERROR:  relation "tab_a"
>> does
>> not exist
>> 2014-12-24 13:11:27 CET postgres(at)postgres CONTEXT:  Remote SQL
>> command:
>> SELECT id, id_a, name FROM andreas.tab_b
>>          SQL function "test_name_b" during inlining
>>
>> As you found out you need to be explicit about your schemas when going
>> through fdw. Either schema qualify object names of set explicit
>> search_path,
>>
>> All this starts when you try to create the foreign table:
>>
>> 2014-12-24 13:11:27 CET postgres(at)postgres STATEMENT:  TABLE ftab_b;
>>
>>
>>>
>>> I believe, that the "inlining" does not use the search_path set in the
>>> ALTER DATABASE.
>>>
>>> Here is a \d output before the END of the transaction in the first part.
>>>                List of relations
>>>   Schema  |   Name   |     Type      |  Owner
>>> ---------+----------+---------------+---------
>>>   andreas | tab_a    | table         | andreas
>>>   andreas | tab_b    | table         | andreas
>>>   test    | unaccent | foreign table | test
>>>
>>> Regards
>>> Andreas
>>>
>>>
>>
>>
> O.K. I've seen.
> I tried again the plpgsql version:
> 1.) the INSERTS on testdb prints the correct search_path in the raise
> WARNING:  test_name_b called: "$user", test, public, ext
> 2.) Here the TABLE ftab_B works; because the check constraint is not
> done (the constraint function is not called)
> Is there any deeper cause, why the check is done with SQL function and
> not with plpgsql in SELECT? It seems not necessary to do the check - for
> me.

This, from what I gather, is the result of the SQL inlining process
whereby SQL functions are optimized ahead of use. So the error shows up
when Postgres walks through the SQL function to do the optimization. In
the plpgsql function this does not happen. Someone with more knowledge
of the internals will have to fill in the details.

> 3.) Is I try to make an INSERT from postgres to testdb, it failes.
> And here is the search path not set:
> WARNING: test_name_b called: pg_catalog

The search_path is set, it is just very restricted.

> I think, that's a gap in the fdw.

Well as Tom said in his post:

"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.
"

>
> My idea was the following: I wont do some refactoring. So a created a
> new DB with a new schema with the refactored data structure. To populate
> this new structure I wanted to use fdw-select to the old structure. The
> problem is, I can't do so much changes in the old structure. Further,
> qualified table access in the function makes them not reuseable in the
> new structure.
>
> O.K. the new idea is. Create a new 'export' schema in the old DB with a
> constraintless export formated tables (JSON), populate this in the old
> DB and then use fdw to these special designed tables.

Well fdw is not the only game in town, there is also dblink:

http://www.postgresql.org/docs/9.3/interactive/dblink.html

>
> Thanks
> Andreas
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com