The following bug has been logged online:
Bug reference: 6307
Logged by: Maksym Boguk
Email address: maxim.boguk@gmail.com
PostgreSQL version: 9.1.1
Operating system: Linux
Description: intarray extention gin index does not work with Hot
standby
Details:
Intarray gin index:
(created as documented in:
http://www.postgresql.org/docs/9.1/interactive/intarray.html
"There is also a non-default GIN operator class gin__int_ops supporting the
same operators."
)
work incorrect on hot standby slaves.
Test case:
setup postgresql 9.1.1 hot standby replication.
Then on master DB:
postgres=# CREATE EXTENSION intarray;
CREATE EXTENSION
postgres=# create table test (id integer primary key, sections integer[]);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey"
for table "test"
CREATE TABLE
postgres=# insert into test select i,array[(random()*20)::integer] as
sections from (select * from generate_series(1,10000) as t(i)) as t;
INSERT 0 10000
postgres=# analyze test;
ANALYZE
postgres=# create index test_gin_intarray on test using gin(sections
gin__int_ops);
CREATE INDEX
postgres=# UPDATE test set sections='{10,1000}'::integer[] where id=1;
UPDATE 1
postgres=# SELECT * from test where sections && '{1000}';
id | sections
----+-----------
1 | {10,1000}
(1 row)
On replica db:
postgres=# SELECT * from test where sections && '{1000}';
id | sections
----+-----------
1 | {10,1000}
(1 row)
Still ok.
Now:
On master db:
postgres=# UPDATE test set sections='{10,2000}'::integer[] where id=3;
UPDATE 1
On master db:
postgres=# SELECT * from test where sections && '{2000}';
id | sections
----+-----------
3 | {10,2000}
(1 row)
On replica (replication not lagged):
postgres=# SELECT * from test where sections && '{2000}';
id | sections
----+----------
(0 rows)
Ooops.