FDW error on remote view

Поиск
Список
Период
Сортировка
От Michael Mauger
Тема FDW error on remote view
Дата
Msg-id pThtLzmXu5kojcHMpyrmR0Q_ohXEMKxSzjeoO1jl88KA4nCZFnZz175HHrqxWl3qWWZftVshZs-LlUSi9nXfcomfjsvoUNgGsCF2nvhbez8=@protonmail.com
обсуждение исходный текст
Ответы Re: FDW error on remote view  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-general
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@MAUGER.COM // FSF and EFF member // GNU Emacs sql.el maintainer



В списке pgsql-general по дате отправления:

Предыдущее
От: Mladen Gogala
Дата:
Сообщение: Re: Can we go beyond the standard to make Postgres radically better?
Следующее
От: Tom Lane
Дата:
Сообщение: Re: FDW error on remote view