Thread: 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.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.
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
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.
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
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
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.
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