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> 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@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. 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@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