BUG #6307: intarray extention gin index does not work with Hot standby

From: "Maksym Boguk" <maxim(dot)boguk(at)gmail(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #6307: intarray extention gin index does not work with Hot standby
Date: 2011-11-24 23:12:24
Message-ID: 201111242312.pAONCO9p013564@wwwmaster.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs


The following bug has been logged online:

Bug reference: 6307
Logged by: Maksym Boguk
Email address: maxim(dot)boguk(at)gmail(dot)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.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Alvaro Herrera 2011-11-25 00:43:21 Re: libpq in android
Previous Message Peter Eisentraut 2011-11-24 22:01:04 Re: libpq in android