Check constraint on foreign table using SQL function

[Andreas Ulbrich <andreas.ulbrich@xxxxxxxxxxxxxx>]

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

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;
*/



--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general