Check constraint on foreign table using SQL function

From: Andreas Ulbrich <andreas(dot)ulbrich(at)matheversum(dot)de>
To: pgsql-general(at)postgresql(dot)org
Subject: Check constraint on foreign table using SQL function
Date: 2014-12-25 11:31:43
Message-ID: 549BF59F.7000305@matheversum.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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(at)testdb ERROR: relation "tab_a" does not
exist at character 24
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 andreas(at)testdb CONTEXT: SQL function
"test_name_b" during inlining
2014-12-24 13:11:27 CET andreas(at)testdb STATEMENT: DECLARE c1 CURSOR FOR
SELECT id, id_a, name FROM andreas.tab_b
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
2014-12-24 13:11:27 CET postgres(at)postgres STATEMENT: TABLE ftab_b;
*/

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2014-12-25 17:16:14 Re: Check constraint on foreign table using SQL function
Previous Message Adrian Klaver 2014-12-24 17:36:36 Re: logging of Logical Decoding