ERROR: data for dropped column persists after re-adding the column manually

Open
#305 0 comments 0 reactions 1 assignee View on GitHub

@sharmay is already working on this.

Since Jan 1, 2026.

Assessment

This issue has not been assessed yet.

Description

Hello, I am testing various conflict scenarios on an active-active setup. I encountered a situation involving a DROP COLUMN operation where the replication broke (as expected), but I was unable to recover it simply by re-adding the column.
Steps to Reproduce:

  1. I have a table test_table (id int primary key, data text) synced on both Node 1 and Node 2.
  2. On Node 1: I dropped the column: ALTER TABLE test_table DROP COLUMN data;
  3. On Node 2: Immediately after (while replication was active), I inserted data targeting that dropped column: INSERT INTO test_table (id, data) VALUES (1, 'val');
  4. Result: The apply worker on Node 1 failed with the expected error:
    ERROR: data for dropped column
    CONTEXT: apply INSERT from remote relation ...
  5. Attempted Fix: To recover the replication without skipping the transaction, I manually added the column back on Node 1:
    ALTER TABLE test_table ADD COLUMN data text;
  6. Observation: Even after adding the column back with the exact same name and type, the Apply Worker continued to fail with the same data for dropped column error. It seems the worker did not recognize the new column because the attnum (attribute number) changed, or the worker was holding onto a stale schema cache.
    Questions:
  7. Is this expected behavior that the Apply Worker does not automatically refresh its relation map when a column is re-added with the same name during a retry loop?
  8. I noticed that ordinal_position changed in information_schema after re-adding the column. Does pgactive (or logical replication in general) rely on column names or internal attribute IDs (attnum) during the apply phase?
  9. Is the only solution in this scenario to manually terminate/restart the Apply Worker process to force a schema cache refresh?
    Any insights or best practices for recovering from this "accidental drop" scenario would be appreciated.
    Thanks!

Environment:

  • PostgreSQL Version: 17.6
  • Extension: pgactive (latest)
  • Setup: Active-Active replication between 2 nodes.
Dominant language
C
Stars
1.1k
Forks
38
Avg merge
4d 14h
Merged PRs (30d)
1

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from aws/pgactive

All issues in aws/pgactive

Similar issues

More C issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.