Support PostgreSQL postgres_fdw extension: Foreign Data Wrappers

[janpio]

Problem

PostgreSQL offers the postgres_fdw extension for Foreign Data Wrappers, "which can be used to access data stored in external PostgreSQL servers".

https://www.postgresql.org/docs/9.5/postgres-fdw.html
https://www.postgresql.org/docs/9.5/sql-importforeignschema.html
https://www.percona.com/blog/2018/08/21/foreign-data-wrappers-postgresql-postgres_fdw/
https://thoughtbot.com/blog/postgres-foreign-data-wrapper

Suggested solution

Prisma should work with servers configured using postgres_fdw.

Alternatives

None.

Additional context

#8610

[janpio]

Prisma currently can not introspect foreign tables like this one:

CREATE FOREIGN TABLE hackernews.hackernews_ask_hn (
	id int8 NULL,
	title text NULL,
	"time" timestamptz NULL,
	"by" text NULL,
	score int8 NULL,
	dead bool NULL,
	deleted bool NULL,
	descendants int8 NULL,
	kids jsonb NULL,
	parent int8 NULL,
	parts jsonb NULL,
	poll int8 NULL,
	"text" text NULL,
	"type" text NULL,
	url text NULL
)
SERVER steampipe
OPTIONS (table 'hackernews_ask_hn');

This is a table set up by cloud.steampipe.io (Sign up, create setup with "Hackernews" setup, use credentials form "Connect" tab). It works fine in Dbeaver:

We should be able to support this in Prisma as well.

#13793 (comment)

[baconcheese113]

This would be awesome to support! Our use case would ensure that all foreign tables are read-only, not sure if that simplifies the problem for you.

[JEbertPrime]

@janpio I might be wrong, but is fixing this as simple as making an edit to the base SQL query to include foreign tables in this file or this file in the schema engine? Local testing using the binaries generated from my fork correctly introspected the foreign tables in my database

[janpio]

Yes and no. These changes might be enough for Introspection to pick these up, and represent them as models. But migrating that Prisma schema would just create normal tables. We could also not use any other potential functionality that they might bring (if any, did not read through the docs now to refresh my memory).

As you are using these, maybe you can help me answer these questions:
What other functionality than just Introspection and Migration would make sense for Foreign Data Wrappers?
Can these only be used via the extension mentioned above?

[JEbertPrime]

To answer your questions, insofar as I can -
1 - In my use case and the one mentioned above, foreign tables are all read-only (because I'm using the file_fdw extension). It may be convenient to just ignore such tables when running migrations, but I don't know if that runs counter to how Prisma likes to do things. Beyond that it would be hard to come up with a list of functionalities since there are so many different types of FDWs
2 - Sort of addressed this before, but there are several native and non-native FDW extensions available for use that all have unique features. But the native extensions all attempt to use standard SQL for querying the foreign tables, and generally they can be queried like any other table (at least for read operations)

[JEbertPrime]

Have some time to come back to this issue - since full support for all foreign tables seems like a whole bag of worms based on other dependent features, would it be prudent to work on adding in the kind of warning outlined in #16311 ? Is there an existing standard for outputting warnings during introspection, such as this PR on the schema engine repo?

[janpio]

Yes, that was how we started to warn about these. Not sure what the state about FDWs is over there though.

[nipu-softic]

Error: ERROR: extension "postgres-fdw" is not available
DETAIL: Could not open extension control file "C:/Program Files/PostgreSQL/16/share/extension/postgres-fdw.control": No such file or directory.
HINT: The extension must first be installed on the system where PostgreSQL is running.
   0: sql_schema_connector::apply_migration::migration_step
           with step=CreateExtension(CreateExtension { id: ExtensionId(0) })
             at schema-engine\connectors\sql-schema-connector\src\apply_migration.rs:21
   1: sql_schema_connector::apply_migration::apply_migration
             at schema-engine\connectors\sql-schema-connector\src\apply_migration.rs:10
   2: schema_core::state::SchemaPush
             at schema-engine\core\src\state.rs:436