Re: Check constraint on foreign table using SQL function

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


--
Adrian Klaver
adrian.klaver@aklaver.com