Re: Relation cache invalidation on replica

The reason of the problem is that invalidation messages are not delivered to replica after the end of concurrent create index.
Invalidation messages are included in xlog as part of transaction commit record.
Concurrent index create is split into three transaction, last of which is just performing inplace update of index tuple, marking it as valid and invalidating cache. But as far as this transaction is not assigned XID, no transaction record is created in WAL and send to replicas. As a result, replica doesn't receive this invalidation messages.

To fix the problem it is just enough to assign XID to transaction.
It can be done by adding GetCurrentTransactionId() call to the end of DefineIdnex function:

diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index 13b04e6..1024603 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -881,6 +881,12 @@ DefineIndex(Oid relationId,
     CacheInvalidateRelcacheByRelid(heaprelid.relId);
 
     /*
+     * Force WAL commit record to ensure that replica receives invalidation
+     * messages.
+     */
+    GetCurrentTransactionId();
+
+    /*
      * Last thing to do is release the session-level lock on the parent table.
      */
     UnlockRelationIdForSession(&heaprelid, ShareUpdateExclusiveLock);

Session opened on replica doesn't see concurrently created indexes at this time on master.

We have master and replica:

1. master: pgbench -i -s 10

2. replica:

explain (analyze,verbose) select * from pgbench_accounts where abalance = 1;

3. master:

ALTER INDEX pgbench_accounts_abalance_idx RENAME TO pgbench_accounts_abalance_idx_delme;
CREATE INDEX CONCURRENTLY pgbench_accounts_abalance_idx ON pgbench_accounts USING btree (abalance);
DROP INDEX pgbench_accounts_abalance_idx_delme;

4. at this time on replica:

explain (analyze,verbose) select * from pgbench_accounts where abalance = 1;
pgbench=# explain (analyze,verbose) select * from pgbench_accounts where abalance = 1;
QUERY PLAN 
------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using pgbench_accounts_abalance_idx on public.pgbench_accounts (cost=0.42..4.44 rows=1 width=97) (actual time=655.781..655.781 rows=0 loops=1)
Output: aid, bid, abalance, filler
Index Cond: (pgbench_accounts.abalance = 1)
Planning time: 9388.259 ms
Execution time: 655.900 ms
(5 rows)

pgbench=# explain (analyze,verbose) select * from pgbench_accounts where abalance = 1;
QUERY PLAN 
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using pgbench_accounts_abalance_idx_delme on public.pgbench_accounts (cost=0.42..4.44 rows=1 width=97) (actual time=0.014..0.014 rows=0 loops=1)
Output: aid, bid, abalance, filler
Index Cond: (pgbench_accounts.abalance = 1)
Planning time: 0.321 ms
Execution time: 0.049 ms
(5 rows)

pgbench=# explain (analyze,verbose) select * from pgbench_accounts where abalance = 1;
QUERY PLAN 
----------------------------------------------------------------------------------------------------------------------------
Seq Scan on public.pgbench_accounts (cost=0.00..28894.00 rows=1 width=97) (actual time=3060.451..3060.451 rows=0 loops=1)
Output: aid, bid, abalance, filler
Filter: (pgbench_accounts.abalance = 1)
Rows Removed by Filter: 1000000
Planning time: 0.087 ms
Execution time: 3060.484 ms
(6 rows)

pgbench=# \d+ pgbench_accounts
Table "public.pgbench_accounts"
Column | Type | Modifiers | Storage | Stats target | Description 
--------------------------------------------------+-----------
aid | integer | not null | plain | | 
bid | integer | | plain | | 
abalance | integer | | plain | | 
filler | character(84) | | extended | | 
Indexes:
"pgbench_accounts_pkey" PRIMARY KEY, btree (aid)
"pgbench_accounts_abalance_idx" btree (abalance)
Options: fillfactor=100

​New opened session successfully uses this index.

Tested with PostgreSQL 9.5.1.

---

Dmitry Vasilyev

Postgres Professional: http://www.postgrespro.com
Russian Postgres Company

-- 
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company