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

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

From
"Maksym Boguk"
Date:
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.

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

From
Simon Riggs
Date:
On Thu, Nov 24, 2011 at 11:12 PM, Maksym Boguk <maxim.boguk@gmail.com> wrot=
e:

> postgres=3D# SELECT * from test where sections && '{2000}';
> =A0id | 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. Tha=
nks

--=20
=A0Simon Riggs=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 http:/=
/www.2ndQuadrant.com/
=A0PostgreSQL Development, 24x7 Support, Training & Services

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

From
Maxim Boguk
Date:
On Fri, Nov 25, 2011 at 11:17 PM, Simon Riggs <simon@2ndquadrant.com> wrote:

> On Thu, Nov 24, 2011 at 11:12 PM, Maksym Boguk <maxim.boguk@gmail.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.

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

From
Tom Lane
Date:
Maxim Boguk <maxim.boguk@gmail.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

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

From
Simon Riggs
Date:
On Fri, Nov 25, 2011 at 6:33 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Maxim Boguk <maxim.boguk@gmail.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. =A0Will fix.

Great detective work Tom as ever, much appreciated.

--=20
=A0Simon Riggs=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 http:/=
/www.2ndQuadrant.com/
=A0PostgreSQL Development, 24x7 Support, Training & Services

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

From
Maxim Boguk
Date:
On Mon, Nov 28, 2011 at 6:02 PM, Simon Riggs <simon@2ndquadrant.com> wrote:

> On Fri, Nov 25, 2011 at 6:33 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > Maxim Boguk <maxim.boguk@gmail.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.

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

From
Tom Lane
Date:
Maxim Boguk <maxim.boguk@gmail.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