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

On 2024-Mar-05, Dean Rasheed wrote:

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

I tried this, but it doesn't actually lead to a good place, because if
we allow deferrable PKs to identify rows, then they are not useful to
find the tuple to update when replicating. Consider the following case:

$node_publisher->safe_psql('postgres',
'create table deferred_pk (id int primary key initially deferred, hidden int, value text)');
$node_subscriber->safe_psql('postgres',
'create table deferred_pk (id int primary key initially deferred, hidden int, value text)');
$node_subscriber->safe_psql('postgres',
'alter subscription tap_sub refresh publication');

$node_publisher->safe_psql('postgres',
"insert into deferred_pk (id, hidden, value) values (1, 1, 'first')");
$node_publisher->wait_for_catchup('tap_sub');
$node_publisher->safe_psql('postgres',
qq{
begin;
insert into deferred_pk values (1, 2, 'conflicting');
update deferred_pk set value = value || ', updated' where id = 1 and hidden = 2;
update deferred_pk set id = 3, value = value || ', updated' where hidden = 2;
commit});
$node_publisher->wait_for_catchup('tap_sub');
my $pubdata = $node_publisher->safe_psql('postgres',
'select * from deferred_pk order by id');
my $subsdata = $node_subscriber->safe_psql('postgres',
'select * from deferred_pk order by id');
is($subsdata, $pubdata, "data is equal");

Here, the publisher's transaction first creates a new record with the
same PK, which only works because the PK is deferred; then we update its
payload column. When this is replicated, the row is identified by the
PK ... but replication actually updates the other row, because it's
found first:

# Failed test 'data is equal'
# at t/003_constraints.pl line 163.
# got: '1|2|conflicting
# 3|2|conflicting, updated, updated'
# expected: '1|1|first
# 3|2|conflicting, updated, updated'

Actually, is that what happened here? I'm not sure, but clearly this is
bogus.

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.

--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/