Re: Dump-restore loosing 'attnotnull' bit for DEFERRABLE PRIMARY KEY column(s).

On 2024-Mar-07, Dean Rasheed wrote:

> On Thu, 7 Mar 2024 at 13:00, Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> wrote:
> >
> > So I think the original developers of REPLICA IDENTITY had the right
> > idea here (commit 07cacba983ef), and we mustn't change this aspect,
> > because it'll lead to data corruption in replication. Using a deferred
> > PK for DDL considerations seems OK, but it seems certain that for actual
> > data replication it's going to be a disaster.
>
> Yes, that makes sense. If I understand correctly though, the
> replication code uses relation->rd_replidindex (not
> relation->rd_pkindex), although sometimes it's the same thing. So can
> we get away with making sure that RelationGetIndexList() doesn't set
> relation->rd_replidindex to a deferrable PK, while still allowing
> relation->rd_pkindex to be one?

Well, not really, because the logical replication code for some reason
uses GetRelationIdentityOrPK(), which falls back to rd->pk_index (via
RelationGetPrimaryKeyIndex) if rd_replindex is not set.

Maybe we can add a flag RelationData->rd_ispkdeferred, so that
RelationGetPrimaryKeyIndex returned InvalidOid for deferrable PKs; then
logical replication would continue to not know about this PK, which
perhaps is what we want. I'll do some testing with this.

--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/