Horton

Thread

Commits

  1. Doc: improve protocol spec for logical replication Type messages.

  1. Logical Replication - Type messages?

    Stefen Hillman <stefen.hillman@fivetran.com> — 2021-11-08T19:16:57Z

    Hello psql-general,


I have an application which processes logical replication output from a
PostgreSQL server. We create publications and read data from a replication
slot with the pgoutput plugin.

Postgres has various message formats for logical replication, as defined
here:
https://www.postgresql.org/docs/10/protocol-logicalrep-message-formats.html

Currently, I process Relation messages to get most of the information I
need to work with Inserts, Updates, and Deletes. However, for type
information I'm currently using a SQL query to get the column type
information. I wanted to use the Type messages, but I never see them. I see
Begin, Commit, etc. but never Type.

   - When are the Type messages generated and sent by the server?
   - Is there a way to cause them to be generated?
   - Are these type id > type mappings set in stone, or does PostgreSQL
   allow for a server to have custom types (I notice that namespace is one of
   the properties sent with the types)?

Any help is greatly appreciated - thank you!


Best regards,


Stefen Hillman

  2. Re: Logical Replication - Type messages?

    Tom Lane <tgl@sss.pgh.pa.us> — 2021-11-10T16:48:52Z

    Stefen Hillman <stefen.hillman@fivetran.com> writes:
> Currently, I process Relation messages to get most of the information I
> need to work with Inserts, Updates, and Deletes. However, for type
> information I'm currently using a SQL query to get the column type
> information. I wanted to use the Type messages, but I never see them. I see
> Begin, Commit, etc. but never Type.

>    - When are the Type messages generated and sent by the server?
>    - Is there a way to cause them to be generated?

Hmm.  AFAICS the Type messages are completely undocumented in
protocol.sgml, which is an oversight.  Looking at the code, they
do exist, but they are sent only when a Relation message refers
to a non-built-in type.  There's a presumption that built-in
types have stable OIDs that will be the same at publisher and
subscriber, so the replication traffic needn't tell the subscriber
what those OIDs mean.  Non-built-in types don't have stable OIDs,
so we want to tell the subscriber what those OIDs mean.

(Actually, it looks like the cutoff is FirstGenbkiObjectId, which
means that the Type message is only suppressed for hand-assigned
type OIDs, an even stricter rule than "built in".  So for testing
purposes you could try something like an array type; replicating
a table with an array column should give rise to Type messages.)

>    - Are these type id > type mappings set in stone, or does PostgreSQL
>    allow for a server to have custom types (I notice that namespace is one of
>    the properties sent with the types)?

Some would say that custom types are THE defining feature of
Postgres, compared to other SQL implementations.

			regards, tom lane