Re: Check constraint on foreign table using SQL function

On 12/25/2014 03:31 AM, Andreas Ulbrich wrote:
> Salvete!
>
> I've made the following observation:
> I have a foreign table (postgres_fdw) to a remote table B.
> On the remote table is a check constraint using a SQL-function with
> access to an other table.
>
> In the remote DB both tables and the check-function resist in a seperate
> schema "andreas".

 From below they don't. CREATE SCHEMA, does just that, it creates a
SCHEMA, it does not assign subsequent objects to itself, nor add itself
to the search_path. You do not use schema qualified names in your table,
function DDLs so they are assigned to the first schema(test I believe)
in the search_path. See here for more detail:

http://www.postgresql.org/docs/9.3/static/ddl-schemas.html

You can confirm by doing \d in testdb at the psql command line.


> This schema is in the search_path via
> ALTER DATABASE testDB SET search_path TO "$user", test, public, ext;
>
> If I set the search_path in the function definition (see comment), it
> works,
> if I use 'FROM andreas.tab_a' too.
>
> If I use the plPgSQL function it works and the raise prints
> psql:s_andreas.sql:39: WARNING:  test_name_b called: "$user", test,
> public, ext
>
> 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?

The search_path is there(you see it in the plpsql function), you are
just telling the FDW the wrong place to look for the table. In other
words you are telling it to look for andreas.tab_b. Because of the above
creation script, the table is actually at (I believe) test.tab_b. Try
without the schema qualification in the fdw table definition. The
difference in behavior between functions is I believe due to inlining of
SQL functions versus later execution in plpgsql functions.

>
> Im using the 9.4.0 version, I havnt't checked in other versions
>
> Thanks and Merry Christmas
> Regards
> Andreas
>
>
>
> Here is a complete example:
> \connect testdb andreas
> BEGIN;
>    CREATE SCHEMA andreas;
>
>    CREATE TABLE IF NOT EXISTS tab_a (
>      id INTEGER PRIMARY KEY,
>      name TEXT NOT NULL UNIQUE
>    );
>
>    INSERT INTO tab_A
>      SELECT i, md5(i::TEXT) FROM Generate_Series(1, 10) AS i;
>
>    CREATE OR REPLACE FUNCTION test_name_b(INTEGER,TEXT) RETURNS BOOLEAN AS
>    $$ SELECT $2 = name FROM tab_a WHERE id = $1 $$
>    LANGUAGE SQL
>    -- SET search_path TO "$user", test, public, ext
>    ;
>
> /*
>    CREATE OR REPLACE FUNCTION test_name_b(INTEGER,TEXT) RETURNS BOOLEAN
> AS $$
>    DECLARE
>      res BOOLEAN;
>      path TEXT;
>    BEGIN
>      SHOW search_path INTO path;
>      RAISE WARNING 'test_name_b called: %', path;
>      SELECT $2 = name INTO res FROM tab_a WHERE id = $1;
>      RETURN res;
>    END $$ LANGUAGE plPgSQL;
> */
>
>    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))
>    );
>
>    INSERT INTO tab_B
>      SELECT i, i, md5(i::TEXT) FROM Generate_Series(1, 10) AS i;
>
> END;
>
> -- For housekeeping:
> -- DROP SCHEMA IF EXISTS andreas CASCADE;
>
> \connect postgres postgres
> BEGIN;
>    CREATE EXTENSION Postgres_FDW;
>    CREATE SERVER testSRV FOREIGN DATA WRAPPER Postgres_FDW
>           OPTIONS (host 'localhost', dbname 'testdb');
>    RESET ROLE;
>
>    CREATE USER MAPPING FOR postgres SERVER testSRV OPTIONS (user
> 'andreas', password 'a6');
>
>    CREATE FOREIGN TABLE IF NOT EXISTS ftab_b (
>      id INTEGER NOT NULL,
>      id_a INTEGER NOT NULL,
>      name TEXT
>    ) SERVER testSRV OPTIONS (table_name 'tab_b', schema_name 'andreas');
> \det+
>
>   TABLE ftab_b;
> ROLLBACK;
>
> \connect testdb andreas
> DROP SCHEMA IF EXISTS andreas CASCADE;
>
> /*
> psql:s_andreas.sql:63: ERROR:  relation "tab_a" does not exist
> CONTEXT:  Remote SQL command: SELECT id, id_a, name FROM andreas.tab_b
> SQL function "test_name_b" during inlining
>
> Server log:
>
> 2014-12-24 13:11:27 CET andreas@testdb ERROR:  relation "tab_a" does not
> exist at character 24
> 2014-12-24 13:11:27 CET andreas@testdb QUERY:   SELECT $2 = name FROM
> tab_a WHERE id = $1
> 2014-12-24 13:11:27 CET andreas@testdb CONTEXT:  SQL function
> "test_name_b" during inlining
> 2014-12-24 13:11:27 CET andreas@testdb STATEMENT:  DECLARE c1 CURSOR FOR
>          SELECT id, id_a, name FROM andreas.tab_b
> 2014-12-24 13:11:27 CET postgres@postgres ERROR:  relation "tab_a" does
> not exist
> 2014-12-24 13:11:27 CET postgres@postgres CONTEXT:  Remote SQL command:
> SELECT id, id_a, name FROM andreas.tab_b
>          SQL function "test_name_b" during inlining
> 2014-12-24 13:11:27 CET postgres@postgres STATEMENT:  TABLE ftab_b;
> */
>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com