Обсуждение: BUG #15597: possible bug in amcheck/amcheck_next (or corrupted index?)
BUG #15597: possible bug in amcheck/amcheck_next (or corrupted index?)
От
PG Bug reporting form
Дата:
The following bug has been logged on the website: Bug reference: 15597 Logged by: Andreas Kunert Email address: kunert@cms.hu-berlin.de PostgreSQL version: 11.1 Operating system: Ubuntu 16.04.5 LTS Description: Hello, I observed the following behavior in Postgres 9.6.?/10.6/11.1 running under Debian/Debian/Ubuntu on three different servers: CREATE TABLE foo ( a integer, b character(255), c character(255), d character(255), e character(25), g date, h date, i date, j character(255), k character(255), m character(10), n character(255), o character(1), p character(4), q character(1), r integer, t character(255), u character(50), v character(100) ); CREATE INDEX i_foo ON foo USING btree (b COLLATE pg_catalog."default", c COLLATE pg_catalog."default"); select oid, relname from pg_class where relname like 'i_foo'; -- result is e.g. 12345 select bt_index_check(12345, true); -- result: everything ok INSERT INTO foo(a,b,c,d,e,g,h,i,j,k,m,n,o,p,q,r,t,u,v) VALUES ('1', 'b', 'c', 'd', 'e', '2000-01-01', '2000-01-01', NULL, 'j', 'k', 'm', 'n', 'o', 'p', 'q', '2', 't', 'u', 'v'); -- result: ok select bt_index_check(12345, true); -- result: ERROR: heap tuple (0,1) from table "foo" lacks matching index tuple within index "i_foo" reindex table foo; select bt_index_check(12345, true); -- result: everything ok again, but after adding another row: INSERT INTO foo(a,b,c,d,e,g,h,i,j,k,m,n,o,p,q,r,t,u,v) VALUES ('1', 'b', 'c', 'd', 'e', '2000-01-01', '2000-01-01', NULL, 'j', 'k', 'm', 'n', 'o', 'p', 'q', '2', 't', 'u', 'v'); select bt_index_check(12345, true); -- result: ERROR: heap tuple (0,2) from table "foo" lacks matching index tuple within index "i_foo" Despite the error message I suspect the index being ok since I can find the aforementioned tuples by using it: explain select * from foo where b='b' and c='c' -- result: Index Scan using i_foo on foo... select * from foo where b='b' and c='c' -- result: 2 rows I tried to simplify the example table as much as possible - if I remove more columns or reduce some of the char(n) lengths, the error does not appear. Moreover I hope you can reproduce the behavior. Regards, Andreas
On Thu, Jan 17, 2019 at 6:00 AM PG Bug reporting form <noreply@postgresql.org> wrote: > select bt_index_check(12345, true); > -- result: ERROR: heap tuple (0,2) from table "foo" lacks matching index > tuple within index "i_foo" > > Despite the error message I suspect the index being ok since I can find the > aforementioned tuples by using it: > > explain select * from foo where b='b' and c='c' > -- result: Index Scan using i_foo on foo... > select * from foo where b='b' and c='c' > -- result: 2 rows This looks like the same bug that I'm currently working through here: https://postgr.es/m/CAH2-WznrVd9ie+TTJ45nDT+v2nUt6YJwQrT9SebCdQKtAvfPZw@mail.gmail.com I have a draft patch that fixes this, but I haven't quite decided if I want to commit to the approach I've taken to normalizing TOASTed tuples. I will definitely fix the externally maintained version (amcheck_next) once this is settled. Thanks for the report. -- Peter Geoghegan
On Fri, Jan 25, 2019 at 9:57 AM Peter Geoghegan <pg@bowt.ie> wrote: > I have a draft patch that fixes this, but I haven't quite decided if I > want to commit to the approach I've taken to normalizing TOASTed > tuples. I will definitely fix the externally maintained version > (amcheck_next) once this is settled. Thanks for the report. I pushed a fix for this to contrib/amcheck, and to the externally maintained amcheck_next codebase. There will be new set of point releases of Postgres on February 14th, 2019. I'll see to cutting a new release of amcheck_next shortly as well. Thanks for the report! -- Peter Geoghegan