pg_squeeze 1.9 on PostgreSQL 16.5 repeatedly causes missing btree entries for composite non-unique index on small table

[xydoublez]

Environment

• PostgreSQL 16.5 (data_checksums = on)
• pg_squeeze 1.9 (latest)
• Table: comm.lis_dict_lab_vs_item (small dictionary table, low row count, frequent lookups)
• Indexes:
  1. PRIMARY KEY (report_item_id) unique btree (remains valid)
  2. Secondary composite btree (lab_id, is_deleted) → consistently becomes invalid

Reproduction (repeatable)

1. CREATE EXTENSION amcheck;
2. Verify index clean:
   SELECT bt_index_check('comm.idx_comm_lis_dict_lab_vs_item_lab_id_deleted_union'::regclass, true);
3. Run squeeze:
   SELECT squeeze.squeeze_table('comm','lis_dict_lab_vs_item', NULL, NULL, NULL);
   (Also reproduced with recreate_index := true, and inside/outside explicit transaction; also with ACCESS EXCLUSIVE pre-lock.)
4. Re-check:
   SELECT bt_index_check('comm.idx_comm_lis_dict_lab_vs_item_lab_id_deleted_union'::regclass, true);
5. Error:
   ERROR: heap tuple (0,1) from table "lis_dict_lab_vs_item" lacks matching index tuple within index "idx_comm_lis_dict_lab_vs_item_lab_id_deleted_union"

Behavior Characteristics

• Always affects the composite non-unique index; primary key stays valid.
• REINDEX INDEX CONCURRENTLY fixes immediately.
• Large tables processed by pg_squeeze do NOT show the problem.
• Occurs even during quiescent period (writes paused) and with lock_timeout=0 (unlimited wait).
• bt_index_parent_check reproduces; bt_metap shows normal metadata.

Why small table + composite index?
Hypothesis:

1. Fast rewrite of small table narrows time between slot creation and index build; tuples committed just before slot start are missed (shadow table lacks them; index built without them).
2. apply_changes() may insert replayed tuples via heap_insert/simple_heap_insert without invoking table_insert or index_insert, so secondary non-unique index misses entries while unique PK path gets handled differently.
3. Composite (lab_id, is_deleted) has frequent updates of is_deleted causing UPDATE replay path; if UPDATE handling skips index maintenance for non-unique indexes, gap appears.
4. Large tables: longer rewrite duration lets logical decoding capture more WAL; window bug not triggered.

Diagnostics Used
SELECT bt_index_parent_check('comm.idx_comm_lis_dict_lab_vs_item_lab_id_deleted_union'::regclass);
SELECT * FROM bt_metap('comm.idx_comm_lis_dict_lab_vs_item_lab_id_deleted_union');
SELECT count(*) FROM comm.lis_dict_lab_vs_item;
REINDEX INDEX CONCURRENTLY comm.idx_comm_lis_dict_lab_vs_item_lab_id_deleted_union; → restores integrity.

Requested Upstream Actions

• Confirm insertion/update replay uses table_insert() (PG16 table AM) or explicit index_insert for all indexes.
• Review start LSN acquisition relative to lock acquisition for small tables.
• Provide guidance or patch to ensure composite non-unique indexes are updated during WAL change replay.

Potential Patch Direction (pseudo)

/* In change replay: replace raw heap_* calls */
- heap_insert(rel, tuple);
+ table_insert(rel, tuple, GetCurrentCommandId(true), 0, NULL); /* ensures index maintenance */

[ahouska]

Thanks for the report. Unfortunately I could not reproduce the problem so far. Can you please try to provide a self-contained test case? Also, please check the server log for ERRORs other than those raised by amcheck.

(Also reproduced with recreate_index := true, and inside/outside explicit transaction; also with ACCESS EXCLUSIVE pre-lock.)

What does recreate_index refer to? I don't see such a variable in PostgreSQL documentation.

1. Fast rewrite of small table narrows time between slot creation and index build; tuples committed just before slot start are missed (shadow table lacks them; index built without them).

Tuples committed before the slot start should be visible to the initial load.

[xydoublez]

Hi @ahouska,
Thank you for looking into this. I understand it’s difficult to reproduce, and I apologize that the issue only occurs consistently in my specific environment.

[ahouska]

No need to apologize. I wonder if some other activity might interfere with pg_squeeze (it should not, but ...).

I'm curious if only some items are missing in the index or if it is completely empty. Can you please install the pageinspect extension and run this query?

select count(*) from bt_page_items(get_raw_page('comm.idx_comm_lis_dict_lab_vs_item_lab_id_deleted_union', 'main', 1));

[xydoublez]

chis=# select bt_index_check('comm.idx_comm_lis_dict_lab_vs_item_lab_id_deleted_union2'::regclass, true);
ERROR:  heap tuple (0,1) from table "lis_dict_lab_vs_item" lacks matching index tuple within index "idx_comm_lis_dict_lab_vs_item_lab_id_deleted_union2"
HINT:  Retrying verification using the function bt_index_parent_check() might provide a more specific error.
chis=# select count(*) from bt_page_items(get_raw_page('comm.idx_comm_lis_dict_lab_vs_item_lab_id_deleted_union2', 'main', 1));
 count 
-------
   185
(1 row)