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);

On 26.02.2016 15:41, Васильев Дмитрий wrote:
> 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