Обсуждение: Check constraint on foreign table using SQL function
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;
*/
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
Andreas Ulbrich <andreas.ulbrich@matheversum.de> writes:
> 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?
Like Adrian, I'm a bit suspicious whether this test script is creating
the objects in the schema you think it is. It might be all right as
long as you execute it as user "andreas", but certainly not without that.
Anyway, as far as your second question goes, the postgres_fdw wrapper
intentionally forces the search_path to be just "pg_catalog" in its
remote session. We're aware that this breaks carelessly written
triggers and CHECK functions and so on, but really such functions
are broken anyhow; they should not be assuming anything about what
search_path they're called with.
As far as the first question goes, that shouldn't happen and in my
testing here I couldn't replicate it. So that fuels some suspicion
as to whether you're really accessing the table you think you are.
Maybe there's a view or something also named "tab_b"?
regards, tom lane
On 12/25/2014 09:27 AM, Tom Lane wrote: > Andreas Ulbrich <andreas.ulbrich@matheversum.de> writes: >> 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? > > Like Adrian, I'm a bit suspicious whether this test script is creating > the objects in the schema you think it is. It might be all right as > long as you execute it as user "andreas", but certainly not without that. Forgot about the special case of schema_name = user_name. The first part of the script does have the username = schema_name(andreas) and the search_path seems to have "$user" at the start, so the objects should be in SCHEMA andreas. Still it would be nice to see confirmation of this. Also to verify, or not, what you mention below, other tab_b objects in the path. > > Anyway, as far as your second question goes, the postgres_fdw wrapper > intentionally forces the search_path to be just "pg_catalog" in its > remote session. We're aware that this breaks carelessly written > triggers and CHECK functions and so on, but really such functions > are broken anyhow; they should not be assuming anything about what > search_path they're called with. > > As far as the first question goes, that shouldn't happen and in my > testing here I couldn't replicate it. So that fuels some suspicion > as to whether you're really accessing the table you think you are. > Maybe there's a view or something also named "tab_b"? > > regards, tom lane > > -- Adrian Klaver adrian.klaver@aklaver.com
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.
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.
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
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
On 25.12.2014 23:50, Adrian Klaver wrote: > 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 >> >> > > O.K. I've seen. I tried again the plpgsql version: 1.) the INSERTS on testdb prints the correct search_path in the raise WARNING: test_name_b called: "$user", test, public, ext 2.) Here the TABLE ftab_B works; because the check constraint is not done (the constraint function is not called) Is there any deeper cause, why the check is done with SQL function and not with plpgsql in SELECT? It seems not necessary to do the check - for me. 3.) Is I try to make an INSERT from postgres to testdb, it failes. And here is the search path not set: WARNING: test_name_b called: pg_catalog I think, that's a gap in the fdw. My idea was the following: I wont do some refactoring. So a created a new DB with a new schema with the refactored data structure. To populate this new structure I wanted to use fdw-select to the old structure. The problem is, I can't do so much changes in the old structure. Further, qualified table access in the function makes them not reuseable in the new structure. O.K. the new idea is. Create a new 'export' schema in the old DB with a constraintless export formated tables (JSON), populate this in the old DB and then use fdw to these special designed tables. Thanks Andreas
On 12/26/2014 05:18 AM, Andreas Ulbrich wrote: > On 25.12.2014 23:50, Adrian Klaver wrote: >> 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 >>> >>> >> >> > O.K. I've seen. > I tried again the plpgsql version: > 1.) the INSERTS on testdb prints the correct search_path in the raise > WARNING: test_name_b called: "$user", test, public, ext > 2.) Here the TABLE ftab_B works; because the check constraint is not > done (the constraint function is not called) > Is there any deeper cause, why the check is done with SQL function and > not with plpgsql in SELECT? It seems not necessary to do the check - for > me. This, from what I gather, is the result of the SQL inlining process whereby SQL functions are optimized ahead of use. So the error shows up when Postgres walks through the SQL function to do the optimization. In the plpgsql function this does not happen. Someone with more knowledge of the internals will have to fill in the details. > 3.) Is I try to make an INSERT from postgres to testdb, it failes. > And here is the search path not set: > WARNING: test_name_b called: pg_catalog The search_path is set, it is just very restricted. > I think, that's a gap in the fdw. Well as Tom said in his post: "Anyway, as far as your second question goes, the postgres_fdw wrapper intentionally forces the search_path to be just "pg_catalog" in its remote session. We're aware that this breaks carelessly written triggers and CHECK functions and so on, but really such functions are broken anyhow; they should not be assuming anything about what search_path they're called with. " > > My idea was the following: I wont do some refactoring. So a created a > new DB with a new schema with the refactored data structure. To populate > this new structure I wanted to use fdw-select to the old structure. The > problem is, I can't do so much changes in the old structure. Further, > qualified table access in the function makes them not reuseable in the > new structure. > > O.K. the new idea is. Create a new 'export' schema in the old DB with a > constraintless export formated tables (JSON), populate this in the old > DB and then use fdw to these special designed tables. Well fdw is not the only game in town, there is also dblink: http://www.postgresql.org/docs/9.3/interactive/dblink.html > > Thanks > Andreas > > -- Adrian Klaver adrian.klaver@aklaver.com