I'm working on building a demonstration of this error, but I figured I'd ask the question first. BACKGROUND I've got two databases, stage and app. There is a postgres_fdw connection between them so that stage can see objects in the app database. The app database contains complex metadata that is used to describe data and the tables/views in stage used to load the data. I have built a view that generates the SQL for creating multiple staging tables and views. I have used PG ENUM types in several places in the metadata that are used in the generation of the stage objects. PROBLEM When I query the view in the app schema, the appropriate SQL is generated. If I save the SQL and run it in the stage database it is successful and creates the correct objects. However, after I create a FOREIGN TABLE in the stage database that references the app view, I get an error when I query that table. The error indicates a problem executing the view in the app database. The error highlights one of the app ENUM types not being defined. There is no direct reference to the ENUM in the view itself, but there is a reference in one of nested functions deep within the view. QUESTION Why is there an error in the app context only when I invoke the view through a FOREIGN TABLE. And more importantly, how do I get around it? DETAILS I am trying to create a demonstration of this error outside of this application so that all of the details can be exposed and debugged. [I've tried to anonymize the naming, so there may be some small issues but the error messages and basic structure is correct.] On the app database: app=> \dv meta.stage_objects List of relations Schema | Name | Type | Owner --------+---------------+------+---------- meta | stage_objects | view | postgres (1 row) app=> \dv+ meta.stage_objects List of relations Schema | Name | Type | Owner | Persistence | Size | Description --------+---------------+------+----------+-------------+---------+------------- meta | stage_objects | view | postgres | permanent | 0 bytes | (1 row) app=> \d+ meta.dataset_stage_objects View "meta.stage_objects" Column | Type | Collation | Nullable | Default | Storage | Description --------+------+-----------+----------+---------+----------+------------- name | name | | | | plain | sql | text | C | | | extended | View definition: SELECT x.name, meta.gen_stage_objects(x.name) AS sql FROM dsource x; app=> TABLE meta.stage_objects ; name | sql --------------+------------------------------------------------------------ ... (2 rows) And then, on the stage database: stage=> \det meta.stage_objects List of foreign tables Schema | Table | Server --------+---------------+-------- meta | stage_objects | app_db (1 row) stage=> \d meta.* Foreign table "meta.stage_objects" Column | Type | Collation | Nullable | Default | FDW options --------+------+-----------+----------+---------+---------------------- name | name | | | | (column_name 'name') sql | text | C | | | (column_name 'sql') Server: app_db FDW options: (schema_name 'meta', table_name 'stage_objects') stage=> TABLE meta.stage_objects ; ERROR: 42704: type "xyzxx_t" does not exist CONTEXT: PL/pgSQL function meta.gen_stage_object_xyzxx(public.xyzxx_t,public.abcaa_t,public.id) line 10 at EXECUTE SQL function "gen_stage_object_detail" statement 1 remote SQL command: SELECT name, sql FROM meta.stage_objects LOCATION: pgfdw_report_error, connection.c:813 The types "xyzxx_t" and "abcaa_t" both exist in schema "public" in the app database. These do not exist and are not needed in the stage database. -- MICHAEL@xxxxxxxxxx // FSF and EFF member // GNU Emacs sql.el maintainer
