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
