Used my fork which supports Message logical replication messages.

select * is not future proof, please consider specifying the column list. In this case we only seem to care about 2, so something like this:

The _object_type and _objid need to be declared of course, names are arbitrary.

Consider renaming to origin_table_oid or something similar.

Note: local table oid on the downstream doesn't have any meaning. It might be useful for oid mapping and general forensics, but we shouldn't rely on it: downstream has it's own oids.

Nit: oid is int4, not int8, casting to bigint is an overkill :-)

This is done on purpose, from the docs it seems like oid is unsigned int4, meaning that the domain of positive values is * 2 the one for signed int4. So, we have to use int8 to represent all possible values.

If that's not true, happy to change it.

For our system we need the table_id of the source Postgres table. I don't know if I misread your comment.

This is done on purpose, from the docs it seems like oid is unsigned int4

good point

For our system we need the table_id of the source Postgres table. I don't know if I misread your comment.

Perhaps I don't understand the intent. I was just pointing out that on the downstream node the oid itself won't refer to the same object, so it's worth specifying that it's the oid of the table on the origin

We are relying on the oid to uniquely identify the table in our state (used by ETL to track progress) and across the entire system.

Perhaps it's worth adding some more context for the user in the detail?

DETAIL: You might need to repeat this command on the downstream to keep logical replication running or something to that effect. Otherwise this is an immediate support request, or something to that effect.

table_oid as a number might be of limited use. Perhaps worth adding the table_name if we have it. One simple trick could be table_oid::regclass::text. Then we'll get either the (qualified) table name (if such a table exists) or the oid as text.

Note: tables can have more than one parent. It is not very popular but is possible. In that case we pick one at random. Perhaps we should handle that better?
What are we trying to achieve here?

We want to fetch the parent of a partitioned table since we replicate partitioned tables as one big table.

Are we doing this to then rebuild the constraint on the downstream?
Perhaps worth to consider pg_get_constraintdef?

Yes, we are building the constraint in the downstream table.

Nit: v_wal_level != 'logical' should be enough. (NULL != 'logical' is true).

Can wal_level be null?

Won't this break when we have more than one attribute as part of the PK?

This is something I considered. Since ETL (the current version in prod), doesn't support schema change, we could technically just run a query to determine the current primary keys and backfill them properly. However, this works only if this assumption is valid and in-an ETL setup which breaks this, this migration might load an inconsistent table schema causing similar issues.

I am torn about what to do.

I'd really consider the following:

select pg_get_constraintdef([constraint_oid]);
┌──────────────────────┐
│ pg_get_constraintdef │
├──────────────────────┤
│ PRIMARY KEY (a, b)   │
└──────────────────────┘

Then on the downstream we can:

execute(format('alter table %I.%I add constraint %s', _nsp, _relname, _constraintdef));

to recreate the same constraint.

🟠 Severity: HIGH

Supply Chain Risk: Personal fork replaces organizational dependency

Core PostgreSQL dependencies (postgres-replication and tokio-postgres) switched from MaterializeInc's organizational fork to a personal GitHub account (iambriccardo). While the account owner is an internal team member, personal forks lack organizational security controls, access management, and audit trails. The specific commit cannot be independently verified without access to the fork.

Recommendation: Move the fork to the Supabase organization GitHub account or use official releases. Organizational repositories provide better security through team access controls, audit logging, and review processes.
Helpful? Add 👍 / 👎