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

From: Alvaro Herrera <[email-masked]>
Date: Tue, 5 Mar 2024 18:18:45 +0100

On 2024-Mar-05, Dean Rasheed wrote:

> Looking at the other places that call RelationGetIndexAttrBitmap()
> with INDEX_ATTR_BITMAP_PRIMARY_KEY, they all appear to want to include
> deferrable PKs, since they are relying on the result to see which
> columns are not nullable.

Hmm, I find this pretty surprising, but you are right.  Somehow I had
the idea that INDEX_ATTR_BITMAP_PRIMARY_KEY was used for planning
activities so I didn't want to modify its behavior ... but clearly
that's not at all the case.  It's only used for DDL, and one check in
logical replication.

> So there are other bugs here. For example:
> 
> CREATE TABLE foo (id int PRIMARY KEY DEFERRABLE, val text);
> CREATE TABLE bar (LIKE foo);
> 
> now fails to mark bar.id as not nullable, whereas prior to
> b0e96f311985 it would have been.

Fun.  (Thankfully, easy to fix. But I'll add this as a test too.)

> So I think RelationGetIndexAttrBitmap() should include deferrable PKs,

Yeah, I'll go make it so.  I think I'll add a test for the case that
changes behavior in logical replication first (which is that the target
relation of logical replication is currently not marked as updatable,
when its PK is deferrable).

> but not all the changes made to RelationGetIndexList() by b0e96f311985
> need reverting.

I'll give this a look too.

-- 
Álvaro Herrera               48°01'N 7°57'E  —  https://www.EnterpriseDB.com/
"Once again, thank you and all of the developers for your hard work on
PostgreSQL.  This is by far the most pleasant management experience of
any database I've worked on."                             (Dan Harris)
http://archives.postgresql.org/pgsql-performance/2006-04/msg00247.php