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

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.

On Thu, Nov 24, 2011 at 11:12 PM, Maksym Boguk <maxim(dot)boguk(at)gmail(dot)com> wrote:

> postgres=# SELECT * from test where sections && '{2000}';
>  id | sections
> ----+----------
> (0 rows)
>
> Ooops.

Can you see if this is just intarray or if there are other failing cases?

It would be good to get more info on this before I start investigating. Thanks

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

On Fri, Nov 25, 2011 at 11:17 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:

> On Thu, Nov 24, 2011 at 11:12 PM, Maksym Boguk <maxim(dot)boguk(at)gmail(dot)com>
> wrote:
>
> > postgres=# SELECT * from test where sections && '{2000}';
> > id | sections
> > ----+----------
> > (0 rows)
> >
> > Ooops.
>
> Can you see if this is just intarray or if there are other failing cases?
>
> It would be good to get more info on this before I start investigating.
> Thanks
>

I know GIST on intarray[] do not have that problem.
Very likely the problem is limited to intarray[] GIN indexes only
(but I going to test some other not-well known GIN indexes tomorrow).

Broken FTS indexes on Hot Standby should be known years before.
And I never heard such reports.

Maxim Boguk <maxim(dot)boguk(at)gmail(dot)com> writes:
> I know GIST on intarray[] do not have that problem.
> Very likely the problem is limited to intarray[] GIN indexes only
> (but I going to test some other not-well known GIN indexes tomorrow).

> Broken FTS indexes on Hot Standby should be known years before.

You might think that, but you'd be wrong :-(. ginRedoUpdateMetapage
is failing to restore the contents of the pending-list correctly,
which means this is broken for all types of GIN indexes. Will fix.

regards, tom lane

On Fri, Nov 25, 2011 at 6:33 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Maxim Boguk <maxim(dot)boguk(at)gmail(dot)com> writes:
>> I know GIST on intarray[] do not have that problem.
>> Very likely the problem is limited to intarray[] GIN indexes only
>> (but I going to test some other not-well known GIN indexes tomorrow).
>
>> Broken FTS indexes on Hot Standby should be known years before.
>
> You might think that, but you'd be wrong :-(.

Yes, that did sound ominous.

> ginRedoUpdateMetapage
> is failing to restore the contents of the pending-list correctly,
> which means this is broken for all types of GIN indexes.  Will fix.

Great detective work Tom as ever, much appreciated.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

On Mon, Nov 28, 2011 at 6:02 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:

> On Fri, Nov 25, 2011 at 6:33 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > Maxim Boguk <maxim(dot)boguk(at)gmail(dot)com> writes:
> >> I know GIST on intarray[] do not have that problem.
> >> Very likely the problem is limited to intarray[] GIN indexes only
> >> (but I going to test some other not-well known GIN indexes tomorrow).
> >
> >> Broken FTS indexes on Hot Standby should be known years before.
> >
> > You might think that, but you'd be wrong :-(.
>
> Yes, that did sound ominous.
>
> > ginRedoUpdateMetapage
> > is failing to restore the contents of the pending-list correctly,
> > which means this is broken for all types of GIN indexes. Will fix.
>
> Great detective work Tom as ever, much appreciated.
>
>
Thank you very much.
Is that fix will be included to the next minor versions releases?
(especially into 9.1.2)?

--
Maxim Boguk
Senior Postgresql DBA.

Maxim Boguk <maxim(dot)boguk(at)gmail(dot)com> writes:
> Is that fix will be included to the next minor versions releases?

Yes, it's in already:
http://git.postgresql.org/gitweb/?p=postgresql.git

regards, tom lane