Case: Partition Data UPDATE Failure on 2026-01-01

Table of Contents

Symptoms
#

On December 30, business errors were reported — data could not be updated:

ERROR:  55000: cannot update table "tablzl_202601" because it does not have a replica identity and publishes updates
HINT:  To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.
LOCATION:  CheckCmdReplicaIdentity, execReplication.c:575

Temporary Recovery
#

The error message was clear: no replica identity. The table was a partitioned table and a 2026 partition, so I immediately suspected the new partition lacked a primary key. (A new table’s replica identity defaults to default, which only uses a primary key as the replica identity. Without a primary key, updates are impossible.)

Further investigation revealed: the parent table had no primary key or indexes, child partitions from 2025 and earlier had both primary keys and indexes, but 2026 and later child partitions had neither — and all child partitions were published. Roughly:

p_parent -- no PK, no indexes
p_child_202511 -- has PK, has indexes, published
p_child_202512 -- has PK, has indexes, published
p_child_202601 -- no PK, no indexes, published
p_child_202602 -- no PK, no indexes, published

Since the parent table had nothing, a partition of child would also have nothing — you must manually create the primary key and indexes for each child partition. So the new partition creation was problematic; the old partitions presumably had them added after creation.

Additionally, publishing partitioned tables via the parent was only supported starting from PG13. Previously, you couldn’t publish via the parent — only via child tables. This database was on PG11.

Allow partitioned tables to be logically replicated via publications (Amit Langote) § §
Previously, partitions had to be replicated individually. Now a partitioned table can be published explicitly, causing all its partitions to be published automatically. Addition/removal of a partition causes it to be likewise added to or removed from the publication. The CREATE PUBLICATION option publish_via_partition_root controls whether changes to partitions are published as their own changes or their parent’s.

After the initial diagnosis and given the urgency, there were three ways to temporarily resolve:

Add primary keys to the 2026 partitions
Set replica identity full on the 2026 partitions
Remove the 2026 partitions from the publication

Since recovery time was about the same for all options, we chose adding primary keys — the lowest operational cost — to at least stop the business errors.

Root Cause Analysis
#

The issue seems clear: “no replica identity + published + no primary key” prevents updates. But several questions still needed answers.

Question 1: Why does the UPDATE fail even though there’s no 202601 data at all (the new partition has zero rows)?
#

The SQL text was:

UPDATE tablzl_202601
         SET idid = $1,...
            date_updated = now()
            WHERE mykey = $4

The partition key for tablzl_202601 is created_date. The SQL WHERE clause didn’t include the partition key, so when attempting to update the 202601 partition, it found no primary key and errored out.

As for whether row existence or replica identity is checked first, we can see from ExecSimpleRelationUpdate. This function has changed very little across PG versions:

/*
 * Find the searchslot tuple and update it with data in the slot,
 * update the indexes, and execute any constraints and per-row triggers.
 *
 * Caller is responsible for opening the indexes.
 */
void
ExecSimpleRelationUpdate(EState *estate, EPQState *epqstate,
					 TupleTableSlot *searchslot, TupleTableSlot *slot)
{
...
	CheckCmdReplicaIdentity(rel, CMD_UPDATE);  // check replica identity

	/* BEFORE ROW UPDATE Triggers */
	if (resultRelInfo->ri_TrigDesc &&
		resultRelInfo->ri_TrigDesc->trig_update_before_row)
	{
		slot = ExecBRUpdateTriggers(estate, epqstate, resultRelInfo,
									&searchslot->tts_tuple->t_self,
									NULL, slot);

		if (slot == NULL)		/* "do nothing" */
			skip_tuple = true;
	}

	if (!skip_tuple)
	{
		List	   *recheckIndexes = NIL;

		/* Check the constraints of the tuple */
		if (rel->rd_att->constr)
			ExecConstraints(resultRelInfo, slot, estate);
		if (resultRelInfo->ri_PartitionCheck)
			ExecPartitionCheck(resultRelInfo, slot, estate, true);

		/* Materialize slot into a tuple that we can scribble upon. */
		tuple = ExecMaterializeSlot(slot);

		/* OK, update the tuple and index entries for it */
		simple_heap_update(rel, &searchslot->tts_tuple->t_self,
						   slot->tts_tuple);

		if (resultRelInfo->ri_NumIndices > 0 &&
			!HeapTupleIsHeapOnly(slot->tts_tuple))
			recheckIndexes = ExecInsertIndexTuples(slot, &(tuple->t_self),
												   estate, false, NULL,
												   NIL);

		/* AFTER ROW UPDATE Triggers */
		ExecARUpdateTriggers(estate, resultRelInfo,
							 &searchslot->tts_tuple->t_self,
							 NULL, tuple, recheckIndexes, NULL);

		list_free(recheckIndexes);
	}
}

ExecSimpleRelationUpdate flow:

Check replica identity
BEFORE ROW UPDATE triggers
Check constraints (both non-partition and partition constraints)
Update the row
Insert index entries
AFTER ROW UPDATE triggers

So PG’s logic checks replica identity first, before row updates and everything else.

Even though the SQL didn’t include the partition key, would adding it trigger partition pruning? The answer is: maybe not.

Partition pruning improvements across versions:

PG10 introduced declarative partitioning. There was no enable_partition_pruning parameter; pruning was done at planning time via constraint_exclusion. So PG10 had no query-execution-time pruning.
PG11 added runtime partition pruning: Allow partition elimination during query execution (David Rowley, Beena Emerson). But it only supports pruning with bound variables, not non-immutable functions (including now()).
PG14 added final pruning: This wins in UPDATEs on partitioned tables when only some of the partitions will actually receive updates. i.e., supports pruning with non-immutable functions.

Since PG11 doesn’t support now() pruning, adding a now() condition to the business SQL wouldn’t trigger pruning — the error would still occur. However, if the business passed a bound variable, pruning would trigger and the error wouldn’t appear. Note: “the error wouldn’t appear” means updating 202512 data wouldn’t error out on the 202601 partition; updating 202601 data would still fail regardless.

Question 2: The partition was created on 2025-12-26, so why was the problem only discovered on December 30?
#

This is even simpler: “no replica identity + published + no primary key” is an AND condition.

Although the new partitions were created early, they were published on the evening of December 29 at 20:47:

cat  postgresql-12-29.csv.bak |grep "alter publication"
2025-12-29 20:48:07.730 CST,"userlzlreplication","lzldb",xxx"statement: alter publication publzl add table ""public"".""tablzl_202601"", ""public"".""tablzl_202602"",...

The first error appeared on December 29 at 22:26, about 1.5 hours later:

 cat  postgresql-12-29.csv.bak |grep "REPLICA IDENTITY"
2025-12-29 22:26:01.404 CST,"userlzlreplication","lzldb",375121,xxx,"cannot update table ""tablzl_202601"" because it does not have a replica identity and publishes updates",,"To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.",,,,"UPDATE tablzl

Summary
#

Root cause overview: The parent table had no primary key, so partition of child partitions naturally also had none. Old child partitions had their primary keys added manually; new child partitions did not, resulting in the 202601 partition lacking a primary key. Logical replication relies on the primary key (default replica identity) for synchronization. Without replica identity, changes can’t be sent downstream, and UPDATE/DELETE statements on published tables cannot execute. In PG11, an UPDATE SQL that does include the partition key condition may still visit the new partition.

A stroke of luck: Due to various factors, this problem was discovered early in this particular database. We had a one-day buffer on December 31 to fix all database instances, ensuring at least that January 1 new partition data updates wouldn’t error out. Otherwise, on January 1, 2026, multiple systems would have likely gone up in flames.

Temporary measures (pick one):

Add primary keys to 2026 partitions
Set replica identity full on 2026 partitions
Remove 2026 partitions from the publication

For replication pipeline optimization:

Tables without primary keys should be detected proactively, otherwise publishing them could cause business-side UPDATE failures

For partition management strategy:

PG’s partitioned tables are highly flexible, and developers generally don’t know how to create partitions correctly. Combined with significant new partitioning features across roughly PG10-15, and the lack of INTERVAL partitioning in PG, partitioned tables can end up a mess. Standardized management of partitioned tables is thus critical. For partition table features and operational tips, see: PostgreSQL Partitioned Tables

As for management tools, I’ll skip those.

Management goals:

Use the parent table structure as the standard: the parent table, being developer-facing, should have primary keys, indexes, and replica identity (unless the PG version doesn’t support it)
Keep parent and child tables consistent; use partition of to create new partitions (yes, I don’t recommend attach)
Keep child tables consistent with each other
Create new partitions in advance; partition data volumes should not be excessive
Default partitions are not recommended; if created, their writes must be monitored
Frequently accessed tables must have partition keys in their SQL queries and use partition pruning; otherwise, convert them to regular tables