Support schema change notifications and introspection reloading from PostgreSQL

[jmealo]

This issue serves as a discussion for adding schema change notifications and introspection reloading support to PostgreSQL.

Relevant documentation pages:

• https://www.postgresql.org/docs/10/static/event-trigger-definition.html
• https://www.postgresql.org/docs/10/static/event-trigger-matrix.html
• https://www.postgresql.org/docs/10/static/event-trigger-table-rewrite-example.html

The following event triggers seem like a good start for most introspection/schema tasks.

ALTER TABLE
ALTER SEQUENCE
ALTER SCHEMA
ALTER TABLE
ALTER VIEW

CREATE SCHEMA
CREATE SEQUENCE
CREATE TABLE
CREATE TABLE AS
CREATE VIEW

DROP SCHEMA
DROP SEQUENCE
DROP TABLE
DROP VIEW

GRANT

It would be trivial to support all events and push filtering into the consumer or using code generation.

It appears that the plpgsql example shows a good path forward as we can emit generic WAL messages easily.

I think it makes sense to specify an introspection function or materialized view and either refresh that view or emit the output of the function as a generic WAL message.

Optionally, we could emit the event trigger as a generic WAL message or NOTIFY and allow processing else where.

The question becomes whether we package schema reloading as an installable extension or suggest that this functionality is pushed into jsoncdc.

Pinging relevant parties on the following issues:

• Restart required for detecting table relations on schema change PostgREST/postgrest#475
• Feature Request/Discussion: Schema changes instructure/jsoncdc#3

[jmealo]

https://www.graphile.org/postgraphile/usage-schema/