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@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
